Hibernate SQLQuery, how to get arrays and rows objects?

Question

Hibernate 3.6 & postgresql 9.1. Using SQLQuery how to get result array data (array of Long - assistants, array of rows of "Text, Long, Timestamp" - accounts)?

    limit = 10000;
    final SQLQuery sqlQuery = getSession().createSQLQuery("SELECT id, name, ts, " +
            " array(SELECT assistant_id FROM user_assistant WHERE p_id=pr.id ORDER BY assistant_id) AS accounts," +
            " array(SELECT row(type,uid,ts) FROM user_account WHERE p_id=pr.id ORDER BY type) AS accs," +
            " FROM profile pr WHERE ts > ? ORDER BY ts LIMIT " + limit);

The most of DAO functions written with hibernate Entities & annotations. But for a few statistics tasks easier to work with HQL or even SQL. As opposed to pure JDBC in hibernateSQL working with arrays is not so intuitive.

JDBC could be a solution, but I haven't found any way to get JDBC Statement from Hibernate Session or Connection. ResultTransformer doesn't help also, fails with:

    org.hibernate.MappingException: No Dialect mapping for JDBC type: 2003

duckduckgo · Accepted Answer · 2012-04-30 08:31:49Z

4

By referring this

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html

What you can do something like;

session.createSQLQuery("Your custom query")
 .addScalar("field1", Hibernate.STRING)
 .addScalar("field2", Hibernate.STRING)
 .addScalar("field3", Hibernate.STRING)

and then

 for(Object rows : query.list()){
    Object[] row = (Object[]) rows;
    String field1 = row[0] // contains field1
    String field2 = row[1]
    ..
    ..
 }

edited Apr 30, 2012 at 8:31

answered Apr 30, 2012 at 8:26

duckduckgo

1,2891 gold badge19 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hibernate SQLQuery, how to get arrays and rows objects?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related