CODEWITHSUNDEEP
postgresqljdbi
Vexxed72
Vexxed72

Reputation: 19

How do you use JDBI to Bind List<String> to ILIKE ANY with PostgreSQL?

I've been trying to write a JDBI SqlQuery that uses ILIKE ALL to search for matches. The raw SQL would be:

SELECT * FROM clients WHERE clients.search_terms ILIKE ALL (VALUES ('%term1%'),('%term2%'),('%etc%'));

or

SELECT * FROM clients WHERE clients.search_terms ILIKE ALL ('{"%term1%", "%term2%", "%etc%"}');

I've tried lots of different options, for example:

@SqlQuery("SELECT * FROM clients WHERE clients.search_terms ILIKE ALL (<searchTerms>);") List<Client> internalSearch(@BindList("searchTerms") List<String> searchTerms);

@SqlQuery("SELECT * FROM clients WHERE clients.search_terms ILIKE ALL (:searchTerms);") List<Client> internalSearch(@BindList("searchTerms") List<String> searchTerms);

@SqlQuery("SELECT * FROM clients WHERE clients.search_terms ILIKE ALL (VALUES :searchTerms);") List<Client> internalSearch(@BindList("searchTerms") List<String> searchTerms);

but I can't get the syntax to work out so I get errors when the query is run.

Upvotes: 0

Views: 34

Answers (0)

Related Questions