数据库封装类新版

前段时间写了个数据库操作的JDBC封装类,经过实际的项目测试,进行了改良.

import java.io.Serializable;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import javax.servlet.http.HttpServletRequest;import com.manager.system.pojo.Config;/** * 数据操作供给类<p> * 基于JDBC操作数据库的封装 * @author tuyan * @version 1.0 */public abstract class DataAccessConcrete implements Serializable {// 连接对象private Connection conn = null;// 事务相关private boolean unitOfWork = false;//页面显示记录数public static int pageSize;/** * 获得连接对象 * * @return * @throws DataOperateException */public Connection getConnection() throws DataOperateException {try {if (conn == null || conn.isClosed()) {conn = ConnectionJDBC.getConnection();}} catch (SQLException e) {throw new DataOperateException(e, DataOperateException.ERR_CONNECTION);}if (DataAccessConcrete.pageSize == 0){ResultSet rs = this.doQuery("select * from t_config where configType = 'USERTYPE'");try {if (rs.next()){DataAccessConcrete.pageSize = rs.getInt("pageSize");}else{DataAccessConcrete.pageSize = Config.DEFAULT_PAGE_SIZE;}System.out.println("page size: " + pageSize);} catch (SQLException e1) {throw new DataOperateException(e1, DataOperateException.ERR_QUERY);}}return conn;}/** * 开始事务 * * @param supportTran * true表示显示调用事务，false表示隐式事务 */public void beginTran(boolean supportTran) throws DataOperateException {unitOfWork = supportTran;try {getConnection();if (supportTran) {conn.setAutoCommit(false);} else {conn.setAutoCommit(true);}} catch (SQLException e) {throw new DataOperateException(e, DataOperateException.ERR_TRAN_COMMIT);}}/** * 事务的提交<br> * 提交当前开启的事务 * * @throws ProviderException */public void commitTran() throws DataOperateException {if (unitOfWork) {try {conn.commit();} catch (SQLException e) {throw new DataOperateException(e, DataOperateException.ERR_TRAN_COMMIT);} finally {closeConnection();}}}/** * 回滚事务<br> * 回滚当前开启的事务<br> * * @return * @throws ProviderException */public boolean rollBackTran() throws DataOperateException {boolean returnValue = false;try {conn.rollback();closeConnection();returnValue = true;} catch (SQLException e) {throw new DataOperateException(e, DataOperateException.ERR_TRAN_ROOLBACK);}return returnValue;}/** * 获得预编译对象 * * @param sql * 相应的SQL语句,带参数的sql，参数为? * @return * @throws SQLException */public PreparedStatement createStatement(String sql)throws ProviderException {PreparedStatement pstmt = null;try {if (conn.isClosed()){getConnection();}pstmt = conn.prepareStatement(sql);} catch (SQLException e) {throw new ProviderException(e);}return pstmt;}/** * 获得记录集对象 * * @param sql * 固定的sql语句 * @return * @throws SQLException */public ResultSet doQuery(String sql) throws DataOperateException {System.out.println("manager select: " + sql);ResultSet rs = null;try {if (conn.isClosed()){getConnection();}rs = conn.createStatement().executeQuery(sql);} catch (SQLException e) {throw new DataOperateException(e, DataOperateException.ERR_QUERY);}return rs;}/** * 根据给定的参数值和sql语句查询数据 * @param sql * @param params * @return * @throws ProviderException */public ResultSet doQuery(String sql, Object[] params)throws ProviderException {if (params == null || params.length == 0) {throw new DataOperateException(null, DataOperateException.ERR_QUERY);}System.out.println("manager select: " + sql);ResultSet rs = null;PreparedStatement pstmt = createStatement(sql);try {for (int i = 0; i < params.length; i++) {pstmt.setObject(i + 1, params[i]);}rs = pstmt.executeQuery();} catch (SQLException e) {throw new DataOperateException(e, DataOperateException.ERR_QUERY);}return rs;}/** * 对于相应表中的记录的删除 * * @param table * 要做删除的表 * @param id * 标识符 * @return * @throws ProviderException */public int doDelete(String table, Object id, String key) throws ProviderException {if (id == null) {throw new DataOperateException(null, DataOperateException.ERR_QUERY);}int i = 0;String sql = "delete " + table + " where " + key + " = ?";System.out.println("manager delete: " + sql);PreparedStatement pstmt = createStatement(sql);try {pstmt.setObject(1, id);i = pstmt.executeUpdate();} catch (SQLException e) {throw new DataOperateException(null, DataOperateException.ERR_QUERY);} finally {closeStatement(pstmt);}return i;}/** * 对于相应表中的记录的批量删除 * * @param table * 要做删除的表 * @param ids * 标识符数组 * @return * @throws ProviderException */public int doDelete(String table, Object[] ids, String key) throws ProviderException {if (ids == null || ids.length == 0) {throw new DataOperateException(null, DataOperateException.ERR_DELETE);}int i = 0;for (int j = 0; j < ids.length; j++) {i += doDelete(table, ids[i], key);}return i;}/** * 更新语句<p> * 根据给定的参数和sql语句更新数据库中相应的记录 * @param sql insert，update语句 * @param params 参数数组 * @return * @throws ProviderException */public int doUpdate(String sql, Object[] params) throws ProviderException{if (params == null || params.length == 0) {throw new DataOperateException(null, DataOperateException.ERR_QUERY);}System.out.println("manager update: " + sql);int j = 0;PreparedStatement pstmt = createStatement(sql);try {for (int i = 0; i < params.length; i++) {pstmt.setObject(i + 1, params[i]);}j = pstmt.executeUpdate();} catch (SQLException e) {throw new DataOperateException(e, DataOperateException.ERR_QUERY);} finally {closeStatement(pstmt);closeConnection();}return j;}/** * 产生一个页面的模板类<br> * 这个模板类中存放了页面相应信息<br> * <code>totalPages</code> 总的页数<br> * <code>fistPageNo</code> 第一页<br> * <code>bottomPageNo</code> 最后一页<br> * <code>previousPageNo</code> 上一页<br> * <code>nextPageNo</code> 下一页<br> * @param totalRecords 总的记录数 * @param list 要显示的数据集合 * @param pageNo 页码 * @param pageSize 页的大小 * @return * @throws ProviderException */public PageModel doPage(int totalRecords, java.util.List list, int pageNo, int pageSize) throws DataOperateException{if (totalRecords == 0 || list == null || list.isEmpty() || pageNo == 0 || pageSize == 0){throw new DataOperateException(null, DataOperateException.ERR_QUERY);}PageModel pageModel = new PageModel();pageModel.setList(list);pageModel.setPageNo(pageNo);pageModel.setPageSize(pageSize);pageModel.setTotalRecords(totalRecords);return pageModel;}/** * 产生一个页面的模板类<br> * 这个模板类中存放了页面相应信息<br> * 将这个模板类放入HttpServletRequest对象中<br> * <code>totalPages</code> 总的页数<br> * <code>fistPageNo</code> 第一页<br> * <code>bottomPageNo</code> 最后一页<br> * <code>previousPageNo</code> 上一页<br> * <code>nextPageNo</code> 下一页<br> * @param totalRecords 总的记录数 * @param list 要显示的数据集合 * @param pageNo 页码 * @param pageSize 页的大小 * @param request 上下文对象 * @throws ProviderException */public void doPage(int totalRecords, java.util.List list, int pageNo, int pageSize, HttpServletRequest request) throws DataOperateException{if (totalRecords == 0 || list == null || list.isEmpty() || pageNo == 0 || pageSize == 0){throw new DataOperateException(null, DataOperateException.ERR_QUERY);}PageModel pageModel = new PageModel();pageModel.setList(list);pageModel.setPageNo(pageNo);pageModel.setPageSize(pageSize);pageModel.setTotalRecords(totalRecords);request.setAttribute("pageModel", pageModel);}/** * 查出相应表的所有记录 * * @param table * 表名 * @return * @throws SQLException */public ResultSet getResultSet(String table) throws DataOperateException {String sql = "select * from " + table;return doQuery(sql);}protected String getPaginationSql(String table, int pageNo, String key) {String sql = "";if (pageNo < 1){pageNo = 1;}if (pageSize == 0){pageSize = 10;}if (pageNo == 1){sql = "select top " + pageSize + " * from " + table;}else{int newPage = (pageNo - 1) * pageSize;sql = "select top " + pageSize + " * from " + table +" where " + key + " > (" +"select max(" + key + ") from (" +"select top " + newPage + " * from " + table + " order by " + key + ") a)";}return sql;}/** * 获得相应表的所有记录数 * * @param table * 表名称 * @return * @throws SQLException */public long getRecordCount(String table) throws DataOperateException {String sql = "select count(*) as counts from " + table;ResultSet rs = doQuery(sql);long count = 0l;try {if (rs.next()) {count = rs.getLong("counts");}} catch (SQLException e) {throw new DataOperateException(e, DataOperateException.ERR_QUERY);} finally {closeResultSet(rs);}return count;}/** * 关闭记录集对象 * * @param rs */public void closeResultSet(ResultSet rs) throws DataOperateException {if (rs != null) {try {rs.close();} catch (SQLException e) {throw new DataOperateException(e, DataOperateException.ERR_CLOSE);}}}/** * 关闭语句对象<br> * Statement、ParperedStatement * * @param stmt */public void closeStatement(Statement stmt) throws DataOperateException {if (stmt != null) {try {stmt.close();} catch (SQLException e) {throw new DataOperateException(e, DataOperateException.ERR_CLOSE);}}}/** * 关闭连接对象 */public void closeConnection() throws DataOperateException {try {if (conn != null && !conn.isClosed()) {conn.close();}} catch (SQLException e) {throw new DataOperateException(e, DataOperateException.ERR_CLOSE);}}}