Round bracket in string with JDBC prepared stateme

问题:

Here is 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.

回答1:

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.

回答2:

You can escape round brackets here by putting them inside single quotes.

回答3:

I thought the problem was the query didn't return the result because it required to set the escape character before parenthesis, it could be done via

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

The SQL syntax allows to have escape characters in the string. In the Java you cannot escape parenthesises in the string.