CODEWITHSUNDEEP
javaoracle-databasehibernateejb-3.0ora-01722
Nuno Furtado
Nuno Furtado

Reputation: 4568

EJB3 Query + in clause

I need to use an IN clause like the following SQL 

SELECT * FROM tableA WHERE colA in (1, 2,3)

How can i do this in EJBQL?

i've tryed the following, which obviously failed:

SELECT tab FROM tableA tab WHERE tab.colA in (:colValues)

then in java i did 

query.setParameter("colValues","1,2,3")

that gives me the following exception

org.apache.openjpa.lib.jdbc.ReportingSQLException: ORA-01722: invalid number

Upvotes: 1

Views: 3039

Answers (2)

mR_fr0g
mR_fr0g

Reputation: 8722

The problem is when you use :parameter_name in an sql fragment and then call query. setParamter("parameter_name", values) it is expected that values should be some sort of collection. Try this.

List<Integer> values = new ArrayList<Integer>();
values.add(1);
values.add(2);
values.add(3);

query.setParameter("colValues", values);

Upvotes: 5

KevMo
KevMo

Reputation: 5610

It's been a long while since I've done EJBQL, but this should work: 

  SELECT OBJECT(t)
       FROM Tab AS t
       WHERE t.colA IN (:colValues)

Also, you may need to set colValues as an array of numbers rather than a string

Upvotes: 1

Related Questions