spring中 连接池的使用

2008-03-19

spring中连接池的配置

在默认通过myeclipse生成的配置里，spring使用的是apache的dbcp连接池

 

<bean id="dataSource"
  class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName"
   value="com.mysql.jdbc.Driver">
  </property>
  <property name="url"
   value="jdbc:mysql://localhost:3306/mysql">
  </property>
  <property name="username" value="root"></property>
  <property name="password" value="root"></property>
 </bean>

如果改为C3P0则为：

<bean id="propertyConfigurer"
        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location">
            <value>classpath:jdbc.properties</value>
        </property>
    </bean>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver" />
        <property name="jdbcUrl" value="${jdbc.url}" />
        <property name="user" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <property name="autoCommitOnClose" value="true"/>
        <property name="checkoutTimeout" value="${cpool.checkoutTimeout}"/>
        <property name="initialPoolSize" value="${cpool.minPoolSize}"/>
        <property name="minPoolSize" value="${cpool.minPoolSize}"/>
        <property name="maxPoolSize" value="${cpool.maxPoolSize}"/>
        <property name="maxIdleTime" value="${cpool.maxIdleTime}"/>
        <property name="acquireIncrement" value="${cpool.acquireIncrement}"/>
        <property name="maxIdleTimeExcessConnections" value="${cpool.maxIdleTimeExcessConnections}"/>
    </bean>
jdbc.properties：

# Database URL
jdbc.url=jdbc:mysql://192.168.0.25"3306/db

# Database login information
jdbc.username=root
jdbc.password=

# Time to wait for an open connection before timing out
# (in milliseconds)
cpool.checkoutTimeout=5000

# Connection pool size
cpool.minPoolSize=5
cpool.maxPoolSize=40

# How long to keep unused connections around(in seconds)
# Note: MySQL times out idle connections after 8 hours(28,800 seconds)
# so ensure this value is below MySQL idle timeout
cpool.maxIdleTime=25200

# How long to hang on to excess unused connections after traffic spike
# (in seconds)
cpool.maxIdleTimeExcessConnections=1800

# Acquiring new connections is slow, so eagerly retrieve extra connections
# when current pool size is reached
cpool.acquireIncrement=5

 

或者将上面的3部分写成一个：
<bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
      <property name="driverClass">
        <value>${jdbc.driverClassName}</value>
      </property>
      <property name="jdbcUrl">
        <value>${jdbc.url}</value>
      </property>
      <property name="user">
       <value>${jdbc.username}</value>
      </property>
      <property name="password">
       <value>${jdbc.password}</value>
      </property>
      <property name="initialPoolSize"><value>10</value></property>
      <property name="minPoolSize"><value>5</value></property>
      <property name="maxPoolSize"><value>30</value></property>
      <property name="acquireIncrement"><value>5</value></property>
      <property name="maxIdleTime"><value>10</value></property>
      <property name="maxStatements"><value>0</value></property>
    </bean>

如果使用的是受管理的J2EE服务器，则在spring中配置为JNDI连接：
<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName">
<value>java:comp/env/jndi/xxx</value>
</property>
</bean>

如果在应用里有独自使用hibernate，则在spring中配置hibernate连接池，使用C3P0如下：
<bean   id="DataSource"  
  class="org.apache.commons.dbcp.BasicDataSource">  
  <property   name="driverClassName">  
  <value>oracle.jdbc.driver.OracleDriver</value>  
  </property>  
  <property   name="url">  
  <value>jdbc:oracle:thin:@172.16.20.241:1521:dbsvr</value>  
  </property>  
  <property   name="username">  
  <value>hl3000</value>  
  </property>  
  <property   name="password">  
  <value>hldw3101</value>  
  </property>  
  </bean>  
  <bean   id="SessionFactory"  
  class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">  
  <property   name="dataSource">  
  <ref   bean="DataSource"   />  
  </property>  
  <property   name="hibernateProperties">  
  <props>  
  <prop   key="hibernate.dialect">org.hibernate.dialect.OracleDialect</prop>  
  <prop   key="hibernate.show_sql">true</prop>  
   
  <!--   C3P0连接池配置   -->  
  <prop   key="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</prop>  
  <prop   key="hibernate.c3p0.max_size">20</prop>  
  <prop   key="hibernate.c3p0.min_size">5</prop>  
  <prop   key="hibernate.c3p0.timeout">120</prop>  
  <prop   key="hibernate.c3p0.max_statements">100</prop>  
  <prop   key="hibernate.c3p0.idle_test_period">120</prop>  
  <prop   key="hibernate.c3p0.acquire_increment">2</prop>  
  <prop   key="myeclipse.connection.profile">hl3000</prop>  
  </props>  
  </property>  
  <property   name="mappingResources">  
  <list>  
  <value>com/hl3000/DBLogic/POJO/PermUserAccount.hbm.xml</value>  
  </list>  
  </property>  
  </bean> 

spring中配置独立使用hibernate时使用jndi的配置：

hibernate.dialect = net.sf.hibernate.dialect.MySQLDialect
hibernate.connection.datasource=java:comp/env/jdbc/SAMPLEDB
hibernate.show_sql=true

如果是使用不受管理的Servlet容器如Tomcat，也可以使用jndi的方式配置，需要在tomcat中配置数据源，在server.xml中增改大致如下：

     <Resource name="jdbc/testDb" auth="Container"
                    type="javax.sql.DataSource"/>
          <ResourceParams name="jdbc/testDB">\\数据源的名称
            <parameter><name>username</name><value>root</value></parameter>数据库的名称
            <parameter><name>password</name><value>password</value></parameter>数据库密码
            <parameter><name>driverClassName</name>
              <value>org.gjt.mm.mysql.Driver</value></parameter>\\要加载的驱动
            <parameter><name>url</name>
              <value>jdbc:mysql://172.20.0.73/rk?</value></parameter>\\要连接的URL
          </ResourceParams>