How To Use 'like' Function To Select Array Of Strings With Jooq

I now want to use 'like' function with JOOQ to select data including array of string data by not case sensitive and partitial-match. Table schema is: CREATE TABLE favorites (

Solution 1:

The PostgreSQL value = ANY (array) operator cannot match values like the LIKE predicate. You will need to resort to an actual LIKE predicate instead. In SQL, you'd write:

SELECT id, items
FROM favorites
WHEREEXISTS (SELECT*FROMunnest(items) AS t(item) WHERE item ILIKE '%OraNge%')

Or, with jOOQ:

context.select(FAVORITES.ID, FAVORITES.ITEMS)
       .from(FAVORITES)
       .whereExists(
            selectFrom(unnest(FAVORITES.ITEMS).as("t", "item")
           .where(field(name("item", String.class)).likeIgnoreCase("%OraNge"))
       )
       .fetch();

The jOOQ version, as always, assumes you have this static import:

importstatic org.jooq.impl.DSL.*;

Solution 2:

In addition, here are a few ways to use LIKE. You can always use the jOOQ LIKE predicates, see their documentation. In my second example, I use sql syntax in a string, just to prove you can. You can also use contains/startsWith/endsWith like you would with strings.

jooq.dsl()
  .select()
  .from(MY_TABLE)
  .where(Employee.EMPLOYEES.LAST_NAME.like("ER")));

jooq.dsl()
  .select()
  .from(EMPLOYEES)
  .where(Employee.EMPLOYEES.LAST_NAME.like("ER"))
  .and("first_name like ?", "ST"));