How to simulate NVL in HQL
The equivalent to the nvl command in HQL is the coalesce command. coalesce(a,b) will return a if a is not null, otherwise b. So you would want something on the lines of: from Table where col1 = coalesce(:par1, ‘asdf’)
hql
Hibernate HQL Query : How to set a Collection as a named parameter of a Query?
Use Query.setParameterList(), Javadoc here. There are four variants to pick from.
NHibernate How do I query against an IList property?
So because of limitations of the Criteria API, I decided to bend my domain classes to fit. I created an entity class for the Tag. I couldn’t even create it as a value object. It had to have its own id. I feel dirty now. But being able to construct a dynamic query without resorting … Read more
Hibernate Named Query Order By parameter
Not supported, input parameters are only allowed in the WHERE and HAVING clauses and you cannot use parameters for the ORDER BY clause. Or if I rephrase, you can’t use parameters for columns, only values. So, either: Have as much named queries as possible sort orders Concatenate the ordering string to the query string Use … Read more
HQL Hibernate INNER JOIN
Joins can only be used when there is an association between entities. Your Employee entity should not have a field named id_team, of type int, mapped to a column. It should have a ManyToOne association with the Team entity, mapped as a JoinColumn: @ManyToOne @JoinColumn(name=”ID_TEAM”) private Team team; Then, the following query will work flawlessly: … Read more
IN-clause in HQL or Java Persistence Query Language
Are you using Hibernate’s Query object, or JPA? For JPA, it should work fine: String jpql = “from A where name in (:names)”; Query q = em.createQuery(jpql); q.setParameter(“names”, l); For Hibernate’s, you’ll need to use the setParameterList: String hql = “from A where name in (:names)”; Query q = s.createQuery(hql); q.setParameterList(“names”, l);
Performing Date/Time Math In HQL?
Why do you need to do it in the query? Why not just handle it in the java code. for example: From RandomThing Where randomTime is not null and randomTime >= :currentTimestamp and randomTime <= :maxTimestamp And then just set the parameters.
How to escape reserved words in Hibernate’s HQL
You could achieve it by a workaround using your custom “alias to map” transformer, so your code would change to something like this Query q = mySession.createQuery( “SELECT u.id AS id, u.name AS text, u AS obj FROM User u”) .setResultTransformer( AliasToMapTransformer.renameAlias(“obj”, “object”).build() ); And then using this class: public class AliasToMapTransformer extends BasicTransformerAdapter { … Read more
How can you call custom database functions with Hibernate?
If you want to use your custom function in HQL, you’ll need to define it in appropriate Dialect Take a look at PostgreSQLDialect (or any other, really) source, and you’ll see a bunch of registerFunction() calls. You’ll need to add one more 🙂 – for your own custom function. You’ll then have to specify your … Read more
How do you create a Distinct query in HQL
Here’s a snippet of hql that we use. (Names have been changed to protect identities) String queryString = “select distinct f from Foo f inner join foo.bars as b” + ” where f.creationDate >= ? and f.creationDate < ? and b.bar = ?”; return getHibernateTemplate().find(queryString, new Object[] {startDate, endDate, bar});