Hi,
I am trying to use a direct sql query using a prepared statement as follows in hibernate.
Session session = HibernateUtil.getSessionFactory().openSession();
Transaction tx = session.beginTransaction();
try {
PreparedStatement preparedStatement = session.connection().prepareStatement("select * from messagerecipients where name = ?");
preparedStatement.setString(1, "\'first recipient\'");
ResultSet resultSet = preparedStatement.executeQuery();
while(resultSet.next()) {
System.out.println("Got :"+resultSet.getString(1));
}
preparedStatement.close();
}catch(SQLException ex) {
System.out.println(ex.getMessage());
throw new RuntimeException();
}
tx.commit();
session.close();

However I do not get anything in the resultset, which I should get. I tried using a Statement instead of PreparedStatement and that works fine. Could anybody explain to me what is happening here?
Feedback will be greatly appreciated.
Thank you.

You need to post more info about the database and table definition.

Wild guess:
The name column is CHAR and not VARCHAR. In some databases, this means you have to match the length when comparing strings using prepared statements, but not when using a constant in the query.
This certainly is the case with Oracle.

Try padding your string with spaces to the exact column length.

More likely, you're assuming that prepared statement IN parameter population is very dumb, when in fact it's only slightly dumb. Try this line instead:java.sql takes care of little things like converting "string" to 'string' for you.

Actually, it's very important (and smart) that you have to not include the quotes in your bind value. What if your bind value was taken from the user? What if it was "Rock'n'roll"? You'd have to search the value for quotes and escape them.
If you're using regular Statement objects, that's what you have to do. If you're using PreparedStatement objects, there's no need to scan, any value is valid. Any time you use a Statement and concatenate String values from the user in the process you're risking SQL Injection!!!

Why are you using PreparedStatement instead of session.createSQLQuery()?

This method let's you execute plain SQL...

Yep, it works without the quotes. Thanks alot tenwit.