类 Hibernate Session DBUtil

package com.core.util;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.core.exception.ServiceException;


public final class DBUtil {
	
	private static final Log log = LogFactory.getLog(DBUtil.class); 
	
	// 定义一个数据库连接对象		
	private DBUtil() {}
	
	private static final ThreadLocal<Connection> connection = new ThreadLocal<Connection>();   	
	
	public static Connection getConnection() {
		Connection conn =  connection.get();
		if(conn == null){
			try {
				Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
				conn = DriverManager.getConnection("proxool.DB");				
				connection.set(conn);
			} catch (ClassNotFoundException e) {
				 log.error("#ERROR# :创建数据库连接发生异常，请检查！", e); 
			}catch (SQLException e) {
				 log.error("#ERROR# :创建数据库连接发生异常，请检查！", e); 
			}
		}			
		return conn;
	}

	/**
	 * 执行数据库增、删、改操作.
	 * 
	 * @param sql
	 *            待操作的sql字符串
	 * @return 执行结果所影响的行数，异常则返回－1
	 * @throws SQLException 
	 * @throws Exception
	 */
	public static int  executeUpdate(String sql){		
		int exeOk = -1;	
		Statement stmt = null;
		try {	
			stmt = getConnection().createStatement();
			exeOk = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			log.error("#ERROR# :执行SQL语句出错，请检查！\n" + sql, e); 
		}finally{
			close(stmt);
		}
		return exeOk;
	}

	/**
	 * 执行批处理操作一般用于事务
	 * 
	 * @param vSqlStr 传入的被操作sql字符串数组
	 * @return 执行批处理后所影响的行数
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static int[] executeUpdate(List sqlList){
		int updateCounts[] = null;
		Statement stmt = null;
		Connection conn = getConnection();
		try {		
			stmt = conn.createStatement();
			conn.setAutoCommit(false);			
			for (int i = 0; i < sqlList.size(); i++) {
				String sql = sqlList.get(i) + "";
				stmt.addBatch(sql);
			}
			updateCounts = stmt.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.error("#ERROR# :执行数据回滚出错，请检查！", e); 
			}
			log.error("#ERROR# :执行批量SQL语句出错，请检查！", e); 
		}finally{
			close(stmt);
		}
		return updateCounts;
	}
	

	@SuppressWarnings("unchecked")
	public static RsList executeQuery(String query){		
		ResultSet resultSet = null;
		ResultSetMetaData metaData = null;
		RsList rs = null;
		Statement stmt = null;
		try {
			stmt = getConnection().createStatement();
			resultSet = stmt.executeQuery(query);
			int colNumber = 0;					
			LinkedHashMap<String, String> recoMap;
			String colName = "";
			String colValue = "";
			rs = new RsList();
			while(resultSet != null && resultSet.next()){					
				recoMap = new LinkedHashMap<String, String>();
				metaData = resultSet.getMetaData();
				colNumber = metaData.getColumnCount();
				for (int i = 1; i <colNumber+1;i++) {
					colName = metaData.getColumnLabel(i)
							.toLowerCase();
					colValue = resultSet.getString(i);
					if(!StringUtil.isBlank(colValue)){
						recoMap.put(colName, colValue);
					}else{
						recoMap.put(colName, "");
					}
				}
				rs.add(recoMap);
			}			
		} catch (SQLException e) {
			log.error("#ERROR# :执行SQL语句出错，请检查！\n" + query, e); 
		} finally {
			close(stmt,resultSet);
		}
		return rs;
	}
	

	/**
	 * 取得数据库服务器的当前日期(年-月-日 时:分:秒)。	 
	 * @return 数据库服务器的当前日期，java.sql.Timestamp对象。
	 */
	public static Timestamp getCurrentTime(){
		String sql = "select sysdate as datevalue from dual";		
		ResultSet rs = null;
		Timestamp dtRtn = null;
		Statement stmt = null;
		try {		
			stmt = getConnection().createStatement();
			rs = stmt.executeQuery(sql);
			while (rs != null && rs.next()) {
				dtRtn = rs.getTimestamp(1);
			}
		} catch (SQLException e) {
			log.error("#ERROR# :执行SQL语句出错，请检查！\n" + sql, e); 
		} finally {
			close(stmt,rs);
		}
		return dtRtn;
	}

	/**
	 * 取得数据库服务器的当前日期(年-月-日)。
	 * 
	 * @return 数据库服务器的当前日期,java.sql.Date对象。
	 */
	public static Date getCurrentDate(){
		String sql = "select sysdate as datevalue from dual";		
		ResultSet rs = null;
		Date dtRtn = null;
		Statement stmt = null;
		try {	
			stmt = getConnection().createStatement();
			rs = stmt.executeQuery(sql);
			while (rs != null && rs.next()){
				dtRtn = rs.getDate(1);
			}
		} catch (SQLException e) {
			log.error("#ERROR# :执行SQL语句出错，请检查！\n" + sql, e); 
		} finally {
			close(stmt,rs);
		}
		return dtRtn;
	}

	/**
	 * 取得某个序列对象的下一个ID。
	 * 
	 * @param sequence
	 *            该对象对应数据库中的SEQUENCE的名字。
	 * @return 该对象的下一个ID，如果失败返回-1。
	 */
	public static long getNextId(String sql){
		ResultSet rs = null;
		long nextId = 1;
		Statement stmt = null;
		try {
			stmt = getConnection().createStatement();
			rs = stmt.executeQuery("select " + sql
					+ ".nextval as id from dual");
			while(rs != null && rs.next()) {
				nextId = rs.getLong(1);
			}
		} catch (SQLException e) {
			log.error("#ERROR# :执行SQL语句出错，请检查！\n" + sql, e); 
		} finally {
			close(stmt,rs);
		}
		return nextId;
	}
	
	/**
	 * 获取结果集的记录段（用于分页）
	 * @param query 查询字符串
	 * @param rowCount  总记录数
	 * @param currPage  当前页码
	 * @param pageSize  每页记录数
	 * @return  RsList  记录内容列表
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	protected static RsList executeQuery(String query, int rowCount, int currPage,
			int pageSize){

		ResultSet resultSet = null;
		ResultSetMetaData metaData = null;
		Statement stmt = null;
		int startPostion =0;
		int endPostion =0;
		
		RsList rs = new RsList();

		int pageCount = -1;
		try {
     
			if (rowCount <= 0)
				throw new ServiceException("记录总数小于0");

			// 计算总页数
			if (pageSize <= 0)
				pageSize = 10;
			
			pageCount = (rowCount + pageSize - 1) / pageSize;

			// 计算要显示的页码
			if (currPage <= 0)
				currPage = 1;
			if (currPage > pageCount)
				currPage = pageCount;

			// 定位记录开始索引
			if (pageCount > 0){
				startPostion = (currPage - 1) * pageSize + 1;
				endPostion = (currPage - 1) * pageSize + pageSize;
			}else if(pageCount == 0){
				startPostion = 1;
				endPostion = pageSize;
			}
			
			//新加代码
			StringBuffer sql = new StringBuffer();
			sql.append("select * from ( select row_.*, rownum rownum_ from ( ");
			sql.append(query.trim());
			sql.append(") row_ where rownum <= ").append(endPostion).append(" ) where rownum_  >= ").append(startPostion);
		
			stmt = getConnection().createStatement();
			resultSet = stmt.executeQuery(sql.toString());		
			
			int colNumber = 0;					
			LinkedHashMap<String, String> recoMap;
			String colName = "";
			String colValue = "";
			while(resultSet != null && resultSet.next()){				
				recoMap = new LinkedHashMap<String, String>();
				metaData = resultSet.getMetaData();
				colNumber = metaData.getColumnCount();
				for (int i = 1; i < colNumber + 1; i++) {
					colName = metaData.getColumnLabel(i)
							.toLowerCase();
					colValue = resultSet.getString(i);
					if(!StringUtil.isBlank(colValue)){
						recoMap.put(colName, colValue);
					}else{
						recoMap.put(colName, "");
					}
				}
				rs.add(recoMap);
			}
		} catch (SQLException e) {			
			log.error("#ERROR# :执行分页查询出错，请检查！\n", e); 
		} finally {
			close(stmt,resultSet);
		}
		return rs;
	}
	
	
	/**
	 * 获得查询总记录数（用于分页）
	 * @param query 查询字符串
	 * @return  rowCount  -1: 无记录 
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	protected static int getRowCount(String query){
		ResultSet resultSet = null;
		Statement stmt = null;
		int rowCount = -1;		
		try {			
			//新加代码
			StringBuffer sql = new StringBuffer();
			sql.append("select count(*) from ( ").append(query.trim()).append(" )");			
			stmt = getConnection().createStatement();
			resultSet = stmt.executeQuery(sql.toString());
			while(resultSet != null && resultSet.next())
				rowCount = resultSet.getInt(1);			
		} catch (SQLException e) {
			log.error("#ERROR# :执行查询记录总数SQL语句出错，请检查！\n" + query, e); 
		} finally {
			close(stmt,resultSet);
		}
		return rowCount;
	}
	
    /**
     * 获取结果集的记录段（用于分页）
     * @param query 查询语句
     * @param currPage　查询页码
     * @return
     * @throws Exception
     */
	@SuppressWarnings("unchecked")
	public static List executeQuery(String query,String currPage){        
		int pageNum = 1;
		int rowCount = 0;
		List result = new ArrayList();
		RsList rs = new RsList();
		try {		
			if(!StringUtil.isBlank(currPage))
				pageNum = Integer.valueOf(currPage.trim()).intValue();
			rowCount = getRowCount(query);
			if(rowCount > 0 ){
				rs = executeQuery(query,rowCount,pageNum,GolbalParameter.PAGE_SIZE);
			}
		}catch (Exception e) {
			log.error("#ERROR# :执行SQL语句出错，请检查！\n" + query, e); 
		}finally{
			result.add(rowCount);
			result.add(rs);
		}
		return result;
	}
	
	
	public static void beginTransaction(){	
		Connection conn = getConnection();
		try {		
			if(conn != null)
				conn.setAutoCommit(false);
		} catch (SQLException e) {
			log.error("#ERROR# :执行开启事务出错，请检查！\n", e); 
		}
	}
	
	public static void commitTransaction(){		
		Connection conn = connection.get();
		try {			
			if(conn != null){
				conn.commit();
				conn.setAutoCommit(true);
			}
		} catch (SQLException e) {
			log.error("#ERROR# :执行提交事务出错，请检查！\n", e); 
		}
	}
	
	public static void rollbackTransaction(){
		Connection conn = connection.get();
		try {			
			if(conn != null)
				conn.rollback();
		} catch (SQLException e) {
			log.error("#ERROR# :执行回滚事务出错，请检查！\n", e); 
		}
	}
	
	
	/**
	 * 关闭数据操作对象ResultSet
	 * 	 
	 * @param rs 数据结果集对象
	 */
	public static void close(ResultSet rs){
		if (rs != null){
			try {
				rs.close();
			} catch (SQLException e) {			
				log.error("#ERROR# :关闭ResultSet发生异常，请检查！\n", e); 
			}	
			rs = null;
		}
	}
	
	protected static void close(Statement stmt){
		if (stmt != null){
			try {
				stmt.close();
			} catch (SQLException e) {			
				log.error("#ERROR# :关闭Statement发生异常，请检查！\n", e); 
			}	
			stmt = null;
		}
	}
	
	/**
	 * 关闭数据操作对象Statement、ResultSet
	 * 
	 * @param stmt
	 * @param rs   数据结果集对象
	 */
	protected static void close(Statement stmt, ResultSet rs) {

		if (rs != null){
			try {
				rs.close();
			} catch (SQLException e) {			
				log.error("#ERROR# :关闭ResultSet发生异常，请检查！\n", e); 
			}	
			rs = null;
		}		
		
		if (stmt != null){
			try {
				stmt.close();
			} catch (SQLException e) {			
				log.error("#ERROR# :关闭Statement发生异常，请检查！\n", e); 
			}	
			stmt = null;
		}
	}

	/**
	 * 关闭数据库连接对象Connection释放资源
	 */
	public static void close(){				
		Connection conn = connection.get();
		connection.set(null);
		if (conn != null){
			try {
				conn.close();				
			} catch (SQLException e){
				log.error("#ERROR# :关闭数据库连接发生异常，请检查！\n", e); 
			}
			conn = null;
	   }		
	}
}