0

I´m in a situation where I have two entities, let´s say A and B, in B I have a relationship ManyToOne with A.But in A I dont have the oneToMany. I´m using Spring-Data repositories, and I was wondering If would be possible make a subQuery using Query annotation. So far I see some example of people using CriteriaQuery, but always with JOIN example, and in my case I need the other way around. I need to find all A´s if their id´s are not in B. Any good and elegant solutions?.

paul
  • 12,873
  • 23
  • 91
  • 153
  • You can use the classic fallback way, in a custom DAO class. – Ralph Jan 29 '14 at 10:32
  • I was looking for a Spring data solution, but thanks – paul Jan 29 '14 at 10:34
  • I only want to give you the pointer to: http://stackoverflow.com/questions/11880924/how-to-add-custom-method-to-spring-data-jpa - if you know this, than everything is right. – Ralph Jan 29 '14 at 10:41
  • Yeah it´s just what I´m tryinng but give me some error becuase the syntax. I have this so far, would be possible?.. @Query(value="SELECT * FROM publication_request WHERE publication_date < :publicationDate AND id NOT IN(SELECT publication_request_id FROM enotification_log)") – paul Jan 29 '14 at 12:00

2 Answers2

1

Just put @Query on your interface methods and write your JPQL there. Examples here: http://docs.oracle.com/javaee/7/tutorial/doc/persistence-querylanguage005.htm#BNBVL

e.g. SELECT DISTINCT a FROM A a WHERE NOT EXISTS (SELECT b FROM B b WHERE <<put your condition here>>)

Matt Byrne
  • 4,908
  • 3
  • 35
  • 52
  • can I use this syntax in Spring data repository?. I will try. – paul Jan 29 '14 at 11:01
  • Yep - in the repository – Matt Byrne Jan 29 '14 at 11:06
  • Sorry but I could not make it works. In my case I canot use the not exist because I dont have any condition. I just want make the typical find if id not exist in another table. @Query(value="SELECT * FROM publication_request WHERE publication_date < :publicationDate AND id NOT IN(SELECT publication_request_id FROM enotification_log)") This is what I have so far – paul Jan 29 '14 at 11:13
  • JPQL isn't SQL. I assumed from you question that both `publication_request` and `enotification_log` have mapped entities ("A" and "B" in your question). Your query needs to use those and not raw SQL. Then read up on JPQL - the solution I gave you should work. I gave a link to Java 6 JPQL though so here's Java 7: http://docs.oracle.com/javaee/7/tutorial/doc/persistence-querylanguage.htm – Matt Byrne Jan 29 '14 at 19:10
  • Yep mu fault, I was using by mistake SQL table names instead entity names :( – paul Jan 31 '14 at 08:41
  • Glad you worked it out - can you please accept the answer if it worked for you. – Matt Byrne Jan 31 '14 at 10:30
0

You can use NamedNativeQuery of JPA or custom pojo class (with constructor to populate all your required field) but in this case you will get array of object and you have to fech your records from object array.

Sai prateek
  • 11,842
  • 9
  • 51
  • 66