连接池_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);
}
}

其余类似：略