面试题：Java JDBC元数据在动态SQL构建中的应用

确保SQL语句正确性和安全性的方法
- 使用数据库元数据检查表和列的存在：
  - 通过JDBC的DatabaseMetaData接口，可以获取数据库中所有表和列的信息。在构建SQL之前，先检查用户选择的表和列是否确实存在于数据库中，这样能确保SQL语句在语法上是正确的。
  - 例如，使用DatabaseMetaData.getTables方法检查表是否存在，使用DatabaseMetaData.getColumns方法检查列是否存在。
- 使用预编译语句确保安全性：
  - 预编译语句（PreparedStatement）会对SQL语句进行预编译，将参数和SQL语句本身分离。这样可以防止SQL注入攻击，因为用户输入的数据不会直接嵌入到SQL语句中，而是作为参数传递。
关键代码片段示例

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DynamicQueryBuilder {
    private Connection connection;

    public DynamicQueryBuilder(Connection connection) {
        this.connection = connection;
    }

    public boolean isValidTable(String tableName) {
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables(null, null, tableName, null);
            return tables.next();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean isValidColumn(String tableName, String columnName) {
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet columns = metaData.getColumns(null, null, tableName, columnName);
            return columns.next();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public ResultSet executeDynamicQuery(String tableName, String[] columnNames, String condition) {
        if (!isValidTable(tableName)) {
            throw new IllegalArgumentException("Invalid table name: " + tableName);
        }
        for (String columnName : columnNames) {
            if (!isValidColumn(tableName, columnName)) {
                throw new IllegalArgumentException("Invalid column name: " + columnName);
            }
        }

        StringBuilder sql = new StringBuilder("SELECT ");
        for (int i = 0; i < columnNames.length; i++) {
            sql.append(columnNames[i]);
            if (i < columnNames.length - 1) {
                sql.append(", ");
            }
        }
        sql.append(" FROM ").append(tableName);
        if (condition != null &&!condition.isEmpty()) {
            sql.append(" WHERE ").append(condition);
        }

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql.toString());
            return preparedStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}

使用示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/yourdatabase";
        String username = "yourusername";
        String password = "yourpassword";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            DynamicQueryBuilder queryBuilder = new DynamicQueryBuilder(connection);
            String[] columnNames = {"column1", "column2"};
            String tableName = "your_table";
            String condition = "column1 =?";
            ResultSet resultSet = queryBuilder.executeDynamicQuery(tableName, columnNames, condition);
            while (resultSet.next()) {
                // 处理查询结果
                System.out.println(resultSet.getString("column1") + " " + resultSet.getString("column2"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

面试题：Java JDBC元数据在动态SQL构建中的应用

知识考点

面试题答案