Using List inside a postgres Query

Question

i have a dynamic list.

list=['a','b','c','d' ..... ] so length may change

i want to compare these list values in a query

select * from student where name in(all the list values);

i want to pass list values into this query

how i can do this.. ??? please help Thank you

Answer 1

In Postgres, you can use arrays. The syntax in the where clause looks like:

where name = any (array[1, 2, 3])

or

where name = any (list_parameter)

Answer 2

You can write a function that gets a list as a parameter and return a string like "'one', 'two','three'".

    // need a string like this 'one', 'two'
    private String arrayToSqlInChecker(List<String> loc_list) {
        StringBuilder value = new StringBuilder("");
        for (int i = 0; i < loc_list.size(); i++) {
            value.append("'" + loc_list.get(i) + "'");
            if (i != loc_list.size() - 1) {
                value.append(",");
            }
        }
        return value.toString();
    }

And then you have to append this string into you PostgreSQL IN query

"id IN (" + this.arrayToSqlInChecker(loc_list) + ")"

You can also handle null or empty values in the function