[尚硅谷JDBC核心技术（新版jdbc）]idea完结撒花大总结！看着一个就够了！

首先，

要使用到工具类JDBCUtils，完成连接的创建和关闭操作，下面是所有课程讲的各种的操作。getConnection3()内的druid.properties配置文件要放在src下，里面包括一些用户名、驱动等的要求。其中getConnection3()方法使用了目前常用的Druid连接池的技术实现连接，closeResource1()方法其中直接调用了commons-dbutils-1.3.jar包中自带的关闭数据库的方法。

package com.atguigu4.util;

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

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

public class JDBCUtils {
    //基础的数据库连接操作
    public static Connection getConnection() throws Exception {
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
    //使用C3P0的数据池的连接操作，数据连接池最好放在外面只要执行一次，而不是每次调用都会执行
    private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
    public static Connection getConnection1() throws Exception{
        Connection conn=cpds.getConnection();
        return conn;
    }
    //使用DPBC的数据池的连接操作，数据连接池最好放在外面只要执行一次，而不是每次调用都会执行
    private static DataSource source;
    static{
        try {
            Properties pros=new Properties();
    /*方式一：
    InputStream is=ClassLoader.getSystemClassLoader().getResourceAsStream("dpbc.properties");*/
            //方式二：
            FileInputStream is=new FileInputStream(new File("src/dpbc.properties"));
            pros.load(is);
            source= BasicDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection2() throws Exception{
        Connection conn=source.getConnection();
        return conn;
    }
    //使用Druid的数据池的连接操作，数据连接池最好放在外面只要执行一次，而不是每次调用都会执行
    private  static DataSource source1;
    static{
        try {
            Properties pros=new Properties();
            InputStream is=ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
            pros.load(is);
            DataSource source1= DruidDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection3() throws Exception{
        Connection conn=source.getConnection();
        return conn;
    }
    public static void closeResource(Connection conn, PreparedStatement ps) {
        try {
            if (ps!=null){
                ps.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (conn!=null){
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static void closeResource(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if (ps!=null){
                ps.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (conn!=null){
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (rs!=null){
                rs.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static void closeResource1(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            DbUtils.close(conn);
            DbUtils.close(ps);
            DbUtils.close(rs);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

其次，

直接用commons-dbutils-1.3.jar包中提供的增删改、查和查询单条结果的的方法，当然也可以自定义ResultSetHandler(这是一个接口)的实现类，实现自己想要的操作。

package com.atguigu5.dbutils;

import com.atguigu2.bean.Customer;
import com.atguigu4.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.testng.annotations.Test;

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

public class QueryRunnerTest {
    //增删改操作
    @Test
    public void testInsrt() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="insert into customers(name,email,birth)values(?,?,?)";
            int insertCount=runner.update(conn,sql,"大大","dada@qq.com","1997-09-08");
            System.out.println("添加了"+insertCount+"条记录");
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    //单条查询操作:BeanHandler<Customer> handler=new BeanHandler<>(Customer.class);
    @Test
    public void testQuery1() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id=?";
            BeanHandler<Customer> handler=new BeanHandler<>(Customer.class);
            Customer customer=runner.query(conn,sql,handler,23);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    //多条查询操作:BeanListHandler<Customer> handler=new BeanListHandler<>(Customer.class);
    @Test
    public void testQuery2(){
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id<?";
            BeanListHandler<Customer> handler=new BeanListHandler<>(Customer.class);
            List<Customer> list=runner.query(conn,sql,handler,23);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    //单条查询操作:MapHandler handler=new MapHandler();
    @Test
    public void testQuery3() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id=?";
            MapHandler handler=new MapHandler();
            Map<String,Object> map=runner.query(conn,sql,handler,23);
            System.out.println(map);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    //多条查询操作:MapListHandler handler=new MapListHandler();
    @Test
    public void testQuery4() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id<?";
            MapListHandler handler=new MapListHandler();
            List<Map<String,Object>> list=runner.query(conn,sql,handler,23);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    //下面两个方法用于返回查询的记录，比如最大值或者顾客数量
    @Test
    public void testQuery5() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select max(birth) from customers ";
            ScalarHandler handler=new ScalarHandler();
            Date maxBirth= (Date) runner.query(conn,sql,handler);
            System.out.println(maxBirth);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    @Test
    public void testQuery6() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select Count(*)from customers ";
            ScalarHandler handler=new ScalarHandler();
            Long count= (Long) runner.query(conn,sql,handler);
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    //自定义ResultSetHandler(这是一个接口)的实现类
    @Test
    public void testQuery7() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id=?";
            ResultSetHandler<Customer> handler=new ResultSetHandler<Customer>() {
                @Override
                public Customer handle(ResultSet resultSet) throws SQLException {
                    return null;
                }
            };
            Customer customer=runner.query(conn,sql,handler);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

}

完结撒花！