连接池_1：JDBC连接多数据库-proxool连接池配置

68 阅读 0 评论 45 点赞

我是靠谱客的博主成就白羊，最近开发中收集的这篇文章主要介绍连接池_1：JDBC连接多数据库-proxool连接池配置，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

近来，公司要求将以前sqlserver2008上的某一部分数据导入到现在的oracle11g上来，因为很多表和字段都没对应上，那肯定不能用sql脚本导，既然要连到多个数据库，自然就想到了连接池。
下列是配置了mysql，sqlserver2008，oracle三种数据库

一、首先：导jar包

proxool连接池：proxool-0.9.1.jar,proxool-cglib.jar,commons-logging.jar
mysql数据库:mysqldriver.jar
sqlserver2008数据库：sqljdbc4-3.0.jar
oracle11数据库：ojdbc14.jar
这些包都可以在网上直接下载，也可以去我的百度云下载
下载链接：http://pan.baidu.com/s/1slKlAnb

二、连接池配置文件:proxool.xml

<?xml version="1.0" encoding="UTF-8"?>
<!--JDBC pool's configuration -->
<!--proxool parameter:
alias:数据库连接别名，程序中使用
driver-class:数据库驱动程序类
driver-url:数据库连接地址
driver-properties:用户名，密码
minmum-connection-count:最小 连接数量建,议设置0以上，保证第一次连接时间
maximum-connection-count:最大连接数量，如果超过最大连接数量则会抛出异常。连接数设置过多，服务器CPU和内存性能消耗很
simultaneous-build-throttle:同时的最大连接数
maximum-active-time：连接的最大时间活动
-->
<conn-pool>
<!--mysql数据库-->
<proxool>
<alias>MySQL1</alias>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<driver-url>jdbc:mysql://localhost:3306/test?charset=utf8</driver-url>
<driver-properties>
<property name="user" value="root"/>
<property name="password" value="root"/>
</driver-properties>
<minmum-connection-count>2</minmum-connection-count>
<maximum-connection-count>10</maximum-connection-count>
<!--配置最大连接时间
15分钟-->
<maximum-active-time>3000000</maximum-active-time>
<simultaneous-build-throttle>50</simultaneous-build-throttle>
<house-keeping-test-sql>select 0</house-keeping-test-sql>
<trace>true</trace>
</proxool>
<!--sqlserver 2008-->
<proxool>
<alias>SQLServer1</alias>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<driver-url>jdbc:sqlserver://ip:1433;DatabaseName=?</driver-url>
<driver-properties>
<property name="user" value="sa"/>
<property name="password" value="password"/>
</driver-properties>
<minmum-connection-count>2</minmum-connection-count>
<maximum-connection-count>10</maximum-connection-count>
<!--配置最大连接时间
15分钟-->
<maximum-active-time>3000000</maximum-active-time>
<simultaneous-build-throttle>50</simultaneous-build-throttle>
<house-keeping-test-sql>select 0</house-keeping-test-sql>
<trace>true</trace>
</proxool>
<!--oracle数据库-->
<proxool>
<alias>Oracle1</alias>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<driver-url>jdbc:oracle:thin:@ip:1521:orcl</driver-url>
<driver-properties>
<property name="user" value="user"/>
<property name="password" value="password"/>
</driver-properties>
<minmum-connection-count>2</minmum-connection-count>
<maximum-connection-count>10</maximum-connection-count>
<!--配置最大连接时间
15分钟-->
<maximum-active-time>3000000</maximum-active-time>
<simultaneous-build-throttle>50</simultaneous-build-throttle>
<house-keeping-test-sql>select 0</house-keeping-test-sql>
<trace>true</trace>
</proxool>

三、配置servlet的访问路径:web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<display-name></display-name>
<servlet>
<servlet-name>InitServlet</servlet-name>
<servlet-class>com.demo.servlet.InitServlet</servlet-class>
<init-param>
<param-name>proxool.init.filename</param-name>
<param-value>WEB-INF/classes/com/demo/config/proxool.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet>
<servlet-name>TestImport</servlet-name>
<servlet-class>com.demo.servlet.TestImport</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>TestImport</servlet-name>
<url-pattern>/test</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
</web-app>

四、编写servlet

工具类：
package com.demo.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* 操作oracle数据库的工具类
* @author xiaohei
*
*/
public class MySqlOpr {
public static Connection conn() throws Exception{
return DriverManager.getConnection("proxool.MySQL1");
}
public static void closeDB(Connection conn){
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void commit(Connection conn){
try {
if(conn!=null){
conn.commit();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void rollback (Connection conn){
try{
if(conn!=null) conn.rollback();
}catch (Exception e) {
e.printStackTrace();
}
}
}

其余两个类似：略

InitServlet.java

package com.demo.servlet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
public class InitServlet extends HttpServlet{
public void init() throws ServletException {
String dir = getServletContext().getRealPath("/");
dir = dir.replace('\', '/');
String configFile =configFile = getInitParameter("proxool.init.filename");
System.out.println("[系统初始化][初始化连接池组件]初始参数proxool.init.filename=" + dir + configFile);
try {
org.logicalcobwebs.proxool.configuration.JAXPConfigurator.configure(dir+configFile, false);
} catch (Exception e) {
e.printStackTrace();
throw new ServletException(e);
}
}
}

TestImport.java

package com.demo.servlet;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.demo.util.MySqlOpr;
/**
* Servlet implementation class DataImport
* @author xiaohei
*
*/
public class TestImport extends HttpServlet {
private static final long serialVersionUID = 1L;
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
public TestImport() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
conn = MySqlOpr.conn();
String sql="select * from address";
stat = conn.prepareStatement(sql);
rs = stat.executeQuery();
while(rs.next()){
System.out.println(rs.getRow()+rs.getString("addressid"));
}
conn = OracleOpr.conn();
String sql="insert into address(id,collage,createtime) values(?,?,?) ";
stat = conn.prepareStatement(sql);
//给数据库添加具体记录
stat.setInt(1, 100);
stat.setString(2, "collage");
stat.setString(4,"createtime");
if(stat.executeUpdate()<0){
System.out.println("数据保存失败，请检查");
}else{
System.out.println("Success");
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try{rs.close();rs=null;}catch(Exception e){}
try{stat.close();stat=null;}catch(Exception e){}
try{conn.close();conn=null;}catch(Exception e){}
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}

其余类似：略