How can I get all SQL entries matching entries in an ArrayList?

Question

I have a database containing a table of messages, where each message entry contains a variable for its author. I also have an ArrayList containing a list of authors. What I'd like to do is to create an SQL query that retrieves all entries from the database where the author exists in the ArrayList.

So something along these lines:

List<Message> result = session.createQuery("from Message where author ISIN ARRAYLIST order by id DESC ").list();

Anyone know how I would go about doing this?

Answer 1

you can set your authorlist as a named parameter:

String cmd = "from Message where author IN (:authorlist) order by id DESC";
Query q = session.createQuery(cmd);
q.setParameterList("authorlist", yourAuthorList);
List<Message> result = q.list();

Answer 2

ArrayList<String> authors=(ArrayList<String>)"<your list>";
String in="(";
for(String author:authors){
in +=author+",";
}
in=in.substring(0,in.length()-1);
 in +=")";
List<Message> result = session.createQuery("from Message where author IS IN "+in+" order by id DESC ").list();

Answer 3

You could like this (same thing, different way):

String hql= "SELECT * FROM Message WHERE author IN ( :authors)";
Query query = session.createQuery(hql);
query.setParameterList("authors", <your authors list>);

Answer 4

String cmd = "from Message where author IN (:authorlist) order by id DESC"; Query q = session.createQuery(cmd);

q.setParameterList("authorlist", yourAuthorList); q.setParameterList("authorlist", yourAuthorList);

List<Message> result = q.list();

You can try like this but before doing like that you should aware of no of elements in ArrayList. Because it violates the query.

Please refer this link.

How to put more than 1000 values into an Oracle IN clause