JDBC

JDBC 是什么？JDBC 英文名为：Java Data Base Connectivity(Java 数据库连接)，官方解释它是 Java 编程语言和广泛的数据库之间独立于数据库的连接标准的 Java API，根本上说 JDBC 是一种规范，它提供的接口，一套完整的，允许便捷式访问底层数据库。可以用 JAVA 来写不同类型的可执行文件：JAVA 应用程序、JAVA Applets、Java Servlet、JSP 等，不同的可执行文件都能通过 JDBC 访问数据库，又兼备存储的优势。简单说它就是 Java 与数据库的连接的桥梁或者插件，用 Java 代码就能操作数据库的增删改查、存储过程、事务等。

我们可以发现，JDK 自带了一个java.sql包，而这里面就定义了大量的接口，不同类型的数据库，都可以通过实现此接口，编写适用于自己数据库的实现类。而不同的数据库厂商实现的这套标准，我们称为数据库驱动。

准备工作

那么我们首先来进行一些准备工作，以便开始 JDBC 的学习：

• 将 idea 连接到我们的数据库，以便以后调试。

• 将 mysql 驱动 jar 依赖导入到项目中（推荐 6.0 版本以上，这里用到是 8.0）

https://dev.mysql.com/downloads/connector/j/

mysql-connector-j-8.2.0.jar 右键, 添加为库

• 向 Jetbrians 申请一个学生/教师授权，用于激活 idea 终极版（进行 JavaWeb 开发需要用到，一般申请需要 3-7 天时间审核）不是大学生的话...emmm...懂的都懂。

• 教育授权申请地址：https://www.jetbrains.com/shop/eform/students

一个 Java 程序并不是一个人的战斗，我们可以在别人开发的基础上继续向上开发，其他的开发者可以将自己编写的 Java 代码打包为jar，我们只需要导入这个jar作为依赖，即可直接使用别人的代码，就像我们直接去使用 JDK 提供的类一样。

使用 JDBC 连接数据库

注意：6.0 版本以上，不用手动加载驱动，我们直接使用即可！

//1. 通过DriverManager来获得数据库连接
try (Connection connection = DriverManager.getConnection("连接URL","用户名","密码");
     //2. 创建一个用于执行SQL的Statement对象
     Statement statement = connection.createStatement()){   //注意前两步都放在try()中，因为在最后需要释放资源！
    //3. 执行SQL语句，并得到结果集
    ResultSet set = statement.executeQuery("select * from 表名");
    //4. 查看结果
    while (set.next()){
        ...
    }
}catch (SQLException e){
    e.printStackTrace();
}
//5. 释放资源，try-with-resource语法会自动帮助我们close

其中，连接的 URL 如果记不住格式，我们可以打开 idea 的数据库连接配置，复制一份即可。（其实 idea 本质也是使用的 JDBC，整个 idea 程序都是由 Java 编写的，实际上 idea 就是一个 Java 程序）

了解 DriverManager

我们首先来了解一下 DriverManager 是什么东西，它其实就是管理我们的数据库驱动的：

public static synchronized void registerDriver(java.sql.Driver driver,
        DriverAction da)
    throws SQLException {

    /* Register the driver if it has not already been added to our list */
    if(driver != null) {
        registeredDrivers.addIfAbsent(new DriverInfo(driver, da));    //在刚启动时，mysql实现的驱动会被加载，我们可以断点调试一下。
    } else {
        // This is for compatibility with the original DriverManager
        throw new NullPointerException();
    }

    println("registerDriver: " + driver);

}

Command + 选中 DriverManager -> DriverManager Class

在刚开始的时候已经自动注册了

com.mysql.cj.jdbc.Driver :

在加载的时候, static 静态代码块就会被执行了

我们可以通过调用 getConnection()来进行数据库的链接：

@CallerSensitive
public static Connection getConnection(String url,
    String user, String password) throws SQLException {
    java.util.Properties info = new java.util.Properties();

    if (user != null) {
        info.put("user", user);
    }
    if (password != null) {
        info.put("password", password);
    }

    return (getConnection(url, info, Reflection.getCallerClass()));   //内部有实现
}

我们可以手动为驱动管理器添加一个日志打印：

static {
    DriverManager.setLogWriter(new PrintWriter(System.out));   //这里直接设定为控制台输出
}

现在我们执行的数据库操作日志会在控制台实时打印。

了解 Connection

Connection 是数据库的连接对象，可以通过连接对象来创建一个 Statement 用于执行 SQL 语句：

Statement createStatement() throws SQLException;

我们发现除了普通的 Statement，还存在 PreparedStatement：

PreparedStatement prepareStatement(String sql)
    throws SQLException;

在后面我们会详细介绍 PreparedStatement 的使用，它能够有效地预防 SQL 注入式攻击。

它还支持事务的处理，也放到后面来详细进行讲解。

了解 Statement

我们发现，我们之前使用了executeQuery()方法来执行select语句，此方法返回给我们一个 ResultSet 对象，查询得到的数据，就存放在 ResultSet 中！

Statement 除了执行这样的 DQL 语句外，我们还可以使用executeUpdate()方法来执行一个 DML 或是 DDL 语句，它会返回一个 int 类型，表示执行后受影响的行数，可以通过它来判断 DML 语句是否执行成功。

也可以通过excute()来执行任意的 SQL 语句，它会返回一个boolean来表示执行结果是一个 ResultSet 还是一个 int，我们可以通过使用getResultSet()或是getUpdateCount()来获取。

执行 DML 操作

我们通过几个例子来向数据库中插入数据。

执行 DQL 操作

执行 DQL 操作会返回一个 ResultSet 对象，我们来看看如何从 ResultSet 中去获取数据：

//首先要明确，select返回的数据类似于一个excel表格
while (set.next()){
    //每调用一次next()就会向下移动一行，首次调用会移动到第一行
}

我们在移动行数后，就可以通过 set 中提供的方法，来获取每一列的数据。

执行批处理操作

当我们要执行很多条语句时，可以不用一次一次地提交，而是一口气全部交给数据库处理，这样会节省很多的时间。

public static void main(String[] args) throws ClassNotFoundException {
    try (Connection connection = DriverManager.getConnection();
         Statement statement = connection.createStatement()){

        statement.addBatch("insert into user values ('f', 1234)");
        statement.addBatch("insert into user values ('e', 1234)");   //添加每一条批处理语句
        statement.executeBatch();   //一起执行

    }catch (SQLException e){
        e.printStackTrace();
    }
}

Bug

会报错 Not fix yet

将查询结果映射为对象

既然我们现在可以从数据库中获取数据了，那么现在就可以将这些数据转换为一个类来进行操作，首先定义我们的实体类：

public class Student {
    Integer sid;
    String name;
    String sex;

    public Student(Integer sid, String name, String sex) {
        this.sid = sid;
        this.name = name;
        this.sex = sex;
    }

    public void say(){
        System.out.println("我叫："+name+"，学号为："+sid+"，我的性别是："+sex);
    }
}

现在我们来进行一个转换：

while (set.next()){
    Student student = new Student(set.getInt(1), set.getString(2), set.getString(3));
    student.say();
}

注意：列的下标是从1开始的。

我们也可以利用反射机制来将查询结果映射为对象，使用反射的好处是，无论什么类型都可以通过我们的方法来进行实体类型映射：

private static <T> T convert(ResultSet set, Class<T> clazz){
    try {
        Constructor<T> constructor = clazz.getConstructor(clazz.getConstructors()[0].getParameterTypes());   //默认获取第一个构造方法
        Class<?>[] param = constructor.getParameterTypes();  //获取参数列表
        Object[] object = new Object[param.length];  //存放参数
        for (int i = 0; i < param.length; i++) {   //是从1开始的
            object[i] = set.getObject(i+1);
            if(object[i].getClass() != param[i])
                throw new SQLException("错误的类型转换："+object[i].getClass()+" -> "+param[i]);
        }
        return constructor.newInstance(object);
    } catch (ReflectiveOperationException | SQLException e) {
        e.printStackTrace();
        return null;
    }
}

现在我们就可以通过我们的方法来将查询结果转换为一个对象了：

while (set.next()){
    Student student = convert(set, Student.class);
    if(student != null) student.say();
}

实际上，在后面我们会学习Mybatis框架，它对JDBC进行了深层次的封装，而它就进行类似上面反射的操作来便于我们对数据库数据与实体类的转换。

Implementing Login and SQL Injection Attack

Before using secure practices, let’s take a look at how a basic login implementation might look if we’re trying to log in a user：

try (Connection connection = DriverManager.getConnection("URL", "username", "password");
     Statement statement = connection.createStatement();
     Scanner scanner = new Scanner(System.in)) {

    ResultSet res = statement.executeQuery(
        "select * from user where username='" + scanner.nextLine() + 
        "' and pwd='" + scanner.nextLine() + "';"
    );

    while (res.next()) {
        String username = res.getString(1);
        System.out.println(username + " logged in successfully!");
    }
} catch (SQLException e) {
    e.printStackTrace();
}

The user can log in by entering their username and password, and at first glance, this might seem fine. But what if the input is something like this:

Test
1111' or 1=1; -- 
# Test logged in successfully!

Since 1=1 is always true, our original SQL statement becomes:

select * from user where username='Test' and pwd='1111' or 1=1; -- '

We can see that if such data is allowed, our original SQL query structure is compromised, enabling users to log in to any account at will. To prevent this, we might consider restricting user input to filter out certain SQL keywords, but because there are so many potential keywords, this approach isn’t ideal for solving the problem.

https://www.bilibili.com/video/BV1CL4y1i7qR?spm_id_from=333.788.videopod.episodes&vd_source=73e7d2c4251a7c9000b22d21b70f5635&p=27

Using PreparedStatement

We’ve seen that using Statement to execute SQL commands leaves our application vulnerable to SQL injection attacks. Fortunately, this problem can be mitigated by using PreparedStatement:

public static void main(String[] args) throws ClassNotFoundException {
    try (Connection connection = DriverManager.getConnection("URL", "username", "password");
         PreparedStatement statement = connection.prepareStatement("select * from user where username= ? and pwd=?;");
         Scanner scanner = new Scanner(System.in)) {

        statement.setString(1, scanner.nextLine());
        statement.setString(2, scanner.nextLine());
        System.out.println(statement); // Print to see the final executed query
        ResultSet res = statement.executeQuery();
        while (res.next()) {
            String username = res.getString(1);
            System.out.println(username + " logged in successfully!");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

With PreparedStatement, we provide the SQL query in advance, using ? as placeholders. This pre-compiles the SQL statement and replaces the placeholders with the actual values safely. Using this approach makes SQL injection ineffective. Let’s see what the actual executed SQL query looks like:

com.mysql.cj.jdbc.ClientPreparedStatement: select * from user where username= 'Test' and pwd='123456'' or 1=1; -- ';

Here, any single quotes (') in our input are automatically escaped as \', and the input is enclosed in actual quotes by PreparedStatement. This prevents the injected SQL from being executed as part of the original query, effectively blocking SQL injection attacks!

https://www.bilibili.com/video/BV1CL4y1i7qR?spm_id_from=333.788.player.switch&vd_source=73e7d2c4251a7c9000b22d21b70f5635&p=28

管理事务

JDBC默认的事务处理行为是自动提交，所以前面我们执行一个SQL语句就会被直接提交（相当于没有启动事务），所以JDBC需要进行事务管理时，首先要通过Connection对象调用setAutoCommit(false) 方法, 将SQL语句的提交（commit）由驱动程序转交给应用程序负责。

con.setAutoCommit();   //关闭自动提交后相当于开启事务。
// SQL语句
// SQL语句
// SQL语句
con.commit();或 con.rollback();

一旦关闭自动提交，那么现在执行所有的操作如果在最后不进行commit()来提交事务的话，那么所有的操作都会丢失，只有提交之后，所有的操作才会被保存！也可以使用rollback()来手动回滚之前的全部操作！

public static void main(String[] args) throws ClassNotFoundException {
    try (Connection connection = DriverManager.getConnection("URL","用户名","密码");
         Statement statement = connection.createStatement()){

        connection.setAutoCommit(false);  //关闭自动提交，现在将变为我们手动提交
        statement.executeUpdate("insert into user values ('a', 1234)");
        statement.executeUpdate("insert into user values ('b', 1234)");
        statement.executeUpdate("insert into user values ('c', 1234)");

        connection.commit();   //如果前面任何操作出现异常，将不会执行commit()，之前的操作也就不会生效
    }catch (SQLException e){
        e.printStackTrace();
    }
}

我们来接着尝试一下使用回滚操作：

public static void main(String[] args) throws ClassNotFoundException {
    try (Connection connection = DriverManager.getConnection("URL","用户名","密码");
         Statement statement = connection.createStatement()){

        connection.setAutoCommit(false);  //关闭自动提交，现在将变为我们手动提交
        statement.executeUpdate("insert into user values ('a', 1234)");
        statement.executeUpdate("insert into user values ('b', 1234)");

        connection.rollback();   //回滚，撤销前面全部操作

        statement.executeUpdate("insert into user values ('c', 1234)");

        connection.commit();   //提交事务（注意，回滚之前的内容都没了）

    }catch (SQLException e){
        e.printStackTrace();
    }
}

同样的，我们也可以去创建一个回滚点来实现定点回滚：

public static void main(String[] args) throws ClassNotFoundException {
    try (Connection connection = DriverManager.getConnection("URL","用户名","密码");
         Statement statement = connection.createStatement()){

        connection.setAutoCommit(false);  //关闭自动提交，现在将变为我们手动提交
        statement.executeUpdate("insert into user values ('a', 1234)");

        Savepoint savepoint = connection.setSavepoint();   //创建回滚点
        statement.executeUpdate("insert into user values ('b', 1234)");

        connection.rollback(savepoint);   //回滚到回滚点，撤销前面全部操作

        statement.executeUpdate("insert into user values ('c', 1234)");

        connection.commit();   //提交事务（注意，回滚之前的内容都没了）

    }catch (SQLException e){
        e.printStackTrace();
    }
}

通过开启事务，我们就可以更加谨慎地进行一些操作了，如果我们想从事务模式切换为原有的自动提交模式，我们可以直接将其设置回去：

public static void main(String[] args) throws ClassNotFoundException {
    try (Connection connection = DriverManager.getConnection("URL","用户名","密码");
         Statement statement = connection.createStatement()){

        connection.setAutoCommit(false);  //关闭自动提交，现在将变为我们手动提交
        statement.executeUpdate("insert into user values ('a', 1234)");
        connection.setAutoCommit(true);   //重新开启自动提交，开启时把之前的事务模式下的内容给提交了
        statement.executeUpdate("insert into user values ('d', 1234)");
        //没有commit也成功了！
    }catch (SQLException e){
        e.printStackTrace();
    }

JDBC Interaction Framework

In addition to MyBatis, which we’ve discussed previously, Spring also provides an official and convenient JDBC tool that supports quick CRUD (Create, Read, Update, Delete) operations. To start using it, first add the dependency to your project with the following starter:

<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

Once the dependency is added, you can easily begin using Spring’s JDBC utilities for database interactions.

JdbcTemplate Class

Besides MyBatis, which we've already discussed, Spring also provides a very convenient tool for JDBC operations. This tool can quickly perform CRUD (Create, Read, Update, Delete) operations. To get started, we first add the dependency using the starter:

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
</dependency>

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

To interact with a database, the simplest and most direct way is to use JdbcTemplate:

@Resource
JdbcTemplate template;

JdbcTemplate provides many built-in methods that make database operations easier. For example, if we want to query a single record from the database:

We can use queryForMap to quickly query a single row of data and retrieve it as a Map:

@Test
void contextLoads() {
    Map<String, Object> map = template.queryForMap("select * from user where id = ?", 1);
    System.out.println(map);
}

This approach is very convenient, as it retrieves the row where id = 1 and maps the column names to their corresponding values, making it easy to access the data in a key-value format.

We can also write a custom mapper to directly map query results into an object:

@Data
@AllArgsConstructor
public class User {
    int id;
    String name;
    String email;
    String password;
}

@Test
void contextLoads() {
    User user = template.queryForObject("select * from user where id = ?",
        (r, i) -> new User(r.getInt(1), r.getString(2), r.getString(3), r.getString(4)), 1);
    System.out.println(user);
}

In this example, queryForObject is used with a custom row mapper to directly retrieve a User object based on the query results, making data retrieval more type-safe and manageable.

Apart from this, JdbcTemplate also provides the update method, which is versatile for insert, update, and delete operations:

@Test
void contextLoads() {
    int update = template.update("insert into user values(2, 'admin', '654321@qq.com', '123456')");
    System.out.println("Updated " + update + " row(s)");
}

This way, for small projects or test cases, JdbcTemplate can be used efficiently for a variety of operations, making it a quick and convenient tool for database interactions.

Simple JDBC Insert in Spring JDBC

For certain insert operations, Spring JDBC offers the SimpleJdbcInsert utility, which makes it easy to perform advanced insert operations. For example, if the table's primary key is an auto-increment ID, SimpleJdbcInsert can insert a record and return the automatically generated ID, which is very convenient:

@Configuration
public class WebConfiguration {

    @Resource
    DataSource source;

    @Test
    void contextLoads() {
        // Creating a SimpleJdbcInsert instance
        SimpleJdbcInsert simple = new SimpleJdbcInsert(source)
                .withTableName("user")   // Set the target table name
                .usingGeneratedKeyColumns("id");    // Specify the auto-increment primary key column

        Map<String, Object> user = new HashMap<>(2);  // Inserting requires a Map to hold the data
        user.put("name", "bob");
        user.put("email", "112233@qq.com");
        user.put("password", "123456");

        Number number = simple.executeAndReturnKey(user);   // The returned Number is the generated primary key
        System.out.println(number);
    }
}

This approach enables quick insertion while also returning the auto-generated primary key, making it quite convenient.

Although Spring JDBC provides these helpful utilities, they are mainly suitable for small, simple projects. As projects grow in complexity, Spring JDBC might not be the ideal choice. JPA framework will be better.