JDBC多数据库连接的兑现
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文件的根目录
// Server
STATUS = 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();
}
// MySql
if(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();
}
// Oracle
if(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;
}
}