JDBC

概念

​ JDBC的全称是Java Database Connectivity（Java数据库连接技术），通俗地讲，就是在Java代码中，使用JDBC提供的方法，可以发送字符串类型的SQL语句到数据库管理软件（Mysql、Oracle等），并且获取语句执行结果，进而实现数据库数据CRUD操作的技术。

• JDBC是Java数据库连接技术的统称
• JDBC由**Java语言的规范（接口）和各个数据库厂商的实现驱动（jar）**组成
• JDBC技术是一种典型的面向接口编程
• JDBC优势
  • 只需要学习JDBC接口规定方法，即可操作所有数据库软件
  • 项目中需要切换数据库，只需要更新第三方驱动jar包，不需要更改代码

简单例子

1.在pom.xml文件中导入mysql的连接驱动包

<dependencies>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.29</version>
    </dependency>
</dependencies>  

2.查询数据库中t_user表的数据

public static void main(String[] args) throws SQLException, ClassNotFoundException {
    // 1.加载驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    // 2.获取连接对象
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "123456");
    // 3.获取执行sql语句的statement对象
    Statement statement = connection.createStatement();
    // 4.执行sql，并获取结果集
    ResultSet resultSet = statement.executeQuery("select  * from t_user");
    // 5.处理结果集
    while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String account = resultSet.getString("account");
        String password = resultSet.getString("password");
        String nickname = resultSet.getString("nickname");
        System.out.printf("id=%d,account=%s,password=%s,nickname=%s \n", id, account, password, nickname);
    }
    // 6.关闭资源
    resultSet.close();
    statement.close();
    connection.close();
}

控制台输出：

id=1,account=zhangsan,password=123456,nickname=张三 id=2,account=lisi,password=123,nickname=李四

相关的类和接口

JDBC 的 API 提供了以下接口和类：

**DriverManager：**驱动管理器，用于注册驱动和获取数据库连接对象等。

Driver : 驱动类，由数据库厂商提供，去操作对应的数据库。

Connection : 数据库连接对象。

Statement : 用于执行sql语句。

ResultSet : 在你使用语句对象执行 SQL 查询后，这些对象保存从数据获得的数据。它作为一个迭代器，让您可以通过它的数据来移动。

SQLException : 这个类处理发生在数据库应用程序的任何错误。

基本步骤

JDBC的一般会经历以下几个步骤：

1. 注册驱动
2. 获取连接
3. 编写sql语句
4. 执行sql语句并获取结果集
5. 解析结果集
6. 关闭资源

注册驱动

加载数据库厂商提供的驱动，从而操作数据库软件

注册驱动的方法：

1.通过DriverManager中的registerDriver()方法注册驱动

2.反射注册驱动（最常用）

// 1.通过DriverManager中的registerDriver()方法注册驱动
DriverManager.registerDriver(new Driver());
// 2.反射注册驱动（最常用）
Class.forName("com.mysql.cj.jdbc.Driver");

Driver类中的静态代码块已调用DriverManager.registerDriver(new Driver())进行注册，可通过创建Driver类的实例来注册驱动，可使用反射机制加载，Class.forName("com.mysql.cj.jdbc.Driver")

获取连接

DriverManager的静态方法getConnection获取与数据库的连接对象，getConnection方法有三个重载的方法，所有获取连接的方式有三种：

一个参数

在url中添加数据库用户和密码信息

// url的组成：jdbc:数据库软件名://ip地址:端口号/数据库名
public static Connection getConnection(String url)

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

两个参数

public static Connection getConnection(String url, java.util.Properties info)

// 获取连接
Properties info = new Properties();
info.put("user", "root");
info.put("password", "123456");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", info);

三个参数

public static Connection getConnection(String url, String user, String password)
    
// 获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "123456");

url的路径属性可选信息

serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=true

• MySQL 8.0.25以后自动识别时区，serverTimezone=Asia/Shanghai不用添加
• 8版本后，默认使用utf-8格式，useUnicode=true&characterEncoding=utf8&useSSL=true可以省略

Statement和PreparedStatement

执行SQL语句的对象有两种，Statement和PreparedStatement，PreparedStatement继承于Statement，statement用于执行静态的SQL语句（没有动态值），PreparedStatement执行预编译的SQL语句（有动态值）。

Statement

实现一个模拟登录，从控制台输入账户和密码，判断是否登录成功

public class UserLogin {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1.输入账户信息
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入账户：");
        String account = scanner.nextLine();
        System.out.println("请输入密码：");
        String password = scanner.nextLine();

        // 2.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 3.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "123456");
        // 4.获取执行sql语句的statement对象
        Statement statement = connection.createStatement();
        // 5.执行sql并获取结果集
        String sql = "select * from t_user where account = "  + "'" + account + "' and password = '" + password + "'";
        ResultSet resultSet = statement.executeQuery(sql);
        // 6.处理结果集
        if (resultSet.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        // 7.关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

存在的问题

• SQL语句需要使用字符串拼接，比较麻烦
• 只能拼接字符串类型，其他的数据类型无法处理
• SQL注入问题

PreparedStatement

prepareStatement执行预编译的sql语句，通过占位符赋值，解决了sql注入和字符串拼接繁琐问题。

public class UserLogin {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1.输入账户信息
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入账户：");
        String account = scanner.nextLine();
        System.out.println("请输入密码：");
        String password = scanner.nextLine();

        // 2.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 3.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "123456");
        // 4.获取执行sql语句的PreparedStatement对象
        String sql = "select * from t_user where account = ? and password = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 5.占位符赋值
        preparedStatement.setString(1, account);
        preparedStatement.setString(2, password);
        // 6.执行sql并获取结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        // 7.处理结果集
        if (resultSet.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        // 8.关闭资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

解析结果集

ResultSet封装了从数据库中查询的数据信息。ResultSet对象保持光标指向其当前数据行。最初，光标位于第一行之前。next方法将光标移动到下一行，因为当ResultSet对象中没有更多行时，它返回false，所以可以在while循环中使用它来遍历结果集。

// 4.执行sql，并获取结果集
ResultSet resultSet = statement.executeQuery("select * from t_user");
// 5.处理结果集
while (resultSet.next()) {
    int id = resultSet.getInt("id");
    String account = resultSet.getString("account");
    String password = resultSet.getString("password");
    String nickname = resultSet.getString("nickname");
    System.out.printf("id=%d,account=%s,password=%s,nickname=%s \n", id, account, password, nickname);
}

在上面的查询t_user表的数据时，ResultSet resultSet = preparedStatement.executeQuery()获取了数据库数据的结果集，此时ResultSet的游标在第一行数据之前。

第一次执行resultSet.next()下移游标，第一行有数据，返回true

第二次执行resultSet.next()下移游标，第二行有数据，返回true

第三次次执行resultSet.next()下移游标，此时游标在最后一行数据之后，没有数据，返回false

结果集的方法

方法名	描述
next()	将光标从当前位置向前移动一行。ResultSet光标最初位于第一行之前；接下来对方法的第一次调用使第一行成为当前行；第二次调用使第二行成为当前行，依此类推。当对next方法的调用返回false时，光标位于最后一行之后
getString(int columnIndex)	获取指定列的数据，第一列的值为1，第二列的值为2，...。返回String类型
getString(String columnLabel)	获取指定列名称的数据，列名称是由as指定的别名，没有别名则使用对应的列名。返回String类型
getInt(int columnIndex)	获取指定列的数据，第一列的值为1，第二列的值为2，...。返回Int类型
getInt(String columnLabel)	获取指定列名称的数据，列名称是由as指定的别名，没有别名则使用对应的列名。返回Int类型
getXXX(int columnIndex)	...
getXXX(String columnLabel)	...

Execute

PreparedStatement

/**
* 在此PreparedStatement对象中执行SQL查询，并返回查询生成的ResultSet对象。
*
* Returns: ResultSet对象，包含查询生成的数据；从不为空
*/
ResultSet executeQuery() throws SQLException;

/**
* 执行PreparedStatement对象的sql语句，这个sql语句必须是DML，例如insert,update,或者delete;
* 或者该sql是DDL，不返回任何东西
*
* Returns: DML返回影响的行数，DDL则返回0
*/
int executeUpdate() throws SQLException;

/**
* 执行此PreparedStatement对象中的SQL语句，该对象可以是任何类型的SQL语句。execute方法处理这些复杂语句以及executeQuery和executeUpdate方法处理的更简单形式的语句。execute方法返回一个布尔值，以指示第一个结果的形式。必须调用getResultSet或getUpdateCount方法来检索结果；必须调用getMoreResults才能移动到任何后续结果。

* Returns: 如果第一个结果是ResultSet对象，则为true；如果第一个结果是更新计数或没有结果，则为false
*
*/
boolean execute() throws SQLException;

CRUD示例

public class CRUDTest {
    @Test
    public void testInsert() throws Exception {
        // 1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "123456");
        // 3.预编译sql
        String sql = "insert into t_user(account, password, nickname) values(?, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 4.占位符赋值
        preparedStatement.setObject(1, "xiao ming");
        preparedStatement.setObject(2, "4321");
        preparedStatement.setObject(3, "小明");
        // 5.执行sql语句并获取影响的行数
        int row = preparedStatement.executeUpdate();
        // 6.处理结果
        System.out.println("影响的行数：" + row);
        // 7.关闭资源
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testUpdate() throws ClassNotFoundException, SQLException {
        // 1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "123456");
        // 预编译sql语句
        String sql = "update t_user set nickname = ? where nickname = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 占位符赋值
        preparedStatement.setObject(1, "小明2");
        preparedStatement.setObject(2, "小明");
        // 执行sql语句并获取影响的行数
        int row = preparedStatement.executeUpdate();
        // 处理结果
        System.out.println("影响的行数：" + row);
        // 关闭资源
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testDelete() throws ClassNotFoundException, SQLException {
        // 1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "123456");
        // 预编译sql语句
        String sql = "delete from t_user where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 占位符赋值
        preparedStatement.setObject(1, 3);
        // 执行sql语句并获取影响的行数
        int row = preparedStatement.executeUpdate();
        // 处理结果
        System.out.println("影响的行数：" + row);
        // 关闭资源
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testSelect() throws Exception {
        // 1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "123456");
        // 3.获取执行sql语句的statement对象
        PreparedStatement preparedStatement = connection.prepareStatement("select * from t_user");
        // 4.执行sql，并获取结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        // 5.处理结果集
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String account = resultSet.getString("account");
            String password = resultSet.getString("password");
            String nickname = resultSet.getString("nickname");
            System.out.printf("id=%d,account=%s,password=%s,nickname=%s \n", id, account, password, nickname);
        }
        // 6.关闭资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

配置文件读取连接信息

1. 在resources文件夹下创建jdbc.properties配置文件

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/jdbc
user=root
password=123456

2. 读取配置文件信息

public class JdbcConfiguration {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    static {
        InputStream inputStream = JdbcConfiguration.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(inputStream);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

3. 使用

@Test
public void testSelect() throws ClassNotFoundException, SQLException {
    // 注册驱动
    Class.forName(JdbcConfiguration.driver);
    // 获取连接
    Connection connection = DriverManager.getConnection(
        JdbcConfiguration.url,
        JdbcConfiguration.user,
        JdbcConfiguration.password);
    // 预编译sql语句
    String sql = "select * from t_user";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    System.out.println(preparedStatement.getMetaData().getColumnCount());
    // 获取ResultSet对象的列信息
    ResultSetMetaData metaData = preparedStatement.getMetaData();
    // 执行sql语句并获取结果集
    ResultSet resultSet = preparedStatement.executeQuery();
    // 处理结果
    while (resultSet.next()) {
        StringBuffer stringBuffer = new StringBuffer();
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            Object object = resultSet.getObject(metaData.getColumnLabel(i));
            stringBuffer.append(object).append(",");
        }
        System.out.println(stringBuffer);
    }
    // 关闭资源
    resultSet.close();
    preparedStatement.close();
    connection.close();
}

工具类

注册驱动、获取连接和释放资源部分代码是重复的，抽取这部分代码到工具类中，可简化开发

1. 在resources文件夹下创建jdbc.properties配置文件

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/jdbc
user=root
password=123456

2. 创建JDBC工具类

public class JDBCUtils {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    static {
        // 通过类加载器读取配置文件
        InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(inputStream);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            // 注册驱动
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接对象
     *
     * @return Connection
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 释放资源
     *
     * @param connection 连接对象
     * @param statement Statement或者PreparedStatement
     */
    public static void close(Connection connection, Statement statement) {
        try {
            if (Objects.nonNull(statement)) {
                statement.close();
            }
            if (Objects.nonNull(connection)) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 释放资源
     *
     * @param connection 连接对象
     * @param statement statement
     * @param resultSet 结果集
     */
    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (Objects.nonNull(resultSet)) {
                resultSet.close();
            }
            close(connection, statement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3. 使用示例

@Test
public void testSelect() throws SQLException {
    // 获取连接
    Connection connection = JDBCUtils.getConnection();
    // 执行sql语句
    PreparedStatement preparedStatement = connection.prepareStatement("select * from t_user");
    ResultSet resultSet = preparedStatement.executeQuery();
    // 解析结果集
    while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String account = resultSet.getString("account");
        String password = resultSet.getString("password");
        String nickname = resultSet.getString("nickname");
        System.out.printf("id=%d,account=%s,password=%s,nickname=%s \n", id, account, password, nickname);
    }
    // 关闭资源
    JDBCUtils.close(connection, preparedStatement, resultSet);
}

批量操作

**注意事项：**在url后添加rewriteBatchedStatements=true

@Test
public void testBatchInsert() throws Exception {
    // 1.注册驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    // 2.获取连接对象
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc?rewriteBatchedStatements=true", "root", "123456");
    // 3.预编译sql语句
    String sql = "insert into t_user(account, password, nickname) values (?, ?, ?)";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    // 4.占位符赋值
    for (int i = 0; i < 10000; i++) {
        preparedStatement.setObject(1, "batch03" + i);
        preparedStatement.setObject(2, "batch03" + i);
        preparedStatement.setObject(3, "batch03" + i);
        preparedStatement.addBatch();
    }
    // 5.执行批量操作
    preparedStatement.executeBatch();
    // 6.关闭资源
    preparedStatement.close();
    connection.close();
}

主键回显

在主键自增id的场景下，插入一条数据时，会自动生成一个id，如何获取这个id呢？

1. 在获取PrepareStatement对象时，设置自动生成主键的标记

connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)

2. 通过getGeneratedKeys方法获取主键结果集

// 获取自增的主键值
ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
if (generatedKeys.next()) {
    Object id = generatedKeys.getObject(1);
    System.out.println("自增的id：" + id);
}

示例

public void testReturnGeneratedKey() throws Exception {
    // 1.注册驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    // 2.获取连接对象
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "123456");
    // 3.预编译sql语句
    String sql = "insert into t_user(account, password, nickname) values (?, ?, ?);";
    PreparedStatement preparedStatement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
    // 4.占位符赋值
    preparedStatement.setObject(1, "test");
    preparedStatement.setObject(2, "123");
    preparedStatement.setObject(3, "testNick");
    // 5.执行sql语句并获取结果集
    int row = preparedStatement.executeUpdate();
    // 6.解析结果集
    if (row > 0) {
        // 获取自增的主键值
        ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
        if (generatedKeys.next()) {
            Object id = generatedKeys.getObject(1);
            System.out.println("自增的id：" + id);
        }
        System.out.println("插入成功");
    } else {
        System.out.println("插入失败");
    }
    // 7.关闭资源
    preparedStatement.close();
    connection.close();
}

事务

类型

• 自动提交：每条语句自动存储一个事务中，执行成功自动提交，执行失败自动回滚。（MySQL）

• 手动提交：手动开启事务，添加语句，手动提交或者手动回滚即可。

try {
    // 关闭自动提交
    connection.setAutoCommit(false);
    ...执行sql和处理结果的步骤
    // 提交事务
    connection.commit();
} catch (Exception e) {
    // 出错后回滚
    connection.rollback(); 
}

Druid连接池

1. 在pom.xml文件中添加druid

<dependencies>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.29</version>
    </dependency>

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.18</version>
    </dependency>
    ...
</dependencies>

2. 在resources文件夹下创建druid.properties配置文件

# 必要参数
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc
username=root
password=123456

# 非必要参数
initialSize=5
maxActive=8

3. 获取连接和回收连接

@Test
public void druidSoft() throws Exception {
    // 读取外部配置文件
    InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("druid.properties");
    Properties properties = new Properties();
    properties.load(inputStream);


    // 使用连接池的工具类的工厂创建连接池
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();

    // 其他步骤

    // 回收连接
    connection.close();
}