up vote 0 down vote favorite

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 (
    id int,
    items    varchar(100)[]
);

Sample data is:

INSERT INTO favorites (id, items)
    VALUES (1, '{orange, lemon, banana}');
INSERT INTO favorites (id, items)
    VALUES (2, '{apple, grape}');

To get first data, SQL is like:

SELECT id, items FROM favorites WHERE 'orange' = ANY (items);

My Goal is to select data by case-sensitive and partitial-match like: For example, using likeIgnoreCase("OraNge") or like("%ang%") ?

To develop below code with LIKE function: 

Connection connection = ...;
DSLContext context = DSL.using(connection, ...);
List<Table> table = context.select().from(TABLE).fetchInto(Table.class);

How can I use like function?

Thank you in Advance.

share|improve this question
up vote 0 down vote

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
WHERE EXISTS (SELECT * FROM unnest(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:

import static org.jooq.impl.DSL.*;
share|improve this answer
up vote 0 down vote

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"));
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.