1、定义oracle.properties
#数据库驱动
oracle.driverClassName=oracle.jdbc.driver.OracleDriver
#用户名
oracle.accountName=xxx
#密码
oracle.password=a
#数据库地址信息
oracle.url=jdbc:oracle:thin:@localhost:1521:orcl
#初始化连接数10个
oracle.initialSize=300
#最小空闲连接
oracle.minIdle=300
#最大空闲连接
oracle.maxIdle=300
#事务自动提交
oracle.defaultAutoCommit=false
#设置了rmoveAbandoned=true那么当getNumActive()快要到getMaxActive()的时候,系统会进行
#removeAbandoned回收的 Connection为这里(默认300秒)设置的秒数后没有使用的
oracle.removeAbandonedTimeout=20
#ogAbandoned=true的话,将会在回收事件后,在log中打印出回收Connection的错误信息,建议设为false
oracle.logAbandoned=false
#从线程池拿出连接时,是否需要检验其有效性,建议为false,因为这样很影响效率
oracle.testOnBorrow=false
#多久启动一次空闲连接检查,1检查有效性,2检查空闲连接数量是否符合设定的最大最小值,单位:毫秒
oracle.timeBetweenEvictionRunsMillis=30000
#连接空闲多久后被回收,单位:毫秒
oracle.minEvictableIdleTimeMillis=12000
#连接是否被空闲连接回收器检查,检查不通过则丢弃该连接
oracle.testWhileIdle=true
#每次空闲连接检查时,检查的连接数量
oracle.numTestsPerEvictionRun=3
#连接池创建的连接是否默认为只读
oracle.defaultReadOnly=false
#数据库隔离级别,READ_COMMITTED为2
oracle.defaultTransactionIsolation=2
#测试空闲连接的语句,一定要注意这里不能写成select 1!
oracle.validationQuery=select 1 from dual
#连接是否在归还到连接池前进行可用性的检验
oracle.testOnReturn=true
说明:如果定义了多个数据库的资源文件,那么这里的资源属性名一定不要相同,否则只会连接一个数据库,因为资源文件是通过访问属性来进行解析的,相当于map中的key,所以该资源文件的属性不能相同。
2、定义oracle.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:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd"
default-lazy-init="false">
<context:property-placeholder location="classpath:oracle.properties" ignore-unresolvable="true"/>
<bean id="oracleDataSource" name="oracleDataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
<property name="driverClassName"><value>${oracle.driverClassName}</value></property>
<property name="url"><value>${oracle.url}</value></property>
<property name="username"><value>${oracle.accountName}</value></property>
<property name="password"><value>${oracle.password}</value></property>
<property name="initialSize"><value>${oracle.initialSize}</value></property>
<property name="minIdle"><value>${oracle.minIdle}</value></property>
<property name="maxIdle"><value>${oracle.maxIdle}</value></property>
<property name="defaultAutoCommit"><value>${oracle.defaultAutoCommit}</value></property>
<property name="removeAbandonedTimeout"><value>${oracle.removeAbandonedTimeout}</value></property>
<property name="logAbandoned"><value>${oracle.logAbandoned}</value></property>
<property name="testOnBorrow"><value>${oracle.testOnBorrow}</value></property>
<property name="timeBetweenEvictionRunsMillis"><value>${oracle.timeBetweenEvictionRunsMillis}</value></property>
<property name="minEvictableIdleTimeMillis"><value>${oracle.minEvictableIdleTimeMillis}</value></property>
<property name="testWhileIdle"><value>${oracle.testWhileIdle}</value></property>
<property name="numTestsPerEvictionRun"><value>${oracle.numTestsPerEvictionRun}</value></property>
<property name="defaultReadOnly"><value>${oracle.defaultReadOnly}</value></property>
<property name="defaultTransactionIsolation"><value>${oracle.defaultTransactionIsolation}</value></property>
<property name="validationQuery"><value>${oracle.validationQuery}</value></property>
<property name="testOnReturn"><value>${oracle.testOnReturn}</value></property>
</bean>
<bean id="transactionManager3" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource">
<ref local="oracleDataSource" />
</property>
</bean>
</beans>
3、定义连接工具类
package com.yx.yzh.dao.impl;
import java.sql.Connection;
import javax.annotation.Resource;
import javax.sql.DataSource;
import com.yx.yzh.dao.DBConnectionDao;
import comyx.yzh.enums.DBEnum;
public class DBConnectionImpl implements DBConnectionDao {
public DataSource mysqlDataSource;
public DataSource sqlServerDataSource;
public DataSource oracleDataSource;
/**
* @author yzh
* @description 获取Oracle的连接
* @return Connection
* @date 2018-10-10
*/
public Connection getOracleConnection(){
Connection con = null;
try{
if(con == null){
con = oracleDataSource.getConnection();
}
}catch(Exception e){
e.printStackTrace();
}
return con;
}
/**
* @author yzh
* @description 获取MySQL的连接
* @return Connection
* @date 2018-10-10
*/
public Connection getMySQLConnection(){
Connection con = null;
try{
if(con == null){
con = mysqlDataSource.getConnection();
}
}catch(Exception e){
e.printStackTrace();
}
return con;
}
/**
* @author yzh
* @description 获取SQLServer2008的连接
* @return Connection
* @date 2018-10-10
*/
public Connection getSQLServer2008Connection(){
Connection con = null;
try{
if(con == null){
con = sqlServerDataSource.getConnection();
}
}catch(Exception e){
e.printStackTrace();
}
return con;
}
/**
* @author yzh
* @description 通过数据库类型获取当前数据库的连接
* @param DBType 数据库类型
* @return Connection
* @date 2018-10-10
*/
public Connection getConnection(String DBType){
Connection con = null;
try{
if(null != DBType && !"".equals(DBType)){
//如果是MySQL数据库
if(DBEnum.MySQL.DBName.equals(DBType.toLowerCase())){
con = mysqlDataSource.getConnection();
//如果是SQLServer2008
}else if(DBEnum.SQLServer2008.DBName.equals(DBType.toLowerCase())){
con = sqlServerDataSource.getConnection();
}else if(DBEnum.ORALCE.DBName.equals(DBType.toLowerCase())){
con = oracleDataSource.getConnection();
}
}
}catch(Exception e){
e.printStackTrace();
}
return con;
}
public DataSource getOracleDataSource() {
return oracleDataSource;
}
@Resource(name="oracleDataSource")
public void setOracleDataSource(DataSource oracleDataSource) {
this.oracleDataSource = oracleDataSource;
}
public DataSource getMysqlDataSource() {
return mysqlDataSource;
}
@Resource(name="mysqlDataSource")
public void setMysqlDataSource(DataSource mysqlDataSource) {
this.mysqlDataSource = mysqlDataSource;
}
public DataSource getSqlServerDataSource() {
return sqlServerDataSource;
}
@Resource(name="sqlServerDataSource")
public void setSqlServerDataSource(DataSource sqlServerDataSource) {
this.sqlServerDataSource = sqlServerDataSource;
}
}
通过@Resource会去spring.xml中获取该bean,因此这里还需要定义一个bean,如下所示。
4、SQLDBMySQLDBOracleDB.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:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd"
default-lazy-init="false">
<bean id="dBConnectionDao" class="com.yx.yzh.utils.DBConnectionUtil" >
<property name="sqlServerDataSource" ref="sqlServerDataSource"></property>
<property name="mysqlDataSource" ref="mysqlDataSource"></property>
<property name="oracleDataSource" ref="oracleDataSource"></property>
</bean>
</beans>
同理,我们可以定义MySQL以及SQLServer2008的数据库的连接。
5、将上面的xml文件引入spring.xml容器
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-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://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd"
default-lazy-init="false">
<import resource="mysql.xml"/>
<import resource="oracle.xml"/>
<import resource="SQLServer2008.xml"/>
<import resource="SQLDBMySQLDBOracleDB.xml"/>
<context:component-scan base-package="com.yx"/>
<context:annotation-config/>
</beans>
6、测试
@Resource(name="dBConnectionDao")
private DBConnectionUtil dBConnectionDao;
@Test
public void testSQLServerConnection(){
System.out.println(dBConnectionDao.getSQLServer2008Connection());
}
@Test
public void testMysqlConnection(){
System.out.println(dBConnectionDao.getMySQLConnection());
}
@Test
public void testOracleConnection(){
System.out.println(dBConnectionDao.getOracleConnection());
}
@Test
public void testDBConnection(){
System.out.println(dBConnectionDao.getConnection("MySQL"));
System.out.println(dBConnectionDao.getConnection("SQLServer2008"));
System.out.println(dBConnectionDao.getConnection("oracle"));
}