java - Java dynamically generate SQL query - ATHENA

Question

I am trying to generate sql query based on user input. There are 4 search fields on the UI: FIRST_NAME, LAST_NAME, SUBJECT, MARKS

Based on user input I am planning to generate SQL query. Input can be of any combination.

eg: select * from TABLE where FIRST_NAME="some_value";

This query needs to be generated when FIRST_NAME is given and other fields are null

select * from TABLE where FIRST_NAME="some_value" and LAST_NAME="some_value";

This query needs to be generated when FIRST_NAME and LAST_NAME are given and other fields are null

Since there are 4 input fields, number of possible queries that can be generated are 24 (factorial of 4).

One idea is to write if condition for all 24 cases.

Java pseudo code:

String QUERY = "select * from TABLE where ";

if (FIRST_NAME!=null) {

    QUERY = QUERY + "FIRST_NAME='use_input_value';"

}
if (LAST_NAME!=null) {

    QUERY = QUERY + "LAST_NAME='use_input_value';"

}
if (SUBJECT!=null) {

    QUERY = QUERY + "SUBJECT='use_input_value';"

}
if (MARKS!=null) {

    QUERY = QUERY + "MARKS='use_input_value';"

}

I am not able to figure out how to generate SQL queries with AND coditions for multiple Input values. I have been through concepts on dynamically generate sql query but couldn't process further.

Can someone help me on this.

FYI: I have been through How to dynamically generate SQL query based on user's selections?, still not able to generate query string based on user input.

标签： javasql

Answer 1

让我们考虑一下如果您只运行您编写的代码并且两者都提供了会发生FIRST_NAME什么LAST_NAME。你会得到这个：

select * from TABLE where FIRST_NAME='use_input_value';LAST_NAME='use_input_value';

这里有两个问题：

1. 查询在语法上不正确。
2. 它包含文字'use_input_value'而不是您想要的值。

为了解决第一个问题，让我们首先and在每个表达式的开头添加，并删除分号，如下所示：

String QUERY = "select * from TABLE where";
if (FIRST_NAME!=null) {
    QUERY = QUERY + " and FIRST_NAME='use_input_value'";
}

注意前面的空格and。我们也可以去掉后面的空格where。

现在，带有 FIRST_NAME 和 LAST_NAME 的查询将如下所示：

select * from TABLE where and FIRST_NAME='use_input_value' and LAST_NAME='use_input_value'

更好，但现在有一个额外的and. 我们可以通过在查询开始时添加一个虚拟的始终为真条件来解决这个问题：

String QUERY = "select * from TABLE where 1=1";

然后我们在评估完所有条件后附加一个分号，并且我们有一个有效的查询：

select * from TABLE where 1=1 and FIRST_NAME='use_input_value' and LAST_NAME='use_input_value';

（可能不需要附加分号。大多数数据库不需要像这样在单个查询的末尾使用分号。）

转到字符串文字。您应该添加一个占位符，同时将您要使用的值添​​加到List.

String QUERY = "select * from TABLE where";
List<String> args = new ArrayList<>();
if (FIRST_NAME!=null) {
    QUERY = QUERY + " and FIRST_NAME=?";
    args.add(FIRST_NAME);
}

处理完所有条件后，您将获得一个带有 N '?' 的字符串 占位符和List具有 N 个值的 a。此时只需从 SQL 字符串准备一个查询并添加占位符。

PreparedStatement statement = conn.prepareStatement(QUERY);
for (int i = 0; i < args.size(); i++) {
    statement.setString(i + 1, args[i]);
}

由于某种原因，列和参数在 JDBC API 中从 1 开始索引，因此我们必须添加 1 来i生成参数索引。

然后执行PreparedStatement.