Java: JPQL select statement

Question

select x from X x where x.a.id = :a_id --> Always 0 objects selected

Why does the above JPQL statement not work, but the one below work?

select a from A a where a.id = :a_id --> a_obj
select x from X x where x.a = :a_obj --> Always correct number of objects selected

Neither query throws an exception during execution, but a different number of results are obtained.

Thanks

Update

I tried the following queries by using joins:
select x from X x, x.a a where x.a.id = :a_id --> TopLink exception for unexpected token

and this: select x from X x JOIN x.a a where a.id = :a_id --> Always correct number of objects selected

With the latter query, I have solved the initial problem at hand. However, now I have got two queries which should work, but for some reason don't.

select x from X x where x.a.id = :a_id --> Always 0 objects selected
select x from X x, x.a a where x.a.id = :a_id --> TopLink exception for unexpected token

Has anyone else encountered similar behaviour?

which JPA provider are you using?

mdma
– mdma

2010-05-24 23:26:29 +00:00
Commented May 24, 2010 at 23:26 — mdma
– mdma, Commented May 24, 2010 at 23:26
@mdma : TopLink/Java DB, on Glassfish 2.1.1

bguiz
– bguiz

2010-05-24 23:34:40 +00:00
Commented May 24, 2010 at 23:34 — bguiz
– bguiz, Commented May 24, 2010 at 23:34

Pascal Thivent · Accepted Answer · 2010-05-24 23:38:15Z

1

With the following entity for X

@Entity
public class EntityX {

    @Id @GeneratedValue
    private Long id;

    @OneToOne
    private EntityA a;

    // ...
}

And this one for A:

@Entity
public class EntityA {
    @Id @GeneratedValue
    private Long id;

   //...
}

The following JPQL query:

from EntityX x where x.a.id = :id

Generates the following SQL:

select
  entityx0_.id as id282_,
  entityx0_.a_id as a2_282_ 
 from
  EntityX entityx0_ 
 where
  entityx0_.a_id=?

It simply works and returns as many results as expected.

Tested with Hibernate (and EclipseLink). If this is not representative of your case, please add more details.

answered May 24, 2010 at 23:38

Pascal Thivent

572k140 gold badges1.1k silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bguiz Over a year ago

Thanks for the answer, you have indeed understood my question correctly, however, I am puzzled by the different result I am getting. Could you please point me in the direction of how to see what SQL is generated, like what you have done? (I'm using Toplink/Java DB on Glassfish 2.1.1, with Netbeans IDE, if it makes a difference)

Pascal Thivent Over a year ago

I don't use TopLink but I think that the toplink.logging.level.sql property should do the trick. Have a look at TopLink JPA Extensions for Logging for details.

mdma · Accepted Answer · 2010-05-24 23:37:25Z

0

I think you have to also bring in entity a in the first example so that it's attributes are visible.

Something like

select x from X x join fetch x.a where x.a.id = :a_id

(I don't use JPA, I stick to HQL, so this is untested, unproven and comes without a money-back guarantee.)

answered May 24, 2010 at 23:37

mdma

57.9k12 gold badges97 silver badges128 bronze badges

2 Comments

mdma Over a year ago

@Pascal - Agreed. I wouldn't have expected it to be necessary either, knowing Hibernate. I'm just pushing for another datapoint to see if we can get TopLink to deliver what's expected.

mdma Over a year ago

@Pascal - thanks for the clarification of what you sed - that's gentler wording.

Collectives™ on Stack Overflow

Java: JPQL select statement

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related