How to Use an Arraylist as a Prepared Statement Parameter

How to use an arraylist as a prepared statement parameter

You may want to use setArray method as mentioned in the javadoc below:

http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#setArray(int, java.sql.Array)

Sample Code:

PreparedStatement pstmt = 
                conn.prepareStatement("select * from employee where id in (?)");
Array array = conn.createArrayOf("VARCHAR", new Object[]{"1", "2","3"});
pstmt.setArray(1, array);
ResultSet rs = pstmt.executeQuery();

PreparedStatement with list of parameters in a IN clause

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

var stmt = String.format("select * from test where field in (%s)",
                         values.stream()
                         .map(v -> "?")
                         .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 
}

Java SQL Prepared Statement Issue with Array and Where... IN List

Yes like the others said the ? is the issue.

In this case I do it like this:

String selectSQL = "SELECT * FROM rekopf INNER JOIN repos ON rekopf.rekopfnum=repos.Reposnum WHERE repos.reposart IN (";

            int count = 0;

            for (String anr : anrs){
                selectSQL += "'"+anr+"'";
                count++;
                if (count < anrs.size()){
                    selectSQL += ",";
                }
            }

            selectSQL += ")";

Thats not nice. But super efficient in this case.

Thanks.

prepare statement : create method using an ArrayList of parameters

You should use an object representation for your parameters, rather than an ArrayList<Object> of size 2. Also, you don't need your own DataType enumeration, there is already a java.sql.Types class. Best of all, there's a setObject() method on PreparedStatement that recognizes these types values, so you don't need a switch statement or calls to type-specific PreparedStatement.set..() methods.

Here's a solution that has an object representation for the parameters, takes advantage of java.sql.Types and preparedStatement.setObject() support for those types, and also insulates you from the type constant values.

First, the object representation for the parameters:

import java.sql.Types;

public class ParamDescriptor {
    // Constructor itself is private, we are encapsulating so that
    // you don't need to write java.sql.Types constants yourself
    private ParamDescriptor(int dataType, Object value) {
        _dataType = dataType;
        _value = value;
    }

    // Factory methods for actual instantiation
    public static ParamDescriptor forInt (int paramVal) {
        return new ParamDescriptor (Types.INTEGER, paramVal);
    }

    public static ParamDescriptor forString (String paramVal) {
        return new ParamDescriptor (Types.VARCHAR, paramVal);
    }

    public static ParamDescriptor forDate (Date paramVal) {
        return new ParamDescriptor (Types.DATE, paramVal);
    }
    // Add more here to support more data types . . . .    

    public int getDataType() {
        return _dataType;
    }

    public Object getValue() {
        return _value;
    }

    private int     _dataType;
    private Object  _value;
}

Next, the new version of executeInsertStmt(). We've reduced it to just a few lines, and it never needs to change again, regardless of future support for parameter types:

  public void executeInsertStmt(String strQuery, List<ParamDescriptor> parameters) throws SQLException {    
        PreparedStatement preparedStatement = cnx.prepareStatement(strQuery);

        int counter = 1;
        for (ParamDescriptor paramDescriptor : parameters) {
            preparedStatement.setObject(counter, 
                                        paramDescriptor.getValue(),
                                        paramDescriptor.getDataType());
            counter++;
        }
    }

Finally, code that would call the new executeInsertStmt():

String strQuery = "insert into toto values (?,?)";

ParamDescriptor paramToPass1 = ParamDescriptor.forString("TheValueForTheString");
ParamDescriptor paramToPass2 = ParamDescriptor.forString("TheValueForTheString2");

List<ParamDescriptor> parameters = new ArrayList<ParamDescriptor>();
parameters.add(paramToPass1);
parameters.add(paramToPass2);

executeInsertStmt(strQuery, parameters);

Note that if you ever plan on handling decimals, there is another version of PreparedStatement.setObject() that supports specifying decimal scale. You may need to add some sort of support for that into your executeInsertStmt().

java PreparedStatement Arraylist SQL

You have to set all three columns before you call executeUpdate even once.

You are doing:

        preparedStmt.setString(1, pars.getListId().get(i));
        preparedStmt.executeUpdate();

So you only set the first parameter in the prepared statement, and then you call executeUpdate. But you need to have all three.

You should have a loop that inserts all three together:

    for (int i = 0; i < pars.getListId().size(); i++) {
        preparedStmt.setString(1, pars.getListId().get(i));
        preparedStmt.setString(2, pars.getTitleId().get(i));
        preparedStmt.setString(3, pars.getAboutAr().get(i));
        preparedStmt.executeUpdate();
    }

This sets all three columns in the statement before executing it. So you will get a proper insert statement with all values set.

One problem here is that it might happen that pars.getListId() has 5 items, for example, while pars.getTitleId() and pars.getAboutAr() have 4 items. Then, when you run the loop above, when i gets to the value 4 you'll get an exception, because pars.getTitleId() doesn't have a value 4 (because it has only 4 items).

This can have several solutions.

1. If you know that the result of your parsing is always equal-length lists, then it's not a problem.

2. You can insert only the values up to the shortest list. You can have a calculation like this:

   int minSize = Collections.min( Arrays.asList( pars.getListId().size(),
                                                 pars.getTitleId().size(),
                                                 pars.getAboutAr().size() ) );
   

   And then use

   for (int i = 0; i < minSize; i++) {
       preparedStmt.setString(1, pars.getListId().get(i));
       preparedStmt.setString(2, pars.getTitleId().get(i));
       preparedStmt.setString(3, pars.getAboutAr().get(i));
       preparedStmt.executeUpdate();
   }
   

   And then it will give you as many rows as the shortest list.

3. You can go with the longest list (do max instead of min in the calculation above) and set nulls for the missing values (that is, check if the list is shorter than i requires. If so, use setNull instead of setString.

4. Personally, I would not build pars the way you have, with three separate lists. Instead, I would create a class, perhaps named Par or whatever makes sense, which has id, title and about fields. Then I would create a single list of Par objects in my pars instead of three separate lists. And then you will not have a problem with different lengths, and the data structure will be more like what you have in the database.

How do I bind an ArrayList to a PreparedStatement in Oracle?

You can't bind a List to a single parameter in a prepared statement.

Generate SQL with the a parameter marker for each element in the list, for example:

SELECT NAME FROM ITEM WHERE ID IN (?, ?, ?, ?)

Even though you'll generate a new statement for each query, I'd still recommend using a PreparedStatement. If your list contains String instances, you'll get the necessary escaping to protect from SQL injection.

But even if it's a safe type, like Integer objects, some drivers or middleware can cache PreparedStatements, and return a cached instance if the same form is requested. Of course, some testing would be necessary. If your lists vary widely in size, you'll have many different statements, and a poorly-implemented cache might not be prepared to handle so many.

How to use Array or List in one PrepareStatement (sqlite)

You need to use IN in your WHERE clause:

SELECT surname FROM users WHERE id IN (?)

and a java.sql.Array object as shown in the answer to the linked question.

---

Related Topics