Multiple select using JPA

Question

I use JPA on my JEE6 application running on Glassfish.

I want to select multiple records from a table. Let's say each record has a column named "serialnumber" And I have a dynamic list of serial numbers and from table I want to select the records with those serial numbers.

Is there a way to this without using a for loop?

now you sure get that you should use IN clause ;) – MoienGK May 08 '13 at 12:41 — MoienGK, May 08 '13 at 12:41

score 3 · Answer 1 · answered May 08 '13 at 12:37

3

You can use IN clause which takes list as a parameter

Ex

select DISTINCT obj from Obj  where obj.number IN :numbersList

See Here for further reference.

answered May 08 '13 at 12:37

Suresh Atta

120,458
37
198
307

pedjaradenkovic · Accepted Answer · 2013-05-08T12:42:09.710

2

You can write NamedQuery in JPQL which selects all entities with given serial numbers using IN statement (just like in plain SQL)

Something like:

SELECT i FROM Item i WHERE i.serialNumber IN :serialNumbers

And then in DAO layer method you pass array of serial numbers to the query:

query.setParameter('serialNumbers', serials);

edited May 08 '13 at 12:42

answered May 08 '13 at 12:37

pedjaradenkovic

241
1
9

thank you, and what if I need something multiple; like a list of records where serialnumber is blah and status is blah – Spring May 08 '13 at 12:54
1

then you extend WHERE statement with: WHERE i.serialNumber IN :serialNumbers AND i.status = :status – pedjaradenkovic May 08 '13 at 12:56

score 1 · Answer 3 · answered May 08 '13 at 12:37

1

You can do it with JPQL and IN clause:

select item from Item item where item.serial IN :serials

answered May 08 '13 at 12:37

Michal Borek

4,584
2
30
40

Multiple select using JPA

3 Answers3