package com.sxt.dao; import java.sql.Connection; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.apache.commons.dbutils.BasicRowProcessor; import org.apache.commons.dbutils.BeanProcessor; import org.apache.commons.dbutils.GenerousBeanProcessor; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.RowProcessor; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import com.sxt.entity.HistoryDB; import com.sxt.utils.C3p0Tool; import com.sxt.utils.PageTool; public class HistoryDao { /**/ QueryRunner queryRunner= new QueryRunner(C3p0Tool.getDataSource()); BeanProcessor bean = new GenerousBeanProcessor(); RowProcessor processor = new BasicRowProcessor(bean); public Integer addHistory(HistoryDB historyDB, Connection conn) throws SQLException { QueryRunner queryRunner = new QueryRunner(); String sql ="insert into t_history (uid,name,account,bid,book_name,begin_time,end_time,status) values (?,?,?,?,?,?,?,?)"; Object[] params = {historyDB.getUid(),historyDB.getName(),historyDB.getAccount(),historyDB.getBid(), historyDB.getBookName(),historyDB.getBeginTime(),historyDB.getEndTime(),historyDB.getStatus()}; return queryRunner.update(conn, sql, params); } //查询图书借阅记录 public PageTool<HistoryDB> listByPage(String currentPage,String pageSize,Integer uid, Integer status){ try { StringBuffer listSql = new StringBuffer("select *"); StringBuffer countSql = new StringBuffer("select count(*)"); StringBuffer sql = new StringBuffer(" from t_history where 1 = 1"); List<Object> params = new ArrayList<Object>(); if (uid != null ) { sql.append(" and uid = ?"); params.add(uid); } if (status != null ) { sql.append(" and status = ?"); params.add(status); } Long total = queryRunner.query(countSql.append(sql).toString(), new ScalarHandler<Long>(),params.toArray()); 代码如何修改
时间: 2025-03-12 18:07:45 浏览: 45
### 改进后的DAO类设计
为了提高代码的可维护性和性能,可以采用以下方法对现有的Java DAO类进行优化:
#### 1. **引入C3P0连接池管理**
通过配置`ComboPooledDataSource`来初始化数据库连接池。这种方式能够有效减少频繁创建和销毁数据库连接带来的开销。
```java
import com.mchange.v2.cpm.PooledDataSource;
import com.mchange.v2.cpm.impl.ComboPooledDataSource;
public class DatabaseConnectionManager {
private static ComboPooledDataSource cpds = new ComboPooledDataSource();
static {
try {
cpds.setDriverClass("com.mysql.cj.jdbc.Driver"); // 设置JDBC驱动程序[^1]
cpds.setJdbcUrl("jdbc:mysql://localhost:3306/library_db?useSSL=false&serverTimezone=UTC");
cpds.setUser("root");
cpds.setPassword("password");
// 配置连接池参数
cpds.setMinPoolSize(5);
cpds.setMaxPoolSize(20);
cpds.setAcquireIncrement(5); // 每次增加的连接数
} catch (Exception e) {
throw new RuntimeException("Error initializing database connection pool", e);
}
}
public static java.sql.Connection getConnection() throws SQLException {
return cpds.getConnection();
}
}
```
#### 2. **利用DbUtils简化SQL执行逻辑**
`QueryRunner` 和 `ResultSetHandler` 是 Apache DbUtils 的核心组件,它们可以帮助开发者更简洁地编写数据访问层代码。
以下是针对图书借阅记录表的操作示例(假设表名为`borrow_records`):
```java
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class BorrowRecordDao {
private QueryRunner queryRunner = new QueryRunner();
/**
* 查询所有未归还的书籍列表
*/
public List<BorrowRecord> findUnreturnedBooks() {
String sql = "SELECT id, book_id, user_id, borrow_date FROM borrow_records WHERE return_date IS NULL";
Connection conn = null;
try {
conn = DatabaseConnectionManager.getConnection(); // 获取数据库连接
BeanListHandler<BorrowRecord> handler = new BeanListHandler<>(BorrowRecord.class);
return queryRunner.query(conn, sql, handler);
} catch (SQLException e) {
throw new RuntimeException("Database error occurred while fetching unreturned books.", e);
} finally {
closeConnection(conn); // 关闭资源
}
}
/**
* 更新某条借阅记录的状态为已归还
*
* @param recordId 借阅记录ID
*/
public void markAsReturned(int recordId) {
String sql = "UPDATE borrow_records SET return_date=CURRENT_DATE WHERE id=?";
Connection conn = null;
try {
conn = DatabaseConnectionManager.getConnection();
queryRunner.update(conn, sql, recordId);
} catch (SQLException e) {
throw new RuntimeException("Failed to update the return status of a borrowing record.", e);
} finally {
closeConnection(conn);
}
}
private void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close(); // 确保释放连接回连接池
} catch (SQLException ignored) {}
}
}
}
```
以上实现方式不仅减少了手动处理 SQL 结果集的工作量,而且增强了异常捕获能力,从而降低了潜在错误的风险。
---
###
阅读全文
相关推荐


















