JAVA-JDBC连接池

最新推荐文章于 2024-09-18 00:12:04 发布

御狐王

最新推荐文章于 2024-09-18 00:12:04 发布

阅读量902

点赞数 18

文章标签： java 开发语言

本文链接：https://blog.youkuaiyun.com/yuhuwangxy/article/details/136103011

版权

JDBC

Java程序操作各种各样的数据库不方便需要一个接口统一处理

API

统一规范了一系列应用程序与数据库的连接执行SQL语句得到返回结果各类操作相关类和接口在 java.sql javax.sql 包中

JDBC程序编写思路

注册驱动得到Driver类
获取连接得到Connection
执行CRUD 发送SQL给MySQL执行
释放资源关闭

模拟JDBC程序

package com.yuhuw.jdbc.myjdbc;

/*
 *@author   yuhuw
 *@date     2024/2/3 10:21
 *@version  1.0
 这是jdbc的接口
 */public interface JdbcInterface {
     //连接接口
     public Object getConnection();
     //关闭连接
     public void  closeConnection();

     public void crud();
}

/*
 *@author   yuhuw
 *@date     2024/2/3 12:52
 *@version  1.0
 这是mysql应该要制作的jar包来支持jdbc的规范
 */
public class MysqlJdbc implements JdbcInterface{


    @Override
    public Object getConnection() {
        System.out.println("Mysql已连接");
        return null;
    }

    @Override
    public void closeConnection() {
        System.out.println("Mysql已关闭");

    }

    @Override
    public void crud() {
        System.out.println("Mysql CRUD");

    }
}

package com.yuhuw.jdbc.myjdbc;

/*
 *@author   yuhuw
 *@date     2024/2/3 13:54
 *@version  1.0
 oracl数据库的jar需要做的
 */
public class OracleJdbc implements JdbcInterface{
    @Override
    public Object getConnection() {
        System.out.println("Oracle已连接");
        return null;
    }

    @Override
    public void closeConnection() {
        System.out.println("Oracle已关闭");
    }

    @Override
    public void crud() {
        System.out.println("CRUD");
    }
}

package com.yuhuw.jdbc.myjdbc;

/*
 *@author   yuhuw
 *@date     2024/2/3 13:51
 *@version  1.0
 调用jdbc具体调用
 */
public class TestJdbc {
    public static void main(String[] args) {
        JdbcInterface jdbcInterface = new MysqlJdbc();
        jdbcInterface.getConnection();//通过接口调用实现类 动态绑定
        jdbcInterface.crud();
        jdbcInterface.closeConnection();

        jdbcInterface = new OracleJdbc();
        jdbcInterface.getConnection();//通过接口调用实现类 动态绑定
        jdbcInterface.crud();
        jdbcInterface.closeConnection();
    }
}

mysql jar包的安装和删除

安装

在这里插入图片描述

删除

在这里插入图片描述

使用idea连接mysql数据库进行增删改查

package com.yuhuw.jdbc;

import com.mysql.jdbc.Driver;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/*
 *@author   yuhuw
 *@date     2024/2/4 13:01
 *@version  1.0
 */
public class jdbc01 {
    public static void main(String[] args) throws SQLException {
        //1.导入驱动jar包
        //2.注册驱动
        Driver driver = new Driver();
        //3.获取数据库连接对象
        //jdbc:mysql: 规定
        //localhost: 表示mysql服务器所在的计算机的ip地址
        //3306： 默认的mysql的端口号
        //yuhuw_db1： 连接到mysql服务器中哪个数据库
        //本质就是 socket连接
        String url = "jdbc:mysql://localhost:3306/yuhuw_db02";
        //4.定义sql语句
        //String sql = "insert into actor values(null,'张三丰','男','2000-12-12','110')";
        //String sql = "update actor set name = '周星驰' where id =1";
        String sql = "delete from actor where id =1";
        Properties properties = new Properties();
        //user 和 password 是固定的 后面的值自己选择
        properties.setProperty("user","root");
        properties.setProperty("password","yuhuw");
        Connection connect = driver.connect(url, properties);
        //5.获取执行静态sql语句的对象Statement
        Statement statement = connect.createStatement();
        //6.执行sql，接受返回结果
        //如果是dml语句，执行成功，返回值是影响的行数
        int rows = statement.executeUpdate(sql);
        //7.处理结果
        System.out.println(rows>0?"成功":"失败");
        //8.释放资源
        statement.close();
        connect.close();
        //评价 灵活性不强 依赖性高 的第三方连接方式
    }
}

连接的五种方式

package com.yuhuw.jdbc;

import com.mysql.jdbc.Driver;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/*
 *@author   yuhuw
 *@date     2024/2/4 17:08
 *@version  1.0
 * java连接的5中方式
 */
public class JdbcConnect {
    //方式一：
    //Driver driver = new com.mysql.jdbc.Driver();
    //方式二：
    //Driver driver = new com.mysql.cj.jdbc.Driver();
    //方式三：
    //Driver driver = new com.mysql.cj.jdbc.Driver();
    //方式四：
    //Driver driver = new com.mysql.cj.jdbc.Driver();
    //方式五：
    //Driver driver = new com.mysql.cj.jdbc.Driver();
    @Test
    public void connect01() throws SQLException {
        Driver driver = new Driver();
        String url = "jdbc:mysql://localhost:3306/yuhuw_db02";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "yuhuw");
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
    }

    @Test
    //方式二 使用反射
    public void connect02() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver)aClass.newInstance();
        String url = "jdbc:mysql://localhost:3306/yuhuw_db02";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "yuhuw");
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);

    }

    @Test
    //方式三 使用DriverManager 统一管理
    public void connect03() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver)aClass.newInstance();
        //注册驱动
        DriverManager.registerDriver(driver);

        String url = "jdbc:mysql://localhost:3306/yuhuw_db02";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "yuhuw");
        Connection connect = DriverManager.getConnection(url, properties);
        System.out.println(connect);
    }

    @Test
    //方式四 使用Class.forName() 反射获取Driver实现类 自动注册驱动
    //所以推荐使用这种方式
    public void connect04() throws SQLException, ClassNotFoundException {
        //在加载Driver类的时候会自动注册源码
        /*
        静态代码块在类加载的时候会执行一次
        因此已经注册驱动
            static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
        */
        //Class.forName("com.mysql.jdbc.Driver");
        //mysql驱动5.16以上甚至可以省略 在jdk1.5之后无需这段代码 因为使用jdbc4会自动调用
        //jar包下的META-INF\services\java.sql.Driver 文件中的Driver类注册
        //建议写上防止驱动变化导致程序错误
        String url = "jdbc:mysql://localhost:3306/yuhuw_db02";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "yuhuw");
        Connection connect = DriverManager.getConnection(url, properties);

        System.out.println(connect);
    }

    @Test
    //方式五 增加配置文件 连接更灵活
    public void connect05() throws SQLException, IOException, ClassNotFoundException {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        //获取相关值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);//建议写上
        Connection connection = DriverManager.getConnection(url, user, password);

        System.out.println(connection);

    }

}

user=root
password=yuhuw
url=jdbc:mysql://localhost:3306/yuhuw_db02
driver=com.mysql.jdbc.Driver

resultSet底层

package com.yuhuw.jdbc.resultset;

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

/*
 *@author   yuhuw
 *@date     2024/2/5 14:49
 *@version  1.0
 */
public class ResultSet {

    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);

        Statement statement = connection.createStatement();

        //执行给定的SQL语句
        String sql = "select id,name,sex,borndate from actor";

        /*
        +----+-------+-----+---------------------+-------+
        | id | name  | sex | borndate            | phone |
        +----+-------+-----+---------------------+-------+
        |  2 | jack  | 男  | 1999-03-02 00:00:00 | 23412 |
        |  3 | Alice |     | 2005-03-02 00:00:00 | 82311 |
        +----+-------+-----+---------------------+-------+
        */
        java.sql.ResultSet resultSet = statement.executeQuery(sql);
        /*
        ResultSet 一个接口 JDBC42ResultSet类实现了这个接口
        data数据存储在ResultSet对象中，是一个字节数组
        具体路径rowData下的rows下的elementData 数据类型是byte 用ASCII码 与源数据对应


        */

        while(resultSet.next()){
            //光标向后移动如果没有更多行返回false
            int id = resultSet.getInt(1);//获取改行的第一列数据
            String name = resultSet.getString(2);
            String sex = resultSet.getString(3);
            Date date = resultSet.getDate(4);
            System.out.println(id+" "+name+" "+sex+" "+date);
        }

        resultSet.close();
        statement.close();
        connection.close();
    }
}

MySQL注入

package com.yuhuw.jdbc.statement_;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

/*
 *@author   yuhuw
 *@date     2024/2/5 19:59
 *@version  1.0
 * 演示SQL注入
 */
public class Statement_ {

    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {

        Scanner scanner = new Scanner(System.in);
        System.out.println("please input name:");
        //让用户输入name和password next()收到控制或者'就结束
        String admin_name = scanner.nextLine();//回车表示结束
        System.out.println("please input password:");
        String admin_pwd= scanner.nextLine();

        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);

        Statement statement = connection.createStatement();
        //执行给定的SQL语句
        String sql = "select name,pwd from admin where " +
                "name ='"+admin_name+"' and pwd ='"+admin_pwd+"'";
        ResultSet resultSet = statement.executeQuery(sql);
        if (resultSet.next()) {
            System.out.println("OK");
        }else {
            System.out.println("failed");
        }

        resultSet.close();
        statement.close();
        connection.close();
        scanner.close();

    }
}

preparedStatement

package com.yuhuw.jdbc.preparedstatement_;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

/*
 *@author   yuhuw
 *@date     2024/2/5 20:21
 *@version  1.0
 *好处
 */
public class PreparedStatement_ {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //1.不在使用+拼接sql语句 减少语法错误
        //2.有效解决注入的问题
        //3.效率更高
        Scanner scanner = new Scanner(System.in);
        System.out.println("please input name:");
        //让用户输入name和password next()收到控制或者'就结束
        String admin_name = scanner.nextLine();//回车表示结束
        System.out.println("please input password:");
        String admin_pwd= scanner.nextLine();

        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);
        String sql = "select name , pwd from admin where name =?and pwd = ?";
        //添加
        String sql1="insert into admin(name,pwd) values(?,?)";
        //修改

        //创建preparedStatement对象
        //?占位符
        //preparedStatement对象实现了PreparedStatement接口实现类的对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);
        //给?赋值 这里对sql处理了
        preparedStatement.setString(1,admin_name);
        preparedStatement.setString(2,admin_pwd);
        preparedStatement1.setString(1,admin_name);
        //执行sql 查询是executeQuery 如果执行的是dml executeUpdate
        //执行的时候不能带上sql 否则会报错
        //ResultSet resultSet = preparedStatement.executeQuery(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            System.out.println("OK");
        }else {
            System.out.println("failed");
        }

        resultSet.close();
        preparedStatement.close();
        connection.close();
        scanner.close();

    }
}

JDBC API

package com.yuhuw.jdbc;

/*
 *@author   yuhuw
 *@date     2024/2/6 12:41
 *@version  1.0
 */
public class APISummary {
    /*DriverManager驱动管理类 getConnection(String url, String user, String password) 获取连接
    Connection接口 createStatement() 创建Statement对象
    prepareStatement 生成预处理对象
    Statement executeUpdate 执行dml语句 返回影响的行数
              executeQuery 执行dql语句 返回ResultSet结果集
              execute 执行任意sql语句 返回布尔值
    preparedStatement        executeUpdate 执行dml
                             executeQuery 执行查询 返回resultSet
                             execute 执行任意sql语句 返回布尔值
                             SetXxx(,) 站位索引 赋值 解决SQL注入
    resultSet next 向下移动一行
              previous 向上移动一行
              getXxx 获取列数据


    */
}

JDBCUtils 工具类

package com.yuhuw.jdbc.utils;

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

/*
 *@author   yuhuw
 *@date     2024/2/6 12:58
 *@version  1.0
 * 统一操作连接 sql 关闭
 */
public class JDBCUtils {
    private static String user;
    private static String password;
    private static String url;
    private static String driver;

    // 静态代码块，用于初始化静态变量
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\mysql.properties"));
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver = properties.getProperty("driver");

        } catch (IOException e) {
            //编译异常就只会抛出
            //实际开发中 编译异常转出运行异常 调用者可以捕获也可以选择默认处理
            throw new RuntimeException(e);
        }
    }
    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    // 关闭相关资源
    /*
    1.ResultSet
    2.Statement
    3.Connection
    */
    public static void close(ResultSet set, Statement statement,Connection connection){
        try {
            if (set != null) {
                set.close();
            }
            if(connection != null){
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

工具类的使用

package com.yuhuw.jdbc.utils;

import java.sql.*;

/*
 *@author   yuhuw
 *@date     2024/2/6 13:47
 *@version  1.0
 */
public class Utils_use {
    public static void main(String[] args) {
        new Utils_use().testDML();
        new Utils_use().testDQL();
    }

public void testDML(){
    Connection connection =JDBCUtils.getConnection();
    String sql = "update actor set name = ? where id = ?";
    PreparedStatement preparedStatement = null;

    try {
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"Mery");
        preparedStatement.setInt(2,2);
        preparedStatement.executeUpdate();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }finally {
        JDBCUtils.close(null,preparedStatement,connection);
    }
}
public void testDQL(){
    Connection connection =JDBCUtils.getConnection();
    String sql = "select * from actor";
    PreparedStatement preparedStatement = null;
    ResultSet set = null;
    try {
        preparedStatement = connection.prepareStatement(sql);
        set = preparedStatement.executeQuery(sql);

        while (set.next()){
            int id = set.getInt("id");
            String name = set.getString("name");
            String sex = set.getString("sex");
            Date borndate = set.getDate("borndate");
            System.out.println(id+"\t"+name+"\t"+sex+"\t"+borndate);
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }finally {
        JDBCUtils.close(set,preparedStatement,connection);
    }

}
}

事务

基本介绍
1.JDBC程序中当一个Connection对象创建时，默认情况下是自动提交事务:每
次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚
2.JDBC程序中为了让多个 SQL 语句作为一个整体执行，需要使用事务
3.调用 Connection 的 setAutoCommit(false)可以取消自动提交事务
4.在所有的 SQL 语句都成功执行后，调用 commit(; 方法提交事务
5.在其中某个操作失败或出现异常时，调用 rollback0; 方法回滚事务

package com.yuhuw.jdbc.transaction_;

import com.yuhuw.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/*
 *@author   yuhuw
 *@date     2024/2/6 15:46
 *@version  1.0
 * 通过事务转账
 */
public class Transaction {
    @Test
    //没有事务
    public void noTransaction() {
        Connection connection = JDBCUtils.getConnection();
        String sql = "update account set balance = balance-100 where id = 100";
        String sql2 = "update account set balance = balance+100 where id = 200";
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            int i = 1 / 0;//抛出异常 这个会导致转账失败
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }

    @Test
    //有事务
    public void useTransaction() {
        Connection connection = null;
        String sql = "update account set balance = balance-100 where id = 100";
        String sql2 = "update account set balance = balance+100 where id = 200";
        PreparedStatement preparedStatement = null;

        try {
            connection = JDBCUtils.getConnection();
            connection.setAutoCommit(false);
            //取消自动提交 相当开启了事务
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();
            //出现异常的时候可以回撤
            int i = 1 / 0;//抛出异常 这个会导致转账失败
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            System.out.println("转账失败");
            //默认回归到事务开始的状态
            try {
                connection.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);

        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }
}

批处理

package com.yuhuw.jdbc.batch_;

import com.yuhuw.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/*
 *@author   yuhuw
 *@date     2024/2/7 15:06
 *@version  1.0
 * 演示java批量处理
 */
public class Batch_ {
    // 传统方法添加 5000条数据
    @Test
    public void noBatch() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admin2 values(null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack" + i);
            preparedStatement.setString(2, "666");
            preparedStatement.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("传统方法添加 5000条数据耗时：" + (end - start) + "ms");
        //传统方法添加 5000条数据耗时：2908ms
        JDBCUtils.close(null, preparedStatement, connection);

    }

    // 批量处理5000条数据
    @Test
    public void Batch() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admin2 values (null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack" + i);
            preparedStatement.setString(2, "666");
            //将SQL语句加入批处理包中
            /*底层是ArrayList
            public void addBatch() throws SQLException {
                synchronized(this.checkClosed().getConnectionMutex()) {
               1. 第一次会创建ArrayList elementData object[]
               2. elementData 会存放预处理的SQL语句
               3. 当elementData满后 按照1.5被扩容
                    if (this.batchedArgs == null) {
                        this.batchedArgs = new ArrayList();
                    }

                    for(int i = 0; i < this.parameterValues.length; ++i) {
                        this.checkAllParametersSet(this.parameterValues[i], this.parameterStreams[i], i);
                    }

                    this.batchedArgs.add(new BatchParams(this.parameterValues, this.parameterStreams, this.isStream, this.streamLengths, this.isNull));
                }
            }



             */
            preparedStatement.addBatch();
            //当有1000条记录时批量执行
            if ((i + 1) % 1000 == 0) {
                preparedStatement.executeBatch();
                //清空批处理包
                preparedStatement.clearBatch();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("批量处理添加 5000条数据耗时：" + (end - start) + "ms");
        //批量处理添加 5000条数据耗时：2880ms
        //增加?rewriteBatchedStatements=true后 批量处理添加 5000条数据耗时：37ms
        JDBCUtils.close(null, preparedStatement, connection);
    }
}

user=root
password=yuhuw
url=jdbc:mysql://localhost:3306/yuhuw_db02?rewriteBatchedStatements=true
driver=com.mysql.jdbc.Driver

CREATE TABLE admin2(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(32) NOT NULL,
`password` VARCHAR(32)  NOT NULL
);

连接池

传统方法弊端

package com.yuhuw.jdbc.datasource;

import com.yuhuw.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;

/*
 *@author   yuhuw
 *@date     2024/2/8 14:46
 *@version  1.0
 */
public class ConQuestion {
    //代码 连接mysql 5000 次
    @Test
    public void testCon() {
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {

            Connection connection = JDBCUtils.getConnection();
            //没有关闭会抛出异常"Too many connections"

            //即使关闭 传统5000次花费的时间为：4157
            JDBCUtils.close(null, null, connection);

        }
        long end = System.currentTimeMillis();
        System.out.println("传统5000次花费的时间为：" + (end - start));

    }
}
/*
传统获取Connection问题分析
1.传统的JDBC数据库连接使用 DriverManager来获取，每次向数据库建立
连接的时候都要将 Connection 加载到内存中再验证IP地址，
用户名和密码(0.05s~ 1s时间)。需要数据库连接的时候就向数据库要求频
繁的进行数据库连接操作将占用很多的系统资源，容易造成服务器崩溃。
2.每一次数据库连接，使用完后都得断开，如果程序出现异常而未能关闭，将
导致数据库内存泄漏，最终将导致重启数据库。
3.传统获取连接的方式，不能控制创建的连接数量，如连接过多，也可能导致内存泄漏，MySQL崩溃。
4.解决传统开发中的数据库连接问题，可以采用数据库连接池技术
connection pool
 */

连接池基本介绍

1.预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去
2.数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个
3.当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中

连接池种类

JDBC 的数据库连接池使用 javax.sglDataSource 来表示，DataSource
只是一个接口，该接口通常由第三方提供实现
1.C3P0 数据库连接池，速度相对较慢，稳定性不错 (hibernate,spring)
2.DBCP数据库连接池，速度相对c3p0较快，但不稳定
3.Proxool数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点
4.BoneCP 数据库连接池，速度快
5.Druid(德鲁伊)是阿里提供的数据库连接池，集DBCP 、C3PO 、Proxool优点于一身的数据库连接池

C3P0数据库连接池的安装

1.下载好完整的压缩包

https://sourceforge.net/projects/c3p0/

2.解压后的目录lib里有jar包和之前一样赋值粘贴导入
在这里插入图片描述

在这里插入图片描述

package com.yuhuw.jdbc.datasource;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLOutput;
import java.util.Properties;

/*
 *@author   yuhuw
 *@date     2024/2/8 15:37
 *@version  1.0
 * 演示C3P0的使用
 */
public class C3P0_ {
    @Test
    //方式一 相关参数在程序中指定
    public void testC3P0_01() throws Exception {
        //1. 创建一个数据源对象 import com.mchange.v2.c3p0.ComboPooledDataSource;实现了DataSource接口
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        //2.通过配置信息获取相关信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));

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

        //给数据源 comboPoolDataSource
        //注意：连接管理是comboPoolDataSource来管理，而配置信息是放在properties对象中
        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);

        //3.获取连接
        //初始化连接数
        comboPooledDataSource.setInitialPoolSize(10);
        //最大连接数
        comboPooledDataSource.setMaxPoolSize(50);

        //测试连接池效率
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {

            //这个方法就是从接口中获得连接的方法
            Connection connection = comboPooledDataSource.getConnection();
//            System.out.println(connection);
            //这里的close是针对连接池的连接，不是数据库的连接
            connection.close();
            //报错java.lang.NoClassDefFoundError: com/mchange/v2/ser/Indirector
            //需要导入mchange包
        }
        long end =System.currentTimeMillis();
        System.out.println("连接池方式执行5000次，耗时"+(end-start));
        //连接池方式执行5000次，耗时227
    }
    @Test
    //第二种方式 使用配置文件模板
    public void testC3P0_02() throws SQLException {
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("yuhuw_edu");
        long start = System.currentTimeMillis();
        for (int i = 0; i < 500000; i++) {
            Connection connection = comboPooledDataSource.getConnection();
            connection.close();
        }
        long end = System.currentTimeMillis();
        //配置文件方式执行5000次，耗时193
        //配置文件方式执行500000次，耗时1107
        System.out.println("配置文件方式执行5000次，耗时"+(end-start));

    }
}

C3P0 config.xml配置编写

<c3p0-config>
    <!--使用默认的配置读取数据库连接池对象  -->
    <named-config name="yuhuw_edu">
        <!--  连接参数 -->
        <property name="driver">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/yuhuw_db02?rewriteBatchedStatements=true</property>
        <property name="user">root</property>
        <property name="password">yuhuw</property>

        <!-- 连接池参数 -->
        <!-- 每次增长的连接数 -->
        <property name="acquireIncrement">5</property>
        <!--初始化申请的连接数量-->
        <property name="initialPoolSize">10</property>
        <!--最大的连接数量-->
        <property name="maxPoolSize">50</property>
        <!--最小的连接数量-->
        <property name="minPoolSize">5</property>
        <!--超时时间-->
<!--        <property name="checkoutTimeout">3000</property>-->

        <!-- 可连接的最多命令对象数 -->
        <property name="maxStatements">5</property>

        <!-- 每个连接对象可连接的最多命令对象数 -->
        <property name="maxStatementsPerConnection">2</property>
    </named-config>
</c3p0-config>

Druid

下载德鲁伊jar包安装并导入

https://repo1.maven.org/maven2/com/alibaba/druid/
在这里插入图片描述

演示driod线程池

package com.yuhuw.jdbc.datasource;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;

/*
 *@author   yuhuw
 *@date     2024/2/8 18:25
 *@version  1.0
 * 演示德鲁伊线程池
 */
public class Druid_ {
    @Test
    public void test() throws Exception {
        //1.加入jar包
        //2.加入配置文件properties
        //3.创建properties对象读取配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\druid.properties"));

        //4.创建一个DataSource对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 500000; i++) {
            Connection connection = dataSource.getConnection();
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("Druid 500000花费的时间为：" + (end - start));
        //Druid 5000花费的时间为：267
        //Druid 500000花费的时间为：335
        //建议使用druid
    }
}

druid 配置文件

#==============druid=============
#key=value
# 驱动注册

driverClassName=com.mysql.jdbc.Driver

#数据库ip
url=jdbc:mysql://localhost:3306/yuhuw_db02?rewriteBatchedStatements=true

# 用户名和密码
username=root
password=yuhuw


#initialSize：连接池初始化大小。
#maxActive：最大活跃连接数。
#minIdle：最小空闲连接数。
#maxWait：获取连接的最大等待时间。
#validationQuery：用于校验连接是否有效的SQL语句。
#testOnBorrow：是否在获取连接时校验连接的有效性。
#testWhileIdle：是否在连接空闲时校验连接的有效性。

initialSize=10

minIdle=5

maxActive=50

maxWait=5000

Druid工具类

package com.yuhuw.jdbc.datasource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/*
 *@author   yuhuw
 *@date     2024/2/8 18:59
 *@version  1.0
 */
public class JDBCUtilsByDruid {
    private static DataSource ds;

    //完成datasource初始化
    static {
        Properties properties = new Properties();
        try {

            properties.load(new FileInputStream("src\\druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);


        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    //关闭连接 关闭使用的连接对象 当然与数据库的连接不会关闭
    public static void close(ResultSet resultSet, Statement statement, Connection conntion) {

        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (conntion != null) {
                conntion.close();
            }
        }catch (SQLException e){
            throw new RuntimeException(e);
        }
    }
}

演示工具类使用

package com.yuhuw.jdbc.datasource;

import com.yuhuw.jdbc.utils.JDBCUtils;
import java.sql.*;

/*
 *@author   yuhuw
 *@date     2024/2/8 19:08
 *@version  1.0
 * 使用德鲁伊的方式完成
 */
public class JDBCUtilsByDruid_ues {
    public static void main(String[] args) throws SQLException {
        new JDBCUtilsByDruid_ues().testDML();
        new JDBCUtilsByDruid_ues().testDQL();
    }

    public void testDML() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        String sql = "update actor set name = ? where id = ?";
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"Mery");
            preparedStatement.setInt(2,2);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }
    public void testDQL() throws SQLException {
        Connection connection =JDBCUtilsByDruid.getConnection();
        System.out.println(connection.getClass());
        //运行类型com.alibaba.druid.pool.DruidPooledConnection
        //之前的JDBC工具类com.mysql.jdbc.JDBC4Connection
        //这也解释类为什么都是Connection
        //JDBC4是直接关闭连接
        // 德鲁伊是把引用的连接放回连接池
        String sql = "select * from actor";
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            set = preparedStatement.executeQuery(sql);

            while (set.next()){
                int id = set.getInt("id");
                String name = set.getString("name");
                String sex = set.getString("sex");
                Date borndate = set.getDate("borndate");
                System.out.println(id+"\t"+name+"\t"+sex+"\t"+borndate);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
        JDBCUtilsByDruid.close(set,preparedStatement,connection);
        }
    }
}

Apache

1.关闭Connection后 resultSet结果集无法使用

2.resultSet不利于数据的管理因为结果集只能用一次

3.使用返回信息也不方便

JavaBean PoJo Domain 做一个映射每一个表的对象就对应了一个表的记录

将每一个对象放入ArrayList集合中

传统方法

    @Test
    public void testSelectToArrayList() throws SQLException {
        Connection connection =null;
        String sql = "select * from actor";
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        ArrayList<Actor> list = new ArrayList<>();
        try {
            connection = JDBCUtilsByDruid.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            set = preparedStatement.executeQuery(sql);

            while (set.next()){
                int id = set.getInt("id");
                String name = set.getString("name");
                String sex = set.getString("sex");
                Date borndate = set.getDate("borndate");
                String phone = set.getNString("phone");
                //把得到的结果集封装到Actor对象放入到list集合
                list.add(new Actor(id,name,sex,borndate,phone));
//                System.out.println(id+"\t"+name+"\t"+sex+"\t"+borndate);
            }
            System.out.println(list);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtilsByDruid.close(set,preparedStatement,connection);
        }
    }

Apache基本介绍

commons-dbutils 是 Apache 组织提供的一个开源JDBC工具类库，它是对JDBC的封装使用dbutils能极大简化jdbc编码的工作量[真的]。

DbUtils类
1.QueryRunner类: 该类封装了SQL的执行，是线程安全的。可以实现增、删、改查、批处理
2.使用QueryRunner类实现查询
3.ResultSetHandler接口: 该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式

ArrayHandler: 把结果集中的第一行数据转成对象数组。
ArrayListHandler: 把结果集中的每一行数据都转成一个数组，再存放到List中
BeanHandler: 将结果集中的第一行数据封装到一个对应的JavaBean实例中。
BeanListHandler: 将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里
ColumnListHandler: 将结果集中某一列的数据存放到List中。
KeyedHandler(name): 将结果集中的每行数据都封装到Map里，再把这些map再存到一个map里，其key为指定的key.
MapHandler: 将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
MapListHandler: 将结果集中的每一行数据都封装到一个Map里，然后再存放到List

DBUtils

下载jar包并和之前一样复制导入

https://commons.apache.org/dbutils/download_dbutils.cgi

在这里插入图片描述

dbutils演示使用

package com.yuhuw.jdbc.datasource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

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

/*
 *@author   yuhuw
 *@date     2024/2/11 19:47
 *@version  1.0
 */
public class DBUtils_use {
    @Test
    public void testQueryMany() throws SQLException {

        // 1.获取连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //前置 需要jar包
        // 2.使用DBUtils接口
        QueryRunner queryRunner = new QueryRunner();
        // 3.使用query方法查询
        // 参数1:sql语句 参数2:sql语句中的参数 参数3:返回结果的类型
        // 返回值:List<Actor>
        // 查询结果封装到List<Actor>中
        // new BeanListHandler<>(Actor.class)底层是Actor.class反射机制获取Actor的属性
        // 1 传给 ? 1也是可变参数可以有多个值 对应多个占位符
        // resultSet会在query方法自动关闭 preparedStatement也会自动关闭
        List<Actor> list = queryRunner.query
                (connection, "select * from actor where id >=?", new BeanListHandler<>(Actor.class), 1);

        /*
        public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        if (conn == null) {
            throw new SQLException("Null connection");
        } else if (sql == null) {
            throw new SQLException("Null SQL statement");
        } else if (rsh == null) {
            throw new SQLException("Null ResultSetHandler");
        } else {
            Statement stmt = null;
            ResultSet resultSet = null;
            T result = null;

           try {
                if (params != null && params.length > 0) {
                    // Create a prepared statement
                    PreparedStatement ps = this.prepareStatement(conn, sql);
                    stmt = ps;
                    // Fill the prepared statement with the given parameters
                    this.fillStatement(ps, params);
                    // Execute the query and wrap the result
                    resultSet = this.wrap(ps.executeQuery());
                } else {
                    // Create a statement
                    stmt = conn.createStatement();
                    // Execute the query and wrap the result
                    resultSet = this.wrap(((Statement)stmt).executeQuery(sql));
                }

                result = rsh.handle(resultSet);
            } catch (SQLException var12) {
                this.rethrow(var12, sql, params);
            } finally {

            //底层关闭resultSet Statement

                this.closeQuietly(resultSet);
                this.closeQuietly((Statement)stmt);
            }

            return result;
        }
    }
        */
        System.out.println("输出集合的信息");
        for (Actor actor : list) {
            System.out.println(actor);
        }

        JDBCUtilsByDruid.close(null, null, connection);

    }

    @Test
    // 演示apache-dbutils + druid 完成单行返回结果 一个对象
    public void testQuerySingle() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();

        String sql = "select * from actor where id = ?";
        Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 3);

        System.out.println(actor);

        JDBCUtilsByDruid.close(null, null, connection);
    }

    @Test
    // 演示apache-dbutils + druid 完成单行单列返回 object
    public void testQueryScalar() throws SQLException {

        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();

        String sql = "select name from actor where id = ?";
        Object object = queryRunner.query(connection, sql, new ScalarHandler<>(),3);

        System.out.println(object);

        JDBCUtilsByDruid.close(null, null, connection);
    }

    // 演示 apache-dbutils + druid 完成dml
    @Test
    public void testDML() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "update actor set sex = ? where id = ?";
        // sql

        // 返回值是受影响的行数
        int affectedRows = queryRunner.update(connection, sql, "女", 3);

        System.out.println(affectedRows>0 ?"执行成功":"未产生影响");

        JDBCUtilsByDruid.close(null, null, connection);
    }

}

Actor对象类

package com.yuhuw.jdbc.datasource;

import java.util.Date;

/*
 *@author   yuhuw
 *@date     2024/2/11 18:18
 *@version  1.0
 * Actor 对象和actor表的记录对应
 */
public class Actor {
    private Integer id;
    private String name;
    private String sex;
    private Date borndate;
    private String phone;

    public Actor(){
        //无参构造器在底层反射需要
    }

    public Actor(Integer id, String name, String sex, Date borndate, String phone) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.borndate = borndate;
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "Actor{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", borndate=" + borndate +
                ", phone='" + phone + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBorndate() {
        return borndate;
    }

    public void setBorndate(Date borndate) {
        this.borndate = borndate;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

BasicDAO

apache-dbutils+Druid 简化了JDBC开发，但还有不足:
1.SQL 语句是固定，不能通过参数传入，通用性不好，需要进行改进，更方便执行增删改查

2.对于select 操作，如果有返回值,返回类型不能固定，需要使用泛型

3.将来的表很多，业务需求复杂,不可能只靠一个Java类完成

4.引出=》BasicDAO

DAO 对每一个表都有DAO 完成增删改查所以将所有DAO共有的部分做一个类

BasicDAO 简化代码提高维护性可读性

DAO一般存在的包

在这里插入图片描述

Actor和JDBCUtilsByDruid工具类上文已经给出

BasicDAO

package com.yuhuw.dao.dao;

import com.yuhuw.dao.utils.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

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

/*
 *@author   yuhuw
 *@date     2024/2/12 19:45
 *@version  1.0
 * BasicDAO 核心思想将上面的DBUtils类全部封装
 */
public class BasicDAO<T> {

    private QueryRunner qr = new QueryRunner();

    //通用DML方法 返回影响的行数
    public int update(String sql, Object... params) {

        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            int update = qr.update(connection, sql, params);
            return update;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }
    //查询//
    // 查询 返回的结果是多行
    /**
     *
     * @param sql sql语句 可以有?
     * @param clazz 传入一个类的CLass对象 底层通过反射获取类的属性
     * @param params 可变形参 表示?
     * @return 返回ArrayList集合
     */
    public List<T> queryMulti(String sql, Class<T> clazz, Object... params) {

        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            List<T> query = qr.query(connection, sql, new BeanListHandler<>(clazz), params);
            return query;
        }catch (SQLException e){
            throw new RuntimeException(e);
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
    //查询 返回的结果是单行 泛型
    public T querySingle(String sql, Class<T> clazz, Object... params) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            T query = qr.query(connection, sql, new BeanHandler<>(clazz), params);
            return query;
        }catch (SQLException e){
            throw new RuntimeException(e);
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
    //查询单行单列 返回一个具体的值 类型Object
    public Object queryScalar(String sql, Object... params) {

        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            Object query = qr.query(connection, sql, new ScalarHandler(), params);
            return query;
        }catch (SQLException e){
            throw new RuntimeException(e);
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
}

ActorDAO

package com.yuhuw.dao.dao;

import com.yuhuw.dao.domain.Actor;

/*
 *@author   yuhuw
 *@date     2024/2/12 20:02
 *@version  1.0
 */
public class ActorDAO extends BasicDAO<Actor>{
    //1. 继承BasicDAO有其父类所有的方法
    //2. 针对Actor类，可以编写特有的方法
    //3. 子类可以重写父类的方法

}

DAO测试

package com.yuhuw.dao.test;

import com.yuhuw.dao.dao.ActorDAO;
import com.yuhuw.dao.domain.Actor;
import org.junit.Test;

import java.util.List;

/*
 *@author   yuhuw
 *@date     2024/2/12 20:03
 *@version  1.0
 通过封装明显看出方法使用方便
 */
public class TestDAO {
    @Test
    //测试ActorDAO 对actor表的CRUD
    public void testDAO(){
        ActorDAO actorDAO = new ActorDAO();
        // 1. 查询多行
        List<Actor> actorList = actorDAO.queryMulti("select * from actor where id >= ? and id <= ?", Actor.class, 1, 10);
        System.out.println("查询多行");
        System.out.println(actorList);
        // 2. 查询单行
        Actor actor = actorDAO.querySingle("select * from actor where id = ?", Actor.class, 3);
        System.out.println("查询单行");
        System.out.println(actor);
        // 3. 查询一行一列
        Object o = actorDAO.queryScalar("select name from actor where id = ?",  3);
        System.out.println("查询单行单列");
        System.out.println(o);
        // 4. 插入
        int updateRows = actorDAO.update("insert into actor(id, name, sex, borndate,phone) values(?,?,?,?,?)", 4, "yuhuw", "男", "1990-01-01","891723");
        System.out.println("updateRows = " + updateRows);
    }
}

额外

Service 业务层组织sql 并调用相应的DAO完成需求

Appview界面层调用Service层得到结果显示数据

JDBC Summary

 }finally {
        JDBCUtilsByDruid.close(null,null,connection);
    }
}

}


### ActorDAO

```java
package com.yuhuw.dao.dao;

import com.yuhuw.dao.domain.Actor;

/*
 *@author   yuhuw
 *@date     2024/2/12 20:02
 *@version  1.0
 */
public class ActorDAO extends BasicDAO<Actor>{
    //1. 继承BasicDAO有其父类所有的方法
    //2. 针对Actor类，可以编写特有的方法
    //3. 子类可以重写父类的方法

}

DAO测试

package com.yuhuw.dao.test;

import com.yuhuw.dao.dao.ActorDAO;
import com.yuhuw.dao.domain.Actor;
import org.junit.Test;

import java.util.List;

/*
 *@author   yuhuw
 *@date     2024/2/12 20:03
 *@version  1.0
 通过封装明显看出方法使用方便
 */
public class TestDAO {
    @Test
    //测试ActorDAO 对actor表的CRUD
    public void testDAO(){
        ActorDAO actorDAO = new ActorDAO();
        // 1. 查询多行
        List<Actor> actorList = actorDAO.queryMulti("select * from actor where id >= ? and id <= ?", Actor.class, 1, 10);
        System.out.println("查询多行");
        System.out.println(actorList);
        // 2. 查询单行
        Actor actor = actorDAO.querySingle("select * from actor where id = ?", Actor.class, 3);
        System.out.println("查询单行");
        System.out.println(actor);
        // 3. 查询一行一列
        Object o = actorDAO.queryScalar("select name from actor where id = ?",  3);
        System.out.println("查询单行单列");
        System.out.println(o);
        // 4. 插入
        int updateRows = actorDAO.update("insert into actor(id, name, sex, borndate,phone) values(?,?,?,?,?)", 4, "yuhuw", "男", "1990-01-01","891723");
        System.out.println("updateRows = " + updateRows);
    }
}