☰
×
阿里开源数据库连接池Druid
  • 2023-03-02 12:20:25
  • 5044 热度
  • 0 评论

使用Druid数据库连接池，然后实用SpringMVC和JSP来从数据库读取一些数据出来。

我们要引用那些Lib包，这里实用Maven配置，注意JSP中使用到了JSTL，我直接在Eclipse里面引用的。

如果大家有哪些Maven的引用不知道怎么写，可以从http://mvnrepository.com 查看相应的Maven配置。

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>test4</groupId>
	<artifactId>test4</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>
	<name>test4</name>
	<description />
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>
	<dependencies>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.0.17</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.26</version>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>3.0</version>
		</dependency>
		<dependency>
			<groupId>commons-collections</groupId>
			<artifactId>commons-collections</artifactId>
			<version>3.2.1</version>
		</dependency>
		<dependency>
			<groupId>commons-fileupload</groupId>
			<artifactId>commons-fileupload</artifactId>
			<version>1.3.1</version>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>
		<!-- Spring Lib -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aspects</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aop</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
	</dependencies>
</project>


引用好Jar包后，在web.xml中配置启用Spring和SpringMVC。

因为我们还要查看监控SQL的执行，所以还要配置一个Druid的Servlet。

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://java.sun.com/xml/ns/javaee"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
	id="WebApp_ID" version="2.5">
	<display-name>zz</display-name>
	<welcome-file-list>
		<welcome-file>index.jsp</welcome-file>
	</welcome-file-list>
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>classpath*:/applicationContext.xml</param-value>
	</context-param>
	<context-param>
		<param-name>log4jConfigLocation</param-name>
		<param-value>/WEB-INF/classes/log4j.properties</param-value>
	</context-param>
	<listener>
		<listener-class>org.springframework.web.util.Log4jConfigListener</listener-class>
	</listener>
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>
	<listener>
		<listener-class>org.springframework.web.util.IntrospectorCleanupListener</listener-class>
	</listener>
	<filter>
		<filter-name>HiddenHttpMethodFilter</filter-name>
		<filter-class>org.springframework.web.filter.HiddenHttpMethodFilter</filter-class>
	</filter>
	<filter-mapping>
		<filter-name>HiddenHttpMethodFilter</filter-name>
		<servlet-name>springServlet</servlet-name>
	</filter-mapping>
	<filter>
		<filter-name>CharacterEncodingFilter</filter-name>
		<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
		<init-param>
			<param-name>encoding</param-name>
			<param-value>UTF-8</param-value>
		</init-param>
		<init-param>
			<param-name>forceEncoding</param-name>
			<param-value>true</param-value>
		</init-param>
	</filter>
	<filter-mapping>
		<filter-name>CharacterEncodingFilter</filter-name>
		<url-pattern>/*</url-pattern>
		<dispatcher>REQUEST</dispatcher>
		<dispatcher>FORWARD</dispatcher>
	</filter-mapping>
	<servlet>
		<servlet-name>springServlet</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>classpath*:/spring-mvc.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>
	<servlet-mapping>
		<servlet-name>springServlet</servlet-name>
		<url-pattern>/</url-pattern>
	</servlet-mapping>
	<servlet>
		<servlet-name>DruidStatView</servlet-name>
		<servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>DruidStatView</servlet-name>
		<url-pattern>/druid/*</url-pattern>
	</servlet-mapping>
</web-app>


Spring的主配置文件applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:jpa="http://www.springframework.org/schema/data/jpa" xmlns:util="http://www.springframework.org/schema/util"
	xsi:schemaLocation="http://www.springframework.org/schema/beans 
		http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
		http://www.springframework.org/schema/aop 
		http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
		http://www.springframework.org/schema/context 
		http://www.springframework.org/schema/context/spring-context-3.2.xsd
		http://www.springframework.org/schema/jdbc 
		http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
		http://www.springframework.org/schema/tx 
		http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
		http://www.springframework.org/schema/data/jpa 
		http://www.springframework.org/schema/data/jpa/spring-jpa.xsd 
		http://www.springframework.org/schema/util 
		http://www.springframework.org/schema/util/spring-util.xsd"
	default-lazy-init="false">
	<context:annotation-config />
	<context:component-scan base-package="com.test4">
		<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller" />
		<context:exclude-filter type="annotation" expression="org.springframework.web.bind.annotation.ControllerAdvice" />
	</context:component-scan>
	<bean id="stat-filter" class="com.alibaba.druid.filter.stat.StatFilter">
		<!-- 配置SQL慢的标准，执行时间超过slowSqlMillis的就是慢，缺省值为3000，也就是3秒。
		同<property name="connectionProperties" value="druid.stat.slowSqlMillis=5000" /> -->
		<property name="slowSqlMillis" value="10000" />
		<property name="logSlowSql" value="true" />
		<!-- 合并参数相同的SQL 同  <property name="filters" value="mergeStat" /> 
		或者 <property name="connectionProperties" value="druid.stat.mergeSql=true" /> -->
		<property name="mergeSql" value="true" />
	</bean>
	<util:properties id="coreConfiguration" location="classpath:dbconfig.properties" />
	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
		<property name="url" value="#{coreConfiguration['jdbc.url']}" />
		<property name="username" value="#{coreConfiguration['jdbc.username']}" />
		<property name="password" value="#{coreConfiguration['jdbc.password']}" />
		<property name="driverClassName" value="#{coreConfiguration['jdbc.driverClassName']}" />
		<!-- 允许druid监控 <property name="filters" value="#{coreConfiguration['jdbc.filters']}" /> -->
		<property name="filters" value="log4j" />
		<property name="proxyFilters">
			<list>
				<ref bean="stat-filter" />
			</list>
		</property>
		<property name="initialSize" value="#{coreConfiguration['jdbc.initialSize']}" />
		<!-- 多个数据源时，配置该属性，标记实用通用的配置模版
		<property name="useGlobalDataSourceStat" value="true" /> -->
	</bean>
	<!-- jdbcTemplate -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource">
			<ref bean="dataSource" />
		</property>
	</bean>
</beans>

除数据源配置内容有所不同外，都和普通项目无差别。

和很多配置一样，同一个配置总是有很多配置方式，对于这些方式了解即可，千万别搞特殊，最后连自己都不知道什么意思了。

数据库连接信息配置文件dbconfig.properties

jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.username=root
jdbc.password=root
jdbc.filters=stat,log4j
jdbc.initialSize=5


SpringMVC配置文件spring-mvc.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd">
	<!-- 自动扫描且只扫描@Controller -->
	<context:component-scan base-package="com.test4" use-default-filters="false">
		<context:include-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
		<context:include-filter type="annotation" expression="org.springframework.web.bind.annotation.ControllerAdvice"/>
	</context:component-scan>
	<mvc:annotation-driven>
		<mvc:message-converters register-defaults="true">
			<!-- 将StringHttpMessageConverter的默认编码设为UTF-8 -->
			<bean class="org.springframework.http.converter.StringHttpMessageConverter">
		    	<constructor-arg value="UTF-8" />
			</bean>
  		</mvc:message-converters>
	</mvc:annotation-driven>
	<bean id="multipartResolver"  
	    class="org.springframework.web.multipart.commons.CommonsMultipartResolver">  
	    <!-- 设置上传文件的最大尺寸为1MB -->  
	    <property name="maxUploadSize">  
	        <value>102400000</value>  
	    </property>
	    <property name="defaultEncoding">  
            <value>UTF-8</value>  
        </property>  
	</bean>
	<!-- SpringMVC在超出上传文件限制时，会抛出org.springframework.web.multipart.MaxUploadSizeExceededException -->  
    <!-- 该异常是SpringMVC在检查上传的文件信息时抛出来的，而且此时还没有进入到Controller方法中 -->  
    <bean id="exceptionResolver"  
        class="org.springframework.web.servlet.handler.SimpleMappingExceptionResolver">  
        <property name="exceptionMappings">  
            <props>  
                <!-- 遇到MaxUploadSizeExceededException异常时，自动跳转到/WEB-INF/jsp/error_fileupload.jsp页面 -->  
                <prop key="org.springframework.web.multipart.MaxUploadSizeExceededException">/error/error_fileupload</prop>  
            </props>  
        </property>  
    </bean> 
	<!-- 定义JSP文件的位置 --> 
	<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix" value="/jsp/"/>
		<property name="suffix" value=".jsp"/>
	</bean>	
	<!-- 容器默认的DefaultServletHandler处理 所有静态内容与无RequestMapping处理的URL-->	
	<mvc:default-servlet-handler/>
	<mvc:view-controller path="/" view-name="redirect:/index.jsp"/> 
</beans>

所有项目配置都是大同小异的，不用关心我这配置了什么。


日志我只输出在控制台，配置文件log4j.properties

log4j.rootLogger=DEBUG,stdout

log4j.logger.com.test4=DEBUG

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
# time thread class level message
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS} [%t] [%c] [%p] - %m%

#log4j.appender.stdout=org.apache.log4j.DailyRollingFileAppender
#log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
#log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS} [%t] [%c] [%p] - %m%
#log4j.appender.stdout.DatePattern='.'yyyy-MM-dd
#log4j.appender.stdout.file=/data/logs/test.log
#log4j.appender.stdout.append=true

注意是DEBUG，否则不打印SQL和执行结果。


编写一个Controller，实用JdbcTemplate从数据看查询数据

package com.test4;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
@Scope("prototype")
@Controller
@Transactional(rollbackFor=Exception.class)
@RequestMapping(value = "/test")
public class TestController {
	@Autowired JdbcTemplate jdbcTemplate;
	@RequestMapping("/test1")
	public String forAdd(HttpServletRequest request) throws Exception{
		List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from test");
		request.setAttribute("list", list);
		return "/test1";
	}
}


在JSP页面输出这些数据

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    <title>test1</title>
  </head>
<body>
<c:forEach items="${ list }" var="obj" varStatus="stauts">
${obj.id }-->${obj.name }<br>
</c:forEach>
</body>
</html>


如果要监控SQL的一些执行情况，可以打开地址：http://localhost:8080/test4/druid/  来查看。

访问地址：http://localhost:8080/test4/test/test1  查看数据库的输出，这里我的表test就两个字段，id和name。

访问结果：

阿里开源数据库连接池Druid

 

工程示例代码：

test4.zip



alay

Flame

Hello world!

0 评论
留下评论
作者
Flame
Flame
Hello world!
私信 Ta
热门分类
后端开发 863 前端开发 58 其它 44
热门标签