如何在 PostgreSQL 中存储文本：提示、技巧和陷阱

原创 CiciLee 2022-08-11

642

基于 JPA 实体定义的 DDL 生成是许多开发人员的日常任务。在大多数情况下，我们使用 Hibernate 的内置生成器或 JPA Buddy 插件等工具。它们使工作更容易，但也有例外。当谈到在数据库中存储大量数据时，事情变得有点复杂。

用例：存储文档

假设我们需要在 PostgreSQL 数据库中存储一个文档对象及其内容。用于此的 JPA 实体代码可能类似于以下代码：

@Entity  
@Table(name = "document")  
public class Document {  
   @Id  
   @GeneratedValue(strategy = GenerationType.IDENTITY)  
   @Column(name = "id", nullable = false)  
   private Long id;  
 
   @Column(name = "date_created", nullable = false)  
   private LocalDateTime dateCreated;  
 
   @Column(name = "doc_txt")  
   private String docText;  
   
   //Getters and setters omitted for brevity  
}  

复制

问题是：如果我们需要存储非常长的文档文本怎么办？在 Java 中，字符串数据类型可以容纳大约 2Gb 的文本数据，但对于上述模型，表列大小默认限制为 255 个字符。那么，我们应该改变什么？

选项 1：使用 LOB 存储

在关系数据库中，存在一种特定的数据类型来存储大量数据：LOB（Large OBject）。一旦我们需要在数据库中存储大文本，我们可以从定义 LOB 列开始。我们需要做的就是用@Lob 注释标记docText 属性。

@Lob   
@Column(name = "doc_txt")   
private String docText;   

复制

让我们使用 Hibernate 为表生成一个 DDL 以映射 Document 实体。 SQL 将是：

create table document ( 
    id int8 generated by default as identity, 
    date_created timestamp not null, 
    doc_txt oid, 
    primary key (id) 
);   


复制

我们可以看到，doc_text 列的数据类型是 oid。它是什么？根据文档：

PostgreSQL 提供了两种不同的方式来存储二进制数据。可以使用数据类型 BYTEA 或使用大对象功能将二进制数据存储在表中，该功能将二进制数据以特殊格式存储在单独的表中，并通过在表中存储 OID 类型的值来引用该表。

在我们的例子中，第二个选项是有效的。这个单独的表的名称是 pg_largeobject，它存储分成“页面”的数据，通常每个 2 kB，如文档中所述。

因此，Hibernate 将大文本作为二进制数据存储在单独的表中。这是否意味着我们应该在选择数据时进行额外的连接或在保存时进行额外的插入？让我们启用 SQL 日志记录，创建 Document 实体并使用 Spring Data JPA 将其保存到数据库中。

Document doc = new Document();   
doc.setDateCreated(LocalDateTime.of(2020, 1, 1, 10, 10));   
doc.setDocText("This is the doc text");   
Document saved = documentRepository.save(doc); 

复制

Hibernate 将在控制台中显示一个普通的 SQL 插入：

insert  
into 
    document 
    (date_created, doc_txt)  
values 
    (?, ?) 


复制

现在我们可以通过在控制台中执行以下 SQL 来检查数据是否正确存储：

select * from document

复制

我们将看到的结果应该与此类似：
微信截图_20220811113744.png

我们在这个表中看不到文档文本，只是对大对象存储中的对象的引用。让我们检查一下 pg_largeobject 表：

select * from pg_largeobject where loid=76338

复制

现在我们可以看到文档文本了。

因此，Hibernate 在后台自动将数据保存到两个表中。现在我们可以尝试使用 Spring Data JPA 获取文档数据：

documentRepository.findById(1L).ifPresent(d -> System.out.println(d.getDocText()));   

复制

我们可以在控制台看到如下SQL：

select   
   document0_.id as id1_0_0_,   
   document0_.date_created as date_cre2_0_0_,   
   document0_.doc_txt as doc_txt3_0_0_    
from   
   document document0_    
where   
   document0_.id=?   

复制

并且输出应该符合预期：

This is the doc text

复制

Hibernate 透明地从 pg_largeobject 表中选择数据。让我们尝试使用 JPQL 来执行相同的查询。为此，我们创建了一个额外的 Spring Data JPA 存储库方法并调用它：

//repository   
@Query("select d from Document d where d.id = ?1")   
Optional<Document> findByIdIs(Long id); 
//... 
//invocation 
documentRepository.findByIdIs(1L).ifPresent(d -> System.out.println(d.getDocText()));

复制

该方法将失败：

org.springframework.orm.jpa.JpaSystemException: Unable to access lob stream   
…   
Caused by: org.hibernate.HibernateException: Unable to access lob stream   
…   
Caused by: org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode.  

复制

Hibernate 执行额外的数据库读取以获取 LOB 数据。在自动提交模式下，此读取在单独的事务中执行。 PostgreSQL 驱动程序明确禁止它，如上面的错误消息所示。要解决此问题，我们需要在一个事务中执行此类查询或禁用自动提交模式。

来自 CrudRepository 的 Spring Data JPA 方法，如 findById() 和 findAll() 默认在一个事务中执行。这就是为什么在第一个示例中一切正常的原因。当我们使用 Spring Data JPA 查询方法或 JPQL 查询时，我们必须显式使用 @Transactional，如下例所示。

@Transactional 
@Query("select d from Document d where d.id = ?1")   
Optional<Document> findByIdIs(Long id); 

@Transactional 
List<Document> findByDateCreatedIsBefore(LocalDateTime dateCreated);

复制

如果禁用自动提交模式似乎比使用 @Transactional 注释更可取，我们应该查看应用程序库的文档。例如，要为 Spring Boot 中的默认连接池实现（HikariCP）执行此操作，我们需要将 spring.datasource.hikari.auto-commit 属性设置为 false。

将文本存储在单独的表中可能会导致其他问题。让我们添加一个存储库方法来使用 docText 字段的 LIKE 子句选择文档：

@Transactional   
List<Document> findByDocTextLike(String text);

复制

此方法将生成以下查询：

select   
   document0_.id as id1_0_,   
   document0_.date_created as date_cre2_0_,   
   document0_.doc_txt as doc_txt3_0_    
from   
   document document0_    
where   
   document0_.doc_txt like ? escape ? 

复制

…并且此查询将失败并出现以下错误：

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a];    
…   
Caused by: org.postgresql.util.PSQLException: ERROR: function pg_catalog.like_escape(bigint, character varying) does not exist   
Hint: No function matches the given name and argument types. You might need to add explicit type casts.   

复制

Hibernate 无法生成正确的 SQL 来处理 LOB 文本列的 LIKE 子句。对于这种情况，我们可以使用本机查询。在此查询中，我们必须从 LOB 存储中获取文本数据并将其转换为字符串格式。之后，我们可以在 LIKE 子句中使用它（不要忘记`@Transactional）：

@Query(value = "select * from document d " +   
       "where convert_from(lo_get(doc_txt), 'UTF-8') like '%'||?1||'%'", nativeQuery = true)   
@Transactional   
List<Document> findByDocTextLike(String text);

复制

现在一切正常。请记住，本机查询可能与其他 RDBMS 不兼容，并且未在运行时验证。仅在绝对必要时使用它们。

结论：将文本存储为 LOB

那么，在 PostgreSQL 中将大文本存储为 LOB 对象的优缺点是什么？
优点：

PostgreSQL 为 LOB 对象使用优化存储
我们可以在那里存储多达 4Gb 的文本

缺点：

WHERE 子句中的某些函数（LIKE、SUBSTRING 等）在 Hibernate 中对 LOB 文本列不起作用。为此，我们需要使用本机查询。
要使用 JPQL 或 Spring Data JPA 存储库查询方法获取文本，我们必须对存储库方法使用 @Transactional 注释或禁用自动提交模式

这里的问题是：为什么我们不将文本数据直接存储在表中？让我们也讨论一下这个选项。

选项 2：列重新定义

PostgreSQL 允许我们将长文本数据存储在特定数据类型的列中 - TEXT。我们可以在注释中指定列定义。

@Column(name = "doc_txt", columnDefinition = "text")   
private String docText;

复制

这使我们能够以“通常”的方式处理长文本。没有事务、本机查询和 JPQL 按预期工作。与 LOB 类型相比有一个限制

…，可以存储的最长可能字符串约为 1 GB

它小于 LOB 存储允许的 4Gb，但对于大多数用例来说仍然足够长。

这里唯一的问题是硬编码的列定义。为了克服它，我们可以在 Hibernate 5 中使用 @Type 注释和转换器 org.hibernate.type.TextType。它比之前的列定义有一个优势：它不是特定于供应商的。

@Type(type = "org.hibernate.type.TextType")   
@Column(name = "doc_txt")   
private String docText;

复制

在 Hibernate 6 中，删除了 org.hibernate.type.TextType 类。要定义一个列来存储长文本，我们可以通过以下方式定义属性：

@Column(name = "doc_txt", length = Length.LOB_DEFAULT)   
private String docText; 

复制

这将为我们提供数据库中的以下列定义：doc_txt varchar(1048576)。它不是 TEXT 数据类型，但它仍然可以在表中存储大约 1Gb 的文本。它是 PostgreSQL 中可能的最大字符串。

我们可以通过像这样定义 docText 属性在 Hibernate 6 中生成具有 TEXT 数据类型的列：

@JdbcTypeCode(SqlTypes.LONG32VARCHAR)   
@Column(name = "doc_txt")   
private String docText;

复制

不幸的是，截至今天（2022 年 6 月），Hibernate 6 无法从表中获取数据。但它会生成正确的 TEXT 类型的表和列定义。从 doc_txt 列提取到实体属性的数据失败。错误文本如下所示：

Unknown wrap conversion requested: [B to java.lang.String : `org.hibernate.type.descriptor.java.StringJavaType` (java.lang.String) 

复制

因此，在 TEXT/VARCHAR 列中存储长文本会带来更少的问题。交易、LIKE 条件等没有问题。唯一的缺点是存储大小（高达 1Gb）。还有其他警告吗？

如果我们在数据库中使用 TEXT 列类型和 @Lob 注解，可能会出现问题。让我们看看它是如何工作的。首先，让我们创建一个表格文档并在其中插入一些数据：

create table document ( 
    id int8 generated by default as identity, 
    date_created timestamp not null, 
    doc_txt text, 
    primary key (id) 
);   
  
insert into document (id, date_created, doc_txt) values (1, '2021-10-10', 'This is the document text number 1');

复制

我们将使用带有@Lob 列的文档实体定义：

@Entity   
@Table(name = "document")   
public class Document {   
   @Id   
   @GeneratedValue(strategy = GenerationType.IDENTITY)   
   @Column(name = "id", nullable = false)   
   private Long id;   
  
   @Column(name = "date_created", nullable = false)   
   private LocalDateTime dateCreated;   
  
   @Lob   
   @Column(name = "doc_txt")   
   private String docText;   
  
   //Getters and setters omitted for brevity   
}

复制

文档获取的代码将是相同的：

documentRepository.findById(1L).ifPresent(d -> System.out.println(d.getDocText()));

复制

如果我们尝试执行 repository 方法，我们将看到以下内容：

java.lang.IllegalStateException: Failed to execute Application   
…  
Caused by: org.hibernate.exception.DataException: could not execute query   
…   
Caused by: org.postgresql.util.PSQLException: Bad value for type long: This is the document text number 1   
…   


复制

正如我们所见，Hibernate 将@Lob 属性值作为对 LOB 对象数据的引用来处理。数据库表中的 TEXT 列类型不会影响此行为。

那么保存数据呢？让我们清理表格，尝试使用 @Lob 字段保存文档实体，并使用 Spring Data JPA 获取它。这是执行此操作的代码：

//Saving 
Document doc = new Document();   
doc.setDateCreated(LocalDateTime.now());   
doc.setDocText("This is another text document");   
documentRepository.save(doc);  
... 
//Fetching 
documentRepository.findAll().forEach(d -> System.out.println(d.getDocText));   
... 
//Result 
This is another text document 


复制

因此，看起来我们的具有 @Lob 属性的实体可以与 TEXT 列一起使用。在数据库表中，我们将看到熟悉的画面：

如果我们使用 SQL 将文档数据插入到表中，然后选择数据，我们将得到以下信息：

insert into document (date_created, doc_txt) values ('2021-10-10', 'This is the document text'); 

select * from document; 

复制

现在我们将无法使用 Spring Data JPA 从数据库中选择数据。选择第二行时，应用程序将因类型转换错误而崩溃。

让我们将@Type 注解添加到属性…

@Lob   
@Type(type = "org.hibernate.type.TextType")   
@Column(name = "doc_txt")   
private String docText;

复制

…并尝试将文档的文本数据打印到应用程序控制台。

documentRepository.findAll().forEach(d -> System.out.println(d.getDocText)); 

复制

我们将看到以下内容：

Hibernate: select document0_.id as id1_0_, document0_.date_created as date_cre2_0_, document0_.text as text3_0_ from document document0_   
  
76388   
This is the document text 

复制

使用@Type 注解，我们可以选择数据，但是 OID 引用被翻译成文本，所以我们“丢失”了存储在 LOB 存储中的文本。

结论：在表格中存储长文本

那么将长文本作为文本列存储在数据库中的优缺点是什么：
优点：

查询按预期工作；无需单独的事务或本机查询

缺点：

存储大小限制为 1Gb
混合 @Lob 属性定义和 TEXT 列数据类型可能会导致意外结果。

最后的话：如何在 PostgreSQL 中存储长文本

在大多数情况下，将长文本数据与其他实体数据一起存储在同一个表中应该可以正常工作。它将允许您使用 Hibernate 和直接 SQL 操作数据。
- 在 Hibernate 5 中，对 JPA 实体属性使用 @Type(type = “org.hibernate.type.TextType”) 注释。
- 如果您使用 Hibernate 6，请优先使用 @Column(name = …, length = Length.LOB_DEFAULT) 注释作为列定义。
- 请注意，使用这种方法时，我们不能存储超过 1Gb 的文本。
如果您计划存储大量字符数据（超过 1Gb），请为 JPA 实体属性使用 @Lob 注释。 Hibernate 将使用 PostgreSQL 为大量数据优化的专用存储。当我们使用 LOB 时，我们应该考虑几件事情。
- 我们必须在一个事务中执行 JPQL 查询和 Spring Data JPA 查询方法，或者显式禁用自动提交模式。
- 要在 WHERE 条件中使用 LOB 列，我们可能需要使用本机查询。
Hibernate 文档中有一个很好的建议：请不要（ab）使用 JPA 的 @Lob 注释，因为你想要一个 TEXT 列。 @Lob 注解的目的不是控制 DDL 生成！因此，不要将@Lob 实体属性定义与 TEXT 列数据类型一起使用。

希望这些简单的规则可以帮助您避免在使用 Hibernate 在 PostgreSQL 中存储文本数据时出现问题。

原文标题：How to Store Text in PostgreSQL: Tips, Tricks, and Traps
原文作者：Andrey Belyaev
原文地址：https://dzone.com/articles/how-to-store-text-in-postgresql-tips-tricks-and-tr

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者