CODEWITHSUNDEEP
arrayspostgresqlhibernatespring-boot
Hearaman
Hearaman

Reputation: 8726

How to use Postgres array "contains" condition with Hibernate?

I wrote a Postgres query as below and it is failing at build. How to implement it in Spring-boot Hibernate?

 @Query(value="SELECT t from CatalogView t WHERE t.locations=:locations AND STRING_TO_ARRAY(t.myColumn, ',') @> STRING_TO_ARRAY(:myColumn, ',')")
 public Set<TrainerGuides> getData(@Param("locations") String locations,@Param("myColumn") String myColumn);

Why i wrote this? to compare a bunch of strings separated by coma with unorder string input in Postgres.

    catalog
    ----------------------------------
    id   title     keywords
    ----------------------------------
    1    Title-1   k2,k1,k3,k4,k5
    2    Title-2   k1,k3,k5,k6

To select the rows by comparing keywords with my input k1,k2,k5

In this above example, row with id: 1 has to come.

SELECT *
FROM   catalog
WHERE  STRING_TO_ARRAY(keywords, ',') @> STRING_TO_ARRAY('k1,k2,k5', ',')

Upvotes: 1

Views: 1734

Answers (1)

Omid P
Omid P

Reputation: 211

you can extend Hibernate Postgresql Dialect and add native database functions to your HQL here is the example :

I've never used STRING_TO_ARRAY but the rest works fine.

public class PostgreSQLDialect extends org.hibernate.dialect.PostgreSQLDialect
{

public PostgreSQLDialect()
{
    super();
    registerFunction("replace", new StandardSQLFunction("replace", Hibernate.STRING));
    registerHibernateType(Types.ARRAY, "array"); 
    registerFunction( "string_agg", new StandardSQLFunction("string_agg", Hibernate.STRING) );
    registerFunction( "generate_series", new StandardSQLFunction("generate_series") );
    registerFunction( "STRING_TO_ARRAY", new StandardSQLFunction("STRING_TO_ARRAY", Hibernate.STRING) );
}

@Override
public boolean supportsTupleDistinctCounts()
{
    return true;
}

}

Upvotes: 1

Related Questions