How to map a PostgreSQL array with Hibernate to custom objects?

Question

Has anyone mapped a numeric array in PostgreSQL to a List of CustomObject in Java via Hibernate?

SQL

CREATE TABLE sal_emp (name varchar, work_station bigint[]);
INSERT INTO sal_emp VALUES ('one', '{1,2}');
INSERT INTO sal_emp VALUES ('two', '{3,4}');
INSERT INTO sal_emp VALUES ('three', '{2,4}');

CREATE TABLE station(station_id bigint, station_name varchar, station_timezone varchar);
INSERT INTO station values (1, 'ABC', 'Sample Time');
INSERT INTO station values (2, 'DEF', 'Sample Time');
INSERT INTO station values (3, 'GHI', 'Sample Time');
INSERT INTO station values (4, 'JKL', 'Sample Time');

Class

public class SalEmp implements Serializable{
    private String name;
    private List<Station> stations;
    ...// getters & setters
}

public class Station implements Serializable{
    private Long stationId;
    private String stationName;
    private String stationTimezone;

    ...// getters & setters
}

I checked the answers in this post, it does not discuss about custom objects. I am not sure if this possible or the database design itself is wrong!

Éva Szloboda · Accepted Answer · 2024-10-01 08:17:02Z

You could make a table between sal_emp and station. You can create the JPA entity for it like this:

@Entity
@Table(name = "sal_emp_station")
class SalEmpStation {

    @Id
    Long id;

    @ManyToOne
    @JoinColumn(name = "sal_emp_id")
    SalEmp salEmp;

    @ManyToOne
    @JoinColumn(name = "station_id")
    Station station;
    
    // additional properties
    // standard constructors, getters, and setters
}

Then you configure the relationships in SalEmp and Station classes. SalEmp and Station entities are bidirectional.

class SalEmp {

    // ...

    @OneToMany(mappedBy = "salEmp", cascade = CascadeType.ALL, orphanRemoval = true)
    Set<SalEmpStation> salEmpStations;

    // ...
}

class Station {

    // ...

    @OneToMany(mappedBy = "station", cascade = CascadeType.ALL, orphanRemoval = true)
    Set<SalEmpStation> salEmpStations;

    // ...
}

Bidirectional associations are easy to use in queries.

Collectives™ on Stack Overflow

How to map a PostgreSQL array with Hibernate to custom objects?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related