how to pass comma seperated UUID in parameterized select query where IN clause

Question

I have a table with UUID as keys and want to fetch multiple rows using select query, so the query is as follow:

select * from table1 where ids in (:commaseperatedUUIDs); //DB is postgreSql

I am trying it in Java code, I have List<UUID> which contains all the UUID but if I make it comma separated of course by using String operations and pass the String in query as parameter then it throws SQL Exception stating

operator does not exist: uuid = character varying

Any clues?

Answer 1

The problem is your query evaluates to:

select * from table1 where ids in ('abc,bcd,dbc')

Instead of the desired form:

select * from table1 where ids in ('abc','bcd','dbc')

You can either build the query manually adding multiple parameters and setting each of them individually:

String sql = "select * from table1 where ids in (";
for (int i = 0; i < ids.length; i++) 
    if (i == 0)
        sql += "?"
    else 
        sql += ",?";

sql += ")";
    
PreparedStatement ps = connection.prepareStatement(sql);
for (int i = 0; i < ids.length; i++)
    ps.setString(i+1, ids[i]);

Or use the PreparedStatement::setArray method:

String sql = "select * from table1 where ids in (?);";
PreparedStatement ps = connection.prepareStatement(sql);

ps.setArray(1, connection.createArrayOf("VARCHAR", ids.toArray()));