JDBC数据库连接与操作：CRUD操作示例

原创系统管理员 2024-09-13 08:54 125阅读 0赞

JDBC（Java Database Connectivity）是Java中用于访问关系型数据库的标准API。以下是使用JDBC进行CRUD（创建、读取、更新和删除）操作的示例：

1. 创建（Create）
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class CreateRecord {
    public static void main(String[] args) {
        // 1. 获取数据库连接
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        try (Connection connection = DriverManager.getConnection(url)) {
            // 2. 创建Statement对象，执行SQL
            Statement stmt = connection.createStatement();
            String sql = "INSERT INTO table_name (column1, column2)) VALUES ('value1', 'value2')"; // 假设你有一个名为'table_name'的表

int rowsInserted = stmt.executeUpdate(sql);
            if (rowsInserted > 0) {
                System.out.println("Record created successfully!");
            } else {
                System.out.println("Failed to create record.");
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Connection error occurred.");
        }
    }
}
```

2. 读取（Read）
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ReadRecord {
    public static void main(String[] args) {
        // 1. 获取数据库连接
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        try (Connection connection = DriverManager.getConnection(url)) {
            // 2. 创建Statement对象，执行SQL
            Statement stmt = connection.createStatement();
            String sql = "SELECT * FROM table_name WHERE id = 1"; // 假设你有一个名为'table_name'的表，并且你知道一个记录的id

ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()) {
                System.out.println("Record found:");
                // 遍历结果集，打印字段值
                for (String field : rs.getColumns()) {
                    System.out.print(field + " : " + rs.getString(field) + "\t");
                }
            } else {
                System.out.println("Record not found.");
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Connection error occurred.");
        }
    }
}
```

3. 更新（Update）
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class UpdateRecord {
    public static void main(String[] args) {
        // 1. 获取数据库连接
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        try (Connection connection = DriverManager.getConnection(url)) {
            // 2. 创建Statement对象，执行SQL
            Statement stmt = connection.createStatement();
            String sql = "UPDATE table_name SET column1 = 'new_value1', column2 = 'new_value2' WHERE id = 1"; // 假设你有一个名为'table_name'的表，并且你知道一个记录的id

int rowsUpdated = stmt.executeUpdate(sql);
            if (rowsUpdated > 0) {
                System.out.println("Record updated successfully!");
            } else {
                System.out.println("Failed to update record.");
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Connection error occurred.");
        }
    }
}
```

4. 删除（Delete）
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class DeleteRecord {
    public static void main(String[] args) {
        // 1. 获取数据库连接
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        try (Connection connection = DriverManager.getConnection(url)) {
            // 2. 创建Statement对象，执行SQL
            Statement stmt = connection.createStatement();
            String sql = "DELETE FROM table_name WHERE id = 1"; // 假设你有一个名为'table_name'的表，并且你知道一个记录的id

int rowsDeleted = stmt.executeUpdate(sql);
            if (rowsDeleted > 0) {
                System.out.println("Record deleted successfully!");
            } else {
                System.out.println("Failed to delete record.");
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Connection error occurred.");
        }
    }
}
```
以上就是使用JDBC进行CRUD操作的基本示例。在实际应用中，你需要根据数据库的表结构和你的具体需求来编写SQL语句。