Hibernate Named Query copy-pasting

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>

开发者_JAVA技巧

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.

---

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();
}

---

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?

---

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.

---

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.