如何处理Java PrepareStatement中的特殊字符？

In the following SQL query using the PreparedStatement class:

在使用PreparedStatement类的以下SQL查询中:

String query_descrip = "insert into timitemdescription (itemkey, languageid, longdesc, shortdesc) values (?, 1033, ?,?)";
PreparedStatement pstmt2 = con.prepareStatement(query_descrip); 
pstmt2.setInt(1, rs4);
pstmt2.setString(2, itemdescription);
pstmt2.setString(3, itemdescription.substring(0,39));
pstmt2.executeUpdate();

I sometimes get apostrophes and single and double quotes in my item descriptions. for example, my late issue with one item is a "Planar 22" monitor". Of course, the string was misinterpreted and thought the description value was just "Planar 22". What is the best way to handle special characters in a string?

我有时在项目描述中得到撇号和单引号和双引号。例如,我的一个项目的后期问题是“Planar 22”监视器“。当然,字符串被误解,并认为描述值只是”Planar 22“。处理字符串中特殊字符的最佳方法是什么?

I've read that some people are using regex, however these seemed to be specific to a case by case basis. Another way I'm working on is reading the string array character by character. I was hoping there was a more efficient and less resource-intensive way of doing this.

我读过有些人正在使用正则表达式,但这些似乎是具体的具体情况。我正在研究的另一种方法是逐个字符地读取字符串数组。我希望有一种更有效,资源更少的方式来做到这一点。

UPDDATE AFter some more extensive testing, it turns out there were more problems occuring in my code. it was also a URL Encoding problem. When the html form was being populated by the jsp code,it would try to move the description field to an online form, it truncates it there on the form rather than on the query. jTDS also corrected the problem receiving the special characters. Because jTDS is a jar, it also helped avoid rebooting the machine. I will award the jTDS thread the bounty since that was what I partially used.

更新一些更广泛的测试,结果发现我的代码中出现了更多问题。它也是一个URL编码问题。当jsp代码填充html表单时,它会尝试将描述字段移动到在线表单,它会在表单上而不是在查询上截断它。 jTDS还纠正了接收特殊字符的问题。因为jTDS是一个jar,它也有助于避免重启机器。我将奖励jTDS线程的赏金,因为那是我部分使用的。

thanks in advance

提前致谢

4 个解决方案

#1

Since you are using PreparedStatement, you don't have to do anything at all, it will be handled for you by the JDBC driver. The only thing you have to look out for is non-ASCII characters, specifically you have to make sure the DB tables use an encoding for textual columns that can handle all characters you're going to use. But that's an SQL issue, not a Java issue.

由于您使用的是PreparedStatement,因此您无需执行任何操作,它将由JDBC驱动程序为您处理。您唯一需要注意的是非ASCII字符,特别是您必须确保数据库表使用文本列的编码来处理您将要使用的所有字符。但这是一个SQL问题,而不是Java问题。

#2

You don't need to handle those characters specially if you're binding them as parameters to a PreparedStatement, as you are doing. That's one of the main benefits of the prepared-statement approach.

如果您将它们作为参数绑定到PreparedStatement,则不需要特别处理这些字符,就像您正在做的那样。这是预备陈述方法的主要好处之一。

#3

Like the others have said, you do not have to do anything to handle the special characters. You need to try a different JDBC driver.

就像其他人所说的那样,你不需要做任何事来处理特殊字符。您需要尝试不同的JDBC驱动程序。

Try using the jTDS driver and see if it helps you with your PreparedStatement. It is an open source database driver for SQL Server. I use it at work now and it works like a champ and actually conforms to the JDBC specification unlike the MS driver.

尝试使用jTDS驱动程序,看看它是否可以帮助您使用PreparedStatement。它是SQL Server的开源数据库驱动程序。我现在在工作中使用它,它像冠军一样工作,实际上符合JDBC规范,不像MS驱动程序。

#4

I'm fairly certain the problem isn't with the code you posted. To help troubleshoot:

我很确定问题不在于您发布的代码。为了帮助排除故障:

Have you tried running the code snippet above in a debugger? What's the value of "itemdescription" before you pass it to the database call?

您是否尝试在调试器中运行上面的代码段?在将它传递给数据库调用之前,“itemdescription”的价值是多少?

How are you actually verifying the value in the database? Is this more Java code? Or are you looking at it with SQLCMD or something like that?

你是如何实际验证数据库中的值的?这是更多的Java代码吗?或者你正在用SQLCMD或类似的东西看它吗?

#1

#2