Binding a list of integers in an IN clause using Cayenne

Question

I'm trying to bind a list of integers into an SQLTemplate IN clause like so:

SELECT * FROM table1 WHERE id IN ( #bind($idList) );

I have a string of ids:

idList = "1, 2, 3, 4";

I can't get the bind to work successfully, it returns no values when I pass in the string as a list of ids to check.

I'm having to use string concatenation to run this (not ideal).

Any ideas as to how I could get it to bind properly?

Thanks in advance.

(I'm using Java/Cayenne/Postgres, and running the query with the idList passed in as a parameter, this is a simplified example, not the actual sql I am running).

UPDATE I figured out how to do it. Answer below.

Answer 1

OK, I found out how to do it as soon as I posted the question.

Instead of having a string, use a list of Integers like so:

List<Integer> ids = {1,2,3,4,5} (pseudocode)

And to do the bind, you need the following:

SELECT * FROM table1 WHERE id IN ( #bind($idList, 'INTEGER') );

Then pass in the parameter as usual and it will work.