How can I avoid creating extra objects?

Question

In my current project, I need to perform some of my own queries, which select some fields from tables joined into a query, for example:

SELECT t1.col1, t2.col5 FROM t1 JOIN t2 ON t2.id = t1.t2_id 

I tried to save them in a class like

 class Result { String t1_col1; String t2_col5; } 

using

 Query q = entityManager.createNativeQuery( "THE SQL SELECT" , Result.class ); 

JPA now complains ("uknown entity: result") that the class result is not an entity, which is likely to be required to map columns to an object. I also tried repeating @Column in the result class.

My question is, how can I declare this without the need to create entites presented as tables in my DB?

Answer 1

If you use JPA / Hibernate to execute SQL queries, you are using the wrong tool. Hibernate is an ORM, and you must display tables for objects. This is the whole point of JPA. I just want to execute SQL queries, use JDBC (and Spring JdbcTemplate, for example)

After tables1 and table2 are compared with entities (let them access these objects T1 and T2), you will no longer need these SQL queries, since JPQL can select only some fields of objects. Your request may look like this (depending on the relationship between t1 and t2):

 select t1.col1, t2.col5 from T1 t1 join t1.t2 t2 

And you just need to iterate over the result (Object [] list) to create your results (which is a DTO, not a mapped entity):

 List<Object[]> rows = (List<Object[]>) query.list(); List<Result> listOfResults = new ArrayList<Result>(rows.size); for (Object[] row : rows) { listOfResults.add(new Result((String) row[0], (String) row[1])); } 

Answer 2

Alas, I see no way to do this in JPA. However, you can do this with the hibernate Query object. to use it:

 org.hibernate.Query query = q.unwrap(org.hibernate.Query.class); 

And then install the result transformer. Look here :

 query.setResultTransformer(Transformers.aliasToBean(Result.class)); 

Answer 3

I can run this query (with slight modifications) in the DataNucleus JPA, and it works just fine, as per the JPA specification.

 SELECT t1.col1 AS t1_col1, t2.col5 AS t2_col5 FROM t1 JOIN t2 ON t2.id = t1.t2_id 

i. make the returned columns rows with field names in the result class. The JPA specification does not say that the result class should be an Entity class; it just says "class of the resulting instance (s)".

Answer 4

You may be able to define a VIEW that returns the concatenated columns needed from its queries and use the view name for your data class.