JPA, How to insert an array?

Question

I have an Object MyObject like :

@Entity
@Table(name = "mytable")
public class MyObject{
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private long id;

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

   @Column(name="users")//, columnDefinition="bigint[]")
   @ElementCollection   
   private List<Long> users = new ArrayList<>();

   //constructor, getters and setters
}

With a DAO MyObjectDAO.class where a create method is define (create(MyObject mo)), and a resource class MyObjectResource with :

@POST
@UnitOfWork
public Response createMyObject(MyObject mo) {
    Response resp;
    try {
        MyObject mo0 = MyObjectDAO.create(mo);
        if(mo0!=null) {
            resp = Response.status(Response.Status.OK).entity(mo0).build();
        }
        else {
            resp = Response.status(Response.Status.INTERNAL_SERVER_ERROR).entity("MyObjectnull, can't be created in database").build();
        }
    }
    catch(Exception e) {
        resp = Response.status(Response.Status.INTERNAL_SERVER_ERROR).entity("MyObjectnull, can't be created in database\n"+e.getMessage()).build();
    }
    return resp;
}

When I try to create with POST a MyObject like {"id":0,"title":"dyud u","users":[334,335]}, I get error :

org.hibernate.engine.jdbc.spi.SqlExceptionHelper: SQL Error: 0, SQLState: 42804 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper: ERREUR: la colonne « users » est de type bigint[] mais l'expression est de type bigint

I have no problem to create, when users is null or empty or manually with sql command :

INSERT INTO mytable ("title","users") VALUES ('t1','{334,335}');

How can I do it?

Possible duplicate of [Mapping a PostgreSQL array with Hibernate](https://stackoverflow.com/questions/1647583/mapping-a-postgresql-array-with-hibernate) — Arnaud, Apr 30 '19 at 12:18

score 2 · Accepted Answer · answered Apr 30 '19 at 12:35

2

Add this dependency to your project and change the field to:

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

This will work for Hibernate versions 5.2+ before 6.0, then the library will need to be updated.

answered Apr 30 '19 at 12:35

coladict

4,799
1
16
27

score 0 · Answer 2 · answered May 03 '19 at 09:29

Else it can be work without this dependency :

@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. There are no problems to create a new MyObject.

JPA, How to insert an array?

2 Answers2