Get SQL String substituted with parameters using java

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…

String hql = "SELECT * FROM USER WHERE NAME= :userName AND SURNAME= :surName";
Query query = session.createQuery(hql);
query.setParameter("userName ", "userName");
query.setParameter("surName", "SurName");
List results = query.list();

An object-oriented representation of a Hibernate query. A Query instance is obtained by calling Session.createQuery(). This interface exposes some extra functionality beyond that provided by Session.iterate() and Session.find():

a particular page of the result set may be selected by calling setMaxResults(), setFirstResult() named query parameters may be used the results may be returned as an instance of ScrollableResults

Named query parameters are tokens of the form :name in the query string. A value is bound to the integer parameter :foo by calling

setParameter("foo", foo, Hibernate.INTEGER);

for example. A name may appear multiple times in the query string.

JDBC-style ? parameters are also supported. To bind a value to a JDBC-style parameter use a set method that accepts an int positional argument (numbered from zero, contrary to JDBC).

You may not mix and match JDBC-style parameters and named parameters in the same query.

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… Java

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

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.