How to use query operators && or @> when creating a native query in hibernate

Question


CREATE TABLE IF NOT EXISTS posts(
    postid SERIAL NOT NULL,
    owner SERIAL NOT NULL,
    title VARCHAR(100) UNIQUE NOT NULL,
    message VARCHAR(500) NOT NULL,
    continent VARCHAR(100) NOT NULL,
    country VARCHAR(100) NOT NULL,
    state VARCHAR(100) NOT NULL,
    languages VARCHAR(100)[] NOT NULL,
    properties varchar(100) ARRAY NOT NULL,
    PRIMARY KEY (postid),
    FOREIGN KEY (owner) REFERENCES users(userid) ON DELETE CASCADE
);

final Query query = em.createNativeQuery("SELECT postid FROM posts WHERE :properties <@ properties");
query.setParameter("properties", propAux);

I'm migrating my project to hibernate (I'm using the version 1.0.0.Final of javax.persistence.Query), and I can't seem to run that query. It says that <@ operator doesn't work with types bytea and suggests to cast it. I tried casting it to text[], replacing :properties <@ properties with cast(:properties as text[]) <@ cast(properities as text[]) but didn't work either.

Any suggestions?

PS: propAux is an array of Strings.

Surely the error message changed when you added the cast? Please copy and paste the exact error message — jjanes
– jjanes, Commented Jun 4, 2021 at 13:36
Caused by: org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to text[] Position: 44 — vanderkute
– vanderkute, Commented Jun 4, 2021 at 14:47
Maybe one of the answer's here would help? Options discussed there are passing in a string like "{'a','b','c'}" and casting to array, or using setParameter("properties", new TypedParameterValue(StringArrayType.INSTANCE, propAux)) You could use something like select pg_typeof(:properties) to see what's bound in. — roby
– roby, Commented Jun 6, 2021 at 1:08

SternK · Accepted Answer · 2021-06-07 08:58:46Z

0

Assuming that you have the following setup:

create table TST_POSTS
(
   postid int,
   properties varchar(200)[],
   
   primary key (postid)
);

insert into TST_POSTS
values (1, '{"aaa", "bbb"}'), (2, '{"aaa", "ccc"}'), (4, '{"ddd", "ccc"}');

You can write the following query:

import org.hibernate.jpa.TypedParameterValue;
import com.vladmihalcea.hibernate.type.array.StringArrayType;

// ...

List<Integer> results = entityManager
   .createNativeQuery("select postid from TST_POSTS where :prop <@ cast(properties as text[])")
   .setParameter("prop", new TypedParameterValue(StringArrayType.INSTANCE, new String[] {"ccc"}))
   .getResultList();

and you will get the output:

2
4

This approach requires to have hibernate-types as a dependency.

P.S. I tested it with hibernate-types-52 (version 2.10.1) and hibernate 5.4.29.Final. See also this article.

EDIT

You can use the following query for hibernate 5.1.0.Final:

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

// ...

Query query = entityManager
   .createNativeQuery("select postid from TEST_SCHEMA.TST_POSTS where :prop <@ cast(properties as text[])")
   .unwrap(Query.class)
   .setParameter("prop", new String[] {"ccc"}, StringArrayType.INSTANCE);

@SuppressWarnings("unchecked")
List<Integer> result = query.list();

and you should use the following dependency:

<dependency>
   <groupId>com.vladmihalcea</groupId>
   <artifactId>hibernate-types-5</artifactId>
   <version>2.11.1</version>
</dependency>

instead of hibernate-types-52.

edited Jun 7, 2021 at 8:58

answered Jun 4, 2021 at 8:55

SternK

13.3k34 gold badges40 silver badges55 bronze badges

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

15 Comments

vanderkute Over a year ago

Yes, I have a similar setup. I'll edit the post and add it so you can see. I did what you suggested here. But still for some reason it reads my varchar(100)[] from the db as bytea and I get this error: Caused by: org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to text[] Position: 44 The problem is obviously coming from the cast(properties as text[]). I also don't have that version of hibernate. I have version 5.1.0.Final.

SternK Over a year ago

1. Why you use 5.1.0.Final instead of the latest version from 5.4 branch? 2. What hibernate dialect do you use?

vanderkute Over a year ago

Apparently I tried doing away with cast() and still got the same error. So maybe the error does come from casting it in the TypedParameterValue function?

vanderkute Over a year ago

I use 5.1.0.Final because other things depend on it and can't change it because they all break.

vanderkute Over a year ago

Even if I do away with TypedParameterValue() it still gives me the same error. I honestly don't know what to do anymore haha

|

Collectives™ on Stack Overflow

How to use query operators && or @> when creating a native query in hibernate

1 Answer 1

15 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related