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.