上一篇, 我们使用了spring一个自带的 org.springframework.jdbc.datasource.DriverManagerDataSource管理类操作jdbcTemplate,这是一个不带连接池的数据源管理驱动类。在这个驱动类的管理下,程序中jdbcTemplate的每次获取连接都会直接向数据库申请建立连接,比较耗费资源。这一篇中,我们尝试将第三方数据连接池框架(阿里巴巴druid)集成到spring的JdbcTemplate中 。
示例代码:
spring-jdbc是一个web工程
web.xml
<?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_3_0.xsd" id="WebApp_ID" version="3.0"> <!-- 防止内存泄漏spring监听器 --> <listener> <listener-class>org.springframework.web.util.IntrospectorCleanupListener</listener-class> </listener> <!-- spring web容器监听器 --> <context-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:/spring.xml</param-value> </context-param> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <welcome-file-list> <welcome-file>index.html</welcome-file> <welcome-file>index.jsp</welcome-file> </welcome-file-list> </web-app>pom.xml
<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/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.tingcream</groupId> <artifactId>spring-jdbc</artifactId> <packaging>war</packaging> <version>0.0.1-SNAPSHOT</version> <name>spring-jdbc</name> <url>http://maven.apache.org</url> <dependencies> <!-- junit 测试框架 --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <!-- servlet api 包,引入后无需再引入tomcat lib目录下面的包 --> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>3.1.0</version> <scope>provided</scope> </dependency> <!--jsp api 包,引入后无需再引入tomcat lib目录下面的包 --> <dependency> <groupId>javax.servlet.jsp</groupId> <artifactId>javax.servlet.jsp-api</artifactId> <version>2.3.1</version> <scope>provided</scope> </dependency> <!-- jstl 标签库依赖jar包 --> <dependency> <groupId>javax.servlet</groupId> <artifactId>jstl</artifactId> <version>1.2</version> </dependency> <!-- 阿里巴巴 fastJson 解析器 jar包 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.31</version> </dependency> <!-- spring 核心jar包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>4.3.2.RELEASE</version> </dependency> <!-- spring beans jar包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>4.3.2.RELEASE</version> </dependency> <!-- spring 事务管理jar包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>4.3.2.RELEASE</version> </dependency> <!-- spring 容器上下文 jar包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.3.2.RELEASE</version> </dependency> <!-- spring 容器 上下文支持jar包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context-support</artifactId> <version>4.3.2.RELEASE</version> </dependency> <!-- spring web相关组件jar包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-web</artifactId> <version>4.3.2.RELEASE</version> </dependency> <!-- spring mvc 支持jar包 --> <!-- <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>4.3.2.RELEASE</version> </dependency> --> <!-- spring aop 切面jar包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aop</artifactId> <version>4.3.2.RELEASE</version> </dependency> <!-- spring 切面 织入jar包--> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aspects</artifactId> <version>4.3.2.RELEASE</version> </dependency> <!-- spring-jdbc 事务支持jar包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.3.2.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-orm</artifactId> <version>4.3.2.RELEASE</version> </dependency> <!-- spring 测试支持jar包 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>4.3.2.RELEASE</version> </dependency> <!-- mysql jdbc 驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.40</version> </dependency> <!-- druid 阿里巴巴数据库连接池 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.31</version> <exclusions> <exclusion> <artifactId>jconsole</artifactId> <groupId>com.alibaba</groupId> </exclusion> <exclusion> <artifactId>tools</artifactId> <groupId>com.alibaba</groupId> </exclusion> </exclusions> </dependency> </dependencies> <build> <finalName>spring-jdbc</finalName> </build> </project>
spring.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:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:cache="http://www.springframework.org/schema/cache" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache-4.3.xsd"> <!-- spring 组件包扫描 --> <context:component-scan base-package="com.tingcream.springJdbc"> </context:component-scan> <!-- <bean id="jdbcTemplateFactory" class="com.tingcream.springJdbc.jdbcTemplate.JdbcTemplateFactory"> </bean> <bean id="jdbcTemplate" factory-bean="jdbcTemplateFactory" factory-method="getJdbcTemplate"> </bean> --> <!-- 引入外部配置文件 (可含多个) --> <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="locations"> <list> <value>classpath:jdbc.properties</value> </list> </property> </bean> <!--阿里巴巴数据库连接池 --> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <property name="driverClassName" value="${jdbc.driverClassName}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}" /> <!-- 初始化连接数量 --> <property name="initialSize" value="${druid.initialSize}"/> <!--最大并发连接数 --> <property name="maxActive" value="${druid.maxActive}" /> <!-- 最小空闲连接数 --> <property name="minIdle" value="${druid.minIdle}"/> <!-- 配置获取连接等待超时的时间 --> <property name="maxWait" value="${druid.maxWait}"/> <!-- 超过时间限制是否回收 --> <property name="removeAbandoned" value="${druid.removeAbandoned}"/> <!-- 超过时间限制多长; --> <property name="removeAbandonedTimeout" value="${druid.removeAbandonedTimeout}"/> <!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 --> <property name="timeBetweenEvictionRunsMillis" value="${druid.timeBetweenEvictionRunsMillis}"/> <!-- 配置一个连接在池中最小生存的时间,单位是毫秒 --> <property name="minEvictableIdleTimeMillis" value="${druid.minEvictableIdleTimeMillis}"/> <!-- 用来检测连接是否有效的sql,要求是一个查询语句 --> <property name="validationQuery" value="${druid.validationQuery}"/> <!-- 申请连接的时候检测 --> <property name="testWhileIdle" value="${druid.testWhileIdle}" /> <!-- 申请连接时执行validationQuery检测连接是否有效,配置为true会降低性能 --> <property name="testOnBorrow" value="${druid.testOnBorrow}"/> <!-- 归还连接时执行validationQuery检测连接是否有效,配置为true会降低性能 --> <property name="testOnReturn" value="${druid.testOnReturn}"/> <!-- 打开PSCache,并且指定每个连接上PSCache的大小 --> <property name="poolPreparedStatements" value="${druid.poolPreparedStatements}"/> <property name="maxPoolPreparedStatementPerConnectionSize" value="${druid.maxPoolPreparedStatementPerConnectionSize}"/> <!-- 属性类型是字符串,通过别名的方式配置扩展插件, 常用的插件有: 监控统计用的filter:stat 日志用的filter:log4j 防御SQL注入的filter:wall --> <property name="filters" value="${druid.filters}"/> </bean> <!-- jdbcTemplate bean配置 --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> </bean> </beans>
jdbc.properties
#驱动全类名 jdbc.driverClassName = com.mysql.jdbc.Driver #本机环境 jdbc.url =jdbc:mysql://localhost:3306/myDB?useUnicode=true&characterEncoding=UTF8&useSSL=false jdbc.username = root jdbc.password = 123456 #初始化连接数 druid.initialSize = 3 #最大活跃连接数 druid.maxActive = 10 #最小空闲连接数 druid.minIdle = 2 #最大连接等待时间 毫秒 druid.maxWait = 60000 #超过时间限制是否回收 druid.removeAbandoned = true #超时丢弃连接 1800秒 即30分钟 druid.removeAbandonedTimeout = 1800 #配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 druid.timeBetweenEvictionRunsMillis = 60000 #配置一个连接在池中最小生存的时间,单位是毫秒 druid.minEvictableIdleTimeMillis = 300000 #用来检测连接是否有效的sql,要求是一个查询语句 druid.validationQuery = SELECT 1 FROM DUAL #申请连接的时候检测 druid.testWhileIdle =true #申请连接时执行validationQuery检测连接是否有效,配置为true会降低性能 druid.testOnBorrow = false #归还连接时执行validationQuery检测连接是否有效,配置为true会降低性能 druid.testOnReturn = false #打开PSCache,并且指定每个连接上PSCache的大小 druid.poolPreparedStatements = true druid.maxPoolPreparedStatementPerConnectionSize = 20 # 属性类型是字符串,通过别名的方式配置扩展插件,常用的插件有: 监控统计stat 日志用的log4j 防御SQL注入的wall druid.filters =stat,config # 解密密码必须要配置的项 config, stat启用监控过滤器 # druid.connectionProperties=config.decrypt\=true
package com.tingcream.springJdbc.web; import java.io.IOException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.springframework.jdbc.core.JdbcTemplate; import com.alibaba.fastjson.JSON; import com.tingcream.springJdbc.common.SpringContextHelper; /** * 查询所有用户名字列表 servlet * @author jelly */ @WebServlet("/userNameFindServlet") public class UserNameFindServlet extends HttpServlet{ private static final long serialVersionUID = 1L; @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { this.doPost(req, resp); } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); //从spring容器中获取bean JdbcTemplate jdbcTemplate= SpringContextHelper.getBean("jdbcTemplate", JdbcTemplate.class); String sql="select name from t_user"; List<String> nameList= jdbcTemplate.queryForList(sql, String.class); // System.out.println(nameList); //输出json response.getWriter().write(JSON.toJSONString(nameList)); } }
SpringContextHelper.java
package com.tingcream.springJdbc.common; import org.springframework.beans.BeansException; import org.springframework.context.ApplicationContext; import org.springframework.context.ApplicationContextAware; import org.springframework.stereotype.Component; /** * Spring 上下文辅助类 * @author jelly * */ @Component public class SpringContextHelper implements ApplicationContextAware { private static ApplicationContext applicationContext; @Override public void setApplicationContext(ApplicationContext applicationContext) throws BeansException { SpringContextHelper.applicationContext = applicationContext; } public static ApplicationContext getApplicationContext(){ return applicationContext; } public static Object getBean(String name){ return applicationContext.getBean(name); } /** * 从spring 上下文中获取bean * @param name * @param requiredClass * @return */ public static <T> T getBean(String name, Class<T> requiredClass){ return applicationContext.getBean(name, requiredClass); } public static <T> T getBean(Class<T> requiredType){ return applicationContext.getBean(requiredType); } }
其他测试代码 JdbcTemplateTest2.java
package com.tingcream.springJdbc.test; import java.util.ArrayList; import java.util.List; import java.util.Map; import javax.annotation.Resource; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import com.tingcream.springJdbc.model.User; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration({"/spring.xml"}) public class JdbcTemplateTest2 { @Resource private JdbcTemplate jdbcTemplate ; /** * queryForObject 统计数据库中用户总数 */ @Test public void test1(){ String sql="select count(*) from t_user"; Integer count = jdbcTemplate.queryForObject(sql, Integer.class); System.out.println("用户总数为:"+count); } /** *queryForList 查询所有用户名字的列表 */ @Test public void test2(){ String sql="select name from t_user"; List<String> nameList= jdbcTemplate.queryForList(sql, String.class); System.out.println(nameList); } /** * query 查询用户列表,使用BeanPropertyRowMapper 封装映射row */ @Test public void test3(){ //String sql="select * from t_user where age>?"; //ok String sql="select id,name,age from t_user where age>?";//ok RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class); List<User> list = jdbcTemplate.query(sql, rowMapper,new Object[]{20}); System.out.println(list); } /** * queryForMap 查询返回一个封装map(而不是封装bean) */ @Test public void test4(){ String sql="select * from t_user where id=?"; //ok Map<String,Object> map= jdbcTemplate.queryForMap(sql,new Object[]{2}); System.out.println(map); } /** * queryForList 查询返回一个List<Map<String,Object>> */ @Test public void test5(){ String sql="select * from t_user where age>?"; //ok List<Map<String,Object>> list =jdbcTemplate.queryForList(sql,new Object[]{20}); System.out.println(list); } /** * batchUpdate 批量插入 */ @Test public void test6(){ String sql ="INSERT INTO t_user(`name`,sex,age,city,`describe`) VALUES(?,?,?,?,?)"; List<Object[]> batchArgs =new ArrayList<Object[]>(); batchArgs.add(new Object[]{"张小明",1,22,"杭州","xxx"}); batchArgs.add(new Object[]{"李小华",1,23,"厦门","xxx"}); batchArgs.add(new Object[]{"王小虎",1,28,"株洲","xxx"}); int[] counts= jdbcTemplate.batchUpdate(sql, batchArgs); System.out.println(counts);// [1,1,1] } }
Copyright © 叮叮声的奶酪 版权所有
备案号:鄂ICP备17018671号-1