5

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?

sesmic
  • 928
  • 4
  • 15
  • 32
  • 4
    You've omitted one of the most important parts: the SQL itself. – Jon Skeet Aug 24 '11 at 13:59
  • Another important thing: you didn't include your setters for the prepared statement. – Jacob Aug 24 '11 at 14:01
  • I don't know if I upvoted that because it's @Jon Skeet or because he has a point :) – Amir Raminfar Aug 24 '11 at 14:01
  • Show us the query where you have the escape. – Kal Aug 24 '11 at 14:02
  • You don't show us the part of the code that could contain the bug: the actual query, and the way you bind the parameters. Moreover, why don't you simply use session.createSQLQuery()? – JB Nizet Aug 24 '11 at 14:03
  • I am not using session.SQLQuery() because I want to use the batch update feature, the number of records to be inserted is very high and I want to check the performance using batch update feature and at the same time avoid Duplicate entries. Not near my pc will update query soon. – sesmic Aug 24 '11 at 14:27

1 Answers1

9

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 don't even have to escape!

PreparedStatement ps = conn.prepareStatement("SELECT * FROM DATA_TABLE WHERE COLUMN1 LIKE ?%");
ps.setString(1, "Having's Quotes");
Community
  • 1
  • 1
Kal
  • 24,724
  • 7
  • 65
  • 65