Hibernate : To handle special characters in HQL

In this article we will see to handle special characters in HQL similar to PreparedStatement in JDBC. It can also used for additional security purpose like to avoid SQL Injection.

For example, I have a below function in my BookDAO class to get detail of a particular book. It accepts bookTitle as a parameter and returns list of books. Here assume bookTitle can contain special characters.

public List findBook(String bookTitle) {
try {
String queryString = "from BsBooks where bookTitle = :bookTitle";
Query queryObject = getSession().createQuery(queryString);
queryObject.setParameter("bookTitle", bookTitle);
return queryObject.list();
} catch (RuntimeException re) {
throw re;
}
}

Note - the method setParameter(), it is used to set the value for bookTitle, which can contain special characters.

queryObject.setParameter("bookTitle", bookTitle);

The above method will give following error if you try to use it like - queryObject.setParameter(0, bookTitle);

Exception in thread "main" java.lang.IndexOutOfBoundsException: Remember that ordinal parameters are 1-based!
at org.hibernate.engine.query.ParameterMetadata.getOrdinalParameterDescriptor(ParameterMetadata.java:79)
at org.hibernate.engine.query.ParameterMetadata.getOrdinalParameterExpectedType(ParameterMetadata.java:85)
at org.hibernate.impl.AbstractQueryImpl.determineType(AbstractQueryImpl.java:421)
at org.hibernate.impl.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:393)


To avoid it you can use it as a '?' placeholder. When you execute the query, you would need to supply the value for it, which would replace the '?' in the query in below function.

public List findBook(String bookTitle) {
try {
String queryString = "from BsBooks where bookTitle = ?";
Query queryObject = getSession().createQuery(queryString);
queryObject.setParameter(0, bookTitle);
return queryObject.list();
} catch (RuntimeException re) {
throw re;
}
}


Below are the variations of the method setParameter, to know please see the API.

setParameter(int position, Object val) - Bind a value to a JDBC-style query parameter.

setParameter(int position, Object val, Type type) - Bind a value to a JDBC-style query parameter.

setParameter(String name, Object val) - Bind a value to a named query parameter.

setParameter(String name, Object val, Type type) - Bind a value to a named query parameter.

setParameterList(String name, Collection vals)  - Bind multiple values to a named query parameter.

setParameterList(String name, Collection vals, Type type) - Bind multiple values to a named query parameter.

setParameterList(String name, Object[] vals) - Bind multiple values to a named query parameter.

setParameterList(String name, Object[] vals, Type type) - Bind multiple values to a named query parameter.

setParameters(Object[] values, Type[] types) - Bind values and types to positional parameters.

JDBC Example – Batch Update using PreparedStatement

Using Batch Update feature of PreparedStatement you can Insert, Update or Delete multiple records with single database hit. For example if you want to insert multiple records into database using Batch Update you will just need to conect to database once. You can loop through records and add it to bach and once done you can execute the bach i.e insert all records in a single shot.


Below is a code snippet which shows how to insert few records in batch process, via JDBC PreparedStatement

PreparedStatement ps=con.prepareStatement("INSERT INTO BS_Books (cat_id,book_title,book_details,book_price,book_author) values (?,?,?,?,?)");

ps.setInt(1, 1);
ps.setString(2, "Book1");
ps.setString(3, "Details of book1");
ps.setDouble(4, 100.0);
ps.setString(5, "Author1");
ps.addBatch();//Adding record to the batch

ps.setInt(1, 1);
ps.setString(2, "Book2");
ps.setString(3, "Details of book2");
ps.setDouble(4, 150.0);
ps.setString(5, "Author2");
ps.addBatch();//Adding record to the batch

ps.executeBatch();//Executing the batch

Please see the complete self explanatory java code below