-1

I use this query on mysql (every record within tbl1 has a list of records within tbl2):

select tbl1.id, tbl2.id from parenttable1 tbl1 join childtable2 tbl2 on tbl1.id = tbl2.tbl1Id

and the result is true but when I use this as a Native Query (javax.persistence.EntityManager#createNativeQuery()) within my application the tbl2.id is repeated. what is the problem?

the result is something like this:

id   id
11   1
11   1
22   3
33   4
44   5
44   5
44   5

but i expect:

id   id
11   1
11   2
22   3
33   4
44   5
44   6
44   7

my code is something like this:

List<MyDTO> foundList = (List<MyDTO>) entityManager.createNativeQuery("the query above", MyDTO.class).getResultList();

nazila
  • 93
  • 1
  • 1
  • 10
  • what do you mean by native query? – John Woo Dec 08 '12 at 07:29
  • I mean javax.persistence.EntityManager#createNativeQuery() – nazila Dec 08 '12 at 07:35
  • How can the result of such a query be `true`? Show us your code, your data, the result you're expecting, and the actual result you get. – JB Nizet Dec 08 '12 at 08:48
  • what's wrong with this?! the result is the id of table 1's records and the id of table 2's records (the number of result is the number of records within table 2 for example if there are 5 parent records within table1 and 10 child records within table2 the result will contain 10 records so the tbl1.id could be repeated but the tbl2.id should be unique) – nazila Dec 08 '12 at 09:30
  • If the query returns that, it probably means you actually have duplicated records in the child table, or that your code, which you neglected to show us, does something weird. Make sure there is a unique or PK constraint on child_table.id. You could simply add the distinct keyword to your query, but it would hide the problem rather than solving it. – JB Nizet Dec 08 '12 at 09:53
  • the query returns the second but JPA native query within application returns first result. child_table.id is PK and tbl2.tbl1Id is foreign key so each record within tbl1 could have a list of records within tbl2. there isn't any specific code! just put the query to createNativeQuery() as a parameter! also the distinct doesn't anything! – nazila Dec 08 '12 at 10:01
  • Let us judge if there's nothing specific. Show us the code used to create, execute and show the results of the query. – JB Nizet Dec 08 '12 at 10:17

2 Answers2

2

I don't see how JPA could map the results of a query returning two columns with the same name to a DTO if you don't tell how these two columns should be mapped. And you don't even show what the DTO looks like.

Execute the following code, and everything should be fine:

List<Object[]> list = (List<Object[]>) em.createNativeQuery(sql).getResultList();
List<MyDTO> result = new ArrayList<MyDTO>(list.size());
for (Object[] row : list) {
    result.add(new MyDTO((Long) row[0], (Long) row[1]));
}

And next time you're being asked to show your code, do it instead of thinking that it's perfect or obvious and that the bug necessarily comes from somewhere else. 99.999% of the time, the bug is in your code, and not in the code of a library used and tested by millions of developers.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
0

Thanks a lot for you inspirations. The problem was that @Id existed on id field of parent table instead of id field of child table (within DTO)!

nazila
  • 93
  • 1
  • 1
  • 10