Without parameter binding, you have to concatenate the parameter String like this (bad code) :
String hql = "from Stock s where s.stockCode = '" + stockCode + "'";
List result = session.createQuery(hql).list();
Pass an unchecked value from user input to the database will raise security concern, because it can easy get hack by SQL injection. You have to avoid the above bad code and using parameter binding instead.
Hibernate parameter binding
There are two ways to parameter binding : named parameters or positional.
1. Named parameters
This is the most common and user friendly way. It use colon followed by a parameter name (:example) to define a named parameter. See examples…
Example 1 – setParameter
The setParameter is smart enough to discover the parameter data type for you.
String hql = "from Stock s where s.stockCode = :stockCode";
List result = session.createQuery(hql)
.setParameter("stockCode", "7277")
.list();
Example 2 – setString
You can use setString to tell Hibernate this parameter date type is String.
String hql = "from Stock s where s.stockCode = :stockCode";
List result = session.createQuery(hql)
.setString("stockCode", "7277")
.list();
Example 3 – setProperties
This feature is great ! You can pass an object into the parameter binding. Hibernate will automatic check the object’s properties and match with the colon parameter.
Stock stock = new Stock();
stock.setStockCode("7277");
String hql = "from Stock s where s.stockCode = :stockCode";
List result = session.createQuery(hql)
.setProperties(stock)
.list();
2. Positional parameters
It’s use question mark (?) to define a named parameter, and you have to set your parameter according to the position sequence. See example…
String hql = "from Stock s where s.stockCode = ? and s.stockName = ?";
List result = session.createQuery(hql)
.setString(0, "7277")
.setParameter(1, "DIALOG")
.list();
This approach is not support the setProperties function. In addition, it’s vulnerable to easy breakage because every change of the position of the bind parameters requires a change to the parameter binding code.
String hql = "from Stock s where s.stockName = ? and s.stockCode = ?";
List result = session.createQuery(hql)
.setParameter(0, "DIALOG")
.setString(1, "7277")
.list();
Conclusion
In Hibernate parameter binding, i would recommend always go for “Named parameters“, as it’s more easy to maintain, and the compiled SQL statement can be reuse (if only bind parameters change) to increase the performance.
About Author
How would you deal with optional query parameters in hibernate?
Hello All,
If any one is looking for how to use IN clause in Hibernate:
ArrayList idList = new ArrayList(); idList.add(3); idList.add(9); idList.add(16); Query query = session.createQuery("from Stock where stockId in (:code)"); query.setParameterList("code", idList); List list = query.list();Hope this helps 🙂
Thanks! It works properly
Hey, it is not adding comma separated list. Its sending as 3916.
Thanks for sharing good info
thanks , this helped me out!
Thanks for sharing your tips.
is Spring Data JPA queries are safe ?
This one is really good
Awesome
Hello All,
I need to achieve this
select empid, name, city, dept from employee where (city, dept) in (:list)
Thanks,
Sammy
Hello All,
I need to achieve this using HQL named query, i know how to do it using criteria any help will be appreciated, right now if i try replacing :str say with the string = (‘city1’, ‘dept1’), (‘city2’, ‘dept2’)
select empid, name, city, dept from employee where (city, dept) in (:str)
It genartes a query liks this
select empid, name, city, dept from employee where (city, dept) in (‘(‘city1’, ‘dept1’), (‘city2’, ‘dept2′)’)
So the extra outer quotes are creating an issue.
Thanks,
Sammy
public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
// elimino tutti caratteri non numerici
String telefono = username.replaceAll(“[^\d]”, “”);
Criterion crit;
if(telefono.length() > 5){
crit = Restrictions.or(
Restrictions.eq(“username”, username),
Restrictions.eq(“email”, username),
Restrictions.like(“phoneNumber”, “%”+telefono+”%”, MatchMode.END));
}else{
crit = Restrictions.or(
Restrictions.eq(“username”, username),
Restrictions.eq(“email”, username));
}
List users = getSession().createCriteria(User.class).add( crit ).getSingleResult();list();
if (users == null || users.isEmpty()) {
throw new UsernameNotFoundException(“user ‘” + username + “‘ not found…”);
} else {
return (UserDetails) users.get(0);
}
}
Why can’t hibernate tell YOU the type of the parameter? You shouldn’t need to tell it – In your example it knows that stockCode is a String.
I don’t mean you personally – I know you didn’t write it 😉 I just don’t follow their rationale here, it’s illogical
I don’t think you can use named parameters when doing an IN() query because the number of items inside the IN() list are variable. In that case the only option is to use positional parameters.
No you can definitely collections for IN’s, and it will translate it into something that works, but if the array or collection is empty then it breaks (which is crappy implementation)
Thanks for the tutorial. It helped me a lot. One request, can you add the code to retrieve values from the list.
EXCELLENT.. TUTORIAL.. THANKS A LOT FOR YOUR SNIPPETS…
Hi,
I’m trying to use named parameters, but I can’t understand why the two following codes give different results:
Query query = session.createQuery(“from Tag t where t.libelle in ( “+ strBuilder.toString() +” )”);
=> gives me the expected result
vs
Query query = session.createQuery(“from Tag t where t.libelle in ( ? )”);
query.setParameter(0, strBuilder.toString());
=> returns me an empty list, as if the parameter was not considered.
Did I do such a big mistake that I can’t see it ?
Thanks
ok, the answer was in a comment from TestConfig:
query.setParameterList("x", strings);must be used for a list (strBuilder was the construction of my parameters separated by comas).
ok, the answer was in a comment from TestConfig:
query.setParameterList("x", strings);must be used for a list (strBuilder was the construction of my parameters separated by comas).
Hi,
Thank you,
I found this is very useful.
hi,in example 1 how the hibernate make the “Named parameters” come true? can one explain the src of hibernate use the colon?
Hi all,
I wanna use the named parameters with CONTAINS like that :
select p from person p where CONTAINS(p.name , :myName) String myName = "Bob Jones"; q.setString("myName", "*" + myName + "*");It doesn’t work, the error :
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2536)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
Caused by: java.sql.SQLException: Erreur de syntaxe près de ‘Jones*’ dans la condition de recherche en texte intégral ‘*Bob Jones*’.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:802)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2533)
… 8 more
I use hibernate 3.6
What should I do?
Thanks!
Thanks man really helpful….
Head shot, exactly what I needed, thanks mate!
Thanks for this helpful example provided!
Just wanted to clarify something since I was burnt by this in the past. I will accept that this may not be the case today.
If your application uses a pre JDBC 3.0 driver or a driver that claims to be JDBC 3.0 compliant (by returning “3.XXX” from the getVersion() method) but in reality isn’t, then it may not support named JDBC parameters. What then happens when you use persistence frameworks such as Hibernate (or even the Spring JDBC template) is the :param gets SUBSTITUTED before it gets passed to the JDBC driver.
In other words, every time you execute the SQL with a different value for the parameter it generates a distinct SQL string which does not afford you the efficiency gained by using a PreparedStatement.
For small applications this may not matter. For large ones is does as I found out to my chagrin. Reverting to positional parameters solved my performance problems.
I just noticed that your webpage content is copied as is at this website : http://gopalakrishnadurga.wordpress.com/2012/04/11/hibernate-query-examples-hql/. Just thought of notifying you.
Your website was posted that topic in 2012. But here in this site it is in 2010. That site copied the information from this. Please try to encourage this website guy.. He is providing important information.