类 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 intexecuteupdate(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每页记录数 * @returnrslist记录内容列表 * @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 查询字符串 * @returnrowcount-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; }}}
页:
[1]