javasqljdbcparametersprepared-statement

Round bracket in string with JDBC prepared statement

Here's my Java JDBC code (modified and simplified for example):

ps = connection.prepareStatement("SELECT a,b,c FROM mytable WHERE category ~ ?");
ps.setString(1, "my/super/category/abc(def");
                                      ^
                                      |
    +---------------------------------+
    |
//this character is problem
result = ps.executeQuery();

It didn't work because of round bracket in string.

How to escape round brackets in prepared statement?

EDIT: based on my answer (see below) I do correct to question.

Solution

  • Will answer myself - problem is in "~" (tilde mark).

    After some elaboration there is interesting finding:

    When SQL code is this (see "equal" mark in SQL code):

    ps = connection.prepareStatement("SELECT a,b,c FROM mytable WHERE category = ?");

    escaping is not needed. But when SQL code is this (see "tilde" mark in SQL code): 

    ps = connection.prepareStatement("SELECT a,b,c FROM mytable WHERE category ~ ?");

    you need to do escaping if there are special character, in this case "(" or ")":

    ps.setString(1, "super/category/abc(def".replaceAll("\\(", "\\\\(")));

    It is because pattern matching: PostgreSQL Pattern Matching because with tilde mark JDBC driver don't know if round bracket is normal character (as in my case) or grouping symbol for pattern matching which group items into one logical item.