?? dbutils.java
字號:
package com.zhangwei.db;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
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.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import oracle.jdbc.OracleResultSet;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
public class DBUtils {
private static Properties props;
static {
props = new Properties();
// mysql
props.put("driver_class", "com.mysql.jdbc.Driver");
props.put("url", "jdbc:mysql://localhost:3306/bank");
props.put("username", "root");
props.put("password", "root");
// sql2005
// props.put("driver_class", "com.microsoft.sqlserver.jdbc.SQLServerDriver");
// props.put("url", "jdbc:sqlserver://localhost:1433;databaseName=petshop4;selectMethod=cursor");
// props.put("username", "sa");
// props.put("password", "sa");
// oracle
// props.put("driver_class", "oracle.jdbc.OracleDriver");
// props.put("url", "jdbc:oracle:thin:@localhost:1521:XE");
// props.put("username", "scott");
// props.put("password", "tiger");
// sql jtds
// props.put("driver_class", "net.sourceforge.jtds.jdbc.Driver");
// props.put("url", "jdbc:jtds:sqlserver://localhost:1433/pubs");
// props.put("username", "sa");
// props.put("password", "sa");
// odbc
// props.put("driver_class", "sun.jdbc.odbc.JdbcOdbcDriver");
// props.put("url", "jdbc:odbc:test");
}
private static DBUtils me = new DBUtils();
private DBUtils() { }
public static DBUtils getInstance() { return me; }
private static ThreadLocal<Connection> localConn = new ThreadLocal<Connection>();
/**
* 打開一個新的Connection
* @return
*/
public Connection openConnection() {
Connection conn = null;
try {
Class.forName(props.getProperty("driver_class"));
conn = DriverManager.getConnection(
props.getProperty("url"),
props.getProperty("username"),
props.getProperty("password"));
conn.setAutoCommit(false);
} catch (Exception e) {
throw new RuntimeException("不能獲得連接", e);
}
return conn;
}
/**
* 釋放ResultSet,Statement資源,請注意參數順序應為ResultSet -> Statement
* 注意Connection不需要你作為參數傳入
*/
public void closeAll(Object... objects) {
if(objects != null) {
for (int i = 0; i < objects.length; i++) {
Object o = objects[i];
if(o == null) continue;
if(o instanceof ResultSet) { closeRs((ResultSet)o); }
else if (o instanceof Statement) { closeStmt((Statement)o); }
else { throw new RuntimeException("錯誤的資源類型，只能是ResultSet,Statement"); }
}
}
closeCurrentConnection();
}
public void closeAll() {
closeAll(null);
}
public void closeRs(ResultSet rs) {
try { if(rs != null) rs.close(); rs = null; } catch (SQLException e) { }
}
public void closeStmt(Statement stmt) {
try { if(stmt != null) stmt.close(); stmt = null; } catch (SQLException e) { }
}
private void closeConn(Connection conn) {
try { if(conn != null) conn.close(); conn = null; } catch (SQLException e) { }
}
/**
* 獲得與當前線程相關的Connection,如果當前已有,則用此Connection
* @return
*/
public Connection getCurrentConnection() {
Connection conn = localConn.get();
if(conn == null){
conn = openConnection();
localConn.set(conn);
}
return conn;
}
/**
* 關閉當前線程相關的Connection
*
*/
private void closeCurrentConnection() {
Connection conn = localConn.get();
if(conn != null) {
closeConn(conn);
localConn.set(null);
}
}
/**
* 提交事務,當業務方法執行成功后調用
*
*/
public void commit() {
Connection conn = getCurrentConnection();
try { conn.commit(); } catch (SQLException e) { }
}
/**
* 回滾事務,當業務方法執行出現異常后調用
*
*/
public void rollback() {
Connection conn = getCurrentConnection();
try { conn.rollback(); } catch (SQLException e) { }
}
private int getSqlType(Object o) {
if (o == null) return Types.NULL;
if(o instanceof String) {
return Types.VARCHAR;
} else if(o instanceof Boolean) {
return Types.BOOLEAN;
} else if (o instanceof Integer) {
return Types.INTEGER;
} else if (o instanceof Long) {
return Types.BIGINT;
} else if (o instanceof Float) {
return Types.FLOAT;
} else if (o instanceof Double) {
return Types.DOUBLE;
} else if (o instanceof BigDecimal) {
return Types.NUMERIC;
} else if (o instanceof Date) {
return Types.TIMESTAMP;
} else if (o instanceof Short) {
return Types.SMALLINT;
} else if (o instanceof InputStream) { // 僅通過sql server 2005 的測試
return Types.BINARY;
}else {
throw new RuntimeException("沒有此類型");
}
}
/**
* 執行增刪改語句,返回影響行數
* @param sql
* @param params
* @return
*/
public int executeUpdate (String sql, Object... params) {
Connection conn = getCurrentConnection();
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
parseParameters(pstmt, params);
return pstmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException("執行增刪改時失敗",e);
} finally {
closeStmt(pstmt);
}
}
private void parseParameters(PreparedStatement pstmt, Object... params) throws SQLException {
if(params != null){
for(int index = 0; index < params.length; index++ ){
Object o = params[index];
if(o == null){
pstmt.setObject(index+1, null);
} else {
pstmt.setObject(index+1, o, getSqlType(o));
}
}
}
}
/**
* 執行增刪改語句,返回影響行數
* 配合prepare方法使用,適用于執行批量操作
* @param pstmt
* @param params
* @return
*/
public int executeUpdate (PreparedStatement pstmt, Object... params) {
try {
parseParameters(pstmt, params);
return pstmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException("執行增刪改時失敗",e);
}
}
public PreparedStatement prepare(String sql) {
Connection conn = getCurrentConnection();
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
return pstmt;
} catch (SQLException e) {
throw new RuntimeException("創建Statement失敗",e);
}
}
public void addBatch (PreparedStatement pstmt, Object... params) {
try {
parseParameters(pstmt, params);
pstmt.addBatch();
} catch (SQLException e) {
throw new RuntimeException("執行增刪改時失敗",e);
}
}
public void executeBatch(PreparedStatement pstmt){
try {
pstmt.executeBatch();
} catch (SQLException e) {
throw new RuntimeException("執行批處理時出錯",e);
}
}
/**
* 執行查詢,返回ResultSet,注意必須由調用者負責關閉
* @param sql
* @param params
* @return
*/
public ResultSet executeQuery (String sql, Object... params) {
Connection conn = getCurrentConnection();
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
parseParameters(pstmt, params);
return pstmt.executeQuery();
} catch (SQLException e) {
throw new RuntimeException("執行查詢時失敗",e);
}
}
/**
* 執行查詢,返回ResultSet,注意必須由調用者負責關閉
* 配合prepare方法使用,適用于執行批量操作
* @param sql
* @param params
* @return
*/
public ResultSet executeQuery (PreparedStatement pstmt, Object... params) {
try {
parseParameters(pstmt, params);
return pstmt.executeQuery();
} catch (SQLException e) {
throw new RuntimeException("執行查詢時失敗",e);
}
}
/**
* 根據異常判斷是否是因為資源被其他會話以NOWAIT方式鎖定
* 僅用于Oracle
* @param e
* @return
*/
public boolean checkIsLock (Exception e) {
if(e == null) {
return false;
}
if(e instanceof SQLException) {
SQLException se = (SQLException)e.getCause();
return (se.getErrorCode() == 54);// 資源被其他會話以NOWAIT方式鎖定
}
Exception cause = (Exception)e.getCause();
if(cause != null) {
if(cause instanceof SQLException) {
SQLException se = (SQLException)cause.getCause();
return (se.getErrorCode() == 54);// 資源被其他會話以NOWAIT方式鎖定
}
}
return false;
}
class IgnoreCaseHashMap extends HashMap {
@Override
public Object put(Object key, Object value) {
return super.put(key.toString().toLowerCase(), value);
}
@Override
public Object get(Object key) {
return super.get(key.toString().toLowerCase());
}
}
public List list (String sql, Object... params) {
Connection conn = getCurrentConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = conn.prepareStatement(sql);
parseParameters(pstmt, params);
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
List total = new ArrayList();
while(rs.next()) {
Map m = new IgnoreCaseHashMap();
for(int i = 0; i < columnCount; i++) {
parseResultSet(rs, rsmd, m, i+1);
}
total.add(m);
}
return total;
} catch (SQLException e) {
throw new RuntimeException("執行查詢時失敗",e);
} finally {
closeRs(rs);
closeStmt(pstmt);
}
}
private void parseResultSet(ResultSet rs, ResultSetMetaData rsmd, Map m, int index) throws SQLException {
String columnName = rsmd.getColumnName(index);
// 針對日期統一返回java.util.Date,針對整數統一返回Integer,小數統一返回BigDecimal
if(rsmd.getColumnType(index) == Types.DATE ||
rsmd.getColumnType(index) == Types.TIME ||
rsmd.getColumnType(index) == Types.TIMESTAMP) {
m.put(columnName, getUtilDate(rs.getTimestamp(columnName)));
} else if (rsmd.getColumnType(index) == Types.SMALLINT ||
rsmd.getColumnType(index) == Types.INTEGER ||
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -