spring配置dbcp链接数据库

最新推荐文章于 2025-03-06 11:45:59 发布

原创最新推荐文章于 2025-03-06 11:45:59 发布 · 6.3k 阅读

7 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#spring #dbcp #testOnBorrow #testWhileIdle

JAVA 同时被 3 个专栏收录

61 篇文章

订阅专栏

Spring

14 篇文章

订阅专栏

Springmvc

7 篇文章

订阅专栏

本文介绍了在Spring MVC项目中配置DBCP连接池来建立数据库链接的方法，特别是针对数据库恢复后应用未重启导致无法连接的问题。通过设置`testWhileIdle`或`testOnBorrow`属性，可以在连接池中检测并移除无效链接，确保连接有效性。

1.spring的配置文件
springmvc-servlet.xml

  <bean id="config" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location">
                <value>classpath:jdbc.properties</value>
        </property>
    </bean>
        <!-- 注解扫描包 -->
      <context:component-scan base-package="com"/> 
            
      <!--配置注解 -->
      <mvc:annotation-driven/>  
      
	<mvc:resources location="/js/" mapping="/js/**"/>
	
	
	
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${driverClassName}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
        <!--maxActive: 最大连接数量-->
        <property name="maxActive" value="${maxActive}"/>
        <!--minIdle: 最小空闲连接-->
        <property name="minIdle" value="${minIdle}"/>
        <!--maxIdle: 最大空闲连接-->
        <property name="maxIdle" value="${maxIdle}"/>
        <!--initialSize: 初始化连接-->
        <property name="initialSize" value="${initialSize}"/>
       	<!--  连接被泄露时是否打印 -->
        <property name="logAbandoned" value="${logAbandoned}"/>
      <!--   removeAbandoned: 是否自动回收超时连接 -->
        <property name="removeAbandoned" value="${removeAbandoned}"/>
       <!--  removeAbandonedTimeout: 超时时间(以秒数为单位) -->
        <property name="removeAbandonedTimeout" value="${removeAbandonedTimeout}"/>
      <!--   maxWait: 超时等待时间以毫秒为单位 1000等于60秒 -->
        <property name="maxWait" value="${maxWait}"/>
      <!--   在空闲连接回收器线程运行期间休眠的时间值,以毫秒为单位. -->
        <property name="timeBetweenEvictionRunsMillis" value="${timeBetweenEvictionRunsMillis}"/>
       <!--   在每次空闲连接回收器线程(如果有)运行时检查的连接数量 -->
        <property name="numTestsPerEvictionRun" value="${numTestsPerEvictionRun}"/>
     <!--    1000 * 60 * 30  连接在池中保持空闲而不被空闲连接回收器线程 -->
        <property name="minEvictableIdleTimeMillis" value="${minEvictableIdleTimeMillis}"/>
        <property name="validationQuery" value="${validationQuery}"/>
		
		<!-- 定时对线程池中的链接进行validateObject校验，对无效的链接进行关闭 -->
	 	 <!--  <property name="testWhileIdle" value="${testWhileIdle}"/>   -->
		<!-- 指定在从连接池中拿连接时，要检查连接是否有效，若无效会将连接从连接池中移除掉 -->
		 <property name="testOnBorrow" value="${testOnBorrow}"/> 
	
    </bean>
    
    <!-- 创建spring jdbcTemplate -->
	<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>

jdbc.properties

#db:oracle
#driverClassName:oracle.jdbc.driver.OracleDriver
#url:jdbc:oracle:thin:@10.4.39.67:11521/yqdb
#username:om
#password:Om4wd5qa


db:oracle
driverClassName:oracle.jdbc.driver.OracleDriver
url:jdbc:oracle:thin:@localhost:1521:orcl
username:lyx
password:lyx



maxActive:100
initialSize:10
maxWait:60000
minIdle:10
maxIdle:15

logAbandoned:true
removeAbandoned:true
removeAbandonedTimeout:10 
timeBetweenEvictionRunsMillis:10000
numTestsPerEvictionRun:10
minEvictableIdleTimeMillis:10000
validationQuery:SELECT 1 FROM DUAL

#\u6821\u9A8C\u94FE\u63A5
testWhileIdle:true
testOnBorrow:true

建立数据库链接：

package com.dbcp.conn;

import java.util.List;
import java.util.Map;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class TestDbcp implements ApplicationContextAware
{
	
	private static ApplicationContext ctx ;
	
	//获取上下文
	@Override
	public void setApplicationContext(ApplicationContext context)
			throws BeansException
	{
		// TODO Auto-generated method stub
		
		ctx = context;
		
	}
	
	
	/**
	 * @param args
	 */
	public static void main(String[] args)
	{
		// TODO Auto-generated method stub
	
		if(ctx == null)
		{
			String path = System.getProperty("user.dir") + "\\src\\springmvc-servlet.xml";
			
			//获取上下文
			ctx = new  FileSystemXmlApplicationContext(path);
				//ClassPathXmlApplicationContext("classpath*:springmvc-servlet.xml");
		}
	
		JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
		
		List<Map<String,Object>> resultList =  jdbcTemplate.queryForList("select t.singerid,t.singername,t.singercountry from SINGER t");
		
		List<Map<String,Object>> resList =  jdbcTemplate.queryForList("select * from COMMENTARY t");
		
		
		System.out.println("歌手表 输出查询结果：-----------------------");
		for (Map<String, Object> map : resultList)
		{
			System.out.println(map.get("singerid")+","+map.get("singername")+","+map.get("singercountry"));
		}
		
		System.out.println("评论表 输出查询结果：-----------------------");
		System.out.println();
		for (Map<String, Object> map : resList)
		{
			System.out.println(map.get("commentaryId")+","+map.get("commentary_context")+","+map.get("commentary_time"));
		}
	}




}