Asked  7 Months ago    Answers:  5   Viewed   55 times

How to set value for in clause in a preparedStatement in JDBC while executing a query.

Example:

connection.prepareStatement("Select * from test where field in (?)");

If this in-clause can hold multiple values how can I do it. Sometimes I know the list of parameters beforehand or sometimes I don't know beforehand. How to handle this case?

 Answers

10

What I do is to add a "?" for each possible value.

var stmt = String.format("select * from test where field in (%s)",
                         values.stream()
                         .collect(Collectors.joining(", ")));

Alternative using StringBuilder (which was the original answer 10+ years ago)

List values = ... 
StringBuilder builder = new StringBuilder();

for( int i = 0 ; i < values.size(); i++ ) {
    builder.append("?,");
}

String placeHolders =  builder.deleteCharAt( builder.length() -1 ).toString();
String stmt = "select * from test where field in ("+ placeHolders + ")";
PreparedStatement pstmt = ... 

And then happily set the params

int index = 1;
for( Object o : values ) {
   pstmt.setObject(  index++, o ); // or whatever it applies 
}
   

   
Tuesday, June 1, 2021
 
JustSteveKing
answered 7 Months ago
16

This guide says:

6.1.5 Sending JDBC NULL as an IN parameter

The setNull method allows a programmer to send a JDBC NULL (a generic SQL NULL) value to the database as an IN parameter. Note, however, that one must still specify the JDBC type of the parameter.

A JDBC NULL will also be sent to the database when a Java null value is passed to a setXXX method (if it takes Java objects as arguments). The method setObject, however, can take a null value only if the JDBC type is specified.

So yes they're equivalent.

Thursday, June 10, 2021
 
DaveRandom
answered 6 Months ago
14

Example, that you've posted is not a valid python code, I could only imagine that you're trying to do something like this:

self.rules[a] = [{b:{'f_expr': c, 'c_expr': d}}]

this way self.rules is a dictionary of a list of a dictionary of a dictionary. I bet there is more sane way to do this.

Wednesday, August 4, 2021
 
RustyFluff
answered 4 Months ago
42

Consider using the PreparedStatement setObject() method instead of setString().

The PreparedStatement setObject() will attempt to convert any of the java.lang types for you if the type is unknown at compile time.

so with an updated for loop (assuming you have java 5.0) and generic null handling:

int i = 0;
for(Object value : temp) {
    if (value == null) {
        // set null parameter if value type is null and type is unknown
        pstmt.setNull(++i, Integer.MIN_VALUE); 
    } else {
        pstmt.setObject(++i, value);
    }
}

Note that setNull() can accept a type as the 2nd parameter if it is known.

Thursday, August 5, 2021
 
derobert
answered 4 Months ago
78
some_list = ["some", "values", "in", "a", "list", ]
func(*some_list)

This is equivalent to:

func("some", "values", "in", "a", "list")

The fixed x param might warrant a thought:

func(5, *some_list)

... is equivalent to:

func(5, "some", "values", "in", "a", "list")

If you don't specify value for x (5 in the example above), then first value of some_list will get passed to func as x param.

Wednesday, October 13, 2021
 
Peteris
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share