JbdcTemplate-具有动态SQL查询的PreparedStatements [英] JbdcTemplate - PreparedStatements with Dynamic SQL Query
问题描述
我知道 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屋!