How to map a PostgreSQL text[] with Hibernate

Question

I use the text[] type in a PostgreSQL table. In java code, I'm trying to use an array of strings. How do I get the contents of a text[] column from the database? And how do I write data to the database from an array of strings?

I tried to use the method demonstrated by user Tk421 in response to this question How to map a PostgreSQL array with Hibernate

But i keep getting the exception:

java.lang.ClassCastException: class com.nitinol.marketplace.properties.PostgreSQLTextArray cannot be cast to class org.postgresql.jdbc.PgArray (com.nitinol.marketplace.properties.PostgreSQLTextArray is in unnamed module of loader org.springframework.boot.devtools.restart.classloader.RestartClassLoader @2d40e4b8; org.postgresql.jdbc.PgArray is in unnamed module of loader 'app')

I need all this so that when loading one or more MultipartFile via the API using Spring Boot, their names are saved in the database table as an array

RestController method:

    @PostMapping(value = "/files/upload-multiply")
    @ResponseBody
    public List<FileResponse> uploadMultipleFiles(@RequestParam("files") MultipartFile[] files,
                                                  @RequestParam("purchaseId")UUID id) {
        Purchase purchase = purchaseService.getPurchase(id);
        List<FileResponse> responses = new ArrayList<>();
        List<String> filenames = new ArrayList<>();

        for (MultipartFile file: files) {
            String name = storageService.store(file);

            String uri = ServletUriComponentsBuilder.fromCurrentContextPath()
                    .path("/download/")
                    .path(name)
                    .toUriString();

            filenames.add(name);
            responses.add(new FileResponse(name, uri, file.getContentType(), file.getSize()));
        }
        purchase.setAttachments(attachmentService.addAttachments(filenames));
        return responses;
}

StorageService.store():

@Override
public String store(MultipartFile file) {
    String filename = StringUtils.cleanPath(Objects.requireNonNull(file.getOriginalFilename()));
    try {
        if (file.isEmpty()) {
            throw new StorageException("Невозможно сохранить пустой файл " + filename);
        }
        if (filename.contains("..")) {
            throw new StorageException(
                    "Не удается сохранить файл с относительным путем вне текущего каталога "
                            + filename);
        }
        try (InputStream inputStream = file.getInputStream()) {
            Files.copy(inputStream, this.rootLocation.resolve(filename),
                    StandardCopyOption.REPLACE_EXISTING);
        }
    }
    catch (IOException e) {
        throw new StorageException("Ошибка сохранения файла " + filename, e);
    }

    return filename;
}

AttachmentService.addAttachments():

@Override
public Attachment addAttachments(List<String> filenames) {
    Attachment attachment = new Attachment();
    attachment.setFilename(filenames);
    return attachmentRepo.saveAndFlush(attachment);
}

Attachment entity:

    @Entity
@Data
@Table(name = "attachments")
@JsonIgnoreProperties(value = {"hibernateLazyInitializer", "handler"}, ignoreUnknown = true)
public class Attachment {

    @Id
    @Column(unique = true, nullable = false)
    @GeneratedValue
    private UUID id;

    @Column(nullable = false, columnDefinition = "text[]")
    @Convert(converter = ListToArrayConverter.class)
    private List<String> filename;

    @OneToOne(mappedBy = "attachments")
    private Purchase purchase;

    @OneToOne(mappedBy = "attachments")
    private Bid bid;
}

And Liquibase migration for Attachments table:

<property name="uuid_type" value="uuid" dbms="postgresql"/>
<property name="uuid_function" value="uuid_generate_v4()" dbms="postgresql"/>
<changeSet id="attachments_table" author="dmitry">
    <createTable tableName="attachments">
        <column name="id" type="${uuid_type}" valueComputed="${uuid_function}">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="filename" type="text[]">
            <constraints nullable="false" unique="false"/>
        </column>
    </createTable>
</changeSet>

I've only been studying Spring Boot and the rest of the tech stack for two months, so my code looks terrible. I will be grateful to you for any hint on how to solve this problem.

This problem could be avoided by using not the OneToOne relationship with the text [] type for storing file names, but OneToMany with the varchar type, but I am sure that this can be implemented in both ways, and I want to figure it out

score 1 · Accepted Answer · answered Dec 03 '20 at 09:02

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.

How to map a PostgreSQL text[] with Hibernate

1 Answers1

Linked