在 JDBC 中执行 SQL 语句的常见步骤涉及创建数据库连接、准备 SQL 语句、执行语句以及处理结果。以下是几种常见的 JDBC 操作示例:
1. 查询操作(SELECT
语句)
执行查询并处理结果集:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcQueryExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/your_database";
String username = "your_username";
String password = "your_password";
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 1. 获取数据库连接
connection = DriverManager.getConnection(url, username, password);
// 2. 创建 SQL 查询语句
String sql = "SELECT id, name, email FROM users WHERE age > ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 18); // 设置参数值
// 3. 执行查询
resultSet = preparedStatement.executeQuery();
// 4. 处理结果集
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String email = resultSet.getString("email");
System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 5. 关闭资源
try {
if (resultSet != null) resultSet.close();
if (preparedStatement != null) preparedStatement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2. 插入操作(INSERT
语句)
向表中插入新记录:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JdbcInsertExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/your_database";
String username = "your_username";
String password = "your_password";
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
// 1. 获取数据库连接
connection = DriverManager.getConnection(url, username, password);
// 2. 创建 SQL 插入语句
String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "John Doe");
preparedStatement.setString(2, "john.doe@example.com");
preparedStatement.setInt(3, 30);
// 3. 执行插入操作
int rowsAffected = preparedStatement.executeUpdate();
System.out.println("Rows affected: " + rowsAffected);
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 4. 关闭资源
try {
if (preparedStatement != null) preparedStatement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3. 更新操作(UPDATE
语句)
更新表中的记录:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JdbcUpdateExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/your_database";
String username = "your_username";
String password = "your_password";
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
// 1. 获取数据库连接
connection = DriverManager.getConnection(url, username, password);
// 2. 创建 SQL 更新语句
String sql = "UPDATE users SET email = ? WHERE id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "new.email@example.com");
preparedStatement.setInt(2, 1);
// 3. 执行更新操作
int rowsAffected = preparedStatement.executeUpdate();
System.out.println("Rows affected: " + rowsAffected);
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 4. 关闭资源
try {
if (preparedStatement != null) preparedStatement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
4. 删除操作(DELETE
语句)
从表中删除记录:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JdbcDeleteExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/your_database";
String username = "your_username";
String password = "your_password";
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
// 1. 获取数据库连接
connection = DriverManager.getConnection(url, username, password);
// 2. 创建 SQL 删除语句
String sql = "DELETE FROM users WHERE id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1); // 删除 ID 为 1 的记录
// 3. 执行删除操作
int rowsAffected = preparedStatement.executeUpdate();
System.out.println("Rows affected: " + rowsAffected);
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 4. 关闭资源
try {
if (preparedStatement != null) preparedStatement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
总结
- 查询操作: 使用
executeQuery
执行SELECT
语句,获取ResultSet
并处理结果。 - 插入操作: 使用
executeUpdate
执行INSERT
语句,检查受影响的行数。 - 更新操作: 使用
executeUpdate
执行UPDATE
语句,检查受影响的行数。 - 删除操作: 使用
executeUpdate
执行DELETE
语句,检查受影响的行数。