Hibernate parameter binding examples

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 the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
dougal
Guest
dougal

How would you deal with optional query parameters in hibernate?

Eder Weiß
Guest
Eder Weiß

Awesome

sammy
Guest
sammy

Hello All,

I need to achieve this

select empid, name, city, dept from employee where (city, dept) in (:list)

Thanks,
Sammy

sammy
Guest
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

Matteo Manili
Guest
Matteo Manili
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); } }
Thonk
Guest
Thonk

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

Philip Tellis
Guest
Philip Tellis

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.

Thonk
Guest
Thonk

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)

santhosh
Guest
santhosh

Thanks for the tutorial. It helped me a lot. One request, can you add the code to retrieve values from the list.

BSG
Guest
BSG

EXCELLENT.. TUTORIAL.. THANKS A LOT FOR YOUR SNIPPETS…

Eusebe
Guest
Eusebe

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

Eusebe
Guest
Eusebe

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).

Eusebe
Guest
Eusebe

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).

sarju
Guest
sarju

Hi,
Thank you,
I found this is very useful.

haiertashu
Guest
haiertashu

hi,in example 1 how the hibernate make the “Named parameters” come true? can one explain the src of hibernate use the colon?

posygary
Guest
posygary

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!

Parthi
Guest
Parthi

Thanks man really helpful….

TestConfig
Guest
TestConfig
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 :)
nigel
Guest
nigel

thanks , this helped me out!

udaybhaskar
Guest
udaybhaskar

Thanks for sharing good info

Aarti Jangid
Guest
Aarti Jangid

Hey, it is not adding comma separated list. Its sending as 3916.

wellwisher
Guest
wellwisher

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 well wisher
Guest
your well wisher

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.

Stef
Guest
Stef

Head shot, exactly what I needed, thanks mate!

irobertyu
Guest
irobertyu

Thanks for this helpful example provided!

Arby
Guest
Arby
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… Read more »
trackback
Hibernate Tutorials | Tutorials

[…] Hibernate parameter binding examples Bind the parameter into HQL with “Named parameters” and “Positional parameters” methods. […]