Can I get the string from preparedStatem.setString()?

Question

I have a problem - I create my SQL queries dynamically and basing on user input options. So the user has 5 parameters (actually it's more) and he can choose to use some of them (all if he wants) or none and specify their value in the query. So I construct my query String (basic the WHERE conditions) by checking if a parameter was selected and if a value was provided. However now there is the problem of special characters like '. I could try to use replaceAll("'", "\\") but this is quite dull and I know that preparedStatement.setString() does the job better. However for me I would need than to check again if the parameter was provided and if the previous one were also (to specify the poison of ? and connect it to the right parameter). This causes a lot of combinations and does not look elegant.

So my question is - can I somehow receive the string preparedStatement.setString() produces? Or is there a similar function that would do the same job and give me the String so I can put it in the query manually.

Maybe the intro was too long but someone might have a better idea and I wanted to explain why I need it.

I don't quite understand your question but you should absolutely 100% be using setString() rather than doing it yourself. — Mike Q, Jan 10 '12 at 23:36
I think you should construct your query string with the `?` characters included, depending on which parameters are provided. Then once you've got the query string, iterate through the parameters again, calling `setString()`. Will that do the trick? — Dawood ibn Kareem, Jan 11 '12 at 00:43

Mark Peters · Answer 1 · 2012-01-11T04:30:15.923

0

What you can do is construct the basic, unparameterized SQL query based on whether the parameters were specified, and then use the prepared statement to fill in the parameters.

It could look something like this (rough sketch):

Map<String, Object> parameterValues = /*from user*/;
List<String> parameterNames = Arrays.asList("field1", "field2", "field3");
List<Object> valueList = new ArrayList<Object>();

StringBuilder statementBuilder = new StringBuilder("select * from table where ");
for ( String parameterName : parameterNames ) {
    if ( parameterValues.containsKey(parameterName) ) {
        statementBuilder.append(parameterName + " = ? AND");
        valueList.add(parameterValues.get(parameterName));
    }
}

PreparedStatement st = conn.prepareStatement(statementBuilder.toString(), 
                       valueList);
//set each parameter here.

It's only hard the first time; then you can make it generic. That said there are probably query builders that abstract all of this away for you. I use QueryDSL but that does not have bindings for pure JDBC but rather JPA and JDO, etc.

edited Jan 11 '12 at 04:30

answered Jan 11 '12 at 04:24

Mark Peters

80,126
17
159
190

Mark - yes I am doing sth very similar. The problem is to set the parameters afterwards - this was my question. Because the parameters are numbered you need to check if the previous one were set. So i.e. for parameterB you need to check if parameter A was set then use setString(2,parB) and if not use setString(1,parB). For parameter C you need to check for A and B because it can be on place 1,2 or 3. This is the hard part. That's why I would to put them manually using some function to prepare the value like setString does. – srd.pl Jan 11 '12 at 09:29
@srd.pl: The point of the list was that you wouldn't have to do that. You could just do a simple for loop and increment an index. Note that I only put the value in `valueList` if it *was* specified, so the element's position in the list is its position in the prepared statement. – Mark Peters Jan 11 '12 at 14:23
1

@mark-peters Querydsl DOES have bindings for JDBC in the Querydsl SQL module. – Timo Westkämper Jan 17 '12 at 09:38

score 0 · Answer 2 · answered Jan 11 '12 at 13:15

On another forum I was given a different, simpler and cleaner approach that work perfectly. Here are some links for others with the same problem: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1669972300346534908 http://www.akadia.com/services/dyn_modify_where_clause.html

Can I get the string from preparedStatem.setString()?

2 Answers2