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

Question

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', ',')

Identifier names such as tables, columns, functions cannot be inserted using parameters. It is a security precaution. — coladict
– coladict, Commented Dec 6, 2018 at 11:11
Does @Query indicate a native query? You won't be able to use DBMS specific functions and operators in HQL or JPQL — user330315
– user330315, Commented Dec 19, 2018 at 8:29
@DHARMENDRASINGH I am able to build it. I hope expected results will come now. Thanks — Hearaman
– Hearaman, Commented Dec 26, 2018 at 5:04

Omid P · Accepted Answer · 2018-12-19 09:59:42Z

1

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;
}

}

answered Dec 19, 2018 at 9:59

Omid P

2114 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related