前言:commons-dbutils是Apache组织提供的一个开源JDBC工具类库,它是对JDBC的简单封装,学习成本极低,并且使用dbutils能极大简化jdbc编码的工作量,同时也不会影响程序的性能。
步骤1、导入DBUtils jar包,
博主免费上传或者自行下载最新版:
步骤2、查看dbutils的帮助文档。
打开commons-dbutils-1.3\apidocs目录下的index.html,做简单了解:其中QueryRunner类中提供了对sql语句操作的API:
QueryRunner类:
update(connection,sql,params):执行任何增删改
query(connection,sql,ResultSetHandler,params):执行任何查询
ResultSetHandler接口:
BeanHandler:将结果集的第一行,封装成对象,并返回 new BeanHandler<>(xx.class)
BeanListHandler:将结果集中的所有行,封装成对象的集合,并返回 new BeanListHandler(xx.class)
ScalarHandler:将结果集中的第一行第一列,以Object形式返回 new ScalarHandler()
步骤3、简单代码实现:
数据库以Admin表为例:
之前还要创建一个Druid德鲁伊数据库连接池获取连接对象:JDBCUtildByBruid.java
/*
* 此类是通过德鲁伊数据库连接池获取连接对象
* */
public class JDBCUtilsByDruid {
static javax.sql.DataSource ds;
static{
try {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\jdbc.properties"));
//1.创建一个指定参数的数据库连接池
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws Exception {
//2.从数据库连接池中获取可用的连接对象
return ds.getConnection();
}
public static void close(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if (connection!=null){
connection.close();
}
if (statement!=null){
statement.close();
}
if (resultSet!=null){
resultSet.close();
}
}
}
定义Admin.java
public class Admin {
private int id;
private String username;
private String password;
public Admin() {
}
public Admin(int id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String usrename) {
this.username = usrename;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "Admin{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
定义一个DBUtilsDemo.java,其中每个一个Test都可用测试类实现
/*
* 此类用于演示DBUtils的使用
*
* ResultSetHandler
*
* */
public class DBUtilsDemo {
//以update为例,实现对数据的 增删改 操作
@Test
public void testUpdate() throws Exception {
//1.获取连接
Connection connection = JDBCUtilsByDruid.getConnection();
//2、执行增删改
QueryRunner qr = new QueryRunner();
int update = qr.update(connection, "update boys set boyname=? where id = 4", "成龙");
System.out.println(update>0?"success":"failure");
//3.关闭连接
JDBCUtilsByDruid.close(connection,null,null);
}
//实现对数据的 Query查询 操作
@Test
public void testQuerySingle() throws Exception {
//1.获取连接
Connection connection = JDBCUtilsByDruid.getConnection();
//2、执行查询
QueryRunner qr = new QueryRunner();
Admin admin = qr.query(connection, "select * from admin where id =?", new BeanHandler<>(Admin.class), 3);
System.out.println(admin);
//3.关闭连接
JDBCUtilsByDruid.close(connection,null,null);
}
//实现对数据的 多行查询 操作
@Test
public void testQueryMulti() throws Exception {
//1.获取连接
Connection connection = JDBCUtilsByDruid.getConnection();
//2、执行多行查询
QueryRunner qr = new QueryRunner();
// Admin admin = qr.query(connection, "select * from admin where id =?", new BeanHandler<>(Admin.class), 3);
// System.out.println(admin);
List<Boys> list = qr.query(connection, "select * from boys where userCP>?", new BeanListHandler<>(Boys.class), 1);
for (Boys boys :list){
System.out.println(boys);
}
//3.关闭连接
JDBCUtilsByDruid.close(connection,null,null);
}
//实现对数据的单个查询操作
@Test
public void testQueryScalar() throws Exception {
//1.获取连接
Connection connection = JDBCUtilsByDruid.getConnection();
//2、查询单个值
QueryRunner qr = new QueryRunner();
Object query = qr.query(connection,"select count(*) from Admin", new ScalarHandler());
System.out.println(query);
//3.关闭连接
JDBCUtilsByDruid.close(connection,null,null);
}
}
项目结构:
最后:关于Durid德鲁伊连接池工具类,没有说明,感兴趣可以自己学学,或者私信博主。