Hibernate Named Query copy-pasting

Question

In my .hbm.xml there are two queries. The first one retrieves number of records in the table:

<query name="Airframe.SearchCount"><![CDATA[
    select
        count(*)
    from
        AirframeBean as a inner join
        a.manufacturer as m
    where
        m.name like :manufacturer and
        a.description like :description and
        ((a.percentSize <= :sizeMax and
        a.percentSize >= :sizeMin) or
        a.percentSize is null) and
        ((a.wingSpanInches <= :spanMax and
        a.wingSpanInches >= :spanMin) or
        a.wingSpanInches is null) and
        ((a.recommendedAuwMinLbs <= :auwMax and
        a.recommendedAuwMaxLbs >= :auwMin) or
        a.recommendedAuwMaxLbs is null)
]]></query>

And the second one gets data page-by-page using offset and limit:

<query name="Airframe.SearchData"><![CDATA[
    select
        a
    from
        AirframeBean as a inner join
        a.manufacturer as m
    where
        m.name like :manufacturer and
        a.description like :description and
        ((a.percentSize <= :sizeMax and
        a.percentSize >= :sizeMin) or
        a.percentSize is null) and
        ((a.wingSpanInches <= :spanMax and
        a.wingSpanInches >= :spanMin) or
        a.wingSpanInches is null) and
        ((a.recommendedAuwMinLbs <= :auwMax and
        a.recommendedAuwMaxLbs >= :auwMin) or
        a.recommendedAuwMaxLbs is null)
]]></query>

The queries are almost identical. the only one difference is that the first starts with select count(*) and the second one starts with select a. Is there a way to avoid copy-pasting?

Update The main problem is that I need Hibernate to validate schema, mappings and HQL queries at startup.

limc · Accepted Answer · 2011-02-19T16:10:49.700

You can do something like this:-

Named Query

#SELECTOR# is the placeholder that your java code will have to replace.

<query name="Airframe"><![CDATA[
    select
        #SELECTOR#
    from
        AirframeBean as a inner join
        a.manufacturer as m
    where
        m.name like :manufacturer and
        a.description like :description and
        ((a.percentSize <= :sizeMax and
        a.percentSize >= :sizeMin) or
        a.percentSize is null) and
        ((a.wingSpanInches <= :spanMax and
        a.wingSpanInches >= :spanMin) or
        a.wingSpanInches is null) and
        ((a.recommendedAuwMinLbs <= :auwMax and
        a.recommendedAuwMaxLbs >= :auwMin) or
        a.recommendedAuwMaxLbs is null)
]]></query>

Hibernate Code

public Long searchCount() {
    String sql = getQueryString("Airframe").replace("#SELECTOR#", "count(*)");
    return (Long) session.createSQLQuery(sql, args...).uniqueResult();
}

@SuppressWarnings("unchecked")
public List<AirframeBean> getAirframeBeans() {
    String sql = getQueryString("Airframe").replace("#SELECTOR#", "a");
    return session.createSQLQuery(sql, args...).list();
}

private String getQueryString(String namedQuery) {
    return session.getNamedQuery(namedQuery).getQueryString();
}

@Antonio: This will do what you want with minimal refactoring in your existing code. Instead of creating two queries, you will create just one. — limc, Feb 19 '11 at 14:59

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

One idea is if you decide to upgrade into programmatic / annotation driven persistance. Once you have your queries as either java HQL string queries or DetachedStatement promoting reuse is a breeze. Consider the scenario of using HQL where you could achieve the desired effect by:

String hql = ... 
hql = hql.replace("count(*)", "a");

Alternatively:

String hql = "from ...";
String q1 = "select count(*) " + hql;
String q2 = "select a " + hql;

See also How this SQL Query in hbm.xml file can be written in source code?

edited May 23 '17 at 12:15

Community

1
1

answered Feb 19 '11 at 13:12

Johan Sjöberg

43,692
19
120
139

If I "upgrade" to programmatic persistance I will loose ability to verify my schema and queries at application launch, but I need this very much. – Antonio Feb 19 '11 at 13:25
1

Isn't this why unit tests exist? I hope you find the answer your looking for :) – Johan Sjöberg Feb 19 '11 at 13:31
@Antonio: If you keep one version as a named query then that will still be checked at launch. The code to change the first query to the second will just be hql.replace("count(*)", "a") and you know that is a valid substitution. – Russ Hayward Feb 19 '11 at 14:05
1

It does, given that `hql` is just `java.lang.String` as illustrated in the [session api](http://docs.jboss.org/hibernate/core/3.5/api/org/hibernate/Session.html). – Johan Sjöberg Feb 19 '11 at 14:52
Indeed! I can use Query.getQueryString(), than replace(...). Could you please update your answer? – Antonio Feb 23 '11 at 08:45

score 0 · Answer 3 · answered Feb 19 '11 at 14:42

0

An other idea is to use Criteria API to manage pagination instead of named query. With hibernate Criteria API you can use method setFirstResult() and setMaxResult() to define the data group to load for the table.

answered Feb 19 '11 at 14:42

Irene Audrito

42
2

score -1 · Answer 4 · answered Feb 19 '11 at 15:15

Criteria API is a different way to create query with hibernate, expetially usefull for pagination.

An example of code using is:

      
  Criteria crit = session.createCriteria(AirframeBean.class);
  crit.add(Restrictions.like("name", "manufacturer"));
  crit.add(Restrictions.like("description", "yourDescription"));
  crit.add(Restrictions.between("percentSize", "sizeMin", "sizeMax"));

  ecc....

  crit.setMaxResults(maxResults);
  crit.setFirstResult(firstResult);
  List result = crit.list()

For add a condition AND or OR you can use: Restrictions.disjunction(); or Restrictions.conjunction();

In this way you use only java code and no xml file to manage and compose your dinamic query.

Hibernate Named Query copy-pasting

4 Answers4