jdbc中通用的转义字符不起作用?!

06-07-23 lyojbuilder

如果在数据库中有一个字符串保存成

java代码:

|"test"| 

 

无论用jdbc怎样转义都报语法错误,我使用mysql4.1,mysql-jdbc-connector-3.0.

我的写法:

java代码:

        ResultSet rs=stm.executeQuery("select * from a where name='\"test \"' s");
 
<p>

报错:

java代码:

Exception in thread "main" java.sql.SQLException: Syntax error or access violati 
on message from server: "You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use near ' 
s' at line 1" 
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997) 
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167) 
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278) 
        at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1224) 
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2244) 
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2192) 
        at com.mysql.jdbc.Statement.executeQuery(Statement.java:1163) 
        at lyo.hotmail.test.Test.main(Test.java:48)
 
<p>

使用单引号转义也没用!难道数据库中的 双引号字符串没办法查出来?

我看到网上的文章,说 jdbc提供了通用的转义方法:

http://www.jguru.com/faq/view.jsp?EID=8881

原文:

引用:

An example of this is if you want to issue the following SQL command: 


SELECT * FROM BIRDS 
WHERE SPECIES='Williamson's Sapsucker' 

In this case, the apostrophe in "Williamson's" is going to cause a problem for the database because SQL will interpret it as a string delimiter. It is not good enough to use the C-style escape \', because that substitution would be made by the Java compiler before the string is sent to the database. 

Different flavors of SQL provide different methods to deal with this situation. JDBC abstracts these methods and provides a solution that works for all databases. With JDBC you could write the SQL as follows: 

Statement statement = // obtain reference to a Statement 
statement.executeQuery( 
"SELECT * FROM BIRDS WHERE SPECIES='Williamson/'s Sapsucker' {escape '/'}"); 

The clause in curly braces, namely {escape '/'}, is special syntax used to inform JDBC drivers what character the programmer has chosen as an escape character. The forward slash used as the SQL escape has no special meaning to the Java compiler; this escape sequence is interpreted by the JDBC driver and translated into database-specific SQL before the SQL command is issued to the database. 
 
<p>

但是他的方法用了也是报错,不知道是怎麽回事儿? 大家有研究过通用的jdbc转义的问题么?

lyojbuilder
2006-07-23 19:33

??? 好像这个论坛的转义字符也没有处理好

我写的test两边的双引号没有转义啊!怎麽双引号变成了 " ???

lyojbuilder
2006-07-23 19:34

??? 好像这个论坛的转义字符也没有处理好

我写的test两边的双引号没有转义啊!怎麽双引号变成了 " ???

superbible
2006-09-12 12:07

在网页显示特殊字符有两层问题:

1)数据库里正确处理(如保存)了这些字符。

2)网页上正确显示了这些字符。如大于号,在网页中显示就要替换为相应的html实体。

你的问题似乎集中在构造合法的jdbc查询问题上。你应该使用PreparedStatement来执行SQL,conn.preparedStatement("select * from a where name=?"),然后PreparedStatement.setString(1,"包含特殊字符的字符串")。这样就不存在转义字符的问题了。

BTW,处理日期数据也应该这么做,就不需要使用to_date()之类的数据库函数了。