supplying a collection as a parameter to a NamedQuery

We're using JPA and I'm trying to find an example of a NamedQuery that accepts a collection, e.g:
"select * from INVOICE where invoice_type in :types"

so when calling the Query I'd expect to pass in query.setParameter("types", myTypes);

where 'myTypes' is a List<InvoiceType>

this doesnt seem to work as outlined here; can someone tell me how to do this?
Who is Participating?
Ajay-SinghConnect With a Mentor Commented:
Use Criteria,
Jinesh KamdarCommented:
I'm not sure if the IN operator works like that. If the list is not too long, u can try looping thru' the list collection and providing the list value for the loop index to the SELECT.
Are your types a list of String?

If they are, you can convert the collection of myTypes to a commea seperated list, and  passing in the commea seperated sting.
public static void main(String[] args ){
  //Code for getting connection and query
  String SQL = "select * from INVOICE where invoice_type in (?)"
  query.setParameter(1, listToString(myTypes) );
private String listToString( List<InvoceType> list ) {
  String str = ""
  if ( list.size() <= 0 ) {
    str += "-1";
  } else {
    for ( int i = 0; i < list.size(); i++ ) {
      InvoceType type = list.get(i);		    	
      str += //Code to get the string value of type from the object		        
      if ( i + 1 != list.size() ) {
        str += ",";
 return str;

Open in new window

All Courses

From novice to tech pro — start learning today.