水一篇去年的jdbc学习笔记

最新推荐文章于 2024-08-09 21:25:57 发布

SFANTx

最新推荐文章于 2024-08-09 21:25:57 发布

阅读量809

点赞数

文章标签：数据库 java mysql

本文链接：https://blog.csdn.net/weixin_50675808/article/details/121711619

版权

学校作业要求贴文献地址，可以贴csdn，就贴一下之前尚硅谷的学习笔记吧嘻嘻

连接数据库

方式一

public class ConnectionTest {
    //方式一
    @Test
    public void test1() throws SQLException {
        Driver driver = new com.mysql.cj.jdbc.Driver();
        String url = "jdbc:mysql://localhost:3306/jdbcstudy";
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","root");
        Connection conn = driver.connect(url, info);
        System.out.println(conn);
    }
}

方式二

//方式2：在程序中不出现第三方的api，使程序具有更高的可移植性
    @Test
    public void testConnect2() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        //1.获取Driver的实现类对象
        Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();

        //2.提供要连接的数据库
        String url = "jdbc:mysql://localhost:3306/jdbcstudy";
        //3.提供连接的账户和密码
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","root");
        //4.获取连接
        Connection connect = driver.connect(url, info);
        System.out.println(connect);
    }

方式三

//方式3：使用DriverManager替换Driver
    @Test
    public void testConnect3() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        //1.注册驱动
        Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();
        DriverManager.registerDriver(driver);

        //提供url，user，password
        String url = "jdbc:mysql://localhost:3306/jdbcstudy";
        String user = "root";
        String password = "root";


        //2.获取数据库连接对象
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }

方式四

//方式4：静态代码块加载驱动
    @Test
    public void testConnect4() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //提供url，user，password
        String url = "jdbc:mysql://localhost:3306/jdbcstudy";
        String user = "root";
        String password = "root";


        //2.获取数据库连接对象
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }

最终版

//方式5：将连接的配置信息，放在配置文件中读取
    /**
     * 好处：
     * 1.实现了数据和代码的分离，实现了解耦
     * 2.如果需要修改配置信息，就可以避免程序重新打包
    */
    @Test
    public void testConnect5() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException, IOException {

        //读取配置文件
        InputStream in = ConnectionTest.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        properties.load(in);
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("username");
        String password = properties.getProperty("password");


        //1.注册驱动
        Class.forName(driver);


        //2.获取数据库连接对象
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }

JDBCUtils

package com.atguigu.util;

/**
 * 操作数据库的工具类
 */

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    /**
     * @Description 获取数据库的连接
     * @return
     * @throws IOException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
        //1.读取配置文件的四个信息
        InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");

        Properties properties = new Properties();
        properties.load(in);

        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String username = properties.getProperty("username");
        String password = properties.getProperty("password");

        //2.加载驱动
        Class.forName(driver);
        //3.获取连接
        Connection conn = DriverManager.getConnection(url, username, password);
        return conn;
    }


    /**
     * @Description 关闭资源
     * @param conn
     * @param st
     */
    public static void closeResource(Connection conn, Statement st){

        try {
            if(st != null)
                st.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if(conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

通用的增删改1.0

//通用的增删改操作
    public void update(String sql, Object ...args){
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JDBCUtils.getConnection();
            st = conn.prepareStatement(sql);

            for(int i = 0; i < args.length; i++)
            {
                st.setObject(i+1,args[i]);
            }

            st.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,st);
        }
    }

通用的增删改2.0

//通用的增删改操作:version 2.0，特点：考虑上事务
    public int update(Connection conn, String sql, Object ...args){
        PreparedStatement st = null;

        try {
            st = conn.prepareStatement(sql);

            for(int i = 0; i < args.length; i++)
            {
                st.setObject(i+1,args[i]);
            }

            return st.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(null,st);
        }
        return 0;
    }

通用查询

Customers类

package com.atguigu.bean;

import java.sql.Date;

//一个类对应一个表，一个对象对应一条记录，一个java对象的属性对应一个字段
public class Customer {
    private int id;
    private String name;
    private String email;
    private Date birth;

    public Customer() {
    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", birth=" + birth +
                '}';
    }

    public Customer(int id, String name, String email, Date birth) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }
}

/**
     * @Description 针对Customers表的通用查询
     */
    public Customer queryForCustomers(String sql,Object ...args){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtils.getConnection();

            st = conn.prepareStatement(sql);
            for(int i = 0; i < args.length;i++)
            {
                st.setObject(i+1,args[i]);
            }
            resultSet = st.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //通过ResultSetMetaData获取结果结中的列数
            int columnCount = rsmd.getColumnCount();

            if(resultSet.next()){
                Customer cust = new Customer();
                //处理结果集一行数据中的每一个列
                for(int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = resultSet.getObject(i + 1);

                    //获取每个列的列名
                    String columnName = rsmd.getColumnName(i+1);

                    //使用列名，运用反射给对象对应属性赋值
                    Field field = Customer.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(cust,columnValue);

                }
                return cust;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,st,resultSet);
        }
        return null;
    }

//通用查询测试
@Test
    public void queryForCustomersTest(){
        String sql = "select id,name,birth,email from customers where id = ?";
        Customer customer = queryForCustomers(sql, 13);
        System.out.println(customer);
    }

Order类

package com.atguigu.bean;

import java.sql.Date;

public class Order {
    private int orderId;
    private String orderName;
    private Date orderDate;


    @Override
    public String toString() {
        return "Order{" +
                "orderId=" + orderId +
                ", orderName='" + orderName + '\'' +
                ", orderDate=" + orderDate +
                '}';
    }

    public int getOrderId() {
        return orderId;
    }

    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    public Date getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }

    public Order() {
    }

    public Order(int orderId, String orderName, Date orderDate) {
        this.orderId = orderId;
        this.orderName = orderName;
        this.orderDate = orderDate;
    }
}

/**
     * @Description 针对Order的通用查询操作
     * @param sql
     * @param args
     * @return
     */
    public Order orderForQuery(String sql,Object ...args){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtils.getConnection();
            st = conn.prepareStatement(sql);

            //填充占位符
            for(int i = 0; i < args.length; i++)
            {
                st.setObject(i+1,args[i]);
            }
            //执行sql语句
            resultSet = st.executeQuery();
            //获取结果集元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //获取列数
            int columnCount = metaData.getColumnCount();

            //获取结果
            if(resultSet.next()){
                Order order = new Order();
                for(int i = 0; i < columnCount; i++){
                    //获取列名:getColumnName(这是表中的名字) :不推荐使用
//                    String columnName = metaData.getColumnName(i+1);
                    
                    //获取列的别名：
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    //获取列值
                    Object object = resultSet.getObject(i + 1);
                    //反射给order对象属性赋值
                    Field field = Order.class.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(order,object);
                }
                return order;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            JDBCUtils.closeResource(conn,st,resultSet);
        }
        return null;
    }

//测试
@Test
    public void testOrderForQuery(){
        String sql = "SELECT order_id orderId,order_name orderName,order_date orderDate from `order` where order_id = ?;";
        Order order = orderForQuery(sql, 1);
        System.out.println(order);
    }

小结

针对表的字段名和列的列名不同的情况：

1. 必须声明sql，使用类的属性名作为字段的别名
2. 在使用ResultSetMetaData时，需要使用getColumnLabel()来替换getColumnName()，获取列的别名
3. 说明：如果sql中没有给字段起别名，getColumnLabel()获取的就是列名

针对不同表的查询

返回一条记录

/**
     * @Decription 针对不同表的查询，此时返回一条记录
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public <T> T getInstance(Class<T> clazz, String sql, Object ...args){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtils.getConnection();

            st = conn.prepareStatement(sql);
            for(int i = 0; i < args.length;i++)
            {
                st.setObject(i+1,args[i]);
            }
            resultSet = st.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //通过ResultSetMetaData获取结果结中的列数
            int columnCount = rsmd.getColumnCount();

            if(resultSet.next()){
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for(int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = resultSet.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i+1);

                    //使用列名，运用反射给对象对应属性赋值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);

                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,st,resultSet);
        }
        return null;
    }

//测试
@Test
    public void testGetInstance(){
        String sql = "select id,name,email from customers where id = ?;";
        Customer instance = getInstance(Customer.class, sql, 1);
        System.out.println(instance);

        String sql1 = "select order_id orderId, order_name orderName from `order` where order_id = ?;";
        Order instance1 = getInstance(Order.class, sql1, 2);
        System.out.println(instance1);
    }

返回一条记录2.0

//通用的查询2.0，特点：考虑上事务
    public <T> T getInstance(Connection conn,Class<T> clazz, String sql, Object ...args){
        PreparedStatement st = null;
        ResultSet resultSet = null;
        try {
            st = conn.prepareStatement(sql);
            for(int i = 0; i < args.length;i++)
            {
                st.setObject(i+1,args[i]);
            }
            resultSet = st.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //通过ResultSetMetaData获取结果结中的列数
            int columnCount = rsmd.getColumnCount();

            if(resultSet.next()){
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for(int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = resultSet.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i+1);

                    //使用列名，运用反射给对象对应属性赋值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);

                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,st,resultSet);
        }
        return null;
    }

返回多条记录

/**
     * @Decription 查询多行记录
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public <T> List<T> getForList(Class<T> clazz, String sql, Object ...args){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtils.getConnection();

            st = conn.prepareStatement(sql);
            for(int i = 0; i < args.length;i++)
            {
                st.setObject(i+1,args[i]);
            }
            resultSet = st.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //通过ResultSetMetaData获取结果结中的列数
            int columnCount = rsmd.getColumnCount();
            ArrayList<T> list = new ArrayList<>();
            //处理数据集中的每一行数据，给每一行数据都分配一个T对象，对象赋值完毕，保存造T的List中
            while(resultSet.next()){
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for(int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = resultSet.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i+1);

                    //使用列名，运用反射给对象对应属性赋值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,st,resultSet);
        }
        return null;
    }

//测试
@Test
    public void testGetForList(){
        String sql = "select id,name,email from customers where id < ?;";
        List<Customer> list = getForList(Customer.class, sql, 12);
        list.forEach(System.out::println);
        System.out.println("===========");
        String sql1 = "select order_id orderId, order_name orderName from `order` where order_id < ?;";
        List<Order> list1 = getForList(Order.class, sql1, 3);
        list1.forEach(System.out::println);
    }

返回多条记录2.0

public <T> List<T> getForList(Connection conn,Class<T> clazz, String sql, Object ...args){
        PreparedStatement st = null;
        ResultSet resultSet = null;
        try {

            st = conn.prepareStatement(sql);
            for(int i = 0; i < args.length;i++)
            {
                st.setObject(i+1,args[i]);
            }
            resultSet = st.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //通过ResultSetMetaData获取结果结中的列数
            int columnCount = rsmd.getColumnCount();
            ArrayList<T> list = new ArrayList<>();
            //处理数据集中的每一行数据，给每一行数据都分配一个T对象，对象赋值完毕，保存造T的List中
            while(resultSet.next()){
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for(int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = resultSet.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i+1);

                    //使用列名，运用反射给对象对应属性赋值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,st,resultSet);
        }
        return null;
    }

PreparedStatement与Statement对比

PreparedStatement操作Blob的数据，而Statement做不到
PreparedStatement可以实现更高效率的批量操作
PreparedStatement解决了sql注入问题

练习

练习1

向Customers中插入一条数据

public class Exer1Test {
    //向Customers中插入一条数据
    @Test
    public void testInsert(){
        Scanner scanner = new Scanner(System.in);
        System.out.print("输入用户名:");
        String name = scanner.next();
        System.out.print("输入email:");
        String email = scanner.next();
        System.out.print("输入birth:");
        String birth = scanner.next();

        String sql = "insert into customers (name,email,birth) values(?,?,?);";

        int num = update(sql, name, email, birth);
        if(num > 0){
            System.out.println("增加成功");
        }else{
            System.out.println("增加失败");
        }
    }
    //通用的增删改操作
    public int update(String sql, Object ...args){
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JDBCUtils.getConnection();
            st = conn.prepareStatement(sql);

            for(int i = 0; i < args.length; i++)
            {
                st.setObject(i+1,args[i]);
            }

            /**
             * st.execute():如果是查询，存在结果返回true
             * 增删改，没有结果，返回false；
             */
            return st.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,st);
        }
        return 0;
    }
}

练习2

package com.atguigu.exercise.bean;
/**
 * Type:
 * IDCard:
 * ExamCard:
 * StudentName:
 * Location:
 * Grade:
 */
public class Student {
    private int FlowId;
    private int Type;
    private String IDCard;
    private String ExamCard;
    private String StudentName;
    private String Location;
    private int Grade;

    @Override
    public String toString() {
        System.out.println("======查询结果=====");
        return info();
    }

    public String info(){
        return  "流水号：" + FlowId +
                "\n四级/六级：" + Type +
                "\n身份证：" + IDCard +
                "\n准考证号：" + ExamCard +
                "\n姓名：" + StudentName +
                "\n城市：" + Location +
                "\n成绩：" + Grade;
    }

    public int getFlowId() {
        return FlowId;
    }

    public void setFlowId(int flowId) {
        FlowId = flowId;
    }

    public int getType() {
        return Type;
    }

    public void setType(int type) {
        Type = type;
    }

    public String getIDCard() {
        return IDCard;
    }

    public void setIDCard(String IDCard) {
        this.IDCard = IDCard;
    }

    public String getExamCard() {
        return ExamCard;
    }

    public void setExamCard(String examCard) {
        ExamCard = examCard;
    }

    public String getStudentName() {
        return StudentName;
    }

    public void setStudentName(String studentName) {
        StudentName = studentName;
    }

    public String getLocation() {
        return Location;
    }

    public void setLocation(String location) {
        Location = location;
    }

    public int getGrade() {
        return Grade;
    }

    public void setGrade(int grade) {
        Grade = grade;
    }

    public Student() {
    }

    public Student(int flowId, int type, String IDCard, String examCard, String studentName, String location, int grade) {
        FlowId = flowId;
        Type = type;
        this.IDCard = IDCard;
        ExamCard = examCard;
        StudentName = studentName;
        Location = location;
        Grade = grade;
    }
}

public class ExerTest2 {

    //问题1：向examstudent表中添加一条记录
    /**
     * Type:
     * IDCard:
     * ExamCard:
     * StudentName:
     * Location:
     * Grade:
     */
    @Test
    public void testInsert(){
        Scanner scanner = new Scanner(System.in);
        System.out.print("四级/六级(4/6):");
        int Type = scanner.nextInt();
        System.out.print("身份证:");
        String IDCard = scanner.next();
        System.out.print("准考证:");
        String ExamCard = scanner.next();
        System.out.print("学生姓名:");
        String StudentName = scanner.next();
        System.out.print("所在城市:");
        String Location = scanner.next();
        System.out.println("学生成绩:");
        int Grade = scanner.nextInt();

        String sql = "insert into examstudent (Type,IDCard,ExamCard,StudentName,Location,Grade) values(?,?,?,?,?,?);";
        int num = update(sql, Type, IDCard, ExamCard, StudentName, Location, Grade);
        if(num > 0){
            System.out.println("添加成功");
        }else{
            System.out.println("添加失败");
        }
    }

    //问题2：输入身份证号或者准考证号，查询信息
    @Test
    public void queryWithIDCadeOrExamCard(){
        Scanner scanner = new Scanner(System.in);
        System.out.println("请选择你要输入的类型：");
        System.out.println("a.准考证号");
        System.out.println("b.身份证号");
        String selection = scanner.next();

        if("a".equalsIgnoreCase(selection)){
            System.out.println("请输入准考证号：");
            String examCard = scanner.next();
            String sql = "select FlowID FlowId,Type,IDCard,ExamCard,StudentName,Location,Grade from examstudent where ExamCard = ?;";
            Student instance = getInstance(Student.class, sql, examCard);
            if(instance != null)
                System.out.println(instance);
            else
                System.out.println("准考证号输入有误！");
        }else if("b".equalsIgnoreCase(selection)){
            System.out.println("请输入身份证号：");
            String IDCard = scanner.next();
            String sql = "select FlowID FlowId,Type,IDCard,ExamCard,StudentName,Location,Grade from examstudent where IDCard = ?;";
            Student instance = getInstance(Student.class, sql, IDCard);
            System.out.println(instance);
            if(instance != null)
                System.out.println(instance);
            else
                System.out.println("身份证号输入有误！");
        }else{
            System.out.println("输入有误,请重新进入程序!");
        }
    }

    //问题3：通过准考证号删除指定的学生信息
    @Test
    public void testDeleteByExamCard(){
        System.out.println("请输入学生的考号：");
        Scanner scanner = new Scanner(System.in);
        String ExamCard = scanner.next();
        //查询指定准考证号的学生
        String sql = "select FlowID FlowId,Type,IDCard,ExamCard,StudentName,Location,Grade from examstudent where ExamCard = ?;";
        Student student = getInstance(Student.class, sql, ExamCard);
        if(student == null){
            System.out.println("查无此人，重新输入!");
        }else{
            String sql1 = "delete from examstudent where ExamCard = ?;";
            int num = update(sql1, ExamCard);
            if(num > 0){
                System.out.println("删除成功!");
            }
        }
    }

    //优化后的操作
    @Test
    public void testDeleteByExamCard1(){
        System.out.println("请输入学生的考号：");
        Scanner scanner = new Scanner(System.in);
        String ExamCard = scanner.next();
        String sql1 = "delete from examstudent where ExamCard = ?;";
        int num = update(sql1, ExamCard);
        if(num > 0){
            System.out.println("删除成功!");
        }else{
            System.out.println("查无此人，请重新输入");
        }

    }

    public <T> T getInstance(Class<T> clazz, String sql, Object ...args){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtils.getConnection();

            st = conn.prepareStatement(sql);
            for(int i = 0; i < args.length;i++)
            {
                st.setObject(i+1,args[i]);
            }
            resultSet = st.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //通过ResultSetMetaData获取结果结中的列数
            int columnCount = rsmd.getColumnCount();

            if(resultSet.next()){
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for(int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = resultSet.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i+1);

                    //使用列名，运用反射给对象对应属性赋值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);

                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,st,resultSet);
        }
        return null;
    }

    //通用的增删改操作
    public int update(String sql, Object ...args){
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JDBCUtils.getConnection();
            st = conn.prepareStatement(sql);

            for(int i = 0; i < args.length; i++)
            {
                st.setObject(i+1,args[i]);
            }

            /**
             * st.execute():如果是查询，存在结果返回true
             * 增删改，没有结果，返回false；
             */
            return st.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,st);
        }
        return 0;
    }
}

操作BLOB类型字段

插入Blob类型的字段

//向数据表customers中插入Blob类型的字段
    @Test
    public void testInsert() throws SQLException, IOException, ClassNotFoundException {
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into customers(name,email,birth,photo) values(?,?,?,?);";
        PreparedStatement st = conn.prepareStatement(sql);
        st.setObject(1,"冉冰");
        st.setObject(2,"bing@123.com");
        st.setObject(3,"2020-07-05");

        FileInputStream in = new FileInputStream(new File("D:\\Program Files\\JetBrains\\jdbc\\src\\in.png"));
        st.setBlob(4,in);
        st.execute();
        JDBCUtils.closeResource(conn,st);
    }

查询的Blob类型字段

//查询customers数据表中的Blob类型字段
    @Test
    public  void testQuery(){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet resultSet = null;
        InputStream is = null;
        FileOutputStream fos = null;

        try {
            conn = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth,photo from customers where id = ?;";


            st = conn.prepareStatement(sql);
            st.setObject(1,23);
            resultSet = st.executeQuery();
            if(resultSet.next()){
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String email = resultSet.getString(3);
                Date birth = resultSet.getDate(4);
                Customer customer = new Customer(id, name, email, birth);

                //将Blob类型的字段下载下来，保存为本地的图片
                Blob photo = resultSet.getBlob(5);
                is = photo.getBinaryStream();
                fos = new FileOutputStream("rb.png");
                byte[] buffer = new byte[1024];
                int len;
                while((len = is.read(buffer)) != -1){
                    fos.write(buffer,0,len);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,st,resultSet);
            try {
                if(is != null)
                    is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                if(fos != null)
                    fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

批量插入数据

方式一

使用Statement，略

方式二

//批量插入，方式二：使用PreparedStatement
    @Test
    public void testInsert1(){
        Connection conn = null;
        PreparedStatement st = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCUtils.getConnection();
            String sql = "insert into goods (name) values(?);";
            st = conn.prepareStatement(sql);
            for(int i = 1; i <= 20000; i++){
                st.setObject(1,"name" + i);
                st.execute();
            }
            long end = System.currentTimeMillis();
            System.out.println((end-start) + "ms");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,st);
        }
    }

方式三

//批量插入：方式三
    /**
     * 1.addBatch()、executeBatch(),clearBatch()
     * 2.mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
     *   ?rewriteBatchedStatements=true 写在配置文件的url后面
     */
    @Test
    public void testInsert2(){
        Connection conn = null;
        PreparedStatement st = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCUtils.getConnection();
            String sql = "insert into goods (name) values(?)";
            st = conn.prepareStatement(sql);
            for(int i = 1; i <= 1000000; i++){
                st.setObject(1,"name" + i);
                //1.攒sql
                st.addBatch();
                if(i % 500 == 0){
                    //2.执行Batch
                    st.executeBatch();
                    //3.清空Batch
                    st.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println((end-start) + "ms");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,st);
        }
    }

方式四

//批量插入：方式四:设置不允许自动提交数据
    @Test
    public void testInsert3(){
        Connection conn = null;
        PreparedStatement st = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCUtils.getConnection();

            //设置不允许自动提交数据
            conn.setAutoCommit(false);

            String sql = "insert into goods (name) values(?)";
            st = conn.prepareStatement(sql);
            for(int i = 1; i <= 1000000; i++){
                st.setObject(1,"name" + i);
                //1.攒sql
                st.addBatch();
                if(i % 500 == 0){
                    //2.执行Batch
                    st.executeBatch();
                    //3.清空Batch
                    st.clearBatch();
                }
            }
            //提交数据
            conn.commit();
            long end = System.currentTimeMillis();
            System.out.println((end-start) + "ms");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,st);
        }
    }

事务

/**
 * 1.什么叫事务？
 *      事务：一组逻辑操作单元，使数据从一种状态变换到另外一种状态
 *          > 一组逻辑操作单元：一个或多个DML操作
 * 2.事务处理的原则？
 *      保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。
 *      当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；
 *      要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。
 * 3.数据一旦提交就不可以回滚
 * 4.哪些操作会导致数据的自动提交？
 *      > DDL操作一旦执行，就会自动提交
 *          >conn.setAutoCommit(false)对DDL无效
 *      > DML默认情况下，一旦执行，就会自动提交
 *          > 我们可以通过conn.setAutoCommit(false);来取消DML的自动提交
 *      > 默认在关闭连接的时候，也会自动提交
 */

//====考虑事务的转账操作====(出问题了这个代码！！！)
    @Test
    public void testUpdateWithTX(){
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();

            //1.取消数据的自动提交
            conn.setAutoCommit(false);
            String sql1 = "update user_table set balance = balance - 100 where user = ?";
            update(conn,sql1,"AA");

            //模拟网络异常
        System.out.println(10/0);

            String sql2 = "update user_table set balance = balance + 100 where user = ?";
            update(conn,sql2,"BB");
            System.out.println("转账成功");
            //2.提交数据
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            //回滚数据
            try {
                //3.回滚数据
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            //修改为自动提交数据
            //主要针对数据路连接池的使用
            try {
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            JDBCUtils.closeResource(conn,null);
        }

    }

//通用的增删改操作:version 2.0
    public int update(Connection conn,String sql, Object ...args){
        PreparedStatement st = null;

        try {
            conn = JDBCUtils.getConnection();
            st = conn.prepareStatement(sql);

            for(int i = 0; i < args.length; i++)
            {
                st.setObject(i+1,args[i]);
            }

            return st.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(null,st);
        }
        return 0;
    }

DAO

①.编写一个通用的BaseDAO类，提供通用的增删改查方法

②.针对特定类所需要的功能编写一个接口

③.编写一个继承BaseDAO，实现接口的类

④.测试

基础

package com.atguigu.dao;

import com.atguigu.util.JDBCUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * DAO:data(base) access object
 * 封装了针对于数据表的通用对的操作
 */
public class BaseDao {



    //查询特殊值的通用方法
    public <E> E getVaulue(Connection conn,String sql, Object ...args){
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++){
                st.setObject(i+1,args[i]);
            }
            rs = st.executeQuery();
            if(rs.next()){
                return (E) rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,st,rs);
        }
        return null;
    }


    /**
     * @Decription 查询多行记录
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public <T> List<T> getForList(Connection conn,Class<T> clazz, String sql, Object ...args){
        PreparedStatement st = null;
        ResultSet resultSet = null;
        try {

            st = conn.prepareStatement(sql);
            for(int i = 0; i < args.length;i++)
            {
                st.setObject(i+1,args[i]);
            }
            resultSet = st.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //通过ResultSetMetaData获取结果结中的列数
            int columnCount = rsmd.getColumnCount();
            ArrayList<T> list = new ArrayList<>();
            //处理数据集中的每一行数据，给每一行数据都分配一个T对象，对象赋值完毕，保存造T的List中
            while(resultSet.next()){
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for(int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = resultSet.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i+1);

                    //使用列名，运用反射给对象对应属性赋值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,st,resultSet);
        }
        return null;
    }

    //通用的查询2.0，特点：考虑上事务
    public <T> T getInstance(Connection conn,Class<T> clazz, String sql, Object ...args){
        PreparedStatement st = null;
        ResultSet resultSet = null;
        try {
            st = conn.prepareStatement(sql);
            for(int i = 0; i < args.length;i++)
            {
                st.setObject(i+1,args[i]);
            }
            resultSet = st.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //通过ResultSetMetaData获取结果结中的列数
            int columnCount = rsmd.getColumnCount();

            if(resultSet.next()){
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for(int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = resultSet.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i+1);

                    //使用列名，运用反射给对象对应属性赋值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);

                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,st,resultSet);
        }
        return null;
    }

    //通用的增删改操作:version 2.0，特点：考虑上事务
    public int update(Connection conn, String sql, Object ...args){
        PreparedStatement st = null;

        try {
            st = conn.prepareStatement(sql);

            for(int i = 0; i < args.length; i++)
            {
                st.setObject(i+1,args[i]);
            }

            return st.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(null,st);
        }
        return 0;
    }
}

package com.atguigu.dao;

import com.atguigu.bean.Customer;

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

/*
* 此接口用来定义customers表的常用操作
*
* */
public interface CustomerDAO {
    /**
     * @Description 将cust对象添加到数据库中
     * @param conn
     * @param cust
     */
    void insert(Connection conn, Customer cust) throws SQLException;

    /**
     * @Description 根据指定的Id，删除表中的一条记录
     * @param conn
     * @param id
     */
    void deleteById(Connection conn, int id);

    /**
     * @Description 针对cust对象，修改数据表的记录
     * @param conn
     * @param cust
     */
    void update(Connection conn, Customer cust);

    /**
     * @Description 针对指定的id查询对应的Customer对象
     * @param conn
     * @param Id
     * @return
     */
    Customer getCustomerById(Connection conn, int Id);

    /**
     * @Description 查询表中所有的记录
     * @param conn
     * @return
     */
    List<Customer> getAll(Connection conn);

    /**
     * @Description 返回数据表中所有的条目数
     * @param conn
     * @return
     */
    Long getCount(Connection conn);

    /**
     * @Description 返回数据表中最大的生日
     * @param conn
     * @return
     */
    Date getMaxBirth(Connection conn);

}

package com.atguigu.dao;

import com.atguigu.bean.Customer;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

public class CustomerDAOImpl extends BaseDao implements CustomerDAO{
    @Override
    public void insert(Connection conn, Customer cust){
        String sql = "insert into customers (name,email,birth) values(?,?,?)";
        update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth());
    }

    @Override
    public void deleteById(Connection conn, int id) {
        String sql = "delete from customers where id = ?";
        update(conn,sql,id);
    }

    @Override
    public void update(Connection conn, Customer cust) {
        String sql = "update customers set name = ?, email = ?, birth = ? where id = ?";
        update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
    }

    @Override
    public Customer getCustomerById(Connection conn, int Id) {
        String sql = "select id,name,email,birth from customers where id = ?";
        Customer instance = getInstance(conn, Customer.class, sql, Id);
        return instance;
    }

    @Override
    public List<Customer> getAll(Connection conn) {
        String sql = "select id,name,email,birth from customers";
        List<Customer> forList = getForList(conn, Customer.class, sql);
        return forList;
    }

    @Override
    public Long getCount(Connection conn) {
        String sql = "select count(*) from customers";
        return getVaulue(conn, sql);
    }

    @Override
    public Date getMaxBirth(Connection conn) {
        String sql = "select max(birth) from customers" ;
        return getVaulue(conn,sql);
    }
}

改进

package com.atguigu.dao2;

import com.atguigu.util.JDBCUtils;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * DAO:data(base) access object
 * 封装了针对于数据表的通用对的操作
 */
public abstract class BaseDao<T> {

    private Class<T> clazz = null;


    {
        //获取子类的class -> 获取父类的原始class
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        //将原始class -> 带参数class，将泛型
        ParameterizedType paramType = (ParameterizedType) genericSuperclass;
        //获取泛型参数
        Type[] actualTypeArguments = paramType.getActualTypeArguments();//获取泛型参数
        //为对象赋值
        clazz = (Class<T>) actualTypeArguments[0];//获取了泛型的一个参数
    }


    //查询特殊值的通用方法
    public <E> E getVaulue(Connection conn,String sql, Object ...args){
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++){
                st.setObject(i+1,args[i]);
            }
            rs = st.executeQuery();
            if(rs.next()){
                return (E) rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,st,rs);
        }
        return null;
    }


    /**
     * @Decription 查询多行记录
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public List<T> getForList(Connection conn, String sql, Object ...args){
        PreparedStatement st = null;
        ResultSet resultSet = null;
        try {

            st = conn.prepareStatement(sql);
            for(int i = 0; i < args.length;i++)
            {
                st.setObject(i+1,args[i]);
            }
            resultSet = st.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //通过ResultSetMetaData获取结果结中的列数
            int columnCount = rsmd.getColumnCount();
            ArrayList<T> list = new ArrayList<>();
            //处理数据集中的每一行数据，给每一行数据都分配一个T对象，对象赋值完毕，保存造T的List中
            while(resultSet.next()){
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for(int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = resultSet.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i+1);

                    //使用列名，运用反射给对象对应属性赋值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,st,resultSet);
        }
        return null;
    }

    //通用的查询2.0，特点：考虑上事务
    public T getInstance(Connection conn,String sql, Object ...args){
        PreparedStatement st = null;
        ResultSet resultSet = null;
        try {
            st = conn.prepareStatement(sql);
            for(int i = 0; i < args.length;i++)
            {
                st.setObject(i+1,args[i]);
            }
            resultSet = st.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //通过ResultSetMetaData获取结果结中的列数
            int columnCount = rsmd.getColumnCount();

            if(resultSet.next()){
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for(int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = resultSet.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i+1);

                    //使用列名，运用反射给对象对应属性赋值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);

                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,st,resultSet);
        }
        return null;
    }

    //通用的增删改操作:version 2.0，特点：考虑上事务
    public int update(Connection conn, String sql, Object ...args){
        PreparedStatement st = null;

        try {
            st = conn.prepareStatement(sql);

            for(int i = 0; i < args.length; i++)
            {
                st.setObject(i+1,args[i]);
            }

            return st.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(null,st);
        }
        return 0;
    }
}

package com.atguigu.dao2;

import com.atguigu.bean.Customer;

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

/*
* 此接口用来定义customers表的常用操作
*
* */
public interface CustomerDAO {
    /**
     * @Description 将cust对象添加到数据库中
     * @param conn
     * @param cust
     */
    void insert(Connection conn, Customer cust) throws SQLException;

    /**
     * @Description 根据指定的Id，删除表中的一条记录
     * @param conn
     * @param id
     */
    void deleteById(Connection conn, int id);

    /**
     * @Description 针对cust对象，修改数据表的记录
     * @param conn
     * @param cust
     */
    void update(Connection conn, Customer cust);

    /**
     * @Description 针对指定的id查询对应的Customer对象
     * @param conn
     * @param Id
     * @return
     */
    Customer getCustomerById(Connection conn, int Id);

    /**
     * @Description 查询表中所有的记录
     * @param conn
     * @return
     */
    List<Customer> getAll(Connection conn);

    /**
     * @Description 返回数据表中所有的条目数
     * @param conn
     * @return
     */
    Long getCount(Connection conn);

    /**
     * @Description 返回数据表中最大的生日
     * @param conn
     * @return
     */
    Date getMaxBirth(Connection conn);

}

package com.atguigu.dao2;

import com.atguigu.bean.Customer;

import java.sql.Connection;
import java.sql.Date;
import java.util.List;

public class CustomerDAOImpl extends BaseDao<Customer> implements CustomerDAO {
    @Override
    public void insert(Connection conn, Customer cust){
        String sql = "insert into customers (name,email,birth) values(?,?,?)";
        update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth());
    }

    @Override
    public void deleteById(Connection conn, int id) {
        String sql = "delete from customers where id = ?";
        update(conn,sql,id);
    }

    @Override
    public void update(Connection conn, Customer cust) {
        String sql = "update customers set name = ?, email = ?, birth = ? where id = ?";
        update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
    }

    @Override
    public Customer getCustomerById(Connection conn, int Id) {
        String sql = "select id,name,email,birth from customers where id = ?";
        Customer instance = getInstance(conn,sql, Id);
        return instance;
    }

    @Override
    public List<Customer> getAll(Connection conn) {
        String sql = "select id,name,email,birth from customers";
        List<Customer> forList = getForList(conn,sql);
        return forList;
    }

    @Override
    public Long getCount(Connection conn) {
        String sql = "select count(*) from customers";
        return getVaulue(conn, sql);
    }

    @Override
    public Date getMaxBirth(Connection conn) {
        String sql = "select max(birth) from customers" ;
        return getVaulue(conn,sql);
    }
}

数据库连接池

C3P0

查看doc中的index获取详细的配置信息

//方式一
    @Test
    public void testGetConnection() throws Exception {
        //获取C3P0数据库连接池
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass( "com.mysql.cj.jdbc.Driver" ); //loads the jdbc driver
        cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
        cpds.setUser("root");
        cpds.setPassword("root");
        cpds.setInitialPoolSize(10);//设置初始时数据库连接池中的连接数

        Connection conn = cpds.getConnection();
        System.out.println(conn);
    }
    //方式2：使用配置文件
    @Test
    public void testGetConnection2() throws SQLException {
        ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
        Connection conn = cpds.getConnection();
        System.out.println(conn);
    }

<?xml version="1.0" encoding="UTF-8"?>

<c3p0-config>
    <named-config name="helloc3p0">
        <!--
            提供获取连接的四个基本信息
        -->
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
        <property name="user">root</property>
        <property name="password">root</property>

        <!--
            进行数据库连接池进行管理的基本信息
        -->
        <!--当数据库连接池中连接不够，C3P0一次性向数据库申请的连接数-->
        <property name="acquireIncrement">50</property>
        <!--c3p0数据库连接池中初始化时的连接数-->
        <property name="initialPoolSize">100</property>
        <!--c3p0数据库连接池中维护的最少连接数-->
        <property name="minPoolSize">50</property>
        <!--c3p0数据库连接池中维护的最多连接数-->
        <property name="maxPoolSize">1000</property>
        <!--c3p0数据库连接池中维护的最多statement的个数-->
        <property name="maxStatements">50</property>
        <!--每个连接最多可以使用的statement的个数-->
        <property name="maxStatementsPerConnection">2</property>

    </named-config>
</c3p0-config>

DBCP

//方式一
    @Test
    public void testGetConnection() throws SQLException {
        //创建了DBCP的数据库连接池
        BasicDataSource source = new BasicDataSource();

        //设置基本信息
        source.setDriverClassName("com.mysql.cj.jdbc.Driver");
        source.setUrl("jdbc:mysql://localhost:3306/test");
        source.setUsername("root");
        source.setPassword("root");

        //还可以设置其他设计数据库连接池的管理属性
        source.setInitialSize(10);
        source.setMaxActive(10);
        //。。。

        Connection conn = source.getConnection();
        System.out.println(conn);
    }

    //方式二:使用配置信息
	private static DataSource dataSource = null;
    static{
        try {
            InputStream is = DBCPTest.class.getClassLoader().getResourceAsStream("dbcp.properties");
            Properties pros = new Properties();
            pros.load(is);
            dataSource = BasicDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    @Test
    public void testGetConnection1() throws Exception {
        Connection conn = dataSource.getConnection();
        System.out.println(conn);
    }

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&rewriteBatchedStatements=true
username=root
password=root
initialSize=10

Druid

properties文件

username=root
password=root
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=utf8&useSSL=false

initialSize=5

maxActive=10

/**
     * 使用druid数据库连接池技术
     */
    private static DataSource source = null;
    static{
        try {
            InputStream is = DruidTest.class.getClassLoader().getResourceAsStream("druid.properties");
            Properties pros = new Properties();
            pros.load(is);
            source = DruidDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    public static Connection getConnection() throws Exception {

        Connection conn = source.getConnection();
//        System.out.println(conn);
        return conn;
    }

dbutils

commons-dbutils 时Apache组织提供的一个开源JDBC工具类库，封装了针对数据库的增删改查操作

//dbutils测试
public class QueryRunnerTest {
    @Test
    public void insertTest() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection();
            String sql = "insert into customers (name,email,birth) values(?,?,?)";
            int num = runner.update(conn, sql, "女巫", "12306@123.com", new Date(1232131231L));
            if (num > 0) {
                System.out.println("插入成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    /**
     * BeanHandler是ReasultSetHandler接口的实现类，用于封装表中的一条记录
     *
     * @throws Exception
     */
    @Test
    public void queryTest() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth from customers where id = ?";
            BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
            Customer query = runner.query(conn, sql, handler, 23);
            System.out.println(query);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    /**
     * BeanListHandler:封装表中的多条记录
     *
     * @throws Exception
     */
    @Test
    public void queryTest1() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth from customers where id < ?";
            BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
            List<Customer> query = runner.query(conn, sql, handler, 23);
            query.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    /**
     * MapHandler:是ReasultSetHandler接口的实现类，对应表中的一条记录，将字段和相应字段的值作为key和value
     */
    @Test
    public void queryTest2() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth from customers where id = ?";
            MapHandler handler = new MapHandler();
            Map<String, Object> query = runner.query(conn, sql, handler, 23);
            System.out.println(query);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    @Test
    public void queryTest3() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth from customers where id < ?";
            MapListHandler handler = new MapListHandler();
            List<Map<String, Object>> query = runner.query(conn, sql, handler, 23);
            query.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }
    
    /**
     * ScalarHandler:用于特殊值的查询
     */
    @Test
    public void queryTest4() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection();

            String sql = "select count(*) from customers";
            ScalarHandler scalarHandler = new ScalarHandler();
            Long query = (Long) runner.query(conn, sql, scalarHandler);
            System.out.println(query);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    @Test
    public void queryTest5() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection();

            String sql = "select max(birth) from customers";
            ScalarHandler scalarHandler = new ScalarHandler();
            Date query = (Date)runner.query(conn, sql, scalarHandler);
            System.out.println(query);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }
}