CODEWITHSUNDEEP
javapostgresqlhibernatejpa
Ehcnalb
Ehcnalb

Reputation: 476

JPA, how to select where array contains value?

I have an Object MyObject like (I use hibernate-array-contributor dependency to manage array) : 

@Entity
@Table(name = "mytable")
@NamedQueries({
        @NamedQuery(name = "findAll",
            query = "SELECT n FROM mytable n"),
        @NamedQuery(name = "getForUsers",
            query = "SELECT n FROM mytable n WHERE users @> :users")            
    })
public class MyObject{
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private long id;

   @Column(name="title")
   private String title; 

   @Column(name="users", columnDefinition="bigint array")
   private Long[] users ;

   //constructor, getters and setters
}

I have some problem when I want to use the namedquery getForUsers :

org.hibernate.HibernateException: Errors in named queries: getForUsers failed because of: org.hibernate.QueryException: unexpected char: '@' [SELECT n FROM MyObject n WHERE users @> :users]

But when I wrote it in pgAdmin, it's working well. How can I do it?
Thanks in advance.

--EDIT--

I think a found a way to do what I want, I modify my object like that :

@Entity
@Table(name="mytable")
@NamedQueries({
   @NamedQuery(name="findAll",query = "SELECT n FROM mytable n"),
   @NamedQuery(name="getForUser", query = "SELECT n FROM mytable n WHERE :user MEMBER OF n.users")
 })
 public class MyObject{
   @Id
   @GeneratedValue(startegy = GenerationType.IDENTITY)
   private long id;

   @Column(name="title")
   private String title;

   @ElementCollection
   private Set<Long> users = new HashSet<>();

   //constructor, getters and setters
}

It add a table myobject_users where there are myobject_id and users columns. Thanks @Victor Gubin for links I knew not the "MEMBER OF"

Upvotes: 0

Views: 4959

Answers (1)

Ehcnalb
Ehcnalb

Reputation: 476

My edit doesn't allow to delete or update (ElementCollection), so with the initial state, this works :

@NamedNativeQueries({
    @NamedNativeQuery(name="getForUser", query = "SELECT * FROM mytable n WHERE n.users @> ?1 ", resultClass=MyObject.class)
})

Upvotes: 1

Related Questions