java - 在准备好的语句中获取列名而不是值

Question

我在准备好的语句中获取列名称而不是值。

这是我的代码:

string q="select ? from EMPLOYEE where salary > ?";

Preparedstatement pst = connectionobject.preparedstatement(q);
pst.setstring(1, "FIRST_NAME");
pst.setint(2, 10000);

当我在 JTable 中打印结果时，它在所有行中显示 FIRST_NAME。

Answer 1

这是不可能的，用你的方式来解决你的问题。

像这样更改您的代码:

String att = "FIRST_NAME";

string q="select " + att + " from EMPLOYEE where salary>?";

Preparedstatement pst=connectionobject.preparedstatement(q);
pst.setint(1,10000); 

如果您担心用户可以进行 SQL 注入(inject)，而不是使用此解决方案

您应该检查您的列是否存在于表中:

SELECT * 
    FROM information_schema.COLUMNS 
    WHERE 
        TABLE_SCHEMA = 'db_name' 
    AND TABLE_NAME = 'table_name' 
    AND COLUMN_NAME = 'column_name'

像这样:

public boolean column_exist(String att) {
    boolean succes = false;
    CreerConnection con = new CreerConnection();
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultat = null;
    try {
        connection = con.getConnection();

        statement = connection.prepareStatement("SELECT * \n"
                + "FROM information_schema.COLUMNS "
                + " WHERE"
                + " TABLE_SCHEMA = 'db_name'"
                + " AND TABLE_NAME = 'table_name'"
                + " AND COLUMN_NAME = ?");
        statement.setString(1, att);
        resultat = statement.executeQuery();

        if (resultat.next()) {
            succes = true;
        }

    } catch (SQLException e) {
        System.out.println("Exception = " + e);
    } finally {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException ex) {
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException ex) {
            }
        }
    }
    return succes;
}

如果该列存在，那么您可以像这样继续:

if(column_exist(att)){
   string q="select " + att + " from EMPLOYEE where salary>?";

   Preparedstatement pst=connectionobject.preparedstatement(q);
   pst.setint(1,10000); 
}

在这里了解更多:

MySQL, Check if a column exists in a table with SQL

希望这可以帮助你。