预编译语句是一种用于执行参数化SQL查询的技术,它可以提高性能并减少SQL注入的风险。预编译语句主要有以下优势:
在Java中,使用java.sql.PreparedStatement
接口实现预编译语句。以下是几个示例,展示了如何使用预编译语句进行各种数据库操作。
7.3.1. 插入数据
以下示例展示了如何使用预编译语句插入数据:
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class PreparedStatementInsertExample { public static void main(String[] args) { try { Class.forName("com.mysql.cj.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true"; String username = "root"; String password = "mypassword"; Connection connection = DriverManager.getConnection(url, username, password); String sql = "INSERT INTO users (name, age) VALUES (?, ?)"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, "User 7"); preparedStatement.setInt(2, 30); preparedStatement.executeUpdate(); preparedStatement.close(); connection.close(); } catch (Exception e) { e.printStackTrace(); } } }
7.3.2. 查询数据
以下示例展示了如何使用预编译语句查询数据:
import java.sql.*; public class PreparedStatementSelectExample { public static void main(String[] args) { try { Class.forName("com.mysql.cj.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true"; String username = "root"; String password = "mypassword"; Connection connection = DriverManager.getConnection(url, username, password); String sql = "SELECT * FROM users WHERE age > ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, 30); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { System.out.println("ID: " + resultSet.getInt("id") + ", Name: " + resultSet.getString("name") + ", Age: " + resultSet.getInt("age")); } resultSet.close(); preparedStatement.close(); connection.close(); } catch (Exception e) { e.printStackTrace(); } } }
7.3.3. 更新数据
以下示例展示了如何使用预编译语句更新数据:
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class PreparedStatementUpdateExample { public static void main(String[] args) { try { Class.forName("com.mysql.cj.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true"; String username = "root"; String password = "mypassword"; Connection connection = DriverManager.getConnection(url, username, password); String sql = "UPDATE users SET age = ? WHERE name = ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, 31); preparedStatement.setString(2, "User 7"); preparedStatement.executeUpdate(); preparedStatement.close(); connection.close(); } catch (Exception e) { e.printStackTrace(); } } }
7.3.4. 删除数据
以下示例展示了如何使用预编译语句删除数据:
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class PreparedStatementDeleteExample { public static void main(String[] args) { try { Class.forName("com.mysql.cj.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true"; String username = "root"; String password = "mypassword"; Connection connection = DriverManager.getConnection(url, username, password); String sql = "DELETE FROMusers WHERE age > ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, 60); preparedStatement.executeUpdate(); preparedStatement.close(); connection.close(); } catch (Exception e) { e.printStackTrace(); } } }
通过这些示例,你应该对如何使用预编译语句有了更清晰的了解。预编译语句使得你能够在查询中使用参数,提高了性能并减少了SQL注入的风险。在实际项目中,尽量使用预编译语句来执行SQL查询。