An Example for Hibernate Named Query with Aggregate Function

1. Does entity fit here?

I don't think so, we'd better have a VO or DTO to represent.

2. hbm.xml

<sql-query name="getCustomerInfo">SELECT baseT.customer_id AS customerId,        phone,         currency,        account_type AS accountType,        active,        customer_type AS customerType,        follow_percentages AS followBetPercentages,        ( Sum(IF(cust_trans_id IS NULL, 0, IF(accnt_trans_type = 'Credit', 0 - Round(amount, 2),                                           IF(accnt_trans_type = 'Debit', Round(amount, 2), 0)))) ) AS balance FROM   (SELECT customerT.customer_id,                customerT.phone,                customerT.credit_limit,                customerT.currency,                customerT.account_type,                customerT.active,                customerT.customer_type,                CONVERT(Ifnull(customerT.follow_percentages, '') USING utf8) AS                follow_percentages         FROM   (SELECT customer.customer_id,                        phone,                        credit_limit,                        currency,                        customer.active,                        customer_type,                        account_type,                        Group_concat(followed_perc SEPARATOR '_') AS                        follow_percentages               ...GROUP  BY baseT.customer_id </sql-query>

Noteworthy:

(1) For the <sql-query>, just configure the name and the SQL sentence, simple enough,don't have to set the <return> series.

3. DAO

public CustomerInfo getCustomerInfo(int customerId) {Query q = currentSession().getNamedQuery("getCustomerInfo");String sql = q.getQueryString();SQLQuery sqlQuery = currentSession().createSQLQuery(sql);sqlQuery.addScalar("customerId", StandardBasicTypes.INTEGER).addScalar("phone", StandardBasicTypes.STRING).addScalar("currency", StandardBasicTypes.STRING).addScalar("accountType", StandardBasicTypes.STRING).addScalar("active", StandardBasicTypes.STRING).addScalar("customerType", StandardBasicTypes.STRING).addScalar("followBetPercentages", StandardBasicTypes.STRING).addScalar("balance", StandardBasicTypes.DOUBLE);sqlQuery.setInteger("customerId", customerId);sqlQuery.setResultTransformer(Transformers.aliasToBean(CustomerInfo.class));return (CustomerInfo)sqlQuery.uniqueResult();}

Noteworthy:

(1) This DAO is the most error-prone part of this example due to inadequate official documentation and lack of online resource. Just feel it!

4. VO

public class CustomerInfo {private int customerId;private String accountType;private String currency;private String active;private String customerType;private String followBetPercentages;private String phone;private double balance; //setters and getters...}

5. Integration Test with JUnit4

@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations = "classpath*:applicationContext-persistence.xml")@ActiveProfiles("test")public class NamedQueriesDaoTest {@Autowiredprivate INamedQueriesDao namedQueriesDao;/** * Beware this is an integration test. *///@Ignore@Testpublic void testGetCustomerInfo() {CustomerInfo customerInfo = namedQueriesDao.getCustomerInfo(2);assertEquals(2, customerInfo.getCustomerId());}}