I am using the new Spring boot JDBCClient, where I am trying to use a List into query IN clauses. I found a solution that does not seem to be a very elegant/handy way of using any() operator, so I am wondering if there is some other approach.

Note: My field is PostgreSQL's Enum type

private final JdbcClient jdbcClient;

String sql = """
                SELECT * 
                FROM configuration 
                WHERE status = ANY(?::configuration_status[])
                """;

String statusArrayLiteral = "{" + request.configurationStatus().stream()
                .map(Enum::name)
                .collect(Collectors.joining(",")) + "}";

        return jdbcClient.sql(sql)
                .param(statusArrayLiteral)
                .query((rs, rowNum) -> parseConfiguration(rs))
                .list();

I am using the new Spring boot JDBCClient, where I am trying to use a List into query IN clauses. I found a solution that does not seem to be a very elegant/handy way of using any() operator, so I am wondering if there is some other approach.

Note: My field is PostgreSQL's Enum type

private final JdbcClient jdbcClient;

String sql = """
                SELECT * 
                FROM configuration 
                WHERE status = ANY(?::configuration_status[])
                """;

String statusArrayLiteral = "{" + request.configurationStatus().stream()
                .map(Enum::name)
                .collect(Collectors.joining(",")) + "}";

        return jdbcClient.sql(sql)
                .param(statusArrayLiteral)
                .query((rs, rowNum) -> parseConfiguration(rs))
                .list();

• spring
• postgresql
• spring-boot
• spring-jdbc

Share Improve this question Follow edited Mar 31 at 14:02 Tsvetoslav Tsvetkov asked Mar 21 at 9:07 Tsvetoslav TsvetkovTsvetoslav Tsvetkov 1,17611 gold badge1111 silver badges3030 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

You can tidy it up but not by much because the parameter wants a list of strings. Expanding all the way to a single comma separated string is not necessary.

Note that I introduced the named parameter configuration_status because .param requires it if a List is passed in. Also your question referred to the IN clause but your code uses ANY, so I changed that too.

String sql = """
                SELECT * 
                FROM configuration 
                WHERE status IN(:configuration_status)
                """;

        return jdbcClient.sql(sql)
                    .param("configuration_status", request.configurationStatus().stream().map(Enum::name).collect(Collectors.toList()))
                .query((rs, rowNum) -> parseConfiguration(rs))
                .list();

I found a solution, how to make proper IN clauses in case somebody needs to search based on multiple values in a field that is from a PosgreSQL type as the John Williams's solution works BUT on Varchar field

 return jdbcClient.sql("""
                                SELECT * 
                                FROM configuration 
                                WHERE status IN (:status)
                        """)
                .param("status", request.configurationStatus().stream().map(Enum::name).collect(Collectors.toList()), OTHER)
                .query((rs, rowNum) -> parseConfiguration(rs))
                .list();

The key thing is that the third parameter should be used, which defines the SQL type

In my case I used OTHER (The type can be seen from java.sql.Types)