Java Hibernate Specification SQL search records based on column type string array

Question

I've got table where one of the column is text[] type.

Front-end send me filters based on that I should return list of records which meets conditions.

screenshot from the database:

lang is an array where user has got all languages which can speak.

Front-end send me a request with language array

and I would like to create Specification in two ways, for any and all match:

1. any: in this particular case I would like to return user which has at least one of that language from filter, with this filters, I should return all users because each of them has at least English or Swedish.

2. all match: in this case I would like to return user which can speak in all inputed languages, so I should return list of users with id: 11 and 5, because two of them, can speak either in English and Swedish

For the first case, any I created SQL script:

return records which has at least one element from array match with user's languages list:

WITH where_clause AS (
  SELECT UNNEST('{English, Polish}'::TEXT[]) AS lang_payload
  )
SELECT DISTINCT up.*
  FROM user_profile up
LEFT JOIN where_clause wc ON 1=1
WHERE wc.lang_payload = ANY(up.lang);

will return users with id: 11, 5, 225 and it's ok.

for all match I don't have an idea how to create SQL script. only user with id: 11 can speak either in English and Polish.

here is the sql playground where I create simple schema.

But I would like to create Specification instead of native query, is it possible to do it?

here is the java implementation so far:

To handle text[] in java I'm using

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>2.4.3</version>
</dependency>

so here is a DAO:

import com.vladmihalcea.hibernate.type.array.StringArrayType;

@Entity
@Table(name = "user-profile")
@TypeDefs({
        @TypeDef(
                name = "string-array",
                typeClass = StringArrayType.class
        )
})
public class ProfileDAO {

    @Id
    @Column(name = "user_id")
    private Long id;

    @Type(type = "string-array")
    @Column(
            name = "lang",
            columnDefinition = "text[]"
    )
    private String[] lang;

in the service I convert payload from above to Map where key is field and value is an array and tried to find by filters with repository:

@Repository
public interface ProfileRepository extends PagingAndSortingRepository<ProfileDAO, Long>, JpaSpecificationExecutor<ProfileDAO> {
    default Page<ProfileDAO> findByFilters(Map<String, Object> filters,
                                                   Pageable pageable) {
        return findAll(Specification
                        .where(isIn("lang", filters.get("lang")))
                ,
                pageable);
    }

}

and specification:

  public static Specification<ProfileDAO> isIn(String attribute, Collection<String> values) {
        return (root, query, cb) -> {
            if (values.isEmpty()) {
                return null;
            }
            Path<Object> objectPath = root.get(attribute);
            return objectPath.in(values);
        };
    }

but I got an error that I cannot cast array to string

Thanks for any help or tips!

Answer 1

In pure SQL (native query), you can solve the second case with not exists:

with p as (select unnest('{English, Polish}'::text[]) as lang_payload)
select up.*
from user_profile up
where not exists (select 1 from p where not p.lang_payload = any(up.lang))