JAVA——JDBC学习

视频连接：https://www.bilibili.com/video/BV1sK411B71e/?spm_id_from=333.337.search-card.all.click&vd_source=619f8ed6df662d99db4b3673d1d3ddcb

《视频讲解很详细！！推荐》

JDBC（Java DataBase Connectivity Java数据库连接）

栗子：类比USB

1. 概述⛺

出现原因

在Java中提供对数据库的访问支持，便于软件开发过程中使用数据库来存储和管理数据
如果用户直接操作数据库，数据库种类变化之后，代码改动量很大。JDBC由各数据库厂商按照统一的规范提供数据库驱动（JDBC实现类），可以操作所有的关系型数据库，减少了用户和底层数据库的交互，增强了代码的通用性。

推行

SUN公司于1996年提供访问数据库的标准Java类库

原理

在这里插入图片描述

步骤

步骤1：java.sql.* JDBC接口对象 = 第三方实现类实例；（JDBC API主要位于java.sql包中，该包定义了一系列访问数据的接口和类）
步骤2：对象.jdbc标准方法();

组成

Java提供的jdbc规范（接口，多态，面向接口编程）
各个数据库厂商的实现驱动jar包

2. JDBC技术组成🌃

2.0 使用步骤

0）准备数据库

mysql> create table t_user(
    -> id int primary key auto_increment comment '用户主键',
    -> account varchar(20) not null unique comment '账户',
    -> password varchar(64) not null comment '密码',
    -> nickname varchar(20) not null comment '昵称');
    
 insert into t_user(account, password, nickname) values ('root', '123456', '经理'), ('admin', '666666', '管理员');

1）注册驱动：安装jar包，注册了之后Java就允许远程数据库的连接了

2）获取连接：建立java程序和数据库软件的通道

3）创建发送sql语句对象：sql语句的载体

4）发送sql语句，并获取返回结果：载体发送sql到数据库，并获取返回结果，得到一个结果对象

5）结果集解析：把结果对象拆出来

6）资源关闭：销毁资源，关闭连接，关闭载体，释放结果对象

2.1 DriverManager

作用

将第三方数据库厂商的实现驱动jar注册到程序中
可以根据数据库的连接信息获取Connection

2.2 Connection

作用

和数据库建立连接，在连接对象上，可以多次执行数据库操作
可以获得下面2.3节的三类对象

2.3 Statement | PreparedStatement | CallableStatement

1）Statement：静态SQL路线（没有动态值语句，也就是没有条件的）

/*
使用statement查询t_user表下，全部数据
 */
public class StatementQueryPart {
    public static void main(String[] args) throws SQLException {
        // 1. 注册驱动
        DriverManager.registerDriver(new Driver());
        // 2. 获取连接
        // 参数1：url——jdbc:数据库厂商名://ip地址:port/数据库名
        // java.sql 接口 = 实现类
        Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test", "xy", "123456");
        // 3. 创建发送sql语句的对象 Statement
        Statement statement = connection.createStatement();
        // 4. 发送sql语句并返回结果集
        String sql = "select * from t_user;";
        ResultSet resultSet = statement.executeQuery(sql);
        // 5. 解析结果
        // next是看看下一行有没有数据，有的话就取，没有的话就退出了。它初始指的位置是指向字段的
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String account = resultSet.getString("account");
            String password = resultSet.getString("password");
            String nickname = resultSet.getString("nickname");
            System.out.println("id:" + id + " | " + "account: " + account + " | " + "password: " + password + " | " + "nickname: " + nickname);
        }
        // 6. 资源关闭
        resultSet.close();
        statement.close();
        connection.close();
    }
}


/* 
结果
id:1 | account: root | password: 123456 | nickname: 经理
id:2 | account: admin | password: 666666 | nickname: 管理员
*/

// 详细知识！！
public class StatementUserLoginPart {
    public static void main(String[] args) throws Exception {
        // 1. 键盘输入事件
        Scanner sc = new Scanner(System.in);
        System.out.print("请输入账号：");
        String account = sc.nextLine();
        System.out.print("请输入密码：");
        String password = sc.nextLine();

        // 2. 注册驱动
        // DriverManager.registerDriver(new Driver());  // 问题：注册两次驱动（方法本身注册一次，new对象里面有静态代码块也会注册一次），性能消耗
        // 改进：只触发静态代码块——类加载的时候1）new关键字；2）调用静态方法；3）调用静态属性；4）接口default默认实现；5）反射；6）子类出发父类；7）程序入口main
        // new Driver();
        // 反射：字符串参数提取到外部配置文件——>灵活
        Class.forName("com.mysql.cj.jdbc.Driver");

        // 3. 获取数据库连接
        // 如果本机并且端口是3306，url可以简写jdbc:mysql:///test
        // 三个参数
        // Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test", "xy", "123456");
        // 两个参数
        // Properties info = new Properties();
        // info.put("user", "xy");
        // info.put("password", "123456");
        // Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test", info);
        // 一个参数
        // jdbc:mysql://***.***.***.***:3306/test?user=xy&password=123456
        Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test?user=xy&password=123456");

        // 4. 创建发送sql语句的Statement对象
        Statement statement = connection.createStatement();

        // 5. 发送sql并返回结果集
        String sql = "select * from t_user where account = '"+ account + "'and password = '" + password + "';";
        // 非DQL int row = executeUpdate(sql) ;  DML影响的行数，其他就是返回0
        // ResultSet resultSet = statement.executeQuery(sql)； 返回的是结果封装对象
        ResultSet resultSet = statement.executeQuery(sql);

        // 6. 查询结果集的解析
//        while (resultSet.next()) {
//            int id = resultSet.getInt(1);
//            String account1 = resultSet.getString("account");
//            String password1 = resultSet.getString(3);
//            String nickname = resultSet.getString("nickname");
//            System.out.println("id:" + id + " | " + "account: " + account1 + " | " + "password: " + password1 + " | " + "nickname: " + nickname);
//        }

        // 检查是否登录成功
        if (resultSet.next()) {
            System.out.println("登录成功啦！") ;
        } else {
            System.out.println("啊哦~~失败咯~");
        }

        // 7. 关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

存在的问题：

SQL语句需要字符串拼接，比较麻烦
只能拼接字符串类型，其他的数据库类型无法处理
可能发生注入共计——动态值充当了SQL语句结构，影响了原有的查询结果

2）PreparedStatement：预编译SQL路线（有动态值语句）——常用

public class PSUserLoginPart {
    public static void main(String[] args) throws Exception {
        // 1. 键盘输入事件
        Scanner sc = new Scanner(System.in);
        System.out.print("请输入账号：");
        String account = sc.nextLine();
        System.out.print("请输入密码：");
        String password = sc.nextLine();

        // 2. 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        // 3. 获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test?user=xy&password=123456");

        // 4. 编写SQL语句
        String sql = "select * from t_user where account = ? and password = ? ;";
        // 创建预编译statement并设置SQL语句结果
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 单独的占位符赋值
        // 第一个参数是index占位符的位置，从左往由从1开始；第二个参数是object占位符的值，可以设置任何类型的数据，避免了拼接和类型更加丰富！
        preparedStatement.setObject(1, account);
        preparedStatement.setObject(2, password);

        // 5. 发送SQL语句，并获取返回结果
        ResultSet resultSet = preparedStatement.executeQuery();  // 不需要传递参数，因为已经知道sql语句并且获得了动态值

        // 6. 结果集解析
        if (resultSet.next()) {
            System.out.println("登录成功啦！") ;
        } else {
            System.out.println("啊哦~~失败咯~");
        }

        // 7. 关闭资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

// 更加灵活地取到结果集对象
public class PSCURDPart {
    public static void main(String[] args) throws Exception {
        // 目标：查询tb_user表中的数据，更加灵活地取到结果集对象
        // 1. 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 2. 获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test?user=xy&password=123456");
        // 3. 获取创建sql语句的对象
        String sql = "select id, account, password, nickname from t_user";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // preparedStatement.setObject();  // 给占位符赋值，这里没有使用占位符，所以不进行赋值操作
        // 4. 将sql语句发送到数据库并返回结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        // 5. 解析结果集的对象
        /* 一般情况
        while (resultSet.next()){
            int id = resultSet.getInt("id");
        }
        存在的问题
        要手动获取列名——>我们就要知道由哪些列，不智能
        万一列名修改了——>代码这里也要进行相应的修改，不通用
        */
        // metaData 可以获取列的名称，也可以获取列的数量
        ResultSetMetaData metaData = resultSet.getMetaData();
        // 获取列的数量
        int columnCount = metaData.getColumnCount();
        List<Map> list = new ArrayList<>();
        while(resultSet.next()) {
            Map map = new HashMap();
            for (int i = 1; i <= columnCount; i++) {
                // 根据下角标获取值
                Object value = resultSet.getObject(i);
                // 获取指定下角标的列名
                String key = metaData.getColumnLabel(i);  // 会获取列的别名
                map.put(key, value);
            }
            list.add(map);
        }
        System.out.println(list);

        // 6. 释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

3）CallableStatement：执行标准存储过程SQL路线（有存储过程的）

2.4 Result

作用

数据库的查询结果表
存储DQL查询数据库结果的对象
需要我们进行解析，获取具体的数据库数据

3. 提升扩展🏙️

3.1 自增长主键回显实现

获取数据库自增长的主键——主键回显

给关联的子表插入的时候使用

public class PSOtherPart {
    // 创建prepareStatement的时候添加一个参数Statement.RETURN_GENERATED_KEYS
    // 解析结果集的时候Statement.getGeneratedKeys()返回ResultSet
    @Test
    public void returnPrimaryKey() throws Exception {
        // 1. 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 2. 获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test?user=xy&password=123456");
        // 3. 创建statement
        String sql = "insert into t_user(account, password, nickname) values (?, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setObject(1, "test1");
        preparedStatement.setObject(2, "123456");
        preparedStatement.setObject(3, "嘎嘎嘎");
        // 4. 发送sql语句并获取结果
        int row = preparedStatement.executeUpdate();
        // 5. 解析结果
        if(row > 0) {
            System.out.println("数据插入成功");
            ResultSet resultSet = preparedStatement.getGeneratedKeys();
            resultSet.next();
            int id = resultSet.getInt(1);
            System.out.println("主键值是：" + id);
        } else{
            System.out.println("数据插入失败");
        }
        // 6. 关闭资源
        preparedStatement.close();
        connection.close();
    }
}

3.2 批量数据插入性能提升

1）url?rewriteBatchedStatements=true

2）statement.addBatch(); // 不执行，追加到values后面

3）statement.executeBatch(); // 执行批量操作

4）insert语句后面不加分号结束

@Test
public void testInsert() throws Exception {
    // 批量插入
    // 一般：插入的时候循环
    // 优化：批量
    // url?rewriteBatchedStatements=true
    // statement.addBatch();  // 不执行，追加到values后面
    // statement.executeBatch();  // 执行批量操作
    // 1. 注册驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    // 2. 获取连接
    Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test?rewriteBatchedStatements=true", "xy", "123456");
    // 3. 创建statement
    String sql = "insert into t_user(account, password, nickname) values (?, ?, ?)";  // 批量的话sql不能加；结束
    PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    for (int i = 100; i < 110; i++) {
        preparedStatement.setObject(1, "test1" + i);
        preparedStatement.setObject(2, "123456" + i);
        preparedStatement.setObject(3, "嘎嘎嘎" + i);
        preparedStatement.addBatch();  // 追加
    }
    preparedStatement.executeBatch();  // 统一批量执行
    // 4. 发送sql语句并获取结果
    // 5. 解析结果
    // 6. 关闭资源
    preparedStatement.close();
    connection.close();
}

3.3 jdbc中数据库事务实现

事务的最基础要求是：必须是同一个连接对象 connection

业务里面开启事务

步骤：

事务添加是在业务方法中
利用try catch，开启事务，提交事务和事务回滚
将connection传入方法中，方法中就不需要close了

业务方法

public class BankService {
    @Test
    public void testtransfer() throws Exception {
        transfer("ergouzi", "lvdandan", 50);
    }


    public void transfer(String addAccount, String subAccount, int money) throws Exception {
        // 1. 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 2. 获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test?user=xy&password=123456");        BankDao bankDao = new BankDao();

        try {
            // 开启事务
            // 关闭事务提交
            connection.setAutoCommit(false);
            // 执行
            bankDao.add(addAccount, money, connection);
            System.out.println("-----");
            bankDao.sub(subAccount, money, connection);
            // 事务提交
            connection.commit();
        } catch (Exception e){
            // 事务回滚
            connection.rollback();
            // 抛出异常
            throw e;
        } finally {
            connection.close();
        }
    }
}

实现方法

public class BankDao {
    // 加钱
    public void add(String account, int money, Connection connection) throws Exception {
        // 3. 创建statement
        String sql = "update t_bank set money = money + ? where account = ?;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 4. 给占位符赋值
        preparedStatement.setObject(1, money);
        preparedStatement.setObject(2, account);
        // 5. 发送SQL语句
        preparedStatement.executeUpdate();
        // 6. 关闭资源
        preparedStatement.close();

        System.out.println("加钱成功");
    }

    // 减钱
    public void sub(String account, int money, Connection connection) throws Exception {
        // 3. 创建statement
        String sql = "update t_bank set money = money - ? where account = ?;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 4. 给占位符赋值
        preparedStatement.setObject(1, money);
        preparedStatement.setObject(2, account);
        // 5. 发送SQL语句
        preparedStatement.executeUpdate();
        // 6. 关闭资源
        preparedStatement.close();

        System.out.println("减钱成功");

    }
}

4. Druid连接池技术使用🌄

4.1 连接性能消耗问题分析

connection可以复用！！容器池——节约了创建和销毁连接的性能消耗，提升了时间的响应

4.2 数据库连接池作用

连接池可以存储一定数量的连接对象
用户要的时候就直接拿，拿完再放回去
池子李的连接都用完了，可以向服务器申请新的连接放到池子里
池中的连接达到最大连接数的时候，就不能申请新的连接了，等等嗷

4.3 市面常见连接池产品和对比

javax.sql.DataSource接口

规范了连接池获取连接的方法
规范了连接池回收连接的方法

DataSource = 第三方连接池的实现

选择考虑

性能优势
性能扩展

4.4 druid连接池使用

硬编码

public class DruidUsePart {

    /*
    * 直接使用代码设置连接池连接参数
    * 1. 创建一个druid连接池对象
    * 2. 设置连接池参数 [必须 | 非必须]
    * 3. 获取连接 [通用方法，所有连接池都一样]
    * 4. 回收连接 [通用方法，所有连接池都一样]
    * */
    public void testHard() throws Exception {
        // 连接池对象
        DruidDataSource dataSource = new DruidDataSource();
        // 设置参数
        // 必须 注册驱动 | url | user| password
        dataSource.setUrl("jdbc:mysql://***.***.***.***:3306/test");
        dataSource.setUsername("xy");
        dataSource.setPassword("123456");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");  // 驱动注册和获取连接
        // 非必须 初始化连接数量，最大的连接数量……
        dataSource.setInitialSize(5); // 初始化连接数量
        dataSource.setMaxActive(10);  // 最大的数量

        // 获取连接
        Connection connection = dataSource.getConnection();
        // 数据库
        // 回收连接
        connection.close();
    }
}

软编码

// 软连接：通过读取外部配置文件的方法，实例化druid连接池对象
@Test
public void testSoft() throws Exception {
    // 1. 读取外部配置文件 Properties
    Properties properties = new Properties();
    // src下的文件，可以使用类加载器提供的方法
    InputStream ips = DruidUsePart.class.getClassLoader().getResourceAsStream("druid.properties");
    properties.load(ips);

    // 2. 使用连接池工具类的工程模式，创建连接池
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();
    // 中间做数据库curd
    connection.close();
}

避免每次重复代码，实现工具类

需求：内部包含一个连接池对象，并对外提供获取连接和回收连接的方法！

工具类

// 内部包含一个连接池对象，并对外提供获取连接和回收连接的方法！
/*
* 属性：连接池对象【实例化一次】
* 方法：
*       对外提供连接的方法
*       回收外部传入连接方法
* */
public class JdbcUtils {
    private static DataSource dataSource = null;  // 连接池对象
    static {
        // 初始化连接池对象
        // 1. 读取外部配置文件 Properties
        Properties properties = new Properties();
        // src下的文件，可以使用类加载器提供的方法
        InputStream ips = DruidUsePart.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            properties.load(ips);
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            // 2. 使用连接池工具类的工程模式，创建连接池
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws Exception {
        return dataSource.getConnection();
    }

    public static void freeConnection(Connection connection) throws Exception {
        connection.close();
    }
}

测试

public class JdbcCrudPart {
    public void testInsert() throws Exception {
        Connection connection = JdbcUtils.getConnection();
        // 数据库curd动作
        JdbcUtils.freeConnection(connection);
    }
}

基于以上部分的思考：在事务中，需要保证整个事务都是一个连接。之前的做法是在业务类中创建连接，在调用实际方法的时候，将连接作为参数传递——>增加参数传递这个步——>实质：保证同一线程的方法使用同一个连接——>将连接保存在这个本地线程对象中，执行的时候确定一下这个本地线程对象中的连接

做法：全局声明一个线程本地对象

工具类中判断线程本地对象中的连接是否存在

public class JdbcUtilsV2 {
    private static DataSource dataSource = null;  // 连接池对象
    // 线程本地对象***
    private static ThreadLocal<Connection> tl = new ThreadLocal<>();
    static {
        // 初始化连接池对象
        // 1. 读取外部配置文件 Properties
        Properties properties = new Properties();
        // src下的文件，可以使用类加载器提供的方法
        InputStream ips = DruidUsePart.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            properties.load(ips);
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            // 2. 使用连接池工具类的工程模式，创建连接池
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws Exception {
        // 查看线程本地变量中是否存在
        Connection connection = tl.get();
        // 判断有没有
        if (connection == null) {
            connection = dataSource.getConnection();
            tl.set(connection);
        }
        return connection;
    }

    public static void freeConnection() throws Exception {
        // 看看线程本地变量中有没有这个连接
        Connection connection = tl.get();
        if (connection != null) {
            tl.remove();  // 情况线程本地变量
            connection.setAutoCommit(true);  // 事务状态回归
            connection.close();
        }

    }
}

5. baseDao🌅

5.1 baseDo概念

Dao：每一个数据表对应的DAO接口及其实现类，实现对数据表的增删改查

DaoseDao：Dao实现类中重复度很高的代码抽象成的公共父类

5.2 非DQL方法封装

public class BaseDao {
    public static int executeUpdate(String sql, Object...parms) throws Exception {
        // 获取连接
        Connection connection = JdbcUtilsV2.getConnection();
        // 创建statement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 1; i <= parms.length; i++) {
            preparedStatement.setObject(i, parms[i-1]);
        }
        // 发送sql语句
        int rows = preparedStatement.executeUpdate();

        // 关闭连接-要看这里是否是事务中的连接，如果是事务里面的，后面可能还会用，就不关闭连接；否则关闭连接
        if (connection.getAutoCommit()) {
            // 没事务
            JdbcUtilsV2.freeConnection();
        }
        return rows;
    }
}

调用一下

@Test
public void testInsert() throws Exception {
    String sql = "insert into t_user(account, password, nickname) values (?, ?, ?)";  // 批量的话sql不能加；结束
    executeUpdate(sql, "测试1", "1234", "222");
    System.out.println("执行成功！");
}

执行之后，数据库内插入成功了，但是报错如下：信息: {dataSource-1} inited

在这里插入图片描述

Debug——配置文件是否有问题

在这里插入图片描述

大no特no——这不是出现问题了(还改了一大堆)

在这里插入图片描述

参考连接：https://wenku.csdn.net/answer/55db899163e9498f8c36d2ff9b8d8504

5.3 DQL查询方式封装

public <T> List<T> executrQuery(Class<T> clazz, String sql, Object... params) throws Exception {
    // 获取连接
    Connection connection = JdbcUtilsV2.getConnection();
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    // 动态值
    if (params != null && params.length != 0) {
        for (int i = 1; i <= params.length; i++) {
            preparedStatement.setObject(i, params[i-1]);
        }
    }
    // 发送sql
    ResultSet resultSet = preparedStatement.executeQuery();
    // 结果集解析
    List<T> list = new ArrayList<>();
    ResultSetMetaData metaData = resultSet.getMetaData();
    int columnCount = metaData.getColumnCount();
    while (resultSet.next()) {
        T t = clazz.newInstance();  // 调用类的无参构造函数实例化对象
        for (int i = 1; i <= columnCount; i++) {
            Object value = resultSet.getObject(i);
            String propertyName = metaData.getColumnLabel(i);
            // 反射——给赋值
            Field field = clazz.getDeclaredField(propertyName);
            field.setAccessible(true);  // 防止私有的不能赋值
            field.set(t, value);
        }
        list.add(t);
    }
    // 关闭资源
    resultSet.close();
    preparedStatement.close();
    if (connection.getAutoCommit()) {
        JdbcUtilsV2.freeConnection();
    }
    return list;
}