CODEWITHSUNDEEP
javaoracle-databaseprepared-statement
Micho Rizo
Micho Rizo

Reputation: 1092

Escaping double quotes Oracle prepared Statement in Java

I checked similar asked questions but did not have an acceptable solution. A table with a list of quoted values in a column:

ROW  COLOR_ARRAY
===  ============
1    "RED", "BLUE", "GREEN", "LIGHT-BLUE"
2    "RED", "GREEN", "LIGHT-BLUE"

I want to pull all rows who contains exact value "BLUE" so, row 1 should only be returned. my prepared statement:

        // must look for color wrapped in double quotes "BLUE"
        String sql = "Select * from myTable where COLOR_ARRAY LIKE '%\"?\"%'";
        selectColor = connection.prepareStatement(sql);
        String myColor = "BLUE";
        selectTags.setString(1, myColor);
        rs = selectTags.executeQuery();

I'm getting a Invalid column index error. I suspect the ? mark in my prepared statement is getting lost in trying to esacape the double quotes in my LIKE clause.

I've tried escaping the double quotes with \\" and \u0022 but no luck.

Upvotes: 0

Views: 2134

Answers (1)

rgettman
rgettman

Reputation: 178333

In JDBC, you cannot place a placeholder ? within an existing SQL string; the value must be standalone. JDBC will treat a ? within a SQL string as a literal ? character; it's just part of the string. Escaping the double-quotes characters within the Java string is correct, however.

Concatenate the ? placeholder value with the % SQL wildcard strings in the query string.

String sql = "Select * from myTable where COLOR_ARRAY LIKE '%\"' || ? || '\"%'";

Upvotes: 1

Related Questions