作者水平有限，如有错误还请批评指正！
前情提要本文主要介绍DbUtils的使用（代码案例）JDBC的基本使用可参考博文（JAVA通过JDBC连接数据库(MySQL)的五种方式(迭代关系)（笔记）_Etui۹(･༥･´)و ̑̑的博客-CSDN博客_java jdbc 连接数据库）数据库连接池技术可参考博文（JDBC数据库连接池技术总结(C3P0,DBCP,Druid)_Etui۹(･༥･´)و ̑̑的博客-CSDN博客）本文涉及的技术点包括Java、JDBC、Druid数据库连接池、Commons DbUtils工具类本文主要为我个人对Java通过JDBC对数据库进行增删改查的最终用法的总结 案例概述
Mysql数据库结构及user_table表如下：
本案例的文件目录如下：
涉及到的API可自行到对应的官网下载
JDBCUtils.java文件为以Druid数据库连接池技术实现的数据库连接
User.java为user_table表的封装类
EndSummary.java为通过DbUtils工具类对user_table进行增删改查的测试代码
endsummary.properties为Druid数据库连接池的参数文件(具体含义可参考： https://blog.csdn.net/m0_47015897/article/details/123509306?spm=1001.2014.3001.5501 )
druid-1.1.10.jar、mysql-connector-java-8.0.27.jar、commons-dbutils-1.3.jar为引用的API
案例源码
endsummary.properties
username=root password=abc123 url=jdbc:mysql:///test driverClassName=com.mysql.cj.jdbc.Driver initialSize=10 maxActive=10 minldle=0
EndSummary.java
package com.Etui5.endSummary;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
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.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.junit.Test;
public class EndSummary {
// 增删改数据通用（以插入为例）
@Test
public void insertTest() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection();
String sql = "insert into user_table(user, password, balance) values(?,?,?)";
int insertCount = runner.update(conn, sql, "EE", "abc123", 5000);
System.out.println("共插入" + insertCount + "条数据");
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null, null);
}
}
// 查询一条数据 BeanHandler
// BeanHandler：是ResultSetHandler接口的实现类，用于封装表中的一条记录
@Test
public void queryTest1() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection();
String sql = "select user, password, balance from user_table where user = ?";
BeanHandler handler = new BeanHandler(User.class);
Object result = runner.query(conn, sql, handler, "CC");
//输出测试
System.out.println(result);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null, null);
}
}
// 查询一条数据 MapHandler
// MayHandler：是ResultSetHandler接口的实现类，用于封装表中的多条记录
// 将字段及相应字段的值作为map中的key和value
@Test
public void queryTest2() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection();
String sql = "select user, password, balance from user_table where user = ?";
MapHandler handler = new MapHandler();
Map result = runner.query(conn, sql, handler, "CC");
// 输出测试
System.out.println(result);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null, null);
}
}
// 查询多条数据 BeanListHandler
// BeanListHandler：是ResultSetHandler接口的实现类，用于封装表中的多条记录
@Test
public void queryTest3() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection();
String sql = "select user, password, balance from user_table";
BeanListHandler handler = new BeanListHandler(User.class);
List resultList = runner.query(conn, sql, handler);
// 输出测试
resultList.forEach(System.out::println);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null, null);
}
}
// 查询多条数据 MapListHandler
// MapListHandler：是ResultSetHandler接口的实现类，用于封装表中的多条记录
// 将字段及字段值作为map的key和value，将这些map添加到List集合中
@Test
public void queryTest4() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection();
String sql = "select user, password, balance from user_table";
MapListHandler handler = new MapListHandler();
List> resultList = runner.query(conn, sql, handler);
// 输出测试
resultList.forEach(System.out::println);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null, null);
}
}
// 查询一条数据，ResultSetHandler：自定义查询结果处理方式
@Test
public void queryTest5() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection();
String sql = "select user, password, balance from user_table where user = ?";
ResultSetHandler handler = new ResultSetHandler() {
public User handle(ResultSet rs) throws SQLException {
if(rs.next()) {
String user = rs.getString(1);
String password = rs.getString(2);
int balance = rs.getInt(3);
return new User(user, password, balance);
}
return null;
}
};
User user = runner.query(conn, sql, handler, "AA");
// 输出测试
System.out.println(user);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null, null);
}
}
// 查询多条数据，ResultSetHandler：自定义查询结果处理方式
@Test
public void queryTest6() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection();
String sql = "select user, password, balance from user_table";
ResultSetHandler > handler = new ResultSetHandler>() {
public List handle(ResultSet rs) throws SQLException {
List list = new ArrayList();
while(rs.next()) {
String user = rs.getString(1);
String password = rs.getString(2);
int balance = rs.getInt(3);
list.add(new User(user, password, balance));
}
return list;
}
};
List list = runner.query(conn, sql, handler);
// 遍历结果集
list.forEach(System.out::println);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null, null);
}
}
}
User.java
package com.Etui5.endSummary;
public class User {
private String user;
private String password;
private int balance;
public User() {
super();
}
public User(String user, String password, int balance) {
super();
this.user = user;
this.password = password;
this.balance = balance;
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getBalance() {
return balance;
}
public void setBalance(int balance) {
this.balance = balance;
}
@Override
public String toString() {
return "User [user=" + user + ", password=" + password + ", balance=" + balance + "]";
}
}
Over!
