MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

Java使用JDBC执行SQL语句

2024-02-012.8k 阅读

Java使用JDBC执行SQL语句

JDBC基础概述

Java Database Connectivity（JDBC）是Java编程语言用于执行SQL语句的应用程序编程接口（API）。它为Java程序员提供了一个标准的、通用的方法来访问各种关系数据库，如MySQL、Oracle、SQL Server等。通过JDBC，Java程序可以与数据库进行交互，包括连接数据库、执行SQL语句、处理结果集等操作。

JDBC API主要由一组接口和类组成，这些接口和类位于java.sql和javax.sql包中。其中一些关键的接口和类包括：

  • DriverManager：这个类管理一组JDBC驱动程序，负责建立与数据库的连接。它跟踪可用的驱动程序，并根据给定的数据库URL选择合适的驱动程序来建立连接。
  • Connection：代表与特定数据库的连接。通过这个接口，我们可以创建用于执行SQL语句的Statement对象，并且还可以控制事务等数据库相关的操作。
  • Statement：用于执行静态SQL语句并返回它所生成结果的对象。有三种类型的Statement：Statement、PreparedStatement和CallableStatement。Statement用于执行简单的SQL语句；PreparedStatement用于执行带参数的SQL语句，并且可以防止SQL注入攻击；CallableStatement用于执行数据库存储过程。
  • ResultSet：表示数据库查询结果的表，通常通过执行Statement或PreparedStatement返回。它提供了一系列方法来逐行访问查询结果，并获取每列的数据。

加载JDBC驱动程序

在使用JDBC连接数据库之前，首先需要加载相应的数据库驱动程序。不同的数据库有各自对应的JDBC驱动程序。例如，对于MySQL数据库，需要加载MySQL Connector/J驱动；对于Oracle数据库，需要加载Oracle JDBC驱动。

加载驱动程序的方式有两种，一种是使用Class.forName()方法，另一种是通过DriverManager.registerDriver()方法。以MySQL为例，使用Class.forName()加载驱动程序的代码如下：

try {
    Class.forName("com.mysql.cj.jdbc.Driver");
    System.out.println("MySQL JDBC Driver Registered!");
} catch (ClassNotFoundException e) {
    System.out.println("Could not register JDBC driver");
    e.printStackTrace();
}

在上述代码中，com.mysql.cj.jdbc.Driver是MySQL 8.0及以上版本的JDBC驱动类名。如果是MySQL 5.x版本，驱动类名是com.mysql.jdbc.Driver。通过Class.forName()方法，Java会在类路径中查找并加载指定的驱动类。当驱动类被加载时，它会自动向DriverManager注册自己。

另一种加载驱动的方式DriverManager.registerDriver()如下：

try {
    Driver driver = new com.mysql.cj.jdbc.Driver();
    DriverManager.registerDriver(driver);
    System.out.println("MySQL JDBC Driver Registered!");
} catch (SQLException e) {
    System.out.println("Could not register JDBC driver");
    e.printStackTrace();
}

这里先创建了驱动类的实例，然后通过DriverManager.registerDriver()方法将驱动注册到DriverManager中。不过，从JDBC 4.0开始，大多数驱动程序都支持自动加载，所以通常情况下，不需要显式地调用DriverManager.registerDriver()或Class.forName()，只要驱动程序的JAR包在类路径中，Java会自动加载驱动。

建立数据库连接

加载驱动程序后，接下来要建立与数据库的连接。DriverManager类的getConnection()方法用于建立连接，该方法有多个重载版本，常用的形式如下：

Connection connection = DriverManager.getConnection(url, username, password);

其中，url是数据库的连接字符串，它包含了数据库的地址、端口号、数据库名称等信息；username是数据库的用户名；password是用户对应的密码。

以MySQL为例，连接字符串的格式通常为：jdbc:mysql://host:port/databaseName。如果需要指定一些连接参数，如字符编码、时区等，可以在连接字符串后面添加参数，例如：jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC。

下面是一个完整的建立MySQL数据库连接的代码示例：

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

public class DatabaseConnection {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected to the database!");
        } catch (SQLException e) {
            System.out.println("Connection failed!");
            e.printStackTrace();
        }
    }
}

在上述代码中，使用try-with-resources语句来管理Connection对象，这样在代码块结束时，会自动关闭连接，避免资源泄漏。如果连接成功，会输出“Connected to the database!”；如果连接失败，会打印异常信息。

使用Statement执行SQL语句

建立连接后，就可以创建Statement对象来执行SQL语句了。Statement对象主要用于执行不带参数的静态SQL语句。

执行查询语句（SELECT）

执行查询语句会返回一个ResultSet对象，该对象包含了查询结果集。下面是一个执行简单SELECT语句的示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class SelectExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SELECT id, name, age FROM users")) {

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在上述代码中，首先创建了Statement对象，然后通过executeQuery()方法执行SELECT语句，返回ResultSet对象。接着，使用while (resultSet.next())循环逐行遍历结果集，通过resultSet.getInt("id")、resultSet.getString("name")和resultSet.getInt("age")方法获取每列的值，并打印出来。

执行更新语句（INSERT、UPDATE、DELETE）

执行更新语句（如INSERT、UPDATE、DELETE）不会返回ResultSet，而是返回一个表示受影响行数的整数。下面是一个执行INSERT语句的示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;

public class InsertExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password);
             Statement statement = connection.createStatement()) {

            int rowsInserted = statement.executeUpdate("INSERT INTO users (name, age) VALUES ('John Doe', 30)");
            if (rowsInserted > 0) {
                System.out.println("A new user was inserted successfully!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在上述代码中，executeUpdate()方法执行INSERT语句，并返回受影响的行数。如果返回值大于0，表示插入操作成功。同样，UPDATE和DELETE语句也可以使用executeUpdate()方法执行。

使用PreparedStatement执行SQL语句

PreparedStatement是Statement的子接口，它用于执行预编译的SQL语句，并且可以接受参数。与Statement相比，PreparedStatement有以下优点：

  • 安全性：PreparedStatement可以防止SQL注入攻击。当使用Statement执行带参数的SQL语句时，如果参数值来自用户输入且没有正确处理，可能会导致SQL注入漏洞。而PreparedStatement会将参数值作为普通数据处理，不会与SQL语句的结构混淆。
  • 性能：PreparedStatement会预编译SQL语句，在多次执行相同结构但参数不同的SQL语句时，预编译的语句可以重复使用，从而提高性能。

执行查询语句（SELECT）

下面是一个使用PreparedStatement执行SELECT语句的示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class PreparedSelectExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            String sql = "SELECT id, name, age FROM users WHERE age >?";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.setInt(1, 25);

                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    while (resultSet.next()) {
                        int id = resultSet.getInt("id");
                        String name = resultSet.getString("name");
                        int age = resultSet.getInt("age");
                        System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在上述代码中，sql字符串中使用?作为参数占位符。通过preparedStatement.setInt(1, 25)方法将参数值设置为25，其中1表示参数的索引位置（从1开始）。然后执行executeQuery()方法获取结果集并处理。

执行更新语句（INSERT、UPDATE、DELETE）

下面是一个使用PreparedStatement执行UPDATE语句的示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PreparedUpdateExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            String sql = "UPDATE users SET age =? WHERE name =?";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.setInt(1, 35);
                preparedStatement.setString(2, "John Doe");

                int rowsUpdated = preparedStatement.executeUpdate();
                if (rowsUpdated > 0) {
                    System.out.println("User's age was updated successfully!");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在上述代码中，UPDATE语句中的?占位符分别通过preparedStatement.setInt(1, 35)和preparedStatement.setString(2, "John Doe")方法设置参数值。executeUpdate()方法执行UPDATE语句并返回受影响的行数。

使用CallableStatement执行存储过程

CallableStatement用于执行数据库中的存储过程。存储过程是一组预编译的SQL语句，存储在数据库中，可以接受输入参数、输出参数或同时接受两者。

假设在MySQL数据库中有一个名为get_user_by_id的存储过程，定义如下：

DELIMITER //
CREATE PROCEDURE get_user_by_id(IN user_id INT, OUT user_name VARCHAR(255), OUT user_age INT)
BEGIN
    SELECT name, age INTO user_name, user_age FROM users WHERE id = user_id;
END //
DELIMITER ;

下面是在Java中使用CallableStatement调用这个存储过程的示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.SQLException;

public class CallableStatementExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            String sql = "{CALL get_user_by_id(?,?,?)}";
            try (CallableStatement callableStatement = connection.prepareCall(sql)) {
                callableStatement.setInt(1, 1);
                callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
                callableStatement.registerOutParameter(3, java.sql.Types.INTEGER);

                callableStatement.execute();

                String name = callableStatement.getString(2);
                int age = callableStatement.getInt(3);
                System.out.println("Name: " + name + ", Age: " + age);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在上述代码中，sql字符串使用{CALL procedure_name(?,?,?)}的格式来调用存储过程，其中?是参数占位符。通过callableStatement.setInt(1, 1)设置输入参数值为1。通过callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR)和callableStatement.registerOutParameter(3, java.sql.Types.INTEGER)分别注册输出参数的类型。执行callableStatement.execute()方法调用存储过程后，通过callableStatement.getString(2)和callableStatement.getInt(3)获取输出参数的值。

处理事务

事务是一组数据库操作的逻辑单元，这些操作要么全部成功执行，要么全部不执行。JDBC提供了对事务的支持，通过Connection对象的方法来控制事务。

开启事务

默认情况下，JDBC的连接是自动提交模式，即每个SQL语句执行后都会立即提交到数据库。要开启事务，需要将自动提交模式设置为false，如下所示：

connection.setAutoCommit(false);

提交事务

当所有的数据库操作都成功完成后，需要提交事务，将所有的更改持久化到数据库中，使用commit()方法：

connection.commit();

回滚事务

如果在事务执行过程中发生错误，需要回滚事务，撤销所有已经执行的操作，使用rollback()方法：

connection.rollback();

下面是一个包含事务处理的示例，假设要向两个表中插入数据，只有当两个插入操作都成功时，事务才提交：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TransactionExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            connection.setAutoCommit(false);

            String insertSql1 = "INSERT INTO table1 (column1, column2) VALUES (?,?)";
            try (PreparedStatement preparedStatement1 = connection.prepareStatement(insertSql1)) {
                preparedStatement1.setString(1, "value1");
                preparedStatement1.setString(2, "value2");
                preparedStatement1.executeUpdate();
            }

            String insertSql2 = "INSERT INTO table2 (column3, column4) VALUES (?,?)";
            try (PreparedStatement preparedStatement2 = connection.prepareStatement(insertSql2)) {
                preparedStatement2.setString(1, "value3");
                preparedStatement2.setString(2, "value4");
                preparedStatement2.executeUpdate();
            }

            connection.commit();
            System.out.println("Transaction committed successfully!");
        } catch (SQLException e) {
            try {
                if (connection != null) {
                    connection.rollback();
                    System.out.println("Transaction rolled back due to an error!");
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
    }
}

在上述代码中，首先将自动提交模式设置为false开启事务。然后执行两个INSERT语句，如果这两个语句都执行成功，就调用commit()方法提交事务；如果在执行过程中发生异常，就调用rollback()方法回滚事务。

处理ResultSet

ResultSet对象包含了数据库查询的结果集，它提供了多种方法来获取结果集中的数据。

遍历结果集

如前面示例中所示，通常使用while (resultSet.next())循环来逐行遍历结果集。next()方法将光标移动到下一行，如果结果集中还有下一行数据，则返回true，否则返回false。

获取列值

ResultSet提供了一系列getXxx()方法来获取不同类型的列值，其中Xxx表示数据类型，如getInt()、getString()、getDouble()等。获取列值时，可以通过列名或列索引来指定要获取的列。例如：

int id = resultSet.getInt("id");
String name = resultSet.getString(2); // 2表示第二列

结果集的光标移动

除了next()方法，ResultSet还提供了其他方法来控制光标的移动，如：

  • previous()：将光标移动到前一行。
  • first()：将光标移动到第一行。
  • last()：将光标移动到最后一行。
  • absolute(int row)：将光标移动到指定的行号。

结果集的类型和并发模式

ResultSet有三种类型：

  • TYPE_FORWARD_ONLY：默认类型，光标只能向前移动。
  • TYPE_SCROLL_INSENSITIVE：光标可以前后移动，并且结果集对其他事务对数据库的更改不敏感。
  • TYPE_SCROLL_SENSITIVE：光标可以前后移动，并且结果集对其他事务对数据库的更改敏感。

ResultSet还有两种并发模式：

  • CONCUR_READ_ONLY：结果集只能读取，不能更新。
  • CONCUR_UPDATABLE：结果集可以更新，并且对结果集的更新会反映到数据库中。

在创建Statement或PreparedStatement时，可以指定结果集的类型和并发模式，例如：

Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

JDBC连接池

在实际应用中，频繁地创建和销毁数据库连接会消耗大量的系统资源，影响应用程序的性能。为了解决这个问题，可以使用JDBC连接池。连接池是一个预先创建好的数据库连接的集合，应用程序需要连接数据库时，从连接池中获取一个连接，使用完毕后再将连接归还到连接池中。

常见的JDBC连接池有：

  • HikariCP：一个高性能的JDBC连接池，具有快速的连接获取速度和低资源消耗。
  • C3P0：一个开源的JDBC连接池，支持自动回收空闲连接等功能。
  • Tomcat JDBC Pool：Tomcat服务器自带的JDBC连接池，性能较好，并且与Tomcat服务器集成度高。

下面以HikariCP为例，展示如何使用连接池：

首先，在项目的pom.xml文件中添加HikariCP的依赖：

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>4.0.3</version>
</dependency>

然后，使用HikariCP获取数据库连接的示例代码如下：

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class HikariCPExample {
    private static HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC");
        config.setUsername("root");
        config.setPassword("password");
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

        dataSource = new HikariDataSource(config);
    }

    public static void main(String[] args) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement("SELECT id, name FROM users");
             ResultSet resultSet = preparedStatement.executeQuery()) {

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("ID: " + id + ", Name: " + name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在上述代码中，首先通过HikariConfig配置连接池的参数，如数据库URL、用户名、密码等。然后创建HikariDataSource对象，该对象负责管理连接池。在main方法中，通过dataSource.getConnection()方法从连接池中获取连接，使用完毕后，try-with-resources语句会自动将连接归还到连接池中。

总结

通过以上内容，我们详细介绍了Java使用JDBC执行SQL语句的各个方面，包括加载驱动、建立连接、使用不同类型的Statement执行SQL语句、处理事务、处理ResultSet以及使用JDBC连接池等。JDBC是Java与关系数据库交互的重要工具，掌握这些知识对于开发高效、安全的数据库应用程序至关重要。在实际开发中，应根据具体的需求选择合适的方式来执行SQL语句，并合理管理数据库连接，以提高应用程序的性能和稳定性。