druid QueryRuner的实现

druid QueryRuner的实现

1.下载dbutils jar包并导入lib

DBUtils是java编程中的数据库操作实用工具，小巧简单实用。
DBUtils封装了对JDBC的增删查改操作，简化了JDBC操作，可以少写代码。
Dbutils三个核心功能介绍：

1. QueryRunner中提供对sql语句操作的API.
2. ResultSetHandler接口，用于定义select操作后，怎样封装结果集.
3. DbUtils类，它就是一个工具类,定义了关闭资源与事务处理的方法

2.druid.properties

# druid.properties文件的配置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=shan5211314..
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大超时时间
maxWait=3000

2.JDBCUtils.java

package com.shan3.databaseConnection.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.DbUtils;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * 使用c3p0技术
 * @author shan
 * @date 2021/5/7
 * 操作数据库的工具类
 */



public class JDBCUtils {
    //获取数据库连接

    //数据库连接池只提供一个即可
    private static ComboPooledDataSource cpds = new ComboPooledDataSource("hello_c3p0");
    public static Connection getConnection() throws SQLException {

        Connection conn = cpds.getConnection();
        return conn;

    }

    // 使用Druid数据库连接池技术
    private static DataSource source;
    static{
        try {
            Properties pros = new Properties();
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
            pros.load(is);
            source = DruidDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnectionDruid() throws SQLException {
        Connection conn = source.getConnection();
        return conn;
    }



    // 查找的 资源关闭 Druid
    public static void closeResource(Connection conn, PreparedStatement ps, ResultSet rs){
        //资源的关闭
//        try {
//            DbUtils.close(conn);
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//        try {
//            DbUtils.close(ps);
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//        try {
//            DbUtils.close(rs);
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }

        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);

    }
}

QueryRunnerTest

package com.shan4.QueryRunner;
/*
*  comment-dbutils 是 Apache 组织提供的一套开源的JDBU工具类库，封装了针对于数据库的增删改查操作
* */

import com.shan.bean.Customer;
import com.shan1.dao.CustomerDAOImpl;
import com.shan3.databaseConnection.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class QueryRunerTest {
    //测试插入
    @Test
    public void testInsert() throws Exception {
        QueryRunner runner = new QueryRunner();
        Connection conn = JDBCUtils.getConnectionDruid();
        String sql = "insert into customers(name,email)values(?,?)";
        int insertCount = runner.update(conn, sql, "毛不易", "mby@126.com");
        System.out.println("添加了 " + insertCount + " 条记录!");

    }

    //测试查询

    // BeanHander: 是ResultBeanHander接口的实现类， 用于封装表中的一条记录。
    @Test
    public void testquery1()  {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnectionDruid();
            String sql = "select * from customers where id = ?";
            BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
            Customer customer = runner.query(conn, sql, handler, 7);
            System.out.println(customer);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn, null);
        }


    }

    // BeanListHander: 是ResultBeanHander接口的实现类， 用于封装表中的多条记录。
    @Test
    public void testquery2() throws Exception {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnectionDruid();
            String sql = "select * from customers where id < ?";

            BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
            List<Customer> customers = runner.query(conn, sql, handler, 7);
            System.out.println(customers);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn, null);
        }

    }
    // MapHander: 是ResultBeanHander接口的实现类， 对应表中的一条记录。
    // 将字段和字段对应的值封装为map中的键和值

    @Test
    public void testquery3() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnectionDruid();
            String sql = "select * from customers where id = ?";
            MapHandler handler = new MapHandler();

            Map<String, Object> map = runner.query(conn, sql, handler, 7);
            System.out.println(map);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn, null);
        }

    }

    // MapListHander: 是ResultBeanHander接口的实现类， 对应表中的多条记录。
    // 将字段和字段对应的值封装为map中的键和值

    @Test
    public void testquery4() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnectionDruid();
            String sql = "select * from customers where id < ?";
            MapListHandler handler = new MapListHandler();

            List<Map<String, Object>> list = runner.query(conn, sql, handler, 7);
            System.out.println(list);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn, null);
        }

    }

    // ScalarHandler: 是ResultBeanHander接口的实现类， 查询特殊值的需求。

    @Test
    public void testquery5() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnectionDruid();
            String sql = "select count(*) from customers";
//            String sql = "select max(birth) from customers";
            ScalarHandler handler = new ScalarHandler();


            Long count =(Long) runner.query(conn, sql, handler);
            System.out.println(count);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn, null);
        }

    }

    // 自定义ResultSetHandler的实现类
    @Test
    public void testquery6() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnectionDruid();
            String sql = "select * from customers where id = ?";
            ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {
                @Override
                public Customer handle(ResultSet rs) throws SQLException {
                    System.out.println("handle");
//                    return null;
//                    return new Customer(7,"毛不易","mby@126.com");
                    if(rs.next()){
                        int id = rs.getInt("id");
                        String name = rs.getString("name");
                        String email = rs.getString("email");
                        Customer customer = new Customer(id, name, email);
                        return customer;
                    }
                    return null;
                }

            };


            Customer customer = runner.query(conn, sql, handler,7);
            System.out.println(customer);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn, null);
        }

    }
}