• 第9章 Apache-Dbutils实现CRUD操作


    1. Apache-DBUtils简介

    *commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,它是对JDBC的简单封装,学习成本极低,并且使用dbutils能极大简化jdbc编码的工作量,同时也不会影响程序的性能。

    *API介绍:

    org.apache.commons.dbutils.QueryRunner

    org.apache.commons.dbutils.ResultSetHandler

    工具类:org.apache.commons.dbutils.DbUtils

    *jar包

    2.使用QueryRunner添加数据

    1. public void testInsert(){
    2. Connection conn = null;
    3. try {
    4. QueryRunner runner = new QueryRunner();
    5. conn = JDBCUtiles3.getConnection();
    6. String sql="insert into customers(name,email,birth)values(?,?,?)";
    7. int insertcount = runner.update(conn, sql, "翟天临", "zhaitianlin@163.com", "1995-01-23");
    8. System.out.println("添加了"+insertcount+"条记录");
    9. } catch (SQLException e) {
    10. e.printStackTrace();
    11. } finally {
    12. JDBCUtiles.closeResource(conn,null);
    13. }
    14. }

     3.使用QueryRunner查询操作

    1. @Test
    2. public void testQuery1(){
    3. Connection conn = null;
    4. try {
    5. QueryRunner runner = new QueryRunner();
    6. conn = JDBCUtiles3.getConnection();
    7. String sql="select * from customers where id=?";
    8. //BeanHandler:是ResultSetHandler接口的实现类,用于封装表中的一条记录
    9. BeanHandler<Customer> handler = new BeanHandler<Customer>(Customer.class);
    10. Customer customer = runner.query(conn, sql, handler, 25);
    11. System.out.println(customer);
    12. } catch (SQLException e) {
    13. e.printStackTrace();
    14. } finally {
    15. JDBCUtiles.closeResource(conn,null);
    16. }
    17. }
    18. @Test
    19. public void testQuery2(){
    20. Connection conn = null;
    21. try {
    22. QueryRunner runner = new QueryRunner();
    23. conn = JDBCUtiles3.getConnection();
    24. String sql="select * from customers where id;
    25. //BeanListHandler:是ResultSetHandler接口的实现类,用于封装表中的多条记录
    26. BeanListHandler<Customer> listHandler = new BeanListHandler<>(Customer.class);
    27. List<Customer> customerList = runner.query(conn, sql, listHandler, 25);
    28. System.out.println(customerList);
    29. } catch (SQLException e) {
    30. e.printStackTrace();
    31. } finally {
    32. JDBCUtiles.closeResource(conn,null);
    33. }
    34. }

    4.使用QueryRunner查询特殊值

    1. public void testQuery3(){
    2. Connection conn = null;
    3. try {
    4. QueryRunner runner = new QueryRunner();
    5. conn = JDBCUtiles3.getConnection();
    6. String sql="select count(1) from customers";
    7. ScalarHandler handler = new ScalarHandler();
    8. Object query = runner.query(conn, sql, handler);
    9. System.out.println(query);
    10. } catch (SQLException e) {
    11. e.printStackTrace();
    12. } finally {
    13. JDBCUtiles.closeResource(conn,null);
    14. }
    15. }

    4.自定义ResultSetHandler

    *使用ResultSetHandler的匿名实现类实现

    1. public void testQuery4(){
    2. Connection conn = null;
    3. try {
    4. QueryRunner runner = new QueryRunner();
    5. conn = JDBCUtiles3.getConnection();
    6. String sql="select * from customers where id;
    7. //自定义ResultSetHandler
    8. ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>(){
    9. //方法重写
    10. @Override
    11. public Customer handle(ResultSet resultSet) throws SQLException {
    12. //resultSet就是结果集
    13. //在重写方法中写入对结果集的操作
    14. System.out.println("handler");
    15. return null;
    16. }
    17. };
    18. Customer query = runner.query(conn, sql, handler, 25);
    19. System.out.println(query);
    20. } catch (SQLException e) {
    21. e.printStackTrace();
    22. } finally {
    23. JDBCUtiles.closeResource(conn,null);
    24. }
    25. }

    5. 使用DbUtils工具类实现资源的关闭

    1. package utils;
    2. import com.alibaba.druid.pool.DruidDataSourceFactory;
    3. import org.apache.commons.dbutils.DbUtils;
    4. import javax.sql.DataSource;
    5. import java.io.InputStream;
    6. import java.sql.Connection;
    7. import java.sql.ResultSet;
    8. import java.sql.SQLException;
    9. import java.sql.Statement;
    10. import java.util.Properties;
    11. public class JDBCUtiles3 {
    12. private static DataSource source;
    13. static {
    14. try {
    15. Properties pros=new Properties();
    16. InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
    17. pros.load(is);
    18. source= DruidDataSourceFactory.createDataSource(pros);
    19. } catch (Exception e) {
    20. e.printStackTrace();
    21. }
    22. }
    23. public static Connection getConnection() throws SQLException {
    24. return source.getConnection();
    25. }
    26. //使用DbUtils工具类实现资源的关闭
    27. public static void closeResource(Connection conn, Statement ps, ResultSet rs){
    28. DbUtils.closeQuietly(conn);
    29. DbUtils.closeQuietly(ps);
    30. DbUtils.closeQuietly(rs);
    31. }
    32. }

  • 相关阅读:
    awk根据某个字段过滤文件航
    React实现一个拖拽排序组件 - 支持多行多列、支持TypeScript、支持Flip动画、可自定义拖拽区域
    云原生 | Kubernetes - 通过KubeKey用脚搭建k8s高可用集群 + KubeSphere
    Linux新建用户、删除用户以及用户之间的切换
    K8s 之 Helm 部署 MySQL 5.7
    LVS-DR模式+keepalived+nginx+tomcat实现动静分离、负载均衡、高可用实验
    SQL Server安装教程(2022年更新)
    基于Docker的安装和配置Canal
    java基础选择题--17(包含全面)
    快速构建后台管理系统-RUOYI学习之-VUE
  • 原文地址:https://blog.csdn.net/weixin_47687315/article/details/127886233