java - How to map a PostgreSQL text[] with Hibernate
问题描述
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
解决方案
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.
推荐阅读
- python - 在python的无向图中查找源和目标之间的所有可能路径
- python - 直接启动线程定时器
- django - 将 Dockerfile 从 python-alpine 转换为 python-slim-buster(基于 debian)
- haskell - 安装 Quipper 时出错 - 无法构建 quipper-utils-0.9.0.0(无法将类型 'Maybe TH.Exp' 与 'TH.Exp' 匹配)
- python - 有人可以看看我的 Keras CNN 模型并帮助我找出提高训练和验证准确性的方法吗?
- python - 使用 python 绘制地理地图上的 3D 高程
- php - laravel url() 方法如何在邮件刀片视图中确定主机名
- python - 在 Pandas MultiIndex 中重新采样会丢失值
- c# - 无法在 ASP.NET 中显示表单输入
- haproxy - 如何让keepalived响应HAProxy上的故障转移?