hibernate 2 min read by 30 comments

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.

30 comments on “Hibernate parameter binding examples”

  3. How would you deal with optional query parameters in hibernate?

    Reply

  5. Hello All,

    I need to achieve this

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

    Thanks,
    Sammy

    Reply

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

      Reply

      1. 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);
        }
        }

        Reply

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

    Reply

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

    Reply

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

      Reply

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

    Reply

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

    Reply

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

    Reply

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

      Reply

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

      Reply

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

    Reply

  13. 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!

    Reply

  15. 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 🙂

    Reply

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

      Reply

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

      Reply

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

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *