带有JDBC预准备语句的字符串中的圆括号 [英] Round bracket in string with JDBC prepared statement

问题描述

这是我的Java JDBC代码(例如，经过修改和简化):

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?

根据我的回答(请参阅下文)，我确实可以提问.

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

推荐答案

会自己回答-问题出在〜"(波浪号)中.

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

经过详细阐述，我们发现了一个有趣的发现:

After some elaboration there is interesting finding:

这是SQL代码时(请参见SQL代码中的等号"):

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

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

不需要转义.但是，如果这是SQL代码(请参见SQL代码中的波浪号"标记):

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("\\(", "\\\\(")));

这是因为模式匹配: PostgreSQL模式匹配因为带有波浪号的JDBC驱动程序不知道圆括号是普通字符(例如我的情况)还是用于模式匹配的分组符号，用于将哪些分组项合并为一个逻辑项.

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.

这篇关于带有JDBC预准备语句的字符串中的圆括号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！