//http://wing929.javaeye.com/blog/185475
package com.hospital.dao.tools;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import org.apache.log4j.Logger;
/**
* 數(shù)據(jù)庫(kù)操作管理類
*
* @author Harlyhood
*
*/
public class DBManager {
// --------------------------------------------------------- Instance
private static Logger logger = Logger.getLogger(DBManager.class);
// --------------------------------------------------------- Methods
// 數(shù)據(jù)庫(kù)連接對(duì)象
private Connection con;
// SQL語(yǔ)句對(duì)象
private Statement stmt;
// 帶參數(shù)的Sql語(yǔ)句對(duì)象
private PreparedStatement pstmt;
// 記錄集對(duì)象
private ResultSet rs;
// 數(shù)據(jù)連接管理（連接池對(duì)象）
private DBConnectionManager dcm = null;
/** ***********************手動(dòng)設(shè)置的連接參數(shù)********************************* */
@SuppressWarnings("unused")
private static String _DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
@SuppressWarnings("unused")
private static String _URL = "jdbc:sqlserver://localhost:1433;database=Hospital_AI_DB;characterEncoding=gb2312";
@SuppressWarnings("unused")
private static String _USER_NA = "sa";
@SuppressWarnings("unused")
private static String _PASSWORD = "";
/** ********************************************************************** */
// 默認(rèn)構(gòu)造
public DBManager() {
}
/** ****************************************************************************************** */
/**
* **************************************** 數(shù)據(jù)庫(kù)連接初始化
* ***********************************
*/
/** ****************************************************************************************** */
/**
* 得到一個(gè)默認(rèn)的數(shù)據(jù)庫(kù)連接[從 com.hospital.dao.tools.db.properties文件初始化]
*
* @throws Exception
*/
private void getConnection() {
logger.info("###############open:::::從默認(rèn)的配置文件得到一個(gè)數(shù)據(jù)庫(kù)連接");
// 獲取一個(gè)連接池管理類的實(shí)例
dcm = DBConnectionManager.getInstance();
// 得到一個(gè)數(shù)據(jù)庫(kù)連接
con = dcm.getConnection("mysql");
try {
con.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 從指定參數(shù)得到一個(gè)連接對(duì)象
*
* @param driver
* @param url
* @param user_na
* @param password
* @throws Exception
*/
public void getConnection(String driver, String url, String user_na,
String password) throws Exception {
try {
logger.info("###############open:::::從指定配置中得到一個(gè)數(shù)據(jù)庫(kù)連接");
Class.forName(driver);
con = DriverManager.getConnection(url, user_na, password);
} catch (ClassNotFoundException ex) {
logger
.info("###############Error[com.hospital.dao.tools.DBManager^^^Method:getConnection^^^Line:81]找不到類驅(qū)動(dòng)類: "
+ driver);
throw ex;
} catch (SQLException ex) {
logger
.info("###############Error[com.hospital.dao.tools.DBManager^^^Method:getConnection^^^Line:81]加載類: "
+ driver + " 時(shí)出現(xiàn) SQLException 異常");
throw ex;
}
}
/** ****************************************************************************************** */
/**
* **************************************** 數(shù)據(jù)庫(kù)操作方法
* ***********************************
*/
/** ****************************************************************************************** */
/**
* 執(zhí)行SQL語(yǔ)句操作(更新數(shù)據(jù) 無(wú)參數(shù))
*
* @param strSql
* SQL語(yǔ)句
* @throws Exception
*/
public boolean executeUpdate(String strSql) throws SQLException {
getConnection();
// getConnection(_DRIVER,_URL,_USER_NA,_PASSWORD);
boolean flag = false;
stmt = con.createStatement();
logger.info("###############::執(zhí)行SQL語(yǔ)句操作(更新數(shù)據(jù) 無(wú)參數(shù)):" + strSql);
try {
if (0 < stmt.executeUpdate(strSql)) {
close_DB_Object();
flag = true;
con.commit();
}
} catch (SQLException ex) {
logger
.info("###############Error DBManager Line126::執(zhí)行SQL語(yǔ)句操作(更新數(shù)據(jù) 無(wú)參數(shù)):"
+ strSql + "失敗!");
flag = false;
con.rollback();
throw ex;
}
return flag;
}
/**
* 執(zhí)行SQL語(yǔ)句操作(更新數(shù)據(jù) 有參數(shù))
*
* @param strSql
* sql指令
* @param prams
* 參數(shù)列表
* @return
* @throws SQLException
*/
public boolean executeUpdate(String strSql, HashMap<Integer, Object> prams)
throws SQLException, ClassNotFoundException {
getConnection();
// getConnection(_DRIVER,_URL,_USER_NA,_PASSWORD);
boolean flag = false;
try {
pstmt = con.prepareStatement(strSql);
setParamet(pstmt, prams);
logger.info("###############::執(zhí)行SQL語(yǔ)句操作(更新數(shù)據(jù) 有參數(shù)):" + strSql);
if (0 < pstmt.executeUpdate()) {
close_DB_Object();
flag = true;
con.commit();
}
} catch (SQLException ex) {
logger
.info("###############Error DBManager Line121::執(zhí)行SQL語(yǔ)句操作(更新數(shù)據(jù) 無(wú)參數(shù)):"
+ strSql + "失敗!");
flag = false;
con.rollback();
throw ex;
} catch (ClassNotFoundException ex) {
logger
.info("###############Error DBManager Line152::執(zhí)行SQL語(yǔ)句操作(更新數(shù)據(jù) 無(wú)參數(shù)):"
+ strSql + "失敗! 參數(shù)設(shè)置類型錯(cuò)誤!");
con.rollback();
throw ex;
}
return flag;
}
/**
* 執(zhí)行SQL語(yǔ)句操作(查詢數(shù)據(jù) 無(wú)參數(shù))
*
* @param strSql
* SQL語(yǔ)句
* @return 數(shù)組對(duì)象列表
* @throws Exception
*/
public ArrayList<HashMap<Object, Object>> executeSql(String strSql)
throws Exception {
getConnection();
// getConnection(_DRIVER,_URL,_USER_NA,_PASSWORD);
stmt = con.createStatement();
logger.info("###############::執(zhí)行SQL語(yǔ)句操作(查詢數(shù)據(jù)):" + strSql);
rs = stmt.executeQuery(strSql);
con.commit();
if (null != rs) {
return convertResultSetToArrayList(rs);
}
close_DB_Object();
return null;
}
/**
* 執(zhí)行SQL語(yǔ)句操作(查詢數(shù)據(jù) 有參數(shù))
*
* @param strSql
* SQL語(yǔ)句
* @param prams
* 參數(shù)列表
* @return 數(shù)組對(duì)象列表
* @throws Exception
*/
public ArrayList<HashMap<Object, Object>> executeSql(String strSql,
HashMap<Integer, Object> prams) throws Exception {
getConnection();
// getConnection(_DRIVER,_URL,_USER_NA,_PASSWORD);
pstmt = con.prepareStatement(strSql);
setParamet(pstmt, prams);
logger.info("###############::執(zhí)行SQL語(yǔ)句操作(查詢數(shù)據(jù)):" + strSql);
rs = pstmt.executeQuery();
con.commit();
if (null != rs) {
return convertResultSetToArrayList(rs);
}
return null;
}
/**
* 執(zhí)行存儲(chǔ)過(guò)程(查詢數(shù)據(jù) 無(wú)參數(shù))
*
* @param procName
* 存儲(chǔ)過(guò)程名稱
* @return 數(shù)組列表對(duì)象
* @throws Exception
*/
public ArrayList<HashMap<Object, Object>> executeProcedureQuery(
String procName) throws Exception {
getConnection();// 獲取連接
String callStr = "{call " + procName + "}";// 構(gòu)造執(zhí)行存儲(chǔ)過(guò)程的sql指令
CallableStatement cs = con.prepareCall(callStr);
logger.info("###############::執(zhí)行存儲(chǔ)過(guò)程(查詢數(shù)據(jù)):" + procName);
rs = cs.executeQuery();
con.commit();
cs.close();
close_DB_Object();
return convertResultSetToArrayList(rs);
}
/**
* 執(zhí)行存儲(chǔ)過(guò)程(查詢數(shù)據(jù),帶參數(shù))返回結(jié)果集合
*
* @param procName
* 存儲(chǔ)過(guò)程名稱
* @param parameters
* 參數(shù)對(duì)象數(shù)組
* @param al
* 數(shù)組列表對(duì)象
* @return 數(shù)組列表對(duì)象
* @throws Exception
*/
public ArrayList<HashMap<Object, Object>> executeProcedureQuery(
String procName, Object[] parameters) throws Exception {
int parameterPoint = 0;
// 獲取存儲(chǔ)過(guò)程信息列表集合
ArrayList<HashMap<Object, Object>> procedureInfo = getProcedureInfo(procName);
// 獲取存儲(chǔ)過(guò)程的完全名稱
String procedureCallName = getProcedureCallName(procName,parameters.length);
// 獲取連接對(duì)象
getConnection();
// 初始化 存儲(chǔ)過(guò)程 執(zhí)行對(duì)象
CallableStatement cs = con.prepareCall(procedureCallName);
// 參數(shù)下標(biāo)變量
int index = 0;
// 獲取 存儲(chǔ)過(guò)程信息列表集合的 迭代器 對(duì)象
Iterator<HashMap<Object, Object>> iter = procedureInfo.iterator();
// 遍歷存儲(chǔ)過(guò)程信息列表集合
while (iter.hasNext()) {
HashMap<Object, Object> hm = iter.next();
parameterPoint++;
// 如果參數(shù)是輸入?yún)?shù) way = 0
if (hm.get("WAY").equals("0")) {
// 設(shè)置參數(shù)到cs
cs.setObject(parameterPoint, parameters[index]);
// 參數(shù)下標(biāo)+1
index++;
}
}
// 釋放這個(gè)對(duì)象,做為第二次使用
procedureInfo = null;
logger.info("###############::執(zhí)行存儲(chǔ)過(guò)程(查詢數(shù)據(jù)):::::" + procedureCallName);
rs = cs.executeQuery();
con.commit();
procedureInfo = convertResultSetToArrayList(rs);
cs.close();
close_DB_Object();
return procedureInfo;
}
/**
* 執(zhí)行存儲(chǔ)過(guò)程(更新，查詢數(shù)據(jù)[簡(jiǎn)單查詢、非紀(jì)錄集]，返回輸出參數(shù)[非紀(jì)錄集])
*
* @param procName
* 存儲(chǔ)過(guò)程名稱
* @param parameters
* 參數(shù)對(duì)象數(shù)組
* @param os
* 輸出參數(shù)對(duì)象數(shù)組
* @return 輸出參數(shù)對(duì)象數(shù)組
* @throws Exception
*/
public Object[] executeProcedureUpdate(String procName, Object[] parameters)
throws Exception {
logger.info("------------------------------------------------------------------------------------------------------");
logger.info(" Run --> executeProcedureUpdate ############## 正在執(zhí)行 存儲(chǔ)過(guò)程: " + procName +" ##############");
CallableStatement cs = null;
Object []returnVal = null;
try {
// 獲取 存儲(chǔ)過(guò)程 調(diào)用全名
String fullPCallName = getProcedureCallName(procName,parameters.length);
logger.info(" Run --> executeProcedureUpdate # 存儲(chǔ)過(guò)程命令: " + fullPCallName +" #");
//獲取存儲(chǔ)過(guò)程參數(shù)信息
ArrayList<HashMap<Object, Object>> p_Call_Info_List = getProcedureInfo(procName);
//獲取連接
getConnection();
//創(chuàng)建 存儲(chǔ)過(guò)程 執(zhí)行對(duì)象
cs = con.prepareCall(fullPCallName);
//數(shù)組下標(biāo)
int index = 1;
//輸出參數(shù)下標(biāo) 紀(jì)錄
ArrayList<Integer> outPutIndexList = new ArrayList<Integer>();
logger.info(" Run --> executeProcedureUpdate # 參數(shù)個(gè)數(shù)是: " + parameters.length +" #");
for(HashMap<Object,Object> tempHash:p_Call_Info_List)
{
if("0".equals(tempHash.get("WAY")))
{
//設(shè)置輸入?yún)?shù)
cs.setObject(index, parameters[index-1]);
logger.info(" Run --> executeProcedureUpdate # 輸入 Input: 編號(hào):" + index +" 值: "+parameters[index-1]+" 類型: "+parameters[index-1].getClass()+" #");
}
else
{
//注冊(cè)輸出參數(shù)
cs.registerOutParameter(index, getDataType(tempHash.get("TYPENAME").toString()));
//紀(jì)錄輸出參數(shù)的下標(biāo)
outPutIndexList.add(index);
logger.info(" Run --> executeProcedureUpdate # 輸出 OutPut: 編號(hào):" + index +" 值: "+parameters[index-1]+" 類型: "+parameters[index-1].getClass()+" #");
}
index++;
}
logger.info(" Run --> executeProcedureUpdate # 參數(shù)設(shè)置完畢,正在執(zhí)行中 
: #");
//-------------------- 執(zhí)行 -----------------
if(!cs.execute())
{
returnVal = new Object[outPutIndexList.size()];
logger.info(" Run --> executeProcedureUpdate # 執(zhí)行成功! : #");
//取輸 出參數(shù)的 返回值
for(int i = 0 ;i<outPutIndexList.size();i++)
{
returnVal[i] = cs.getObject(outPutIndexList.get(i));
logger.info(" Run --> executeProcedureUpdate # 返回值 "+(i+1)+" "+returnVal[i]+" #");
}
con.commit();//提交
}
} catch (Exception e) {
logger.info(" Run --> executeProcedureUpdate # 執(zhí)行失敗!事務(wù)回滾中 : #");
con.rollback();
throw e;
}
logger.info("------------------------------------------------------------------------------------------------------");
return returnVal;
}
/** ****************************************************************************************** */
/**
* ********************************* 小工具
* ************************************************
*/
/** ****************************************************************************************** */
/**
* 關(guān)閉數(shù)據(jù)對(duì)象
*/
public void close_DB_Object() {
logger.info("###############close:::::關(guān)閉連接對(duì)象，語(yǔ)句對(duì)象，記錄集對(duì)象");
if (null != rs) {
try {
rs.close();
} catch (SQLException ex) {
rs = null;
}
}
if (null != stmt) {
try {
stmt.close();
} catch (SQLException ex) {
stmt = null;
}
}
if (null != pstmt) {
try {
pstmt.close();
} catch (SQLException ex) {
pstmt = null;
}
}
if (con != null) {
dcm.freeConnection("mysql", con);
}
}
/**
* 設(shè)置Sql 指令參數(shù)
*
* @param p_stmt
* PreparedStatement
* @param pramets
* HashMap
*/
private PreparedStatement setParamet(PreparedStatement p_stmt,
HashMap<Integer, Object> pramets) throws ClassNotFoundException,
SQLException {
// 如果參數(shù)為空
if (null != pramets) {
// 如果參數(shù)個(gè)數(shù)為0
if (0 <= pramets.size()) {
for (int i = 1; i <= pramets.size(); i++) {
try {
// 字符類型 String
if (pramets.get(i).getClass() == Class
.forName("java.lang.String")) {
p_stmt.setString(i, pramets.get(i).toString());
}
// 日期類型 Date
if (pramets.get(i).getClass() == Class
.forName("java.sql.Date")) {
p_stmt.setDate(i, java.sql.Date.valueOf(pramets
.get(i).toString()));
}
// 布爾類型 Boolean
if (pramets.get(i).getClass() == Class
.forName("java.lang.Boolean")) {
p_stmt.setBoolean(i, (Boolean) (pramets.get(i)));
}
// 整型 int
if (pramets.get(i).getClass() == Class
.forName("java.lang.Integer")) {
p_stmt.setInt(i, (Integer) pramets.get(i));
}
// 浮點(diǎn) float
if (pramets.get(i).getClass() == Class
.forName("java.lang.Float")) {
p_stmt.setFloat(i, (Float) pramets.get(i));
}
// 雙精度型 double
if (pramets.get(i).getClass() == Class
.forName("java.lang.Double")) {
p_stmt.setDouble(i, (Double) pramets.get(i));
}
} catch (ClassNotFoundException ex) {
throw ex;
} catch (SQLException ex) {
throw ex;
}
}
}
}
return p_stmt;
}
/**
* 轉(zhuǎn)換記錄集對(duì)象為數(shù)組列表對(duì)象
*
* @param rs
* 紀(jì)錄集合對(duì)象
* @return 數(shù)組列表對(duì)象
* @throws Exception
*/
private ArrayList<HashMap<Object, Object>> convertResultSetToArrayList(
ResultSet rs) throws Exception {
logger.info("###############::轉(zhuǎn)換記錄集對(duì)象為數(shù)組列表對(duì)象");
// 獲取rs 集合信息對(duì)象
ResultSetMetaData rsmd = rs.getMetaData();
// 創(chuàng)建數(shù)組列表集合對(duì)象
ArrayList<HashMap<Object, Object>> tempList = new ArrayList<HashMap<Object, Object>>();
HashMap<Object, Object> tempHash = null;
// 填充數(shù)組列表集合
while (rs.next()) {
// 創(chuàng)建鍵值對(duì)集合對(duì)象
tempHash = new HashMap<Object, Object>();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
// 遍歷每列數(shù)據(jù)，以鍵值形式存在對(duì)象tempHash中
tempHash.put(rsmd.getColumnName(i + 1).toUpperCase(), rs
.getString(rsmd.getColumnName(i + 1)));
}
// 第一個(gè)鍵值對(duì)，存儲(chǔ)在tempList列表集合對(duì)象中
tempList.add(tempHash);
}
close_DB_Object();// 關(guān)閉相關(guān)鏈接
return tempList;// 返回填充完畢的數(shù)組列表集合對(duì)象
}
/**
* 從數(shù)據(jù)庫(kù)得到存儲(chǔ)過(guò)程信息
*
* @param procName
* 存儲(chǔ)過(guò)程名稱
* @return 數(shù)組列表對(duì)象
* @throws Exception
*/
private ArrayList<HashMap<Object, Object>> getProcedureInfo(String procName)
throws Exception {
return this.executeSql("select Syscolumns.isoutparam as Way,systypes.name as TypeName from sysobjects,syscolumns,systypes where systypes.xtype=syscolumns.xtype and syscolumns.id=sysobjects.id and sysobjects.name='"
+ procName + "' order by Syscolumns.isoutparam");
}
/**
* 從數(shù)據(jù)庫(kù)得到存儲(chǔ)過(guò)程參數(shù)個(gè)數(shù)
*
* @param procName
* 存儲(chǔ)過(guò)程名稱
* @return 數(shù)組列表對(duì)象
* @throws Exception
*/
@SuppressWarnings("unused")
private int getParametersCount(String procName) throws Exception {
int returnVal = 0;
for (HashMap<Object, Object> tempHas : this
.executeSql("select count(*) as RowsCount from sysobjects,syscolumns,systypes where systypes.xtype=syscolumns.xtype and syscolumns.id=sysobjects.id and sysobjects.name='"
+ procName + "'")) {
returnVal = Integer.parseInt(tempHas.get("ROWSCOUNT").toString());
}
return returnVal;
}
/**
* 得到調(diào)用存儲(chǔ)過(guò)程的全名
*
* @param procName
* 存儲(chǔ)過(guò)程名稱
* @return 調(diào)用存儲(chǔ)過(guò)程的全名
* @throws Exception
*/
private String getProcedureCallName(String procName, int prametCount)
throws Exception {
String procedureCallName = "{call " + procName;
for (int i = 0; i < prametCount; i++) {
if (0 == i) {
procedureCallName = procedureCallName + "(?";
}
if (0 != i) {
procedureCallName = procedureCallName + ",?";
}
}
procedureCallName = procedureCallName + ")}";
return procedureCallName;
}
/**
* 得到數(shù)據(jù)類型的整型值
*
* @param typeName
* 類型名稱
* @return 數(shù)據(jù)類型的整型值
*/
private int getDataType(String typeName) {
if (typeName.equals("varchar"))
return Types.VARCHAR;
if (typeName.equals("int"))
return Types.INTEGER;
if (typeName.equals("bit"))
return Types.BIT;
if (typeName.equals("float"))
return Types.FLOAT;
return 0;
}
// 設(shè)置驅(qū)動(dòng)路徑
@SuppressWarnings("static-access")
public void set_DRIVER(String _DRIVER) {
this._DRIVER = _DRIVER;
}
// 設(shè)置數(shù)據(jù)庫(kù)密碼
@SuppressWarnings("static-access")
public void set_PASSWORD(String _PASSWORD) {
this._PASSWORD = _PASSWORD;
}
// 設(shè)置數(shù)據(jù)庫(kù)連接字符串
@SuppressWarnings("static-access")
public void set_URL(String _URL) {
this._URL = _URL;
}
// 設(shè)置數(shù)據(jù)庫(kù)用戶名
@SuppressWarnings("static-access")
public void set_USER_NA(String _USER_NA) {
this._USER_NA = _USER_NA;
}
}
posted on 2010-09-11 05:33
SIMONE 閱讀(2279)
評(píng)論(1) 編輯 收藏 所屬分類:
JAVA