关于人工智能:73-预编译语句

预编译语句是一种用于执行参数化 SQL 查问的技术，它能够进步性能并缩小 SQL 注入的危险。预编译语句次要有以下劣势：

1. 防止 SQL 注入攻打。
2. 进步性能，因为预编译语句只编译一次，而后能够屡次执行。

在 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 查问。
举荐浏览：

https://mp.weixin.qq.com/s/dV2JzXfgjDdCmWRmE0glDA

https://mp.weixin.qq.com/s/an83QZOWXHqll3SGPYTL5g