java - Oracle SQL 中如何处理单引号？

Question

我有一个用Java构建的查询来搜索和获取记录，当使用值'(单引号)构建时(例如:New's) 那么我就没有得到任何结果。

这是我设置值New's的查询:

SELECT COUNT(1) FROM AGX_THERAPEUTIC_AREA T WHERE  UPPER(T.THERAPEUTIC_NAME) LIKE ? ESCAPE \  OR  UPPER(T.THERAPEUTIC_AREA_DESC) LIKE ? ESCAPE \ 
    bind => [NEW'S%, NEW'S%]

这是我处理单引号的方法:

public static String handleSingleQuote(String searchString) {
        if ((searchString == null) || searchString.trim().equals("")) {
            searchString = "";
        }

        searchString = searchString.trim();

        int length = searchString.length();
        StringBuffer searchBuffer = new StringBuffer();

        for (int index = 0; index < length; index++) {
            if (searchString.charAt(index) == '\'') {
                searchBuffer.append("''");
            } else {
                searchBuffer.append(searchString.charAt(index));
            }
        } // end of for loop

        searchString = searchBuffer.toString();

        return (searchString);
    } 

Answer 1

I have a query constructed in Java to search and fetch records,when constructing with the values ' (Single Quote)(For Example:New's) then I'm not getting any results.

那么你的构建方式是错误的。听起来您正在使用字符串连接。 Never do that (见下文)。相反，使用 PreparedStatement :

PreparedStatement ps = connection.prepareStatement(
    "SELECT FOO FROM BAR WHERE COLUMN LIKE ? ESCAPE \\"
);
ps.setString("this 'has' single quotes");
ResultSet rs = ps.executeQuery();

您的 JDBC 连接器将确保字符串正确发送。

<小时/>

回复您的评论，说您正在使用 JPA:我不知道 JPA，但是 this page建议它看起来像:

TypedQuery<Thingy> query = em.createQuery(
    "SELECT FOO FROM BAR WHERE COLUMN LIKE :search ESCAPE \\",
    Thingy.class
);
return query.setParameter("search", "this 'has' quotes").getSingleResult();

<小时/>

关于“永远不要这样做”: