JDBC多数据库连接的实现
?１.使用ＪＤＢＣ来完成同类多数据的连接，或不同类数据库之间的连接，通用ＸＭＬ文件配置来完成。
?? 1.1? datebase_cfg.xml [数据库连接配置]
<?xml version="1.0" encoding="UTF-8"?><database> <sqlserver> <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver><!-- 数据库驱动 --> <userName>sa</userName><!-- 用户名 --> <userPass>123</userPass><!-- 密码 --> <state>1</state> <!-- 开启1，关闭0 --><!-- 状态，是否开启 --> <dataBaseName>framemanager</dataBaseName><!-- 默认数据库名 --> <baseURL>localhost</baseURL><!-- 服务器地址 --> </sqlserver> <mysql> <driver>com.mysql.jdbc.Driver</driver> <userName>root</userName> <userPass>111111</userPass> <state>0</state> <!-- 开启2，关闭0 --> <dataBaseName>bsdev</dataBaseName> <baseURL>localhost</baseURL></mysql><oracle> <driver>oracle.jdbc.driver.OracleDriver</driver> <userName>root</userName> <userPass>root</userPass> <state>0</state> <!-- 开启3，关闭0 --> <dataBaseName>test</dataBaseName> <baseURL>localhost</baseURL></oracle></database>
?
1.2? GetConfig.java [数据库连接]
package com.smartcom.ma.util.DbUtils;import java.net.URL;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import org.dom4j.Document;import org.dom4j.Element;import org.dom4j.io.SAXReader;/************************************************* * 采用单例模式读取xml配置文件sys-config.xml * @author Liyongbin * *************************************************/ public class GetConfig { //静态的私有成员变量private static GetConfig instance = new GetConfig();private String DRIVER;//数据库驱动private String URL;//连接字符串private String USERNAME;//用户名private String PASSWORD ;//用户密码private String STATUS;//状态private String DatabaseName="";//数据库名public enum DBname{ //定义枚举类型DOCMANAGER,FRAMEMANAGER,WWWMANAGER ; ////定义多个SqlServer数据库名字枚举}private Connection conn = null;private PreparedStatement pst = null;private CallableStatement cs= null;private ResultSet res = null;private Statement st = null;private Document doc;//公共的静态入口方法public static GetConfig getInstance() {return instance;} /********************************************** * 私有的构造方法[读取配置文件] * @author Liyongbin [Apr 28, 2012 6:53:24 PM] * @return ***********************************************/private GetConfig() {try {//获取当前项目在本地的物理路径// String url = ConfigReader.class.getClass().getResource("/").getPath(); URL url = GetConfig.class.getResource("datebase_cfg.xml"); SAXReader reader = new SAXReader();doc = reader.read(url.getFile());// System.out.println(doc.asXML()); Element root = doc.getRootElement();//xml文件的根目录 // ServerSTATUS = root.element("sqlserver").element("state").getText();if(null!=STATUS && "1".equals(STATUS)){DRIVER = root.element("sqlserver").element("driver").getText();USERNAME = root.element("sqlserver").element("userName").getText();PASSWORD = root.element("sqlserver").element("userPass").getText();//DatabaseName = root.element("sqlserver").element("dataBaseName").getText();//在后面设置URL = "jdbc:sqlserver://" + root.element("sqlserver").element("baseURL").getText() + ":1433;databasename=" + DatabaseName; }else if(null!=STATUS && "0".equals(STATUS)){STATUS = root.element("mysql").element("state").getText();} // MySqlif(null!=STATUS && "2".equals(STATUS)){DRIVER = root.element("mysql").element("driver").getText();USERNAME = root.element("mysql").element("userName").getText();PASSWORD = root.element("mysql").element("userPass").getText();DatabaseName = root.element("mysql").element("dataBaseName").getText();URL = "jdbc:mysql://" + root.element("mysql").element("baseURL").getText() + ":3306/" + DatabaseName;}else if(null!=STATUS && "0".equals(STATUS)){STATUS = root.element("oracle").element("state").getText();} // Oracleif(null!=STATUS && "3".equals(STATUS)){STATUS = root.element("oracle").element("state").getText();DRIVER = root.element("oracle").element("driver").getText();USERNAME = root.element("oracle").element("userName").getText();PASSWORD = root.element("oracle").element("userPass").getText();DatabaseName = root.element("oracle").element("dataBaseName").getText();URL = "jdbc:oracle:thin:@" + root.element("oracle").element("baseURL").getText() + ":1521:" + DatabaseName;}if(null==STATUS || "0".equals(STATUS)){//读取配置文件出错[无可用的数据库配置]}System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>DRIVER："+DRIVER);System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>USERNAME："+USERNAME);System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>PASSWORD："+PASSWORD);System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>DatabaseName："+DatabaseName);System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>URL："+URL);} catch (Exception e) {System.out.println(e.getMessage());e.printStackTrace();}} /********************************************** * 获取数据库的连接[默认] * * @return ***********************************************/ public Connection getConn(DBname dbType){ System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>dbType:"+dbType); try { Class.forName(DRIVER); if(URL.indexOf("jdbc:sqlserver")!=-1){//Sql Server URL = URL.substring(0,URL.lastIndexOf("=")+1); //去掉数据库名称，重新组装[仅适用于多个SqlServer数据库的情况] switch (dbType) { case FRAMEMANAGER: //框架 URL += "framemanager"; break; case WWWMANAGER: //网站 URL += "wwwmanager"; break; default: //知识管理[缺省] URL += "docmanager"; break;} }else if(URL.indexOf("jdbc:mysql")!=-1){//MySQL }else if(URL.indexOf("jdbc:oracle")!=-1){//Oracle } System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>URL:"+URL); conn = java.sql.DriverManager.getConnection(URL, USERNAME, PASSWORD); conn.setAutoCommit(false); //关闭自动提交功能，改为人工提交事务 } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn; } /********************************************* * 获取MA数据库的连接[框架数据库] * @author Liyongbin [Apr 28, 2012 7:11:12 PM] * @return 框架数据库连接 *********************************************/ public Connection getMAConn(){ try { conn = getConn(DBname.FRAMEMANAGER); } catch (Exception e) { e.printStackTrace(); } return conn; } /********************************************* * 获取KM数据库的连接[知识管理系统] * @author Liyongbin [Apr 28, 2012 7:11:13 PM] * @return 知识管理数据库连接 *********************************************/ public Connection getKMConn(){ try { conn = getConn(DBname.DOCMANAGER); } catch (Exception e) { e.printStackTrace(); } return conn; } /********************************************* * 获取网站数据库的连接 * @author Liyongbin [Apr 28, 2012 7:11:15 PM] * @return 网站数据库连接 ********************************************/ public Connection getWWWConn(){ try { conn = getConn(DBname.WWWMANAGER); } catch (Exception e) { e.printStackTrace(); } return conn; } /********************************************** * 关闭数据库参数 * @author Liyongbin [Apr 29, 2012 7:25:31 AM] * @return **********************************************/ public void close(){ try { if(res != null){ res.close(); } if(pst != null){ pst.close(); } if(st != null){ st.close(); } if(cs != null){ cs.close(); } if(conn != null){ conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } }?
1.3 DBOperation.java [ＣＲＵＤ]
package com.smartcom.ma.util.DbUtils;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement; public class DBOperation {private static Connection conn = null;private static PreparedStatement pst = null;private static CallableStatement cs= null;private static ResultSet res = null;private static Statement st = null; /********************************************** * 执行sql语句的增删改 * @author Liyongbin [Apr 28, 2012 7:20:34 PM] * @return ***********************************************/ public static Integer executeSQL(String sql,String[] param) throws SQLException{ Integer result = 0; conn = null; pst = null; try { conn = GetConfig.getInstance().getConn(null); pst = conn.prepareStatement(sql); if(param != null){ for(int i = 0; i < param.length; i++){ pst.setString(i + 1, param[i]); } } result = pst.executeUpdate(); conn.commit();//提交事务 } catch (SQLException e) { conn.rollback();//回滚事务 e.printStackTrace(); }finally{ GetConfig.getInstance().close(); } return result; } /********************************************** * 普通sql查询 * @author Liyongbin [Apr 29, 2012 7:48:11 AM] * @param sql * @param param * @return ***********************************************/ public static Object[][] executeQuery(Connection conn,String sql,String[] param){ ResultSetMetaData metadata = null; //数据的结构数据 Object[][] rets =null; //输出结果 int columnCount=0;//行数 int rowCount = 0; //列数 try { //连接数据库 pst = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); //设置参数 if(param != null){ for(int i = 0; i < param.length; i++){ pst.setString(i + 1, param[i]); } } //执行查询 res = pst.executeQuery(); //取数据结构数据 metadata = res.getMetaData(); //取行数 columnCount = metadata.getColumnCount(); res.last(); //取列数 rowCount = res.getRow(); res.first(); //创建结果数组 rets = new Object[rowCount][columnCount]; //遍历数据集 for(int i=0;i<rowCount;res.next()){ for(int j=0;j<columnCount;j++){ rets[i][j] = res.getObject(j+1); } i++; } } catch (SQLException e) { e.printStackTrace(); }finally{ if(null!=conn){ try {conn.close();} catch (SQLException e) {e.printStackTrace();} } } return rets; } /********************************************** * 普通sql查询 * @author Liyongbin * @param sql * @param param * @return ***********************************************/ public static Object[][] executeQuery(String sql,String[] param){ try { Connection conn = GetConfig.getInstance().getConn(null); return executeQuery(conn,sql,param);} catch (Exception e) { e.printStackTrace();}finally{ if(null!=conn){ try {conn.close();} catch (SQLException e) {e.printStackTrace();} } } return null; } /********************************************* * 获取MA数据库的连接[框架数据库] * @author Liyongbin [Apr 28, 2012 7:11:12 PM] * @return 框架数据库连接 *********************************************/ public static Connection getMAConn(){ Connection conn =null; try { conn = GetConfig.getInstance().getMAConn(); } catch (Exception e) { e.printStackTrace(); } return conn; } /********************************************* * 获取KM数据库的连接[知识管理系统] * @author Liyongbin [Apr 28, 2012 7:11:13 PM] * @return 知识管理数据库连接 *********************************************/ public Connection getKMConn(){ Connection conn =null; try { conn = GetConfig.getInstance().getKMConn(); } catch (Exception e) { e.printStackTrace(); } return conn; } /********************************************* * 获取网站数据库的连接 * @author Liyongbin [Apr 28, 2012 7:11:15 PM] * @return 网站数据库连接 ********************************************/ public Connection getWWWConn(){ Connection conn =null; try { conn = GetConfig.getInstance().getWWWConn(); } catch (Exception e) { e.printStackTrace(); } return conn; } }?