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

查看:80
本文介绍了JbdcTemplate-具有动态SQL查询的PreparedStatements的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我知道 jdbcTemplate 可以用于创建 PreparedStatements ,如果您将其设置为:

  private JdbcTemplate jdbcTemplate;字符串sqlQuery =从表中的列*处选择*";字符串值=值";this.jbdcTemplate.query(sqlQuery,new Object [] {value},rs,rowNum-> {System.out.println(rs.getString("column"));}); 

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

  private JdbcTemplate jdbcTemplate;StringBuilder sqlQuery = new StringBuilder(从表中选择*,其中1 = 1");//基础查询如果(someCondition)sqlQuery.append("And column1 ='" + value1 +'");如果(someCondition2)sqlQuery.append("And column2 ='" + value2 +'");如果(someCondition3)sqlQuery.append("And column3 ='" + value3 +'");//等等... 

使用这种类型的动态查询,由于 someCondition new Object [] {} 字段中放置多少个值.>字段总是在运行时更改.

是否可以编写 this.jdbcTemplate.query(sqlQuery.toString(),new Object [] {} ... 来容纳这些动态AND语句?

解决方案

我考虑了@mustaccio在对我的原始问题的评论中所说的内容后,找到了解决方案.我还从此stackoverflow问题中获得了一部分解决方案,并将其用于我自己的解决方案中.

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

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

  private JdbcTemplate jdbcTemplate;列表< QueryResults>jdbcQuery(QueryParams queryParams){/*基本查询*/StringBuilder sqlQuery = new StringBuilder(从表中选择*,其中1 = 1");/*存储动态prepareStatement参数*/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);}//等等.../*这是我上面的stackoverflow问题中使用的部分*/Object [] prepareStatementArgs = new Object [queryArgs.size()];for(int i = 0; i 

{QueryResults结果=新的QueryResults();/*存储查询结果... */});}

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

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

,其中括号内的值在运行时也是动态的.我的服务接收到一个类似于以下内容的字符串值:

 字符串queryParams.value4 ="x,y,z,etc ..."; 

我不能这样编写prepareStatement: AND column4 IN(?),然后只需插入 queryParams.value4 ,因为它将处理 queryParams.value4 作为字符串文字,这会导致错误.

为解决此问题,我创建了另一个名为 value4Array 的字符串数组列表.我遍历 queryParams.value4 中的每个字符,然后检查循环中的当前字符是否等于逗号(我们的定界符).如果是这样,那么我将创建一个导致该逗号的所有字符的子字符串,并将该新创建的字符串添加到 value4Array .

下一步是创建动态 AND column4 IN(?)语句.我通过遍历刚刚创建的 value4Array 数组列表中的每个字符串值,并根据其中包含多少个字符串进行 sql.append(?")来执行此操作 value4Array .之后,其余逻辑与我上面的解决方案相同.

 /*此函数采用逗号分隔的字符串文字(value4:"x,y,z,etc ...")并将其解析为字符串数组.*/私有列表< String>parseValue4(字符串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;}否则if(i == value4.length()-1){value4Array.add(value4.substring(valueIndex,value4.length()));}}返回value4Array;}if(someCondition4){List< String>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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆