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?

score 6 · Answer 1 · answered Oct 28 '11 at 09:59

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

JB Nizet · Accepted Answer · 2011-10-28T10:23:03.593

5

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

edited Oct 28 '11 at 10:23

answered Oct 28 '11 at 09:59

JB Nizet

678,734
91
1,224
1,255

1

Agreed. Just to add one small (but quite nice) feature: you can use the NEW operator in JPQL query, like this: SELECT NEW com.Result(t1.col1, t2.col5) FROM T1 t1 join t1.t2 t2 If you have a Result(String, String) constructor, it will be invoked to create your new Result object which doesn't need to be an Entity itself. – Piotr Nowicki Oct 28 '11 at 10:17
Yeah, I know that, but find it horrible. It doesn't allow refactoring, and only saves two or three trivial lines of code. Plus it makes debugging harder. – JB Nizet Oct 28 '11 at 10:20
what do you mean by 'doesn't allow refactoring'? If the parameters or their types change than neither using or not using the NEW construct will save you. I think it's a matter of a trade-off just like with Criteria API - it's much more refactoring-friendly but personally it's harder to read for me. – Piotr Nowicki Oct 28 '11 at 10:27
3

If I add an argument to my constructor, the code above will stop compiling, and I'll know I have to fix the query. With new in the JPQL query, I'll just have an exception at runtime. If I switch the order of the arguments in the constructor, my IDE will switch all the invocations. But not if the new is in the JPQL. And if my query returns some wrong type, I'll have a clean ClassCastException, instead of some obscure "can't find constructor" exception. And I'll be able to inspect the result of the query to see what's wrong. – JB Nizet Oct 28 '11 at 10:33
This wasn't what the question asked for. It was asking for a solution to a native query. – Bill Rosmus Jul 12 '12 at 21:58
@BillR: yet the OP accepted the answer. When someone asks how to drive a nail with a screwdriver, the best answer is: don't do it like this, use a hammer. – JB Nizet Jul 12 '12 at 22:05
You provided the typical ORM pedantic answer. I don't know if it was the best answer. I don't see ORM as the be all and end all. He could have accepted it because he couldn't find anyone who actually wanted to answer the question and had to settle for second best answer. I have the same question and it wasn't answered here. It would have been nice to see it answered here. There are million other ORM pedantic posts around that say the same thing. I refuse to drink the coolaid however and will use what I think is the best solution not just follow the group think. In 5 years ORM will = XML. – Bill Rosmus Jul 13 '12 at 00:58
@BillR: The question is tagged JPA and Hibernate. The OP *chose* to use JPA and Hibernate. If you don't think ORMs are a good thing, then don't use JPA and Hibernate. Nothing forces you to use an ORM. But if you choose to use an ORM, you'll have to play its rules. Just because you don't like ORMs doesn't mean my answer is incorrect. – JB Nizet Jul 13 '12 at 05:58
Just because it says "hibernate" and JPA doesn't mean you have to map everything to entities using ORM. People use hibernate to create a native queries too. If 'ORM only' was best do you think the Hibernate devs would have bothered to implement "createSQLQuery" (their non-JPA version of native queries)? Even they recognize only ever using ORM might be an antipattern. But everyone wants to use the same square ORM peg even if there is a round hole. Think about it, Hibernate supports native queries. Why? ... So when someone asks how to solve a problem with a native query... That's all I'll say. – Bill Rosmus Jul 13 '12 at 19:17

score 1 · Answer 3 · answered Oct 28 '11 at 10:47

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)".

score 0 · Answer 4 · answered Oct 28 '11 at 10:00

0

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.

answered Oct 28 '11 at 10:00

rsp

23,135
6
55
69

How can I avoid the creation of superfluous entities?

4 Answers4

Linked