JDBC笔记 09:Apache-DBUtils实现CRUD操作

it2024-11-22  3

1. Apache-DBUtils简介

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,它是对JDBC的简单封装,学习成本极低,并且使用dbutils能极大简化jdbc编码的工作量,同时也不会影响程序的性能。ResultSetHandler 接口用于处理数据库查询操作得到的结果集。不同结果集的情形,由其不同的子类来实现QueryRunner 提供数据库操作的一系列重载的 update() 和 query() 操作

2. 使用

增删改操作

public class Demo { @Test public void test() { Connection connection = null; try { QueryRunner runner = new QueryRunner(); connection = JDBCUtils.getConnection(); String sql = "insert into customers (name,email,birth) values (?,?,?)"; int count = runner.update(connection, sql, "费渡", "feidu@qq.com", "1997-12-08"); System.out.println("添加了" + count + "条记录!"); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, null); } } }

查询一条数据(BeanHandler)

public class Demo { @Test public void test() throws Exception { Connection connection = null; try { QueryRunner runner = new QueryRunner(); connection = JDBCUtils.getConnection(); String sql = "select id,name,email,birth from customers where id = ?"; BeanHandler<Customers> handler = new BeanHandler<>(Customers.class); Customers customer = runner.query(connection, sql, handler, 21); System.out.println(customer); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, null); } } }

查询多条数据(BeanListHandler)

public class Demo { @Test public void test() throws Exception { Connection connection = null; try { QueryRunner runner = new QueryRunner(); connection = JDBCUtils.getConnection(); String sql = "select id,name,email,birth from customers"; BeanListHandler<Customers> handler = new BeanListHandler<>(Customers.class); List<Customers> customersList = runner.query(connection, sql, handler); for (Customers customers : customersList) { System.out.println(customers); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, null); } } }

MapHandler

public class Demo { @Test public void test() throws Exception { Connection connection = null; try { QueryRunner runner = new QueryRunner(); connection = JDBCUtils.getConnection(); String sql = "select id,name,email,birth from customers where id = ?"; MapHandler handler = new MapHandler(); Map<String, Object> map = runner.query(connection, sql, handler, 21); System.out.println(map);//输出为{name=费渡, birth=1997-12-08, id=21, email=feidu@qq.com} } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, null); } } }

MapListHandler

public class Demo { @Test public void test() throws Exception { Connection connection = null; try { QueryRunner runner = new QueryRunner(); connection = JDBCUtils.getConnection(); String sql = "select id,name,email,birth from customers"; MapListHandler handler = new MapListHandler(); List<Map<String, Object>> mapList = runner.query(connection, sql, handler); for (Map<String, Object> objectMap : mapList) { System.out.println(objectMap); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, null); } } }

查询特殊值(ScalarHandler)

public class Demo { @Test public void test() throws Exception { Connection connection = null; try { QueryRunner runner = new QueryRunner(); connection = JDBCUtils.getConnection(); String sql = "select count(*) from customers"; ScalarHandler handler = new ScalarHandler(); Long count = (Long) runner.query(connection, sql, handler); System.out.println(count); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, null); } } }

自定义 ResultSetHandler 的实现类

public class Demo { @Test public void test() throws Exception { Connection connection = null; try { QueryRunner runner = new QueryRunner(); connection = JDBCUtils.getConnection(); String sql = "select id,name,email,birth from customers where id = ?"; Customers customers = runner.query(connection, sql, new ResultSetHandler<Customers>() { //该方法的返回值即为查询结果 @Override public Customers handle(ResultSet resultSet) throws SQLException { //在这里实现resultSet的处理 return null; } }, 21); System.out.println(customers); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, null); } } }

关闭资源

public class JDBCUtils { public static void closeResource(Connection connection, Statement statement, ResultSet resultSet) { try { DbUtils.close(connection); DbUtils.close(statement); DbUtils.close(resultSet); } catch (SQLException e) { e.printStackTrace(); } //DbUtils.closeQuietly(connection);//使用该方法不需要处理异常 } }
最新回复(0)