JbdcTemplate - 带有动态 SQL 查询的 PreparedStatements [英] JbdcTemplate - PreparedStatements with Dynamic SQL Query

问题描述

我知道 jdbcTemplate 可以用来创建 PreparedStatements 如果你设置它这样做:

即

私有 JdbcTemplate jdbcTemplate;String sqlQuery = "Select * from table where column = ?";字符串值 = "值";this.jbdcTemplate.query(sqlQuery, new Object[] { value }, rs, rowNum -> {System.out.println(rs.getString("column"));});

但是，我在 Where 子句中有一个包含许多 AND 运算符的查询，并且根据某些条件，可能会或可能不会将特定的 AND 语句添加到查询字符串中.

即

私有 JdbcTemplate jdbcTemplate;StringBuilder sqlQuery = new StringBuilder("Select * from table where 1=1 ");//基本查询如果(某些条件)sqlQuery.append("And column1 = '" + value1 + "'");如果(某些条件2)sqlQuery.append("And column2 = '" + value2 + "'");如果(某些条件3)sqlQuery.append("And column3 = '" + value3 + "'");//等等...

使用这种类型的动态查询，我无法在编译时确切知道要在 new Object[] {} 字段中放置多少值，因为 someCondition 字段在运行时总是在变化.

有没有办法编写 this.jdbcTemplate.query(sqlQuery.toString(), new Object[] {}... 来适应这些动态 AND 语句?

解决方案

在考虑了@mustaccio 在他对我最初问题的评论中所说的话后，我找到了一个解决方案.我还参与了this stackoverflow question的部分解决方案，并在我自己的解决方案中使用了它.

我遇到的主要问题是在运行时动态创建 Object[] 数组，因为您无法将元素动态添加到 Object[] 数组.它们在初始化时必须具有定义的大小.

首先，我创建一个名为 queryArgs 的字符串数组列表.每当其中一个 if 条件证明为真并且我们在查询中添加 AND 语句时，我还会添加另一行代码，将要插入到preparedStatement 中的值添加到 queryArgs数组列表.完成后，我创建一个新的 Object[] 数组，其大小被初始化为 queryArgs 数组列表的大小.最后，我遍历 Object[] 数组中的每个元素，将它们设置为等于 queryArgs 中的值.

私有 JdbcTemplate jdbcTemplate;列出<查询结果>jdbcQuery(QueryParams queryParams) {/* 基本查询 */StringBuilder sqlQuery = new StringBuilder("Select * from table where 1=1 ");/* 存储动态preparedStatement 参数*/列表<字符串>queryArgs = new ArrayList<>();如果(某些条件){sqlQuery.append("and column1 = ?");queryArgs.add(queryParams.value1);}如果(某些条件2){sqlQuery.append("and column2 = ?");queryArgs.add(queryParams.value2);}如果(某些条件3){sqlQuery.append("And column3 = ?");queryArgs.add(queryParams.value3);}//等等.../* 这是我在上面的 stackoverflow 问题中使用的部分 */对象[]preparedStatementArgs = new Object[queryArgs.size()];for(int i = 0; i 

异常值是上面的动态 AND 语句之一是这样写的:

AND column4 IN ('x','y','z','etc..'‌ )

，括号内的值在运行时也是动态的.我的服务收到一个如下所示的字符串值:

String queryParams.value4 = "x,y,z,etc...";

我不能这样编写preparedStatement:AND column4 IN (?) 然后简单地插入queryParams.value4，因为它会处理queryParams.value4 作为字符串文字，这会导致错误.

为了解决这个问题，我创建了另一个名为 value4Array 的字符串数组列表.我循环遍历 queryParams.value4 中的每个字符，并检查循环中的当前字符是否等于逗号，即我们的分隔符.如果是，那么我创建一个以逗号开头的所有字符的子字符串，并将新创建的字符串添加到 value4Array.

下一步是创建动态 AND column4 IN (?) 语句.我通过遍历我们刚刚创建的 value4Array 数组列表中的每个字符串值，并根据有多少字符串执行 sql.append("?") 来做到这一点value4Array.之后，剩下的逻辑和我上面的解决方案一样.

/* 此函数采用逗号分隔的字符串文字(value4 : "x,y,z,etc...")并将其解析为字符串数组.*/私有列表<字符串>parseValue4(字符串值4){整数值索引= 0；列表<字符串>value4Array = new ArrayList<>();for(int i = 0; i < value4.length(); i++){if(value4.charAt(i) == ','){value4Array.add(value4.substring(valueIndex, i));值索引 = i + 1;}否则 if(i == value4.length() - 1){value4Array.add(value4.substring(valueIndex, value4.length()));}}返回值4数组；}如果(某些条件4){列表<字符串>value4Array = parseValue4(queryParams.value4);sqlQuery.append("And column4 IN (");/* 基础 AND 语句 */for(int i = 0; i < value4Array.size(); i++){if(i == value4Array.size() - 1)sqlQuery.append("?)");else/* 动态附加 ? 的 */sqlQuery.append("?,");queryArgs.add(value4Array.get(i));}}

I know jdbcTemplate can be used to create PreparedStatements if you set it up to do so:

i.e.

private JdbcTemplate jdbcTemplate;
String sqlQuery = "Select * from table where column = ?";
String value = "value";
this.jbdcTemplate.query(sqlQuery, new Object[] { value }, rs, rowNum -> {
  System.out.println(rs.getString("column"));
});

However, I have a query with many AND operators in the Where clause, and depending on some condition, a specific AND statement may or may not be added to the query string.

i.e.

private JdbcTemplate jdbcTemplate;
StringBuilder sqlQuery = new StringBuilder("Select * from table where 1=1 "); //base query
if(someCondition)
   sqlQuery.append("And column1 = '" + value1 + "'");
if(someCondition2)
   sqlQuery.append("And column2 = '" + value2 + "'");
if(someCondition3)
   sqlQuery.append("And column3 = '" + value3 + "'");
//etc...

With this type of dynamic query, I am unable to know exactly how many values to place in the new Object[] {} field at compile time, because the someCondition fields are always changing at runtime.

Is there a way to write the this.jdbcTemplate.query(sqlQuery.toString(), new Object[] {}... to accommodate these dynamic AND statements?

解决方案

I found a solution after taking into consideration what @mustaccio said in his comment to my original question. I also took part of the solution from this stackoverflow question and used it in my own solution.

The main issue I was having was dynamically creating an Object[] array at runtime, since you can't dynamically add elements to an Object[] array. They must have a defined size when initialized.

First, I create an arraylist of strings called queryArgs. Every time one of the if conditions proves true and we add an AND statement to the query, I also add another line of code that adds the value to be plugged in the preparedStatement to the queryArgs arraylist. Once that's done, I create a new Object[] array whose size is initialized to the size of the queryArgs arraylist. Lastly, I loop through each element in the Object[] array, setting them equal to the values in queryArgs.

private JdbcTemplate jdbcTemplate;

List<QueryResults> jdbcQuery(QueryParams queryParams) {
    /* base query */
    StringBuilder sqlQuery = new StringBuilder("Select * from table where 1=1 ");
    /* stores the dynamic preparedStatement arguments */
    List<String> queryArgs = new ArrayList<>();

    if(someCondition){
       sqlQuery.append("And column1 = ? ");
       queryArgs.add(queryParams.value1);
    }
    if(someCondition2){
       sqlQuery.append("And column2 = ? ");
       queryArgs.add(queryParams.value2);
    }
    if(someCondition3){
       sqlQuery.append("And column3 = ? ");
        queryArgs.add(queryParams.value3);
    }
    //etc...

    /* this is the part I used from the above stackoverflow question */
    Object[] preparedStatementArgs = new Object[queryArgs.size()];
    for(int i = 0; i < preparedStatementArgs.length; i++){
        preparedStatementArgs[i] = queryArgs.get(i);
    }

    /* Lastly, execute the query */
    return this.jdbcTemplate.query(sqlQuery.toString(),
    preparedStatementArgs, (rs, rowNum) -> {

        QueryResults result = new QueryResults();
        /* store the results of the query... */
    });
}

The outlier is that one of the dynamic AND statements above is written like this:

AND column4 IN ('x','y','z','etc..'‌​)

, where the values inside of the parentheses are also dynamic at runtime. My service receives a string value that looks like this:

String queryParams.value4 = "x,y,z,etc...";

I can't write the preparedStatement like this: AND column4 IN (?) and then simply plug in queryParams.value4 because it will treat queryParams.value4 as a string literal, which leads to errors.

To solve this issue, I create another arraylist of strings called value4Array. I loop through every character in queryParams.value4, and I check if the current character in the loop is equal to a comma, our delimiter. If it is, then I create a substring of all the characters leading up to that comma, and add that newly created string to value4Array.

The next step is to create the dynamic AND column4 IN (?) statement. I do this by looping through each string value in the value4Array arraylist we just created, and doing a sql.append("?"), based on how many strings are in value4Array. After this, the rest of the logic is the same as my solution above.

/* this function takes the comma delimited string literal (value4 : "x,y,z,etc...")
   and parses it into an array of strings. */
private List<String> parseValue4(String value4){
    int valueIndex= 0;
    List<String> value4Array = new ArrayList<>();
    for(int i = 0; i < value4.length(); i++){
        if(value4.charAt(i) == ','){
            value4Array.add(value4.substring(valueIndex, i));
            valueIndex = i + 1;
        }
        else if(i == value4.length() - 1){
            value4Array.add(value4.substring(valueIndex, value4.length()));
        }
    }
    return value4Array;
}
if(someCondition4){
   List<String> value4Array = parseValue4(queryParams.value4);
   sqlQuery.append("And column4 IN ("); /* base AND statement */
    for(int i = 0; i < value4Array.size(); i++){
        if(i == value4Array.size() - 1)
            sqlQuery.append("?)");
        else                       /* dynamically appending ?'s */
            sqlQuery.append("?,");
        queryArgs.add(value4Array.get(i));
    }
}

这篇关于JbdcTemplate - 带有动态 SQL 查询的 PreparedStatements的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！