Prepared Statement setString adding unnecessary single quotes to String

Question

Background

I am trying to set the contents of an ArrayList into an IN clause in a Db2 SQL statement. I am using the PreparedStatement to build my query. This is our coding standard.

What I tried #1

I researched a couple ways to achieve this. I first tried using the setArray() as show in this question: How to use an arraylist as a prepared statement parameter The result was I was getting a error of Err com.ibm.db2.jcc.am.SqlFeatureNotSupportedException: [jcc][t4][10344][11773][3.65.110] Data type ARRAY is not supported on the target server. ERRORCODE=-4450, SQLSTATE=0A502 After this roadblock, I moved on to #2

What I tried #2

I then tried using the Apache Commons StringUtils to convert the ArrayList into a comma separated String like I needed for my IN clause. The result is that this did exactly what I needed, I have a single String with all my results separated by a comma.

The problem:

The setString() method is adding single quotes to the beginning and end of my String. I have used this many times, and it has never done this. Does anyone know if there is a way around this, or an alternative using the PreparedStatement?? If I use String concatenation my query works.

Code (explained above):

    List<String> selectedStatuses = new ArrayList<String>(); //Used to store contents of scoped var

    //Get Contents of Checkbox which are in the form of a List
    selectedStatuses = (List) viewScope.get("selectedStatuses");

    String selectedStatusesString = StringUtils.join(selectedStatuses, ",");

    .... WHERE ATM_DET_ATM_STAT IN (?)";
    ps.setString(1, selectedStatusesString);

Log Value showing correct value of String

DEBUG: selectedStatusesString: 'OPEN','CLOSED','WOUNDED','IN PROGRESS'

Visual of incorrect result

The quotes at the beginning and end are the problem.

It appears you are trying to pass multiple arguments to a single parameter by joining them in one long string. See [question 17842211](http://stackoverflow.com/questions/17842211/how-to-use-an-arraylist-as-a-prepared-statement-parameter) — matt, Oct 06 '15 at 18:27
It is a single string after using the StringUtils. If it was not a String, it wouldn't compile. I did see that question, and referenced it above in the 'What I tried #1' section. I couldn't get the setArray() to work after getting strange DB2 error. — Steve Zavocki, Oct 06 '15 at 18:31
Did you try it without quotes on your arguments? Yes I see that you are converting a list of arguments to a single string. Prepared statements are supposed to be so that you cannot do that though. setArray should be the proper way. I would downvote that one, but I cannot set it up to make certain it is broken at the moment. — matt, Oct 06 '15 at 18:35
Did you check this one? [24528337](http://stackoverflow.com/questions/24528337/passing-array-parameter-in-prepare-statement-getting-java-sql-sqlfeaturenotsu) — matt, Oct 06 '15 at 18:37
Yes, I did see that one too. I agree that setArray() should be the proper way. Without quotes on the arguments, it won't work. But you have me thinking..let me try escaping the single quotes and see if that works. — Steve Zavocki, Oct 06 '15 at 18:42

Andreas · Accepted Answer · 2020-07-02T17:07:19.680

For an IN clause to work, you need as many markers as you have values:

String sql = "SELECT * FROM MyTable WHERE Stat IN (?,?,?,?)";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setString(1, "OPEN");
    stmt.setString(2, "CLOSED");
    stmt.setString(3, "WOUNDED");
    stmt.setString(4, "IN PROGRESS");
    try (ResultSet rs = stmt.executeQuery()) {
        // use rs here
    }
}

Since you have a dynamic list of values, you need to do this:

List<String> stats = Arrays.asList("OPEN", "CLOSED", "WOUNDED", "IN PROGRESS");

String markers = StringUtils.repeat(",?", stats.size()).substring(1);
String sql = "SELECT * FROM MyTable WHERE Stat IN (" + markers + ")";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    for (int i = 0; i < stats.size(); i++)
        stmt.setString(i + 1, stats.get(i));
    try (ResultSet rs = stmt.executeQuery()) {
        // use rs here
    }
}

Starting with Java 11, StringUtils is no longer needed:

String markers = ",?".repeat(stats.size()).substring(1);

Excellent! Thank you sir. Bottom line: setString() doesn't work for multiple markers in an IN statement. — Steve Zavocki, Oct 06 '15 at 20:11
Ehh.... Bottom line: All the `setXxx(index, value)` methods works for setting *the* value for the marker identified by the first parameter. — Andreas, Oct 06 '15 at 20:14

score 1 · Answer 2 · answered Oct 06 '15 at 19:01

1

Use two apostrophes '' to get a single apostrophe on DB2, according to the DB2 Survival Guide. Then call .setString().

answered Oct 06 '15 at 19:01

ursa

4,404
1
24
38

Z41N · Answer 3 · 2022-08-23T21:47:22.450

To anyone else experiencing the issue with single quotes, I had to modify my function so that it doesn't use ? to set the value; instead, I just treat the entire query as a string:

public static void runQuery(String tableName, String columnName, int value, String whereName, String whereValue) {
        try (Connection con = DatabaseConnection.getConnection()) {
            try (PreparedStatement ps = con.prepareStatement("UPDATE " + tableName + " SET " + columnName + " = " + value + " WHERE " + whereName + " = " + "'" + whereValue + "'")) {
                ps.executeUpdate();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

Hope this helps

Prepared Statement setString adding unnecessary single quotes to String

3 Answers3