准备好的声明不是逃避撇号 [英] Prepared Statement Not Escaping Apostrophe

问题描述

我使用从hibernate获得的JDBC连接对象来执行更新，我这样做是因为我需要使用MySql ON DUPLICATE功能。但是，当试图插入时，我无法插入说这个字符串有特殊字符，

  Session session = sessionFactory.openSession（ ）; 
 PreparedStatement pstm = null; 
 Connection conn = session.connection（）; 
 
尝试
 {
 IRKeyWordTweet记录= null; 
 conn.setAutoCommit（false）; 
 
 for（Iterator itrList = statusToInsert.iterator（）; itrList.hasNext（）;）
 {
 try 
 {
 record =（IRKeyWordTweet） itrList.next（）; 
 pstm =（PreparedStatement）conn.prepareStatement（INSERT QUERY）; 
 
 System.err.println（record.getTwtText（））; 
 
 // tweetId 
 pstm.setLong（1，record.getTweetId（））; 
 
 Setters For Prepared statement .... 
 pstm.addBatch（）; 
 int [] updateCounts = pstm.executeBatch（）; 
 
 catch（Exception e）
 {
 e.printStackTrace（）; 
 
 
 $ b catch（Exception e）
 {
 log.error（发生的异常，e）; 
} 
 finally 
 {
 try 
 {
 pstm.close（）; 
 conn.close（）; 
} 
 catch（SQLException e）{
 e.printStackTrace（）; 
} 
 session.close（）; 
} 
  

这可能是什么原因造成的？

 为了避免单引号，你可以使用JDBC的转义序列。 Statement statement = 
 statement.executeQuery（
SELECT * FROM DATA_TABLE WHERE COLUMN1 LIKE'Having'\\'s Quotes％'{escape'\'}）; 
  

{escape'\'} 通知JDBC驱动程序将'\'字符转换为特定于数据库的转义字符。

有用的链接此处

另外，如果您使用PreparedStatement，你不必逃跑！

  PreparedStatment ps = conn.prepareStatement（SELECT * FROM DATA_TABLE WHERE COLUMN1 LIKE？％）; 
 ps.setString（1，有行情）; 
  

I am using JDBC connection object obtained from hibernate to perform bath update, I am doing this because I need to use the MySql ON DUPLICATE feature. But, when trying to insert I am not able to inset saying the string has special characters,

Session session = sessionFactory.openSession();
PreparedStatement pstm = null;
Connection conn = session.connection();

try
{
    IRKeyWordTweet record = null;
    conn.setAutoCommit(false);

    for (Iterator itrList = statusToInsert.iterator(); itrList.hasNext();) 
    {   
        try
        {
            record = (IRKeyWordTweet) itrList.next();
            pstm = (PreparedStatement) conn.prepareStatement("INSERT QUERY");

            System.err.println(record.getTwtText());

            //tweetId
            pstm.setLong(1, record.getTweetId());

            Setters For Prepared statement....
            pstm.addBatch();
            int[] updateCounts = pstm.executeBatch();
        }
        catch (Exception e) 
        {
            e.printStackTrace();
        }
    }
}
catch (Exception e) 
{
    log.error("Exception Occured",e);
}
finally
{   
    try 
    {
        pstm.close();
        conn.close();
    }
    catch (SQLException e) {
        e.printStackTrace();
    }
    session.close();
}

What could be causing this?

解决方案

To escape single quotes you can use JDBC's escape sequence.

Statement statement = 
statement.executeQuery(
  "SELECT * FROM DATA_TABLE  WHERE COLUMN1 LIKE 'Having\'s Quotes%' {escape '\'}");

The { escape '\'} informs the JDBC driver to translate the '\' character to the database-specific escape character.

Helpful link here

Also, if you use a PreparedStatement, you dont have to escape!

PreparedStatment ps = conn.prepareStatement("SELECT * FROM DATA_TABLE WHERE COLUMN1 LIKE ?%");
ps.setString(1, "Having's Quotes");

这篇关于准备好的声明不是逃避撇号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！