学习视频：https://www.bilibili.com/video/BV1sK411B71e

JDBC 技术概述

jdbc 技术概念和理解

  • JDBC 概念：Java Database Connectivity | Java 连接数据库技术!

    通俗点说,在Java代码中，使用JDBC提供的方法，可以发送字符串类型的SQL语句到数据库管理(MySQL,Oracle等)，并且获取语句执行结果！进而实现数据库数据CURD操作的技术！

  • JDBC 本质理解

  • 总结

    1. jdbc 是(Java Database Connectivity)单词的缩写，翻译为 java 连接数据库

    2. jdbc 是 java 连接数据库的技术统称

    3. jdbc 是 java 语言的规范(接口)和各个数据库厂商的实现驱动(jar)组成

    4. jdbc 是一种典型的面向接口编程

    5. jdbc 优势

      a. 只需要学习 jdbc 规范接口的方法，即可操作所有的数据库软件

      b. 项目中期切换数据库软件，只需要更换对应的数据库驱动jar 包，不需要更改代码

jdbc核心api和使用路线

  • jdbc 技术组成

    1. jdk 下 jdbc 规范接口，存储在 java.sql 和 javax，sql 包中的 api

      为了项目代码的可移植性，可维护性，SUN 公司从最初就制定了 Java 程连接各种数据库的统一接口规范，这样的话，不管是连接哪一种 DBMS 软件，Java 代码可以保持一致性。

    2. 各个数据库厂商提供的驱动 jar 包

      因为各个数据库厂商的 DBMS 软件各有不同，那么内部D何通过 sql 实现增、删、改、查等管理数据，只有这个数据库厂商自己更清楚，因此把接口规范的实现交给各个数据库厂商自己实现。

    3. jar 包是什么?

      java 程序打成的一种压包格式，你可以将这些 jar 包引入你的项目中，然后你可以使用这个java 程序中类和方法以及性了!

  • 涉及具体核心类和接口

    • DriverManager

      • 将第三方数据库厂商的实现驱动 jar 注册到程序中

      • 可以根据数据库连接信息获取connection

    • Connection

      • 和救据库建立的连接，在连接对象上可以多次执行数据库 curd 动作

      • 可以获取statement和preparedstatement, callablestatement对象

    • Statement | PreparedStatement | CallableStatement

      • 具体发送 SQL 语句到数据库管理软件的对象

      • 不同发送方式稍有不同！preparedstatement 使用为重点！

    • Result

  • jdbc api使用路线

    jdbc api使用路线

JDBC核心API

引入 mysql-jdbc 驱动jar

最新教程请访问：MySQL下载、安装及Java JDBC配置连接数据库

  1. 驱动 jar 版本选择

    我这里选择mysql 8.0.27 + 8.0.27的jar驱动

  2. java 工程导入依赖

    a. 项目创建lib文件夹

    b. 导入驱动依赖 jar 包

    c. jar 包右键-添加为项目依赖

jdbc 基本使用步骤分析

  1. 注册驱动
  2. 获取连接
  3. 创建发送 sql 语句对象
  4. 发送 sql 语句，并获取返回结果
  5. 结果集解析
  6. 资源关闭

基于 statement 演示查询

下面演示了java查询db01数据库中的emp表中的id, name, age, entrydate等字段的数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
package com.atxiong.api.statement;
import com.mysql.cj.jdbc.Driver;
import java.sql.*;

/**
 *
 * @Author Xiong
 * @data 2023/8/17
 * @Description 使用statement查询表中全部数据
 */
public class StatementQueryPart {
    /**
     * TODO:
     *      DriverManager
     *      Connection
     *      Statement
     *      ResultSet
     */
    public static void main(String[] args) throws SQLException {
        //1.注册驱动：这里用com.mysql.cj.jdbc.Driver
        DriverManager.registerDriver(new Driver());
        //2.获取连接：java程序和数据库创建连接；参数1：url（格式："jdbc:数据库厂商名://ip地址:port/数据库名"），参数2：username（数据库账号），参数3：password
        //java.sql 接口 = 实现类
        Connection connection = DriverManager.
                getConnection("jdbc:mysql://127.0.0.1:3306/db01", "root", "mysql");
        //3.创建statement
        Statement statement = connection.createStatement();
        //4.发送SQL语句并且获取结果
        String sql = "select id, name, age, entrydate from emp;";
        ResultSet resultSet = statement.executeQuery(sql);
        //5.进行结果解析
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            Date entrydate = resultSet.getDate("entrydate");

            System.out.println(id+"--"+name+"--"+age+"--"+entrydate);
        }

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

基于 statement 方式问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
package com.atxiong.api.statement;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

/**
 *
 * @Author Xiong
 * @data 2023/8/17
 * @Description 模拟用户登录
 *
 * TODO:
 *      1. 明确jdbc的使用流程 和 详细讲解内部设计api方法
 *      2. 发现问题，引出preparedStatement
 * TODO:
 *      输入账号和密码
 *      进行数嬉库信息查询(db01)
 *      反馈登录成功还是登录失败
 * TODO:
 *      1. 链盘输入事件，收集账号和密码信息
 *      2. 注册驱动
 *      3. 获取连接
 *      4. 创建statement
 *      5. 发送查询SQL 语句，并获取返回结果
 *      6. 结果判断，显示登录成功还是失败
 *      7. 关闭资源
 */
public class StatementUserLoginPart {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.获取用户密码
        Scanner scanner = new Scanner(System.in);
        System.out.println("Enter username: ");
        String account = scanner.nextLine();
        System.out.println("Enter password: ");
        String password = scanner.nextLine();

        //2.注册驱动
        /*
            问题: 注册两次驱动
                1.DriverManager.registerDriver() 方法本身会注一次!
                2.Driver.static{ DriverManager.registerDriver() } 静态代码块,也会注册一次!
            解决: 只想注册一次驱动
                只触发静态代码块即可! Driver
            触发静态代码块:
                类加获机制: 类加载的时刻，会触发静态代码块!
                加载 [cLass文件 -> jvm虚拟机的cLass对泉]
                连接[验证(检查文件类型) -> 准备(静态变量默认值) -> 解析(触发静态代码块)]
                初始化(静态属性赋真实值)
            触发类加载:
                1.new 关键字
                2.调用静态方法
                3.调用静态属性
                4.接口 1.8 default默认实现
                5.反射
                6.子类触发父类
                7.程序的入口main
         */
        //方案1：
        //DriverManager.registerDriver(new Driver());
        //方案2：
        //new Driver();  //注册驱动 固定的写法! mysql - mysql Driver || 切换了 oracle driver | 还需要改代码

        //使用反射机制：字符串--->提取到外部的配置文件--->可以在不改变代码的情况下，完成数据库驱动的切换!
        Class.forName("com.mysql.cj.jdbc.Driver"); //触发类加载，触发静态代码块的调用

        //3.获取数据库连接
        /*
            核心属性：
                1.数据库软件所在的主机的ip地址: Localhost / 127.0.0.1
                2.数据库软件所在的主机的端口号: 3306
                3.连接的具体库: db01
                4.连接的账号: root
                5.连接的密码: mysql
                6.可选的信息: 没有

            三个参数：
                String url      数据库软件所在的信息，连接的具体库，以及其他可选信息!
                                语法：jdbc:数据库管理软件名称[mysql,oracle]://ip地址|主机名:port端口号/数据库名?key=value
                                     &key=vaLue 可还信息!
                                具体: jdbc:mysql://127.0.0.1:3306/db01
                                     jdbc:mysql://localhost:3306/db01
                                本机的省略写法: 如果你的数据库软件安装到本机，可以进行一些省略
                                        jdbc:mysql://127.8.0.1:3306/db01 = jdbc:mysql:///db01（省略[本机地址]和[3306]）
                                        注意：若端口号不是使用3306则不可省略
                String user
                String password

            两个参数：
                String url: 此url和三个参数的url的作用一样! 数据库ip，端口号，具体的数据库和可述信息
                Properties info: 存储账号和密码
            一个参数：
                String url: 收据库ip,端口号,具体的数语库 可选信息(账号密码)
                            jdbc:数据库软件名://ip:port/数据率?key=vaLue&key=value&key=value

                            jdbc:mysql://Localhost:3306/db01?user=root&password=mysql
                            携带固定的参数名 user password 传递账号和密码信息! [乌龟的屁,规定!]

            urL的路经属性可选信息:
                urL?user=账号&password=密码
                8.0.27版本驱动，下面都是一些可选属性!
                    8.0.25以后,自动识别时区! serverTimezone=Asia/Shanghai 不用添加! 8.0.25之前版本,下面一句话还是要加的!
                    8版本以后,默认使用的就是utf-8格式,useUnicode=true&characterEncoding=utf8&useSSL=true 都可以省略了!
                serverTimezone-Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=true
         */

        // 三个参数的方式：
        Connection connection1 = DriverManager.getConnection("jdbc:mysql:///db01", "root", "mysql");

        // 两个参数的方式：
//        Properties info = new Properties();
//        info.put("user","root");
//        info.put("password", "mysql");
//        Connection connection2 = DriverManager.getConnection("jdbc:mysql://localhost:3306/db01", info);
//
//        // 一个参数的方式：
//        Connection connection3 = DriverManager.getConnection("jdbc:mysql://Localhost:3306/db01?user=root&password=mysql");

        //4.创建statement
        Statement statement = connection1.createStatement();

        //5.发送SQL语句并且获取结果
        String sql = "select id, name, age, entrydate from emp where account = '" + account + "' and password = '" + password + "';";
        /*
            参数: sql 非DQL
            返回: int
                情况1: DML 返回影响的行,如: 删除了三条 return 3; 插入了两条 return 2; 修改了0条 return 0;
                情况2: 非DML return 0;
            int row = executeUpdate(sql)

            参数: sqL DQL
            返回: resultSet 结果封装对象
            ResultSet resultSet = executequery(sql);
         */
        // int i = statement.executeUpdate(sql);
        ResultSet resultSet = statement.executeQuery(sql);



        //6.进行结果解析
        /**
         *
         * TODO:1.需要理解ResultSet的数据结构和小海豚查询出来的是一样，需要在脑子里构建结果表！
         * TODO:2.有一个光标指向的操作数据行，默认指向第一行的上边！我们需要移动光标，指向行，在获取列即可！
         *        boolean = next()
         *              false: 没有数据，也不移动了！
         *              true:  有更多行，并且移动到下一行！
         *       推荐：推荐使用if 或者 while循环，嵌套next方法，循环和判断体内获取数据！
         *       if(next()){获取列的数据！} ||  while(next()){获取列的数据！}
         *
         * TODO：3.获取当前行列的数据！
         *         get类型(int columnIndex | String columnLabel)
         *        列名获取  //label 如果没有别名，等于列名， 有别名label就是别名，他就是查询结果的标识！
         *        列的角标  //从左到右 从1开始！ 数据库全是从1开始！
         */
        if (resultSet.next()){
            System.out.println("登陆成功！");
        }else {
            System.out.println("登录失败");
        }

        //6.关闭资源
        resultSet.close();
        statement.close();
        connection1.close();
//        connection2.close();
//        connection3.close();
    }

}

存在问题：

  1. SQL 语句需要字符申拼接比较麻烦

  2. 只能拼接字符里类型，其他的数据库类型无法处理

  3. 可能发生注入攻击

    动态值充当了 SQL 语句结构，影响了原有的查询结果！

解决方法：使用preparedStatement

基于 preparedStatement 方式优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
package com.atxiong.api.preparedstatement;
import java.sql.*;
import java.util.Scanner;

/**
 *
 * @Author Xiong
 * @data 2023/8/17
 * @Description 使用PreparedStatement完成用户登录
 *
 * TODO: 防止注入攻击  /  演示preparedStatement的使用流程
 *
 */

public class PreparedStatementUserLoginPart {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.获取用户密码
        Scanner scanner = new Scanner(System.in);
        System.out.println("Enter username: ");
        String account = scanner.nextLine();
        System.out.println("Enter password: ");
        String password = scanner.nextLine();

        //2.preparedStatement的数据库流程
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///db01?user=root&password=mysql");


        //3.创建preparedStatement
        //connection.createStatement();
        //TODO 需要传入SQL语句结构
        //TODO 要的是SQL语句结构，动态值的部分使用 ? ,  占位符！
        //TODO ?  不能加 '?'  ? 只能替代值，不能替代关键字和容器名
        String sql = "select * from emp where account = ? and password = ?;";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setObject(1,account);
        preparedStatement.setObject(2,password);

        //4.发送SQL语句并且获取结果
        //statement.executeUpdate / executeQuery(String sql);
        //preparedStatement.executeUpdate / executeQuery(); TOD0: 此时()里不需要再传入sql语句，因为它已经知道语句,知道语句动态值!
        ResultSet resultSet = preparedStatement.executeQuery();

        //5.进行结果解析
        if (resultSet.next()){
            System.out.println("登陆成功！");
        }else {
            System.out.println("登录失败");
        }



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

两种方式的区别

基于 preparedStatement 演示 curd

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
package com.atxiong.api.preparedstatement;
import org.junit.Test;
import java.sql.*;

/**
 *
 * @Author Xiong
 * @data 2023/8/17
 * @Description 使用preparedStatement进行emp表的curd动作
 */
public class PreparedStatementCrudPart {
    @Test
    public void testInsert() throws ClassNotFoundException, SQLException {
        //user插入一条数据 name | age  | status | gender

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

        //2.获取链接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db01", "root", "mysql");

        //3.编写sql语句结果，动态值的部分使用?代替
        String sql = "insert into user (name,age,status,gender) values (?,?,?,?);";

        //4.创建preparedStatement，并传入sql语句结果
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //5.占位符赋值
        preparedStatement.setObject(1, "XiaoMing");
        preparedStatement.setObject(2, 23);
        preparedStatement.setObject(3, 0);
        preparedStatement.setObject(4, "男");

        //6.发送sql语句
        int resultSet = preparedStatement.executeUpdate();

        //7.输出结果
        if (resultSet > 0) {
            System.out.println("插入成功！");
        }else {
            System.out.println("插入失败！");
        }

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

    @Test
    public void testUpdate() throws ClassNotFoundException, SQLException {
        //user修改一条数据 name | age  | status | gender

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

        //2.获取链接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///db01?user=root&password=mysql");

        //3.编写sql语句结果，动态值的部分使用?代替
        String sql = "update user set name = ?, age = ? where name = ?;";

        //4.创建preparedStatement，并传入sql语句结果
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //5.占位符赋值
        preparedStatement.setObject(1, "xiaozhao");
        preparedStatement.setObject(2, 38);
        preparedStatement.setObject(3, "xiaoming");

        //6.发送sql语句
        int result = preparedStatement.executeUpdate();

        //7.输出结果
        if (result > 0) {
            System.out.println("修改成功！");
        }else {
            System.out.println("修改失败！");
        }

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

    @Test
    public void testDelete() throws ClassNotFoundException, SQLException {
        //user删除一条数据 name | age  | status | gender

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

        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///db01?user=root&password=mysql");

        //3.编写SQL语句，动态值部分使用?代替
        String sql = "delete from user where name = ?;";

        //4.创建preparedStatement，并传入SQL语句结果
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //5.占位符赋值
        preparedStatement.setObject(1, "xiaozhao");

        //6.发送sql语句
        int result = preparedStatement.executeUpdate();

        //7.输出结果
        if (result > 0) {
            System.out.println("删除成功！");
        }else {
            System.out.println("删除失败！");
        }

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

    /**
     * 目标：查询全部数据!
     *      将数据存到List<Map>中
     *      map -> 对应一行数据
     *          map key -> 数据库列名或者别名
     *          map value -> 数据库列的值
     *
     * TODO: 思路分析
     *    1.先创建一个List<Map>集合
     *    2.遍历resultSet对象的行数据
     *    3.将每一行数据存储到一个map对象中!
     *    4.将对象存到List<Map>中
     *    5.最终返回
     *
     * 难点：
     *     如何获取列的名称？
     */
    @Test
    public void testSelect() throws ClassNotFoundException, SQLException {
        //user查询一条数据 name | age  | status | gender

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

        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///db01?user=root&password=mysql");

        //3.编写SQL语句，动态值部分使用?代替
        String sql = "select name, age, status, gender from user;";

        //4.创建preparedStatement，并传入SQL语句结果
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //5.占位符赋值

        //6.发送sql语句
        //遍历行
        ResultSet resultSet = preparedStatement.executeQuery();

        //7.进行结果解析
        List<Map<Object, Object>> list = new ArrayList<>();

        //TODO: metaDate 获取列的信息对象！（它可以根据下角标获取列的名称，可以获取列的数量）
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();// 可以水平遍历列！

        while (resultSet.next()){
            Map<Object, Object> map = new HashMap<>();

            //不推荐的方式
//            map.put("name", resultSet.getString("name"));
//            map.put("age", resultSet.getInt("age"));
//            map.put("status", resultSet.getInt("status"));
//            map.put("gender", resultSet.getString("gender"));

            //遍历列，从1开始， <=
            for (int i = 1; i <= columnCount; i++) {
                Object object = resultSet.getObject(i);

                //getColumnLabel: 会获取别名, 如果没有写别名才是列的名称; 不要使用 getColumnName : 只会获取列的名称
                String columnLabel = metaData.getColumnLabel(i);
                map.put(columnLabel, resultSet.getObject(columnLabel));

            }


            list.add(map);
        }

        //使用迭代器遍历
//        Iterator<Map<Object, Object>> iterator = list.iterator();
//        while (iterator.hasNext()){
//            System.out.println(iterator.next());
//        }

        //使用增强for
        for (Map map : list) {
            System.out.println(map);
        }

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

preparedStatement 使用方式总结

  • 使用步骤总结

    1. 注册驱动
    2. 获取连接
    3. 编写SQL语句
    4. 创建preparedStatement并传入SQL语句结构
    5. 占位符赋值
    6. 发送SQL语句，并获取结果
    7. 结果集解析
    8. 关闭资源

  • 使用API总结

    1. 注册驱动
      方案1：调用静态方法，但是会注册两次

      1
      DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());

      方案2：反射触发

      1
      Class.forName("com.mysql.cj.jdbc.Driver");

    2. 获取连接

      1
      2
      3
      4
      5
      Connection connection = DriverManager.getConnection();

      //方式1 (String url,string user ,string password)
      //方式2 (String url,Properties info(user password))
      //方式3 (String url?user=账号&password=密码)

    3. 创建statement

      • 静态

        1
        Statement statement = connection.createStatement();

      • 预编译（推荐）

        1
        PreparedStatement preparedStatement = connection.preparedStatement(sql语句结构):

    4. 占位符赋值

      1
      preparedStatement.setObject(?的位置 从左到右 从1开始, 值)

    5. 发送 SQL 语句获取结果

      1
      2
      int rows = executeUpdate(); //非DQL
      Resultset resultSet = executeQuery(); //DQL

    6. 查询结果集解析

      移动光标指向行数据 next() if(next()) while(next())
      获取列的数据即可 get类型(int 列的下角标 从1开始 | int 列的label (别名或者列名)
      获取列的信息 getMetadata(); ResultsetMetaData对象 包含的就是列的信息
      getColumnCount(); | getcloumnLebal(index)

    7. 关闭资源

      1
      2
      3
      resultSet.close(); //若是rows，则不需要关闭，因为rows不是结果资源
      preparedStatement.close() // 或 statement.close();
      connection.close();

JDBC扩展提升

自增长主键回显实现

  • 功能需求

    1. java 程序获取插入数据时 mysql 维护自增长维护的主键 id 值这就是主键回显

    2. 作用：在多表关联插入数据时，一般主表的主键都是自动生成的，所以在插入数据之前无法知道这条数据的主键但是从表需要在插入数据之前就绑定主表的主键这是可以使用主键回显技术.

  • 功能实现

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    package com.atxiong.api.preparedstatement;
    import org.junit.Test;
    import java.sql.*;

    /**
     *
     * @Author Xiong
     * @data 2023/8/19
     * @Description 练习ps的特殊使用情况
     */
    public class PreparedStatementOtherPart {

        /**
         *
         * @throws SQLException
         * @throws ClassNotFoundException
         *
         * TODO: user插入一条数据！并且获取数据库自增长的主键
         *
         * TODO: 使用总结
         *       1. 创建preparedStatement的时候，告知，携带会数据库自增长的主键 connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
         *       2. 获取司机装主键值的结果集对象,一行一列,获取对应的数据即可 ResultSet resultSet = statement,getGeneratedKeys();
         */
        @Test
        public void returnPrimaryKey() throws SQLException, ClassNotFoundException {
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            //2.获取连接
            Connection connection = DriverManager.getConnection("jdbc:mysql:///db01?user=root&password=mysql");

            //3.编写SQL语句
            String sql = "insert into user (name,age,status,gender) values (?,?,?,?);";

            //4.创建preparedStatement
            PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            //5.占位符赋值
            preparedStatement.setObject(1, "XiaoMing");
            preparedStatement.setObject(2, 23);
            preparedStatement.setObject(3, 0);
            preparedStatement.setObject(4, "男");

            //6.发送SQL语句，并且获取结果
            int result = preparedStatement.executeUpdate();

            //7.结果解析
            if (result > 0) {
                System.out.println("插入成功！");

                //获取回显主键
                ResultSet resultSet = preparedStatement.getGeneratedKeys();
                resultSet.next();
                int id = resultSet.getInt(1);
                System.out.println("id= " + id);

            }else {
                System.out.println("插入失败！");
            }

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

批量数据插入性能提升

  • 功能需求：实现高效的批量插入数据

  • 功能实现

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    /**
         * 使用批量的方式插入10000条数据
         * TODO: 总结批量插入
         *    1. 路径后面添加?rewriteBatchedStatements=true 允许批量插入
         *    2. insert into values [必须写] 语句不能添加;结束
         *    3. 不是执行语句每条，是批量添加 addBatch();
         *    4. 遍历添加完毕以后，统一批量执行 executeBatch()
         */
        @Test
        public void testBatchInsert() throws SQLException, ClassNotFoundException {
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            //2.获取连接
            Connection connection = DriverManager.getConnection("jdbc:mysql:///db01?rewriteBatchedStatements=true", "root", "mysql");

            //3.编写SQL语句
            String sql = "insert into user (name,age,status,gender) values (?,?,?,?)";

            //4.创建preparedStatement
            PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            long start = System.currentTimeMillis();

            for (int i = 0; i < 10000; i++) {
                //5.占位符赋值
                preparedStatement.setObject(1, "Xi" + i);
                preparedStatement.setObject(2, 23);
                preparedStatement.setObject(3, 0);
                preparedStatement.setObject(4, "男");

                preparedStatement.addBatch(); //不执行，追加到values的后面
            }

            //6.发送SQL语句，并且获取结果
            preparedStatement.executeBatch();  //批量执行操作

            long end = System.currentTimeMillis();

            System.out.println("执行时间：" + (end - start));


            //7.结果解析


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

jdbc 中数据库事务实现

  • 章节目标

    • 使用 jdbc 代码，添加数据库事务动作！
    • 开启事务
    • 事务提交 / 事务回滚

  • 数据库表数据

    1
    2
    3
    4
    5
    6
    # 继续在atguigu的库中创建银行表
    CREATE TABLE Bank(
        Id INT PRIMARY KEY AUTO_INCREMENT COMMENT '账号主键',
        account VARCHAR(20) NOT NULL UNIQUE COMMENT '账号',
        money INT UNSIGNED COMMENT '金额，不能为负值');
    INSERT INTO Bank(account, money) VALUES ('xiong', 1000), ('shen', 1000);

  • 代码结构设计

  • jdbc事务实现

例子实现：

银行卡业务方法以及测试方法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
package com.atxiong.api.transaction;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 *
 * @Author Xiong
 * @data 2023/8/19
 * @Description 银行卡业务方法，调用Dao方法
 */
public class BankService {

    @Test
    public void transferTest() throws Exception {
        BankService bankService = new BankService();

        bankService.transfer("xiong", "shen", 500);
    }

    /**
     * TODO:
     *      事务添加是在业务方法中!
     *      利用try catch代码块，开启事务和提交事务，和事务回滚!
     *      将connection传入dao层即可! dao只负责使用，不要close();
     *
     * @param addAccount
     * @param subAccount
     * @param money
     * @throws Exception
     */
    public void transfer(String addAccount, String subAccount, int money) throws Exception{
        BankDao bankDao = new BankDao();

        Class.forName("com.mysql.cj.jdbc.Driver");

        Connection connection = DriverManager.getConnection("jdbc:mysql:///db01", "root", "mysql");

        try {
            //开启事务
            //关闭事务提交！
            connection.setAutoCommit(false);

            //执行事务动作
            bankDao.add(addAccount, money, connection);
            System.out.println("---------");
            bankDao.sub(subAccount, money, connection);

            //事务提交
            connection.commit();

        } catch (ClassNotFoundException e) {
            connection.rollback();

            e.printStackTrace();
        }finally {
            connection.close();
        }
    }
}

Bank表的数据库操作方法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
package com.atxiong.api.transaction;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 *
 * @Author Xiong
 * @data 2023/8/19
 * @Description Bank表的数据库操作方法存储类
 */
public class BankDao {

    /**
     * 加钱
     * @param account
     * @param money
     */
    public int add(String account, int money, Connection connection) throws ClassNotFoundException, SQLException {

        String sql = "update bank set money =  money + ? where account = ?;";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setObject(1, money);
        preparedStatement.setObject(2, account);

        int rows = preparedStatement.executeUpdate();

        preparedStatement.close();

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


    /**
     * 减钱
     * @param account
     * @param money
     */
    public int sub(String account, int money, Connection connection) throws ClassNotFoundException, SQLException {

        String sql = "update bank set money =  money - ? where account = ?;";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setObject(1, money);
        preparedStatement.setObject(2, account);

        int rows = preparedStatement.executeUpdate();

        preparedStatement.close();

        System.out.println("减钱成功！");
        return rows;
    }
}

Druid连接池技术

连接性能消耗问题分析

由于连接创建和回收的会消耗大量时间，甚至可能大于连接使用的时间，因此可以想办法复用connection，于是出现了连接池。

数据库连接池的作用

总结缺点：

  1. 不使用数据库连接池，每次都通过DriverManager获取新连接，用完直接抛弃断开，连接的利用率太低，太浪费。

  2. 对于数据库服务器来说，压力太大了。我们数据库服务器和 Java 程序对连接数也无法控制，很容易导致数据库服务器崩溃。

我们就希望能管理连接。

  • 我们可以建立一个连接池，这个池中可以容纳一定数量的连接对象，一开始，我们可以先替用户先创建好一些连接对象，等用户要拿连接对象时，就直接从池中拿，不用新建了，这样也可以节省时间。然后用户用完后，放回去，别人可以接着用。
  • 可以提高连接的使用率。当池中的现有的连接都用完了，那么连接池可以向服务器申请新的连接放到池中。
  • 直到池中的连接达到“最大连接数“，就不能在申请新的连接了，如果没有拿到连接的用户只能等待。

市面常见连接产品和对比

JDBC的数据库连接池使用 javax.sql.DataSource接口进行规范，所有的第三方连接池都实现此接口，自行添加具体实现！也就是说，所有连接池获取连接的和回收连接方法都一样，不同的只有性能和扩展功能！

  • DBCP 是 Apache 提供的数据库连接池，速度相对 c3p0 较快，但因自身存在BUG

  • C3P0 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以

  • Proxool 是 sourceforge 下的一个开源项目数据库连接池，有监控连接池状态的功能稳定性较 c3p0 差一点

  • Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、ProxooL 优点于一身的数据库连接池，要要国货之光!!!!

国货之光druid连接池使用

使用前要导入druid工具类的 jar

  • 硬编码方式（了解，不推荐）

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    /**
    	创建druid连接池对象，使用硬编码进行核心参数设置!
        	必须参数: 账号
        			密码
        			url
        			driverClass
        	非必须参数:
        			初始化个数
        			最大数量等等    不推荐设置
    */

    @Test
    public void druidHard() throws SQLException {
        
        DruidDataSource dataSource = new DruidDataSource();
        
        //设置四个必须参数
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("mysql");
        dataSource.setUrl("jdbc:mysql:///db01");
        
        //获取连接
        Connection connection = dataSource .getConnection0);
        
        // JDBC的步骤
        //回收连接
        connection.close();
    }

  • 软编码方式

    • 外部配置文件

      存放在src/druid.properties

      1
      2
      3
      4
      5
      # druid连接池需要的配置参数,key固定命名
      driverClassName=com.mysql.cj.jdbc.Driver
      username=root
      password=mysql
      url=jdbc:mysql:///db01

    • druid声明代码

      1
      2
      3
      4
      5
      6
      7
      @Test
      public void druidSoft() throws Exception {
          Properties properties = new Properties();
          InputStream ips = DruidDemo.class.getClassLoader().getResourceAsStream("druid");
          properties.load(ips);
          DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
      }

JDBC使用优化以及封装

jdbc工具类封装 v1.0

用于封装注册驱动(连接池)、获取连接、回收连接等连接相关的代码

外部配置文件
位置: src/druid.properties

1
2
3
4
5
# druid连接池需要的配置参数,key固定命名
driverClassName=com.mysql.cj.jdbc.Driver
username=root
password=mysql
url=jdbc:mysql:///db01

工具类代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
package com.atxiong.api.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 *
 * @Author Xiong
 * @data 2023/8/20
 * @Description:
 *
 * v1.0版本工具类
 *      内部包含一个连接池对象，并且对外提供获取连接和回收连接的方法!
 *
 * 小建议:
 *      工具类的方法，推荐写成静态，外部调用会更加方便!
 *
 * 实现:
 *      属性 连接池对象 [实例化一次]
 *          单例模式
 *          static{
 *              全局调用一次
 *          }
 *      方法
 *          对外提供连接的方法
 *          回收外部传入连接方法
 *
 */
public class JdbcUtils {
    private static DataSource dataSource = null; //连接池对象

    static {

        Properties properties = new Properties();
        InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 对外提供连接的方法
     */
    public static Connection getConnection() throws SQLException {

        return dataSource.getConnection();
    }

    /**
     * 释放连接
     */
    public static void freeConnection(Connection connection) throws SQLException {
        connection.close();
    }
}

jdbc工具类封装 v2.0

优化工具类v1.0版本，考虑事务的情况下！如何使一个线程的不同方法获取同一个连接？

ThreadLocal的介绍：

JDK1.2的版本中就提供java.lang.ThreadLocal，为解决多线程程序的并发问题提供了一种新的思路使用这个工具类可以很简洁地编写出优美的多线程程序。通常用来在在多线程中管理共享数据库连接、Session等

ThreadLocal用于保存某个线程共享变量，原因是在 Java 中，每一个线程对象中都有一个ThreadLocalMap<ThreadLocal，Object>，其 key 就是一个ThreadLocal，而object即为该线程的共享变量。而这个 map 是通过ThreadLocal的 set 和 get 方法操作的。对于同一个static ThreadLocal，不同线程只能从中 get，set，remove 自己的变里，而不会影响其他线程的变里。

  1. ThreadLocal对象.get: 获取 ThreadLocal 中当前线程共享变量的值。
  2. ThreadLocal对象.set: 设置 ThreadLocal 中当前线程共享变里的值。
  3. ThreadLocal对象.remove: 移除 ThreadLocal 中当前线程共享变量的值。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
package com.atxiong.api.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
 *
 * @Author Xiong
 * @data 2023/8/20
 * @Description:
 *
 * v2.0版本工具类
 *      内部包含一个连接池对象，并且对外提供获取连接和回收连接的方法!
 *
 * 小建议:
 *      工具类的方法，推荐写成静态，外部调用会更加方便!
 *
 * 实现:
 *      属性 连接池对象 [实例化一次]
 *          单例模式
 *          static{
 *              全局调用一次
 *          }
 *      方法
 *          对外提供连接的方法
 *          回收外部传入连接方法
 * TODO:
 *      利用线程本地变量，存储连接信息! 确保一个线程的多个方法可以获取同一个connection！
 *      优势: 事务操作的时候 service 和 dao 属于同一个线程，不同再传递参数了!
 *      大家都可以调用getConnection自动获取的是相同的连接池!
 */
public class JdbcUtilsV2 {
    private static DataSource dataSource = null; //连接池对象

    private static ThreadLocal<Connection> tl = new ThreadLocal<>();

    static {

        Properties properties = new Properties();
        InputStream inputStream = JdbcUtilsV2.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 对外提供连接的方法
     */
    public static Connection getConnection() throws SQLException {
        //先查看线程本地变量是否存在
        Connection connection = tl.get();

        if (connection == null) {
            //线程本地变量没有，连接池获取
            connection = dataSource.getConnection();
            tl.set(connection);
        }

        return connection;
    }

    /**
     * 释放连接
     */
    public static void freeConnection() throws SQLException {

        Connection connection = tl.get();
        if (connection != null) {
            tl.remove();  //清空本地变量数据
            connection.setAutoCommit(true);  //事务状态回归
            connection.close(); //回收到连接池
        }
    }
}

高级应用层封装 BaseDao

用于封装查询方法以及非查询方法的代码

基本上每一个数据表都应该有一个对应的 DAO 接口及其实现类，发现对所有表的操作(增、删、改、查)代码重复度很高，所以可以抽取公共代码，给这些DAO 的实现类可以抽取一个公共的父类，我们称为 BaseDao。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
 package com.atxiong.api.utils;

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

/**
 *
 * @Author Xiong
 * @data 2023/8/20
 * @Description 封装Dao数据库重复代码
 *
 * TODO:
 *      封装两个方法 1.非简化DQL(增、删、改) 2.简化DQL(查)
 *
 */
public abstract class BaseDao {

    /**
     * 封装简化非dql语句
     * @param sql
     * @param params 占位符的值
     * @return 执行的行数
     */
    public int executeUpdate(String sql, Object ... params) throws SQLException {

        //获取连接
        Connection connection = JdbcUtilsV2.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //可变参数可以当作数组使用
        for (int i = 1; i <= params.length; i++) {
            preparedStatement.setObject(i, params[i - 1]);
        }

        int rows =  preparedStatement.executeUpdate();

        preparedStatement.close();
        //是否回收连接，需要考虑是不是事务！
        if (connection.getAutoCommit()) {
            //没有开启事务则正常回收连接
            JdbcUtilsV2.freeConnection();
        }
        //若开启事务了 不要管连接即可! 业务层处理!

        return rows;
    }

    /**
     * 封装简化dql语句
     *
     * 将查询结果封装到一个实体类集合!
     * @param clazz 要接值的实体类集合的模板对象
     * @param sql  查询语句，要求列名或者别名等于实体类的属性名!
     * @param params 占位符的值 要和?位置对象传递
     * @param <T> 声明结果的泛型
     * @return 查询的实体类集合
     */
    public <T> List<T> executeQuery(Class<T> clazz, String sql, Object ... params) throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException {

        //获取连接
        Connection connection = JdbcUtilsV2.getConnection();

        //创建preparedStatement，并传入SQL语句结果
        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]);
            }
        }

        //6.发送sql语句
        //遍历行
        ResultSet resultSet = preparedStatement.executeQuery();

        //7.进行结果解析
        List<T> list = new ArrayList<>();

        //获取列的信息对象
        //TODO: metaDate 获取列的信息对象！（它可以根据下角标获取列的名称，可以获取列的数量）
        ResultSetMetaData metaData = resultSet.getMetaData();

        int columnCount = metaData.getColumnCount();// 可以水平遍历列！

        while (resultSet.next()){
            T t = clazz.newInstance(); //调用类的无参构造函数实例化对象!

            //遍历列，从1开始， <=
            for (int i = 1; i <= columnCount; i++) {

                //获取属性值
                Object value = resultSet.getObject(i);

                //getColumnLabel: 会获取别名, 如果没有写别名才是列的名称; 不要使用 getColumnName : 只会获取列的名称
                String propertyName = metaData.getColumnLabel(i);

                //反射，给对象的属性值赋值
                Field field = clazz.getDeclaredField(propertyName);
                field.setAccessible(true); //属性可以设置，打破private的修饰限制

                //参数1：要赋值的对象，如果属性是静态的，第一个参数可以为null；参数2：具体的属性值
                field.set(t, value);
            }

            list.add(t);
        }

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

        if (connection.getAutoCommit())
            JdbcUtilsV2.freeConnection();

        return list;
    }
}