类 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 &amp;&amp; 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 &amp;&amp; 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 &amp;&amp; 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 &amp;&amp; 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 &amp;&amp; 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 &amp;&amp; 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;	   }			}}