How to Map a Postgresql Array with Hibernate

How to map a PostgreSQL array with Hibernate

Hibernate does not support database arrays (e.g. ones mapped to java.sql.Array) out of the box.

array and primitive-array types provided by Hibernate are for mapping Java arrays into backing table - they're basically a variation of one-to-many / collection-of-elements mappings, so that's not what you want.

Latest PostgreSQL JDBC driver (8.4.whatever) supports JDBC4 Connection.createArrayOf() method as well as ResultSet.getArray() and PreparedStatement.setArray() methods, though, so you can write your own UserType to provide array support.

Here is a UserType implementation dealing with Oracle array that provides a good starting point, it's reasonably straightforward to adapt it to handle java.sql.Array instead.

How to map a PostgreSQL text[] with Hibernate

There are several approaches you can take here. The first one is to pick a delimiter that will not be encountered within the filenames (usually : or ; depending on the system) and use a text field, instead of a an array, and just have some internal conversion

@Transient
private List<String> filename; // this is the data as convenient for you to use

@Column(nullable = false)
private String filenames; // this is the database column format

@PrePersist
@PreUpdate
private void toFilenameColumn() {
    if (filename == null) {
        filenames = "";
        return;
    }
    StringBuilder sb = new StringBuilder();
    String glue = "";
    for (String fn : filename) {
        sb.append(glue).append(fn);
        glue = ";";
    }
    filenames = sb.toString();
}

@PostLoad
private void fromFilenameColumn() {
    filename = Arrays.asList(filenames.split(";"));
}

But if you're set on using arrays, you can add this library to your project dependencies and define your column like so:

@Column(nullable = false, columnDefinition = "text[]")
private String[] filename;

The library is plug-and-play.

And finally you can do it the most standard way by defining another table with a one-to-many relation to the attachments table.

Related Topics