编写一个使用QueryRunner类和ResultSetHandler接口,对数据库数据增删改查的代码
时间: 2024-03-20 21:44:27 浏览: 73
以下是一个使用QueryRunner类和ResultSetHandler接口,对数据库数据增删改查的示例代码:
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
public class DBUtilsExample {
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
private static final String DB_URL = "jdbc:mysql://localhost:3306/test";
private static final String USER = "root";
private static final String PASS = "password";
public static void main(String[] args) throws SQLException, ClassNotFoundException {
// 加载驱动
Class.forName(JDBC_DRIVER);
// 建立连接
Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
// 创建QueryRunner对象
QueryRunner qr = new QueryRunner();
// 插入数据
String insertSql = "INSERT INTO user(name, age) VALUES (?, ?)";
qr.update(conn, insertSql, "张三", 20);
// 删除数据
String deleteSql = "DELETE FROM user WHERE id=?";
qr.update(conn, deleteSql, 1);
// 更新数据
String updateSql = "UPDATE user SET age=? WHERE name=?";
qr.update(conn, updateSql, 25, "张三");
// 查询单条数据
String queryOneSql = "SELECT * FROM user WHERE name=?";
ResultSetHandler<User> userHandler = new BeanHandler<>(User.class);
User user = qr.query(conn, queryOneSql, userHandler, "张三");
System.out.println(user.getName() + ", " + user.getAge());
// 查询多条数据
String queryListSql = "SELECT * FROM user";
ResultSetHandler<List<User>> userListHandler = new BeanListHandler<>(User.class);
List<User> userList = qr.query(conn, queryListSql, userListHandler);
for (User u : userList) {
System.out.println(u.getName() + ", " + u.getAge());
}
// 查询数据总数
String countSql = "SELECT COUNT(*) FROM user";
ResultSetHandler<Long> countHandler = new ScalarHandler<>();
Long count = qr.query(conn, countSql, countHandler);
System.out.println("数据总数:" + count);
// 关闭连接
conn.close();
}
}
```
在这个示例代码中,我们使用QueryRunner类和ResultSetHandler接口完成了数据库的增删改查操作。其中,BeanHandler和BeanListHandler是用来将查询结果映射成Java对象的ResultSetHandler实现类,ScalarHandler则是用来获取单个值的ResultSetHandler实现类。通过使用这些实现类,我们可以更加方便地操作数据库。
阅读全文