我只想卷死各位，或者被各位卷死，在此特别感谢康师傅的JDBC教程

12:26:52

尚硅谷JDBC核心技术视频教程（康师傅带你一站式搞定jdbc）

155.9万 3.7万

JDBC概述

数据的持久化

  • 持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以”固化”，而持久化的实现过程大多通过各种关系数据库来完成。
  • 持久化的主要应用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中。

Java中的数据存储技术

  • 在Java中，数据库存取技术可分为如下几类：
    • JDBC直接访问数据库
    • JDO (Java Data Object )技术
    • 第三方O/R工具，如Hibernate, Mybatis 等
  • JDBC是java访问数据库的基石，JDO、Hibernate、MyBatis等只是更好的封装了JDBC。

JDBC介绍

  • JDBC(Java Database Connectivity)是一个独立于特定数据库管理系统、通用的SQL数据库存取和操作的公共接口（一组API），定义了用来访问数据库的标准Java类库，（java.sql,javax.sql）使用这些类库可以以一种标准的方法、方便地访问数据库资源。
  • JDBC为访问不同的数据库提供了一种统一的途径，为开发者屏蔽了一些细节问题。
  • JDBC的目标是使Java程序员使用JDBC可以连接任何提供了JDBC驱动程序的数据库系统，这样就使得程序员无需对特定的数据库系统的特点有过多的了解，从而大大简化和加快了开发过程。
  • 如果没有JDBC，那么Java程序访问数据库时是这样的：
  • 有了JDBC，Java程序访问数据库时是这样的：
  • 总结如下：

JDBC体系结构

  • JDBC接口（API）包括两个层次：
    • 面向应用的API：Java API，抽象接口，供应用程序开发人员使用（连接数据库，执行SQL语句，获得结果）。
    • 面向数据库的API：Java Driver API，供开发商开发数据库驱动程序用。

JDBC是sun公司提供一套用于数据库操作的接口，java程序员只需要面向这套接口编程即可。不同的数据库厂商，需要针对这套接口，提供不同实现。不同的实现的集合，即为不同数据库的驱动。——面向接口编程

JDBC程序编写步骤

补充：ODBC(Open Database Connectivity，开放式数据库连接)，是微软在Windows平台下推出的。使用者在程序中只需要调用ODBC API，由 ODBC 驱动程序将调用转换成为对特定的数据库的调用请求。

获取数据库连接

要素一：Driver接口实现类

Driver接口介绍

  • java.sql.Driver 接口是所有 JDBC 驱动程序需要实现的接口。这个接口是提供给数据库厂商使用的，不同数据库厂商提供不同的实现。
  • 在程序中不需要直接去访问实现了 Driver 接口的类，而是由驱动程序管理器类(java.sql.DriverManager)去调用这些Driver实现。
    • Oracle的驱动：oracle.jdbc.driver.OracleDriver
    • mySql的驱动： com.mysql.jdbc.Driver

加载与注册JDBC驱动

  • 加载驱动：加载 JDBC 驱动需调用 Class 类的静态方法 forName()，向其传递要加载的 JDBC 驱动的类名
    • Class.forName(“com.mysql.jdbc.Driver”);
  • 注册驱动：DriverManager 类是驱动程序管理器类，负责管理驱动程序
  • 使用DriverManager.registerDriver(com.mysql.jdbc.Driver)来注册驱动
  • 通常不用显式调用 DriverManager 类的 registerDriver() 方法来注册驱动程序类的实例，因为 Driver 接口的驱动程序类都包含了静态代码块，在这个静态代码块中，会调用 DriverManager.registerDriver() 方法来注册自身的一个实例。下面是MySQL的Driver实现类的源码：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package com.mysql.jdbc;

import java.sql.DriverManager;
import java.sql.SQLException;

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

要素二：URL

  • JDBC URL 用于标识一个被注册的驱动程序，驱动程序管理器通过这个 URL 选择正确的驱动程序，从而建立到数据库的连接。
  • JDBC URL的标准由三部分组成，各部分间用冒号分隔。
    • jdbc:子协议:子名称
    • 协议：JDBC URL中的协议总是jdbc
    • 子协议：子协议用于标识一个数据库驱动程序
    • 子名称：一种标识数据库的方法。子名称可以依不同的子协议而变化，用子名称的目的是为了定位数据库提供足够的信息。包含主机名(对应服务端的ip地址)，端口号，数据库名
  • 举例
1
2
3
4
5
6
7
8
9
jdbc:mysql://localhost:3306/dbtest

其中
jdbc --> 协议
mysql --> 子协议
localhost:3306/dbtest --> 子名称
    localhost --> 主机名
    3306 --> 端口号(MySQL默认端口号为3306)
    dbtest --> 数据库名
  • 几种常用数据库的 JDBC URL
    • MySQL的连接URL编写方式：
      • jdbc:mysql://主机名称:mysql服务端口号/数据库名称?参数=值&参数=值
      • jdbc:mysql://localhost:3306/atguigu
      • jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8（如果JDBC程序与服务器端的字符集不一致，会导致乱码，那么可以通过参数指定服务器端的字符集）
      • jdbc:mysql://localhost:3306/atguigu?user=root&password=123456
    • Oracle 9i的连接URL编写方式：
      • jdbc:oracle:thin:@主机名称:oracle服务端口号:数据库名称
      • jdbc:oracle:thin:@localhost:1521:atguigu
    • SQLServer的连接URL编写方式：
      • jdbc:sqlserver://主机名称:sqlserver服务端口号:DatabaseName=数据库名称
      • jdbc:sqlserver://localhost:1433:DatabaseName=atguigu

要素三：用户名和密码

  • user,password可以用“属性名=属性值”方式告诉数据库
  • 可以调用 DriverManager 类的 getConnection() 方法建立到数据库的连接

数据库连接方式举例

连接方式一

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Test
    public void testConnection01() throws SQLException {
        //1. 提供java.sql.Driver接口实现类的对象
        Driver driver = new com.mysql.jdbc.Driver();

        //2. 提供url，指明具体操作的数据
        String url = "jdbc:mysql://localhost:13306/atguigudb";

        //3. 提供Properties对象，指明用户名和密码
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","yourPassword");

        //4. 调用driver的connect()，获取连接
        Connection connect = driver.connect(url, info);
        System.out.println(connect);
    }

说明：上述代码中显式出现了第三方数据库的API

连接方式二

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Test
    public void testConnection02() throws Exception {
        //1. 实例化Driver
        String className = "com.mysql.jdbc.Driver";
        Class clazz = Class.forName(className);
        Driver driver = (Driver) clazz.newInstance();

        //2. 提供url，指明具体操作的数据
        String url = "jdbc:mysql://localhost:13306/atguigudb";

        //3. 提供Properties对象，指明用户名和密码
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","yourPassword");

        //4. 调用driver的connect()，获取连接
        Connection connect = driver.connect(url, info);
        System.out.println(connect);
    }

说明：相较于方式一，这里使用反射实例化Driver，不在代码中体现第三方数据库的API。体现了面向接口编程思想。

连接方式三

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Test
    public void testConnection03() throws Exception {
        //1.数据库连接的4个基本要素：
        String className = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:13306/atguigudb";
        String user = "root";
        String password = "yourPassword";

        //2. 实例化Driver
        Class clazz = Class.forName(className);
        Driver driver = (Driver) clazz.newInstance();

        //3. 注册驱动
        DriverManager.registerDriver(driver);

        //4. 获取连接
        Connection connect = DriverManager.getConnection(url,user,password);
        System.out.println(connect);
    }

说明：使用DriverManager实现数据库的连接。体会获取连接必要的4个基本要素。

连接方式四

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Test
    public void testConnection04() throws Exception {
        //1.数据库连接的4个基本要素：
        String className = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:13306/atguigudb";
        String user = "root";
        String password = "yourPassword";

        //2. 加载驱动
        Class.forName(className);

        //3. 获取连接
        Connection connect = DriverManager.getConnection(url,user,password);
        System.out.println(connect);
    }

说明：不必显式的注册驱动了。因为在DriverManager的源码中已经存在静态代码块，实现了驱动的注册。

连接方式五(最终版)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Test
    public void testConnection05() throws Exception {
        //1. 加载配置文件
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);

        //2. 读取配置信息
        String className = properties.getProperty("className");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        //2. 加载驱动
        Class.forName(className);

        //4. 获取连接
        Connection connect = DriverManager.getConnection(url,user,password);
        System.out.println(connect);
    }
  • 其中，配置文件声明在工程的src目录下：【jdbc.properties】
1
2
3
4
user=root
password=yourpassword
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver
  • 说明：使用配置文件的方式保存配置信息，在代码中加载配置文件
  • 使用配置文件的好处：
    • 实现了代码和数据的分离，如果需要修改配置信息，直接在配置文件中修改，不需要深入代码
    • 如果修改了配置信息，省去重新编译的过程。

使用PreparedStatement实现CRUD操作

操作和访问数据库

  • 数据库连接被用于向数据库服务器发送命令和 SQL 语句，并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接。
  • 在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：
    • Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
    • PrepatedStatement：SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。
    • CallableStatement：用于执行 SQL 存储过程

使用Statement操作数据表的弊端

  • 通过调用 Connection 对象的 createStatement() 方法创建该对象。该对象用于执行静态的 SQL 语句，并且返回执行结果。
  • Statement 接口中定义了下列方法用于执行 SQL 语句：
1
2
int excuteUpdate(String sql) //执行更新操作INSERT、UPDATE、DELETE
ResultSet executeQuery(String sql) //执行查询操作SELECT

  • 但是使用Statement操作数据表存在弊端：

    • 问题一：存在拼串操作，繁琐
    • 问题二：存在SQL注入问题

  • SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令，从而利用系统的 SQL 引擎完成恶意行为的做法。

  • 对于 Java 而言，要防范 SQL 注入，只要用 PreparedStatement(从Statement扩展而来) 取代 Statement 就可以了。

  • SQL注入示例

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
import org.junit.Test;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.Properties;

public class StatementTest {
    @Test
    public void testLogin() {
        String userName = "1' or ";
        String password = "=1 or '1' = '1";
        String sql = "select user,password from user_table where user = '" + userName + "' and password = '" + password + "'";
        User user = get(sql, User.class);
        if (user == null)
            System.out.println("用户名或密码错误！");
        else System.out.println("登陆成功！");
    }

    // 使用Statement实现对数据表的查询操作
    public <T> T get(String sql, Class<T> clazz) {
        T t = null;
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            // 1.加载配置文件
            InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties pros = new Properties();
            pros.load(is);
            // 2.读取配置信息
            String user = pros.getProperty("user");
            String password = pros.getProperty("password");
            String url = pros.getProperty("url");
            String driverClass = pros.getProperty("className");
            // 3.加载驱动
            Class.forName(driverClass);
            // 4.获取连接
            conn = DriverManager.getConnection(url, user, password);
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            // 获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            // 获取结果集的列数
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    // 1. 获取列的别名
                    String columnName = rsmd.getColumnLabel(i + 1);
                    // 2. 根据列名获取对应数据表中的数据
                    Object columnVal = rs.getObject(columnName);
                    // 3. 将数据表中得到的数据，封装进对象
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, columnVal);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
}
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
public class User {
    private String user;
    private String password;

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public User(String user, String password) {
        this.user = user;
        this.password = password;
    }

    public User() {
    }

    @Override
    public String toString() {
        return "User{" +
                "user='" + user + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

正常情况下，我们需要输入正确的用户名和密码才能登陆成功，但下面的代码也能登陆成功，这就是拼串的弊端
下面我们将用户名和密码带入到SQL语句中，不难发现，WHERE中的条件中有一个 '1' = '1'，是必然成立的，所以采用拼串的方式，是可以登陆成功的

1
2
3
4
5
6
7
8
SELECT USER,PASSWORD
FROM user_table
WHERE USER = '' AND PASSWORD = '';

-- 填入用户名和密码
SELECT USER,PASSWORD
FROM user_table
WHERE USER = '1' OR ' and password = ' =1 OR '1' = '1';
  • 综上，我们改用PreparedStatement来替换掉Statement来进行增改删查

PreparedStatement的使用

PreparedStatement介绍

  • 可以通过调用 Connection 对象的 preparedStatement(String sql) 方法获取 PreparedStatement 对象
  • PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句
  • PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引(从1开始)，第二个是设置的 SQL 语句中的参数的值

PreparedStatement vs Statement

  • 代码的可读性和可维护性
  • PreparedStatement 能最大可能提高性能：
    • DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。
    • 在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。
  • PreparedStatement 可以防止 SQL 注入

Java与SQL对应数据类型转换表

Java类型 SQL类型
BOOLEAN BIT
BYTE TINYINT
short SMALLINT
INT INTEGER
LONG BIGINT
STRING CHAR,VARCHAR,LONGVARCHAR
BYTE array BINARY , VAR BINARY
java.sql.Date DATE
java.sql.Time TIME
java.sql.Timestamp TIMESTAMP

使用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
@Test
    public void testInsert() throws Exception{
        //1. 读取配置文件
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String className = properties.getProperty("className");
        //2. 加载驱动
        Class.forName(className);
        //3. 获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        //4. 预编译SQL语句，返回PreparedStatement实例
        String sql = "insert into customers(name,email,birth) values(?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date date =  sdf.parse("1999-12-21");
        //5. 填充占位符
        ps.setObject(1,"张三");
        ps.setObject(2,"zhangsan@gmail.com");
        ps.setObject(3,date);
        //6. 执行操作
        ps.execute();
        //7. 资源的关闭
        ps.close();
        connection.close();
    }

遇到资源的关闭，还是要用try/catch/finally处理的

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
@Test
    public void testInsert() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            //1. 读取配置文件
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(is);
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String className = properties.getProperty("className");
            //2. 加载驱动
            Class.forName(className);
            //3. 获取连接
            connection = DriverManager.getConnection(url, user, password);
            //4. 预编译SQL语句，返回PreparedStatement实例
            String sql = "insert into customers(name,email,birth) values(?,?,?)";
            ps = connection.prepareStatement(sql);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date date =  sdf.parse("1999-08-11");
            //5. 填充占位符
            ps.setObject(1,"李四");
            ps.setObject(2,"lisi@gmail.com");
            ps.setObject(3,date);
            //6. 执行操作
            ps.execute();
        } catch (IOException | ClassNotFoundException | SQLException | ParseException e) {
            e.printStackTrace();
        } finally {
            //7. 资源的关闭
            if (ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
  • 增删改的通用操作
    注意到增删改都需要建立连接和关闭资源，所以可以将这二者封装成两个方法，写成一个工具类

工具类提供通用的连接数据库和关闭资源的操作

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
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtil {
    public static Connection getConnection() throws Exception{
        //1. 读取配置文件
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String className = properties.getProperty("className");
        //2. 加载驱动
        Class.forName(className);
        //3. 获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    public static void clossResource(Connection connection, Statement ps){
        //资源的关闭
        if (connection!=null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps!=null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
  • 根据不同的SQL语句和占位符，可以实现任意的增删改操作，由于占位符的个数不确定，所以这里采用可变形参。
  • 可变形参的底层是一个数组，所以我们可以用args.length来获取形参个数，从而进行遍历填充占位符
  • 关于可变形参的说明：
    • 1.可变形参的个数可以是0个，1个，任意个
    • 2.可变形参的底层就是一个数组
    • 3.和可变形参相同类型的数组不构成方法重载
    • 4.可变形参只能放在形参列表的最后一个
    • 5.在一个形参列表中最多只能有一个可变形参
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
public void update(String sql, Object ...args) {
    Connection connection = null;
    PreparedStatement ps = null;
    try {
        //1. 获取数据库的连接
        connection = JDBCUtil.getConnection();
        //2. 预编译SQL语句，返回PreparedStatement的实例
        ps = connection.prepareStatement(sql);
        //3. 填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        //4. 执行
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //5. 资源的关闭
        JDBCUtil.clossResource(connection, ps);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Test
    public void testUpdate() throws Exception{
        // 增
        String sql1 = "insert into customers(name,email,birth) values(?,?,?)";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date date = sdf.parse("1997-02-27");
        update(sql1,"王五","wangwu@gmail.com",date);
        
        // 删
        String sql2 = "delete from customers where id = ?";
        update(sql2,4);
        
        // 改
        String sql3 = "update `order` set order_name = ? where order_id = ?";
        update(sql3,"DD",2);
    }

使用PreparedStatement实现查询操作

查询操作不同于增删改操作，查询操作需要我们返回一个对象
Java中万事万物皆对象，例如我们在SQL中查询到的一条Customers对象，就可以对应一个Java类的对象
其中Customers表中的每一个字段，都对应着Java类中的一个属性

  • 针对Customers表的 不通用的 查询操作

下面是Customers表的描述，暂时无视掉这个photo，根据表的描述建一个Customer类

Field Type Null Key Default Extra
id int(10) NO PRI auto_increment
name varchar(15) YES
email varchar(20) YES auto_increment
birth date YES
photo mediumblob YES 
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
import java.util.Date;

public class Customer {
    private int id;
    private String name;
    private String email;
    private Date birth;

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getEmail() {
        return email;
    }

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

    public Date getBirth() {
        return birth;
    }

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

    public Customer() {
    }

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

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

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
@Test
    public void testQuery() throws Exception {
        //1. 获取数据库连接
        Connection connection = JDBCUtil.getConnection();
        //2. 预编译SQL语句，返回PreparedStatement对象实例
        String sql = "select id,name,email,birth from customers where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        //3. 填充占位符
        ps.setObject(1, 1);
        //4. 执行并返回结果集
        ResultSet rs = ps.executeQuery();
        //5. 处理结果集
        //next():判断结果集的下一条是否有数据，有则返回true，无则返回false
        //如果查询到了多条数据，把if换成while，这里的示例只查询了一条，所以这里用了if
        if (rs.next()) {
            //获取当前数据的各个字段值，上面有Java与SQL对应的数据类型转换表
            int id = rs.getInt(1);
            String name = rs.getString(2);
            String email = rs.getString(3);
            Date date = rs.getDate(4);
            //万事万物皆对象，将数据封装成一个类
            Customer customer = new Customer(id, name, email, date);
            System.out.println(customer);
        }
        //6. 关闭资源，ResultSet也需要关闭，所以在JDBCUtil工具类中，重载一下clossResource方法
        JDBCUtil.clossResource(connection, ps, rs);
    }

由于结果集也需要我们关闭一下，所以在JDBCUtil工具类中，重载一下关闭资源的方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public static void clossResource(Connection connection, Statement ps,ResultSet rs){
        //资源的关闭
        if (connection!=null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps!=null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
  • 针对Customers表 通用的 查询操作
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
public Customer customerForQuery(String sql, Object... args) {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        //1. 连接数据库
        connection = JDBCUtil.getConnection();
        //2. 预编译SQL语句，获取PreparedStatement实例对象
        ps = connection.prepareStatement(sql);
        //3. 填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        //4. 执行executeQuery()，获得结果集
        rs = ps.executeQuery();
        //5. 获得结果集的元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //6. 通过元数据获取ColumnCount，即结果集中有几个列
        int columnCount = rsmd.getColumnCount();
        //7. 如果有数据
        if (rs.next()) {
            //则生成一个对象实例，当然这里可以使用泛型
            Customer customer = new Customer();
            //遍历每一个列
            for (int i = 0; i < columnCount; i++) {
                //8. 获取列值
                Object columnValue = rs.getObject(i + 1);
                //9. 获取列名
                String columnName = rsmd.getColumnName(i + 1);
                //10. 使用反射给相应属性赋值，注意属性都是private的，将Accessible设置为true
                Field field = customer.getClass().getDeclaredField(columnName);
                field.setAccessible(true);
                field.set(customer, columnValue);
            }
            return customer;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //11. 资源的关闭
        JDBCUtil.clossResource(connection, ps, rs);
    }
    return null;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
@Test
    public void testQuery2() {
        String sql = "select id,name,email,birth from customers where id = ?";
        Customer customer = customerForQuery(sql, "1");
        System.out.println(customer);
        //Customer{id=1, name='汪峰', email='wf@126.com', birth=2010-02-02}

        sql = "select name,birth from customers where name = ?";
        customer = customerForQuery(sql,"周杰伦");
        System.out.println(customer);
        //Customer{id=0, name='周杰伦', email='null', birth=1979-11-15}
        //这里只查询了两个字段，故查询结果中的其他字段为默认值        
    }
  • 针对Order表 通用的 查询操作

根据下表创建一个Order类，但SQL中的命名方式与Java中的命名方式不一样，我们按照Java中的命名方式即可，但是这样会导致反射时找不到对应的字段名去赋值。解决办法也很简单，在SQL查询语句中，可以给列起别名，将别名设定为Java中的命名方式即可，获取元数据的时候，使用getColumnLabel()方法来获取列的别名，这样就完美解决了这二者不同的命名方式产生的问题

Field Type Null Key Default Extra
order_id int(10) NO PRI auto_increment
order_name varchar(20) YES
order_date date YES 
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
import java.util.Date;

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

    public int getorderId() {
        return orderId;
    }

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

    public String getorderName() {
        return orderName;
    }

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

    public Date getorderDate() {
        return orderDate;
    }

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

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

    public Order() {
    }

    @Override
    public String toString() {
        return "Order{" +
                "orderId=" + orderId +
                ", orderName='" + orderName + '\'' +
                ", orderDate=" + orderDate +
                '}';
    }
}
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
public Order queryForOrder(String sql, Object... args) {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        connection = JDBCUtil.getConnection();
        ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        rs = ps.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        if (rs.next()) {
            Order order = new Order();
            for (int i = 0; i < columnCount; i++) {
                //这里改成了getColumnLabel来获取别名，如果未给列取别名，那么别名就是列名
                String columnLabel = rsmd.getColumnLabel(i + 1);
                Object columnValue = rs.getObject(i + 1);
                //别名与Java中命名方式相同，所以反射的时候通过别名来找到对应字段值来赋值
                Field field = order.getClass().getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(order, columnValue);
            }
            return order;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.clossResource(connection, ps, rs);
    }
    return null;
}
1
2
3
4
5
6
@Test
    public void testQuery() {
        String sql = "select order_name as orderName,order_date as orderDate from `order` where order_id = ?";
        Order order = queryForOrder(sql, 2);
        System.out.println(order);
    }
  • 小结：
  • 当表的字段名和类的属性名不同时：
    • 1.在声明sql时，使用类的属性名来命名字段的别名
    • 2.使用ResultSetMetaData的getColumnLabel()方法来替代getColumnName()方法，获得列的列名的别名。
    • 3.说明：没有别名时，getColumnLabel()方法获取的就是列名；所以无论有没有别名，都使用getColumnLabel()方法
  • 通用的查询操作，但只返回一条记录

利用泛型方法来解决

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
public <T> T getInstance(Class<T> clazz, String sql, Object... args) {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        connection = JDBCUtil.getConnection();
        ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        rs = ps.executeQuery();
        if (rs.next()) {
            T t = clazz.newInstance();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            for (int i = 0; i < columnCount; i++) {
                Object columnValue = rs.getObject(i + 1);
                String columnLabel = rsmd.getColumnLabel(i + 1);
                Field field = t.getClass().getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, columnValue);
            }
            return t;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.clossResource(connection, ps, rs);
    }
    return null;
}
1
2
3
4
5
6
7
8
9
10
@Test
    public void queryTest() {
        String sql1 = "select order_id as orderId,order_name as orderName,order_date as orderDate from `order` where order_id = ?";
        Order order = getInstance(Order.class, sql1, 1);
        System.out.println(order);

        String sql2 = "select id,name,email,birth from customers where id = ?";
        Customer customer = getInstance(Customer.class, sql2, 7);
        System.out.println(customer);
    }
  • 通用的查询操作，返回多条记录

只需要在返回一条记录的基础上，修改一些代码即可

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
//把返回类型改为集合
public <T> ArrayList<T> getForList(Class<T> clazz, String sql, Object... args){
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    ArrayList<T> ts = null;
    try {
        connection = JDBCUtil.getConnection();
        ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        rs = ps.executeQuery();
        ts = new ArrayList<>();
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        //这里的if改成while
        while (rs.next()) {
            T t = clazz.newInstance();
            for (int i = 0; i < columnCount; i++) {
                Object columnValue = rs.getObject(i + 1);
                String columnLabel = rsmd.getColumnLabel(i + 1);
                Field field = t.getClass().getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, columnValue);
            }
            //没查询完一条数据，将其加入到集合中
            ts.add(t);
        }
        return ts;
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.clossResource(connection, ps, rs);
    }
    return null;
}

查询Customers表中id小于某个值的所有数据

1
2
3
4
5
6
7
8
@Test
    public void queryTest() {
        String sql = "select id,name,email,birth from customers where id < ?";
        ArrayList<Customer> ts = getForList(Customer.class,sql, 13);
        for (Customer c : ts) {
            System.out.println(c);
        }
    }

使用PreparedStatement代替Statement原因：

  1. PreparedStatement是预编译的sql语句，可以解决Statement的拼串和sql注入问题；
    • PreparedStatement首先确定了语法逻辑，然后填充相应的数值；
    • 而Statement会连着数值里包含的非法语法一起编译，就会造成对原来语法逻辑的破坏。
  2. PreparedStatement还可以操作Blob类型的数据，而Statement不行；
  3. PreparedStatement可以实现跟高效的批量操作：
    • 如果访问10000条数据，PreparedStatement会将语法固定，只用填充占位符就好了。

JDBC API小结

  • 两种思想

    • 面向接口编程的思想
    • ORM思想(object relational mapping)
      • 一个数据表对应一个java类
      • 表中的一条记录对应java类的一个对象
      • 表中的一个字段对应java类的一个属性

  • sql是需要结合列名和表的属性名来写。注意起别名。

  • 两种技术

    • JDBC结果集的元数据：ResultSetMetaData
      • 获取列数：getColumnCount()
      • 获取列的别名：getColumnLabel()
    • 通过反射，创建指定类的对象，获取指定的属性并赋值

操作BLOB类型字段

MySQL BLOB类型

  • MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。
  • 插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。
  • MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)
类型 大小{单位:字节)
TINYBLOB 最大255
BLOB 最大65K
MEDIUMBLOB 最大16M
LONGBLOB 最大4G
  • 实际使用中根据需要存入的数据大小定义不同的BLOB类型。
  • 需要注意的是：如果存储的文件过大，数据库的性能会下降。
  • 如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数： max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务。

向数据表中插入大数据类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@Test
    public void blobTest() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            String sql = "insert into customers(name,email,birth,photo) values(?,?,?,?)";
            //获取连接
            connection = JDBCUtil.getConnection();
            //预编译SQL语句，获取prepareStatement实例对象
            ps = connection.prepareStatement(sql);
            //填充占位符
            ps.setObject(1, "Kyle");
            ps.setObject(2, "Kyle@126.com");
            ps.setObject(3, "1997-08-07");
            //操作Blob类型变量
            ps.setBlob(4, new FileInputStream("头像.png"));
            //执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, ps);
        }
    }

如果在指定了相关的Blob类型以后，还报错∶xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数: max_allowed_packet=16M。同时注意∶修改了my.ini文件之后，需要重新启动mysql服务。

从数据表中读取大数据类型

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
@Test
    public void blobTest2() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        InputStream is = null;
        OutputStream os = null;
        try {
            String sql = "select id,name,email,birth,photo from customers where id = ?";
            connection = JDBCUtil.getConnection();
            ps = connection.prepareStatement(sql);
            ps.setObject(1, 16);
            rs = ps.executeQuery();
            if (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date birth = rs.getDate("birth");
                Customer customer = new Customer(id, name, email, birth);
                System.out.println(customer);
                Blob photo = rs.getBlob("photo");
                is = photo.getBinaryStream();
                os = new FileOutputStream("照片.png");
                byte[] buffer = new byte[1024];
                int len;
                while ((len = is.read(buffer)) != -1) {
                    os.write(buffer, 0, len);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            JDBCUtil.clossResource(connection, ps, rs);
        }
    }

批量插入

批量执行SQL语句

  • 当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率
  • JDBC的批量处理语句包括下面三个方法：
    • addBatch(String)：添加需要批量处理的SQL语句或是参数；
    • executeBatch()：执行批量处理语句；
    • clearBatch():清空缓存的数据
  • 通常我们会遇到两种批量执行SQL语句的情况：
    • 多条SQL语句的批量处理；
    • 一个SQL语句的批量传参；

高效的批量插入

  • 举例：向数据表中插入20000条数据
    • 首先先创建一个goods表
    1
    2
    3
    4
    CREATE TABLE goods(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(25)
    );
  • 下面是四种实现方式，效率逐渐提高，都是基于低层次实现的优化

使用Statement

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
//耗时：15006ms
@Test
    public void testInsert01() {
        Connection connection = null;
        Statement st = null;
        try {
            connection = JDBCUtil.getConnection();
            st = connection.createStatement();
            long start = System.currentTimeMillis();
            for (int i = 1; i <= 20000; i++) {
                String sql = "insert into goods(name) values('name_ " + i + "')";
                st.executeUpdate(sql);
            }
            long end = System.currentTimeMillis();
            System.out.println("耗时：" + (end - start)  + "ms");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, st);
        }
    }
  • 相较于实现层次一的优点
  • 即PreparedStatement优于Statement的地方：
    • 在于sql语句Statement内存中会有很多个sql语句，并且每次都会做一次语法检查，而PreparedStatement只有一个sql语句，每次只是填充占位符。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
//耗时：14682ms
@Test
    public void testInsert02(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            String sql = "insert into goods(name) values(?)";
            connection = JDBCUtil.getConnection();
            ps = connection.prepareStatement(sql);
            long start = System.currentTimeMillis();
            for (int i = 0; i < 20000; i++) {
                ps.setObject(1, "name_" + i);
            }
            long end = System.currentTimeMillis();
            System.out.println("耗时：" + (end - start) + "ms");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, ps);
        }
    }
  • 相较于实现层次二的优点
    • 使用Batch批量处理：addBatch()、executeBatch()、clearBatch()
    • mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。在jdbc.properties配置文件的url后添上：?rewriteBatchedStatements=true
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
//耗时：196ms
@Test
    public void testInsert03() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            String sql = "insert into goods(name) values(?)";
            connection = JDBCUtil.getConnection();
            ps = connection.prepareStatement(sql);
            long start = System.currentTimeMillis();
            for (int i = 0; i < 20000; i++) {
                ps.setObject(1, "name_" + i);
                //1.“攒”sql
                ps.addBatch();
                if (i % 10000 == 0){
                    //2.执行
                    ps.executeBatch();
                    //3.清空
                    ps.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println("耗时：" + (end - start) + "ms");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, ps);
        }
    }
  • 再快点
    • 每500条数据执行一次ps.executeBatch();
    • 这样每500条就会提交一次。
    • 但每次提交都会占用一点时间，所以先不提交，都传完以后，最后再提交。
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
//耗时：157ms
@Test
    public void testInsert04() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            String sql = "insert into goods(name) values(?)";
            connection = JDBCUtil.getConnection();
            //1.设置为不自动提交数据
            connection.setAutoCommit(false);
            ps = connection.prepareStatement(sql);
            long start = System.currentTimeMillis();
            for (int i = 0; i < 20000; i++) {
                ps.setObject(1, "name_" + i);
                //1.“攒”sql
                ps.addBatch();
                if (i % 10000 == 0){
                    //2.执行
                    ps.executeBatch();
                    //3.清空
                    ps.clearBatch();
            }
            //2.提交数据
            connection.commit();
            long end = System.currentTimeMillis();
            System.out.println("耗时：" + (end - start) + "ms");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, ps);
        }
    }

数据库事务

数据库事务介绍

  • 事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。
  • 事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。
  • 为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

JDBC事务处理

  • 数据一旦提交，就不可回滚。
  • 哪些操作会导致数据的自动提交？
    • DDL操作一旦执行，都会自动提交；set autocommit = false对操作失效；
    • DML默认情况下，一旦执行，就会自动提交；但我们可以通过set autocommit = false的方式取消DML操作的自动提交；
    • 默认在关闭连接的时候也会自动提交
  • JDBC程序中为了让多个 SQL 语句作为一个事务执行：
    • 调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务
    • 在所有的 SQL 语句都成功执行后，调用 commit(); 方法提交事务
    • 在出现异常时，调用 rollback(); 方法回滚事务

    若此时 Connection 没有被关闭，还可能被重复使用，则需要恢复其自动提交状态setAutoCommit(true)。尤其是在使用数据库连接池技术时，执行close()方法前，建议恢复自动提交状态。

  • 案例：用户AA向用户BB转账100

代码运行后，查询数据，发现AA少了100块钱，而BB却没有收到100块钱，这样的结果显然是我们不能接受的，所以要采用JDBC的事务处理

1
2
3
4
5
6
7
8
9
10
@Test
    public void testJDBCTransaction() {
        String sql1 = "update user_table set balance = balance - 100 where user = ?";
        update(sql1,"AA");
        //模拟网络异常
        int a = 1 / 0;
        String sql2 = "update user_table set balance = balance + 100 where user = ?";
        update(sql2,"BB");
        System.out.println("转账成功");
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
public void update(String sql, Object... args) {
    Connection connection = null;
    PreparedStatement ps = null;
    try {
        connection = JDBCUtil.getConnection();
        ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.clossResource(connection, ps);
    }
}
  • 考虑事务的转账操作
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
@Test
    public void testJDBCTransaction() {
        Connection connection = null;
        try {
            connection = JDBCUtil.getConnection();
            //设置不要自动提交
            connection.setAutoCommit(false);
            String sql1 = "update user_table set balance = balance - 100 where user = ?";
            update(connection, sql1, "AA");
            //模拟网络异常
            int a = 1 / 0;
            String sql2 = "update user_table set balance = balance + 100 where user = ?";
            update(connection, sql2, "BB");
            System.out.println("转账成功");
            //转账成功之后再提交
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            //如果出现了异常，那就回滚
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            //恢复自动提交
            try {
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            JDBCUtil.clossResource(connection, null);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
public void update(Connection connection, String sql, Object... args) {
    PreparedStatement ps = null;
    try {
        ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.clossResource(null, ps);
    }
}

事务的ACID属性

  1. 原子性（Atomicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  2. 一致性（Consistency） 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
  3. 隔离性（Isolation） 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
  4. 持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

数据库的并发问题

  • 对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
    • 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
    • 不可重复读:对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。（例如在购物网站刷新界面，库存可能会增加或减少）
    • 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行。（刷新购物网站，多了几条新商品）
  • 数据库事务的隔离性:数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。
  • 一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高

四种隔离级别

  • 数据库提供的4种事务隔离级别：
隔离级别 描述
READ UNCOMMITTED (读未提交数据) 允许事务读取未被其他事物提交的变更.脏读,不可重复读和幻读的问题都会出现
READ COMMITED (读已提交数据) 只允许事务读取已经被其它事务提交的变更.可以避免脏读,但不可重复读和幻读问题仍然可能出现
REPEATABLE READ (可重复读) 确保事务可以多次从一个字段中读取相同的值.在这个事务持续期间, 禁止其他事物对这个字段进行更新.可以避免脏读和不可重复读,但幻读的问题仍然存在.
SERIALIZABLE (串行化) 执行插入,更新和删除操作.所有并发问题都可以避免, 但性能十分低下确保事务可以从一个表中读取相同的行.在这个事务持续期间,禁止其他事务对该表
  • Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED 。
  • Mysql 支持 4 种事务隔离级别。Mysql 默认的事务隔离级别为: REPEATABLE READ。

在MySql中设置隔离级别

  • 每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation,表示当前的事务隔离级别。
  • 查看当前的隔离级别:
1
SELECT @@tx_isolation;
  • 设置当前 mySQL 连接的隔离级别:
1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  • 设置数据库系统的全局的隔离级别:
1
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
  • 补充操作：
    • 创建mysql数据库用户：
    1
    CREATE USER tom IDENTIFIED BY 'abc123';
    • 授予权限
    1
    2
    3
    4
    -- 授予通过网络方式登录的tom用户，对所有库所有表的全部权限，密码设为abc123.
    GRANT ALL PRIVILEGES ON *.* TO tom@'%' IDENTIFIED BY 'abc123';
    -- 给tom用户使用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限。
    GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO tom@localhost IDENTIFIED BY'abc123';

在Java代码中设置隔离级别

  • 使用getTransactionIsolation()可以获得当前隔离级别的int值，对应如下
    • int TRANSACTION_READ_UNCOMMITTED = 1;
    • int TRANSACTION_READ_COMMITTED = 2;
    • int TRANSACTION_REPEATABLE_READ = 4;
    • int TRANSACTION_SERIALIZABLE = 8;
  • 使用setTransactionIsolation可以设置隔离级别
1
2
3
4
5
6
7
8
9
10
11
@Test
    public void testTransactionUpdate() throws Exception{
        Connection connection = JDBCUtil.getConnection();
        //获取数据库隔离级别
        System.out.println(connection.getTransactionIsolation());
        //设置数据库隔离级别
        connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        String sql = "update user_table set balance = ? where user = ?";
        update(sql,5000,"CC");
        System.out.println("修改结束");
    }

DAO及相关实现类

  • DAO：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、Delete），而不包含任何业务相关的信息。有时也称作：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
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;

public abstract class BaseDAO {

    //通用的增删改操作，考虑了事务
    public void update(Connection connection, String sql, Object... args) {
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(null, ps);
        }
    }

    //通用的查询操作，返回一条记录，考虑了事务
    public <T> T getInstance(Connection connection, Class<T> clazz, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                T t = clazz.newInstance();
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Field field = t.getClass().getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, ps, rs);
        }
        return null;
    }

    //通用的查询操作，返回多条记录，考虑了事务
    public <T> ArrayList<T> getForList(Connection conn, Class<T> clazz, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<T> ts;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ts = new ArrayList<>();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Field field = t.getClass().getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                ts.add(t);
            }
            return ts;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(null, ps, rs);
        }
        return null;
    }

    public <E> E getValue(Connection connection, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                return (E) rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(null, ps, rs);
        }
        return null;
    }
}
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
import java.util.Date;

public class Customer {
    private int id;
    private String name;
    private String email;
    private Date birth;

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getEmail() {
        return email;
    }

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

    public Date getBirth() {
        return birth;
    }

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

    public Customer() {
    }

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

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

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
/*
 * 此接口用于规范对于Customers表的常用操作
 */

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

public interface CustomerDAO {
    //将customer对象添加到数据库中
    void insert(Connection connection, Customer customer);

    //将指定id的customer从数据库中删除
    void deleteById(Connection connection, int id);

    //针对于内存中的customer对象，修改数据库中指定的记录
    void updateById(Connection connection, Customer customer);

    //针对指定的id查询得到对应的Customer对象
    Customer getCustomerById(Connection connection, int id);

    //查询表中所有的记录返回集合
    List<Customer> getAll(Connection connection);

    //返回数据表中的条目数
    Long getCount(Connection connection);

    //返回数据表中的最大生日
    Date getMaxBirth(Connection connection);
}

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
import java.sql.Connection;
import java.util.Date;
import java.util.List;

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

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

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

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

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

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

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

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

import blog.jdbc06.dao.Customer;
import blog.jdbc06.dao.CustomerDaoImpl;
import blog.jdbc06.dao.JDBCUtil;
import org.junit.jupiter.api.Test;

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



/*
 * @Author:Kyle
 * @Date:2022/8/7
 * @Description:
 * @VERSION:1.8
 */

class CustomerDaoImplTest {
    CustomerDaoImpl c = new CustomerDaoImpl();

    @Test
    void insert() {
        Connection connection = null;
        try {
            connection = JDBCUtil.getConnection();
            Customer customer = new Customer(16, "艾克", "EIKO@gmail.com", null);
            c.insert(connection, customer);
            System.out.println("插入成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, null);
        }
    }

    @Test
    void deleteById() {
        Connection connection = null;
        try {
            connection = JDBCUtil.getConnection();
            c.deleteById(connection, 26);
            System.out.println("删除成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, null);
        }
    }

    @Test
    void updateById() {
        Connection connection = null;
        try {
            connection = JDBCUtil.getConnection();
            Customer customer = new Customer(16, "拉克丝", "LUX@gmail.com", null);
            c.updateById(connection, customer);
            System.out.println("修改成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, null);
        }
    }

    @Test
    void getCustomerById() {
        Connection connection = null;
        try {
            connection = JDBCUtil.getConnection();
            Customer customer = c.getCustomerById(connection, 25);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, null);
        }
    }

    @Test
    void getAll() {
        Connection connection = null;
        try {
            connection = JDBCUtil.getConnection();
            List<Customer> customers = c.getAll(connection);
            for (Customer customer : customers) {
                System.out.println(customer);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, null);
        }
    }

    @Test
    void getCount() {
        Connection connection = null;
        try {
            connection = JDBCUtil.getConnection();
            System.out.println("表中共有" + c.getCount(connection) + "条数据");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, null);
        }
    }

    @Test
    void getMaxBirth() {
        Connection connection = null;
        try {
            connection = JDBCUtil.getConnection();
            System.out.println("表中的最大生日为:" + c.getMaxBirth(connection));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, null);
        }
    }
}

  • 基于上述代码的优化

  • CustomerDAOImpl类中的操作对象都是Customer，但调用getForList()和getInstance()时，竟然需要把Customer.class传入，表明是操作Customers表的，这显得有些不合理，因为我们在进行操作的时候，也不会在CustomerDAOImpl中操作别的类，所以现在我们要把Customer.class这个参数给去掉（getInstance()和getForList()在BaceDAO中）

  • 但总归还是要说明造的是哪个对象。将CustomerDAOImpl声明为public class CustomerDaoImpl extends BaseDAO<Customer> implements CustomerDAO ，然后在CustomerDAOImpl获取父类的泛型，这样就知道是操作哪个类了。我们把它封装在一个构造代码块中。

  • 子类继承父类，在创建对象的时候，就会执行构造代码块，从而获取父类的泛型，这样就知道操作的是哪个类了

  • 以查询返回一条记录为例，将Class<T> clazz这个参数去掉，然后在父类中声明一个Class<T> clazz代码如下

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
public abstract class BaseDAO<T> {
    //声明一个clazz，
    private Class<T> clazz;

    //获取父类的泛型
    {
        //获取带泛型的父类
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType parmeType = (ParameterizedType) genericSuperclass;
        //获取泛型参数的数组（因为泛型参数可能不止一个）
        Type[] types = parmeType.getActualTypeArguments();
        //由于这里只有一个参数，所以这个参数就是我们需要的类型
        clazz = (Class<T>) types[0];
    }

    //这里已经将Class<T> clazz去掉了
    public T getInstance(Connection connection, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                //去掉参数之后，这里仍需表明是创建哪个对象
                T t = clazz.newInstance();
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Field field = t.getClass().getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, ps, rs);
        }
        return null;
    }
}
  • 优化后的完整代码，方便与上面对比
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
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;

public abstract class BaseDAO<T> {
    private Class<T> clazz;

    {
        //获取带泛型的父类
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType parmeType = (ParameterizedType) genericSuperclass;
        //获取泛型参数的数组（因为泛型参数可能不止一个）
        Type[] types = parmeType.getActualTypeArguments();
        //由于这里只有一个参数，所以这个参数就是我们需要的类型
        clazz = (Class<T>) types[0];
    }

    //通用的增删改操作，考虑了事务
    public void update(Connection connection, String sql, Object... args) {
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(null, ps);
        }
    }

    //通用的查询操作，返回一条记录，考虑了事务
    public T getInstance(Connection connection, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                T t = clazz.newInstance();
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Field field = t.getClass().getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, ps, rs);
        }
        return null;
    }

    //通用的查询操作，返回多条记录，考虑了事务
    public ArrayList<T> getForList(Connection conn, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<T> ts;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ts = new ArrayList<>();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Field field = t.getClass().getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                ts.add(t);
            }
            return ts;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(null, ps, rs);
        }
        return null;
    }

    public <E> E getValue(Connection connection, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                return (E) rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(null, ps, rs);
        }
        return null;
    }
}

这里就将extends BaseDAO 改成了 extends BaseDAO<Customer>

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
import java.sql.Connection;
import java.util.Date;
import java.util.List;

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

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

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

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

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

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

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

数据库连接池

JDBC数据库连接池的必要性

  • 在使用开发基于数据库的web程序时，传统的模式基本是按以下步骤：

    • 在主程序（如servlet、beans）中建立数据库连接
    • 进行sql操作
    • 断开数据库连接

  • 这种模式开发，存在的问题:

    • 普通的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection加载到内存中，再验证用户名和密码(得花费0.05s～1s的时间)。需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。数据库的连接资源并没有得到很好的重复利用。若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。
    • 对于每一次数据库连接，使用完后都得断开。否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。
    • 这种开发不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃。

数据库连接池技术

  • 为解决传统开发中的数据库连接问题，可以采用数据库连接池技术。
  • 数据库连接池的基本思想：就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。
  • 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
  • 数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。
  • 数据库连接池技术的优点
    1. 资源重用
      由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。
    2. 更快的系统反应速度
      数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间
    3. 新的资源分配手段
      对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源
    4. 统一的连接管理，避免数据库连接泄漏
      在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露

多种开源的数据库连接池

  • JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：
    • DBCP 是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持。
    • C3P0 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以。hibernate官方推荐使用
    • Proxool 是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点
    • BoneCP 是一个开源组织提供的数据库连接池，速度快
    • Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不确定是否有BoneCP快
  • DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池
  • DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度。
  • 特别注意：
    • 数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源（可以使用静态代码块来解决，这样只会执行一次）即可。
    • 当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但conn.close()并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

Druid（德鲁伊）数据库连接池

  • Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的连接池之一。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

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

public class DruidTest {

    @Test
    public void testGetConnection() throws Exception {
        //读取配置文件
        Properties pros = new Properties();
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("Druid.properties");
        pros.load(is);
        //获取数据源
        DataSource source = DruidDataSourceFactory.createDataSource(pros);
        //获取连接
        Connection connection = source.getConnection();
        //成功输出
        System.out.println(connection);
    }
}
1
2
3
4
username=root
url=jdbc:mysql://localhost:13306/test
password=yourpassword
driverClassName=com.mysql.jdbc.Driver

  • 如何保证整个应用只需要一个数据源？

    • 想办法将获取数据源的操作写进静态代码块中即可，因为静态代码块只执行一次
      • 将获取连接的方式写入我们的JDBCUtil工具类中
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      public class JDBCUtil {

          private static DataSource source;
          static {
              try {
                  Properties properties = new Properties();
                  InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("Druid.properties");
                  properties.load(is);
                  source = DruidDataSourceFactory.createDataSource(properties);
              } catch (Exception e) {
                  e.printStackTrace();
              }
          }
          public static Connection getConnection() throws Exception{
              return source.getConnection();
          }
      }

  • 其他详细配置参数：

配置 缺省 说明
name 配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。如果没有配置，将会生成一个名字，格式是：”DataSource-” + System.identityHashCode(this)
url 连接数据库的url，不同数据库不一样。例如：mysql:jdbc:mysql://10.20.153.104:3306/druid2 oracle:jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username 连接数据库的用户名
password 连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用ConfigFilter。详细看这里：https://github.com/alibaba/druid/wiki/使用ConfigFilter
driverClassName 根据url自动识别这一项可配可不配，如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName(建议配置下)
initialSize 0 初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
maxActive 8 最大连接池数量
maxIdle 8 已经不再使用，配置了也没效果
minIdle 最小连接池数量
maxWait 获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
poolPreparedStatements false 是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
maxOpenPreparedStatements -1 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
validationQuery 用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。
testOnBorrow true 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testOnReturn false 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testWhileIdle false 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
timeBetweenEvictionRunsMillis 有两个含义： 1)Destroy线程会检测连接的间隔时间2)testWhileIdle的判断依据，详细看testWhileIdle属性的说明
numTestsPerEvictionRun 不再使用，一个DruidDataSource只支持一个EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls 物理连接初始化的时候执行的sql
exceptionSorter 根据dbType自动识别当数据库抛出一些不可恢复的异常时，抛弃连接
filters 属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有：监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
proxyFilters 类型是List，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系

Apache-DBUtils实现CRUD操作

Apache-DBUtils简介

  • commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。
  • API介绍：
    • org.apache.commons.dbutils.QueryRunner
    • org.apache.commons.dbutils.ResultSetHandler
    • 工具类：org.apache.commons.dbutils.DbUtils

主要API的使用

DbUtils

  • DbUtils ：提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的。主要方法如下：
    • public static void close(...) throws java.sql.SQLException： DbUtils类提供了三个重载的关闭方法。这些方法检查所提供的参数是不是NULL，如果不是的话，它们就关闭Connection、Statement和ResultSet。
    • public static void closeQuietly(...): 这一类方法不仅能在Connection、Statement和ResultSet为NULL情况下避免关闭，还能隐藏一些在程序中抛出的SQLEeception。
    • public static void commitAndClose(Connection conn)throws SQLException： 用来提交连接的事务，然后关闭连接
    • public static void commitAndCloseQuietly(Connection conn)： 用来提交连接，然后关闭连接，并且在关闭连接时不抛出SQL异常。
    • public static void rollback(Connection conn)throws SQLException：允许conn为null，因为方法内部做了判断
    • public static void rollbackAndClose(Connection conn)throws SQLException
    • rollbackAndCloseQuietly(Connection)
    • public static boolean loadDriver(java.lang.String driverClassName)：这一方装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，你不需要捕捉这个异常ClassNotFoundException。

QueryRunner类

  • 该类简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。

  • QueryRunner类提供了两个构造器：

    • 默认的构造器
    • 需要一个 javax.sql.DataSource 来作参数的构造器

  • QueryRunner类的主要方法：

    • 更新
      • public int update(Connection conn, String sql, Object… params) throws SQLException:用来执行一个更新（插入、更新或删除）操作。
    • 插入
      • public T insert(Connection conn,String sql,ResultSetHandler rsh, Object… params) throwsSQLException：只支持INSERT语句，其中 rsh - The handler used to create the result object from the ResultSet of auto-generated keys. 返回值: An object generated by the handler.即自动生成的键值
    • 批处理
      • public int[] batch(Connection conn,String sql,Object[][] params)throws SQLException： INSERT,UPDATE, or DELETE语句
      • public T insertBatch(Connection conn,String sql,ResultSetHandler rsh,Object[][] params)throws SQLException：只支持INSERT语句
    • 查询
      • public Object query(Connection conn, String sql, ResultSetHandler rsh,Object… params) throws SQLException：执行一个查询操作，在这个查询中，对象数组中的每个元素值被用来作为查询语句的置换参数。该方法会自行处理 PreparedStatement 和 ResultSet 的创建和关闭。

  • 插入测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Test
    public void testInsert() {
        Connection connection = null;
        try {
            String sql = "insert into customers(name,email,birth) values(?,?,?)";
            connection = JDBCUtil.getConnection();
            QueryRunner runner = new QueryRunner();
            int insertCount = runner.update(connection, sql, "JAX", "JAX@126.com", "1997-08-11");
            System.out.println("添加了" + insertCount + "条记录");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, null);
        }
    }
  • 查询测试
  • BeanHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Test
    public void testQuery01() {
        Connection connection = null;
        try {
            String sql = "select id,name,email,birth from customers where id = ?";
            connection = JDBCUtil.getConnection();
            QueryRunner runner = new QueryRunner();
            BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
            Customer customer = runner.query(connection, sql, handler, 3);
            System.out.println(customer);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, null);
        }
    }
  • BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Test
    public void testQuery02() {
        Connection connection = null;
        try {
            String sql = "select id,name,email,birth from customers where id < ?";
            connection = JDBCUtil.getConnection();
            QueryRunner runner = new QueryRunner();
            BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
            List<Customer> customers = runner.query(connection, sql, handler, 13);
            customers.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.clossResource(connection, null);
        }
    }
  • scalarHandler：用于查询特殊值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Test
	public void testQuery03() {
		Connection connection = null;
		try {
			String sql = "select count(*) from customers";
			connection = JDBCUtil.getConnection();
			QueryRunner runner = new QueryRunner();
			ScalarHandler handler = new ScalarHandler();
			Long count = (Long) runner.query(connection, sql, handler);
			System.out.println("表中有" + count + "条记录");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.clossResource(connection, null);
		}
	}

ResultSetHandler接口及实现类

  • 该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式。
  • ResultSetHandler 接口提供了一个单独的方法：Object handle (java.sql.ResultSet .rs)。
  • 接口的主要实现类：
    • ArrayHandler：把结果集中的第一行数据转成对象数组。
    • ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。
    • BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
    • BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
    • ColumnListHandler：将结果集中某一列的数据存放到List中。
    • KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。
    • MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
    • MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List
    • ScalarHandler：查询单个值对象

完结撒花

  • 之前自己写的连接方式和增删改查的操作，其实人家都已经封装好了，直接用人家的就行了，学这些就当是手写一下底层的实现，多了解底层实现才能走得远，杜绝成为CV工程师