CODEWITHSUNDEEP
javahibernatejpa
stacker
stacker

Reputation: 68972

How can I avoid the creation of superfluous entities?

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?

Upvotes: 6

Views: 3153

Answers (4)

DataNucleus
DataNucleus

Reputation: 15577

I can run that query (with a slight change) in DataNucleus JPA and it works fine, as it should per the JPA spec.

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

i.e make the return columns line up with the field names in the result class. The JPA spec does not say that the result class has to be an Entity class; it simply says "the class of the resulting instance(s)".

Upvotes: 1

JB Nizet
JB Nizet

Reputation: 691943

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]));
}

Upvotes: 5

rsp
rsp

Reputation: 23373

You might get away with defining a VIEW which returns the joined columns needed from its queries and use the view name for your dataholder class.

Upvotes: 0

Bozho
Bozho

Reputation: 597234

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));

Upvotes: 6

Related Questions