Spring整合jdbc

首先web.xml文件跟往常一样，加载spring容器和加载org.springframework.web.context.ContextLoaderListener读取applicationContext.xml文件初进行始化。

使用spring整合jdbc工具步骤：

　　1.使用连接池com.mchange.v2.c3p0.ComboPooledDataSource等工具创建数据源。

　　2.把数据源交给LazyConnectionDataSourceProxy进行管理

　　3.把LazyConnectionDataSourceProxy管理的数据源注入到需要的JavaBean中

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:p="http://www.springframework.org/schema/p"
    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:dwr="http://www.directwebremoting.org/schema/spring-dwr"
    xmlns:jaxws="http://cxf.apache.org/jaxws"
    xsi:schemaLocation="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  
            http://www.springframework.org/schema/aop  
            http://www.springframework.org/schema/aop/spring-aop-3.2.xsd  
            http://www.springframework.org/schema/tx  
            http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
            http://cxf.apache.org/jaxws http://cxf.apache.org/schemas/jaxws.xsd">
            
    <!-- 读取jdbc的配置文件中的jdbc连接属性 （以下两种都可以）-->
    <!-- <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
        lazy-init="false">
        <property name="locations">
            <list>
                <value>classpath*:jdbc.properties</value>
            </list>
        </property>
    </bean> -->
    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">  
       <property name="locations">  
          <value>classpath*:jdbc.properties</value>  
       </property>   
    </bean> 
    <!-- 配置数据源 -->
    <bean id="mainDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        <property name="driverClass">
            <value>${db.driverClass}</value>
        </property>
        <property name="jdbcUrl">
            <value>${db.url}</value>
        </property>
        <property name="user">
            <value>${db.userName}</value>
        </property>
        <property name="password">
            <value>${db.password}</value>
        </property>
        <property name="minPoolSize">
            <value>20</value>
        </property>
        <property name="maxPoolSize">
            <value>100</value>
        </property>
        <property name="initialPoolSize">
            <value>20</value>
        </property>
        <property name="maxIdleTime">
            <value>7200</value>
        </property>
        <property name="acquireIncrement">
            <value>10</value>
        </property>
        <property name="maxStatements">
            <value>20</value>
        </property>
        <!-- 每60秒检查所有连接池中的空闲连接。Default: 0 -->
        <property name="idleConnectionTestPeriod">
            <value>60</value>
        </property>
        <!-- 定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 -->
        <property name="acquireRetryAttempts">
            <value>100</value>
        </property>
        <property name="acquireRetryDelay">
            <value>10</value>
        </property>
        <property name="breakAfterAcquireFailure">
            <value>false</value>
        </property>
        <!-- 测试连接的有效性 消耗很大 -->
        <property name="testConnectionOnCheckout">
            <value>false</value>
        </property>
        <!-- 测试连接的有效性 消耗很大 自动检测连接状况 -->
        <property name="testConnectionOnCheckin">
            <value>false</value>
        </property>
        <property name="preferredTestQuery">
            <value>SELECT count(*) FROM linktest</value>
        </property>
    </bean>
    <!-- 用LazyConnectionDataSourceProxy管理数据源 -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
        <property name="targetDataSource">
            <ref local="mainDataSource" />
        </property>
    </bean>
    <!-- 配置jdbc模板 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> 
        <property name="dataSource" ref="dataSource"/> 
    </bean>
    
    <bean id="BaseDaoImpl" class="com.qm.frame.core.dao.BaseDaoImpl">
        <property name="dataSource" ref="dataSource" /> 
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>
</beans>

jdbc.properties文件

db.driverClass=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/inter_tv
db.userName=root
db.password=root

BaseDaoImpl.class

package com.qm.frame.core.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;

public class BaseDaoImpl implements IBaseDao{
    //spring的模板工具
    private JdbcTemplate jdbcTemplate;
    //jdbc的数据源
    private DataSource dataSource;public int update(){
    System.out.println("hashCode " + jdbcTemplate.hashCode());
    try {
      Connection connection =  dataSource.getConnection();
      PreparedStatement preparedStatement = connection.prepareStatement("select * from EIBS_BASE_TASKINFO");
     ResultSet resultSet =  preparedStatement.executeQuery();
     while (resultSet.next()) {
         System.out.println("checkId " + resultSet.getLong(1));
    }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return  0; 
    }
    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    public DataSource getDataSource() {
        return dataSource;
    }
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
}