dbutils.java

DWR 3级联动代码 基于DWR 的整合 是一个不错的例子

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 ||