Hibernate SQLQuery how to get arrays and string objects?
Hibernate 3.6 and postgresql 9.1. Using SQLQuery to get results array data (Long-assistants array, "Text, Long, Timestamp" string array - 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);
Most DAO functions written with entity hibernation and annotations. But for multiple statistics tasks, it is easier to work with HQL or even SQL. Unlike pure JDBC in hibernateSQL, working with arrays is not that intuitive.
JDBC might be a solution, but I haven't found a way to get the JDBC expression from a Hibernate session or connection. ResultTransformer doesn't help either:
org.hibernate.MappingException: No Dialect mapping for JDBC type: 2003
+3
source to share
1 answer
We send this
http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html
What can you do for example:
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]
..
..
}
+4
source to share