一个简单的JDBC操作对象的封装

来源：互联网发布：捷易通软件可靠吗编辑：程序博客网时间：2024/05/21 17:45

对于JDBC操作数据库的项目要求,自己写了一个JDBC操作对象的封装类,可以继承这个类来使用其中所提供的方法,也可以根据需求,不将其建为抽象类,源码如下,大家给点参考:

package org.developtools.db;

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 org.developtools.db.pool.DBConnectionManager;
import org.developtools.util.PageModel;
/**
* 数据操作供给类
* 基于JDBC操作数据库的封装
* @author tuyan
* @version 1.0
*/
public abstract class DataAccessConcrete implements Serializable {

// 连接对象
private Connection conn = null;
// 事务相关
private boolean unitOfWork = false;

/**
* 从数据库连接池中获得连接对象
*
* @return
* @throws SQLException
*/
public Connection getConnection() throws DataOperateException {
  try {
   if (conn == null || conn.isClosed()) {
    conn = DBConnectionManager.getConnection();
   }
  } catch (SQLException e) {
   throw new DataOperateException(e, DataOperateException.ERR_CONNECTION);
  }
  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);
  }
}

/**
* 事务的提交 
* 提交当前开启的事务
*
* @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();
 }
 }
}

/**
* 回滚事务 
* 回滚当前开启的事务 
*
* @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 {
   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 {
 ResultSet rs = null;
 try {
 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);
 }
 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);
 } finally {
 closeStatement(pstmt);
 }

  return rs;
}
/**
* 对于相应表中的记录的删除
*
* @param table
*            要做删除的表
* @param id
*            标识符
* @return
* @throws ProviderException
*/
public int doDelete(String table, Object id) throws ProviderException {
  if (id == null) {
   throw new DataOperateException(null, DataOperateException.ERR_QUERY);
  }
  int i = 0;
  String sql = "delete from " + table + " where id = ?";
  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) 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]);
 }
 return i;
}
/**
* 更新语句
* 根据给定的参数和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);
 }
 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;
}
/**
* 产生一个页面的模板类 
* 这个模板类中存放了页面相应信息 
* <code>totalPages</code> 总的页数 
* <code>fistPageNo</code> 第一页 
* <code>bottomPageNo</code> 最后一页 
* <code>previousPageNo</code> 上一页 
* <code>nextPageNo</code> 下一页 
* @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;
}
/**
* 产生一个页面的模板类 
* 这个模板类中存放了页面相应信息 
* 将这个模板类放入HttpServletRequest对象中 
* <code>totalPages</code> 总的页数 
* <code>fistPageNo</code> 第一页 
* <code>bottomPageNo</code> 最后一页 
* <code>previousPageNo</code> 上一页 
* <code>nextPageNo</code> 下一页 
* @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);
}

/**
* 根据条件查出相应表的符合条件的记录
*
* @param table
*            表名称
* @param condition
*            条件
* @return
* @throws SQLException
*/
public ResultSet getResultSet(String table, String condition)
   throws DataOperateException {
  String sql = "select * from " + table + " where id is not null and " + condition;
  return doQuery(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 table
*            表名称
* @param condition
*            条件
* @return
* @throws SQLException
*/
public long getRecordCount(String table, String condition)
   throws DataOperateException {
  String sql = "select count(*) as counts from " + table + " where id is not null and " + condition;
  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);
   }
  }
}

/**
* 关闭语句对象 
* 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);
  }
}
}

-------------------------------------------------------------------------------------------------------

我有使用到自定义的异常,是继承的SQLException,还有就是用到了数据库连接池