Java语言实现对MySql数据库中数据的增删改查操作的代码

下面是Java语言实现对MySql数据库中数据的增删改查操作的完整攻略。这里使用JDBC API来操作数据库。

步骤

步骤一：导入JDBC API和JDBC驱动包

在项目中引入JDBC API 和 MySQL Connector/J驱动包，这里以Maven为例，在pom.xml中添加如下依赖：

<!-- JDBC API -->
<dependency>
    <groupId>javax.sql</groupId>
    <artifactId>javax.sql-api</artifactId>
    <version>2.0.0</version>
</dependency>

<!-- MySQL Connector/J -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.27</version>
</dependency>

步骤二：创建数据库连接

使用JDBC连接MySQL先建立数据库连接，需要提供数据库的JDBC URL、数据库用户名和密码。示例代码如下：

String jdbcUrl = "jdbc:mysql://localhost:3306/db_name";
String username = "root";
String password = "password";
Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

步骤三：执行SQL语句

增

使用Statement对象执行INSERT语句，示例代码如下：

Statement statement = connection.createStatement();
String sql = "INSERT INTO users (name, age) VALUES ('Tom', 20)";
int rows = statement.executeUpdate(sql);
System.out.println("插入成功，影响行数：" + rows);

删

使用PreparedStatement对象执行DELETE语句，示例代码如下：

String sql = "DELETE FROM users WHERE id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
int rows = preparedStatement.executeUpdate();
System.out.println("删除成功，影响行数：" + rows);

改

使用PreparedStatement对象执行UPDATE语句，示例代码如下：

String sql = "UPDATE users SET age = ? WHERE name = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 30);
preparedStatement.setString(2, "Tom");
int rows = preparedStatement.executeUpdate();
System.out.println("更新成功，影响行数：" + rows);

查

使用Statement对象执行SELECT语句，示例代码如下：

Statement statement = connection.createStatement();
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
    System.out.println(
            resultSet.getInt("id")
            + " " + resultSet.getString("name")
            + " " + resultSet.getInt("age"));
}

步骤四：关闭连接

我们需要在使用完数据库后关闭连接。

if (resultSet != null) {
    resultSet.close();
}
if (statement != null) {
    statement.close();
}
if (connection != null) {
    connection.close();
}

示例

下面是一个完整的示例代码，实现了用户信息的增删改查操作。

import java.sql.*;

public class DatabaseDemo {

    public static void main(String[] args) throws SQLException {
        String jdbcUrl = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "123456";
        Connection connection = null;
        Statement statement = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            // 创建数据库连接
            connection = DriverManager.getConnection(jdbcUrl, username, password);

            // 插入数据
            statement = connection.createStatement();
            String sql = "INSERT INTO users (name, age) VALUES ('Tom', 20)";
            int rows = statement.executeUpdate(sql);
            System.out.println("插入成功，影响行数：" + rows);

            // 删除数据
            sql = "DELETE FROM users WHERE id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            rows = preparedStatement.executeUpdate();
            System.out.println("删除成功，影响行数：" + rows);

            // 更新数据
            sql = "UPDATE users SET age = ? WHERE name = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 30);
            preparedStatement.setString(2, "Tom");
            rows = preparedStatement.executeUpdate();
            System.out.println("更新成功，影响行数：" + rows);

            // 查询数据
            statement = connection.createStatement();
            sql = "SELECT * FROM users";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println(
                        resultSet.getInt("id")
                        + " " + resultSet.getString("name")
                        + " " + resultSet.getInt("age"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭连接
            if (resultSet != null) {
                resultSet.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }
}