spring jdbcTemplate结合数据库连接池

最新推荐文章于 2024-06-27 08:00:00 发布

jasnet_u

最新推荐文章于 2024-06-27 08:00:00 发布

阅读量3k

点赞数 1

分类专栏： java编程 javaWeb框架文章标签： jdbcTemplate spring jdbc

本文链接：https://blog.csdn.net/jasnet_u/article/details/81038636

版权

java编程同时被 2 个专栏收录

91 篇文章 0 订阅

订阅专栏

javaWeb框架

10 篇文章 0 订阅

订阅专栏

上一篇，我们使用了spring一个自带的 org.springframework.jdbc.datasource.DriverManagerDataSource管理类操作jdbcTemplate,这是一个不带连接池的数据源管理驱动类。在这个驱动类的管理下，程序中jdbcTemplate的每次获取连接都会直接向数据库申请建立连接，比较耗费资源。这一篇中，我们尝试将第三方数据连接池框架(阿里巴巴druid)集成到spring的JdbcTemplate中。

示例代码：

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

UserNameFindServlet.java

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]
    }
     
}