How can I avoid the creation of superfluous entities?

Question

In my current project I need to perform a few native queries which pick some fields from tables joined in the query e.g.:

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

I tried to store 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' isn't an entity which is probably required to map the columns into the object. I also tried to repeat the @Column declarations in the result class.

My question is how can I declare this without having to create the entites represented as tables in my DB?

Answer 1

Alas, I don't see a way to do it in JPA. However, you can do it with the hibernate Query object. to obtain it use:

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

And then set a result transformer. See here:

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

Answer 2

If you're using JPA/Hibernate to perform SQL queries, then you're using the wrong tool. Hibernate is an ORM, and you're supposed to map tables to entities. That's the whole point of JPA. I you just want to perform SQL queries, use JDBC (and Spring's JdbcTemplate for example)

Once table1 and table2 are mapped to entities (let's call these entities T1 and T2), you won't need these SQL queries anymore, because JPQL is able to select only some fields of the entities. Your query could the look like this (depending on the association between t1 and t2):

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

And you would just have to iterate over the result (a list of Object[]) to build your results (which is a DTO and 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]));
}