如何在PostgreSQL中存储文本

如何在***中存储文本

​译者 | 赵青窕

审校 | 孙淑娟

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

用例：存储文档

假设需要在***数据库中存储一个非空的文档对象。JPA实体代码如下所示:

Java：@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的文本数据，但是对于上面的模型，表的列(table column)大小将被限制为255个字符。那么，我们应该改变什么呢?

方法1：使用LOB存储

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

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

让我们使用Hibernate为表生成DDL，以映射“Document”实体。SQL将是:

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。它是什么?在文档中有如下说明:

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

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

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

Java：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插入:

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

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

SQL:select * from document

我们将看到上述命令的结果应该与下表类似:

id

data_created

doc_txt

1

2020-01-01 10:10:00

76388

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

SQLselect * from pg_largeobject where loid=76338

此时，就可以看到文档文本了。

loid

pageno

data

76388

0

This is the doc text

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

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

我们可以在控制台中看到以下SQL语句:

SQLselect 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=?

输出应符合下面的预期：

Plain TextThis is the doc text

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

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

这种方式将无法完成我们的预期工作：

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

Java@Transactional @Query("select d from Document d where d.id = ?1") Optional findByIdIs(Long id); @Transactional List findByDateCreatedIsBefore(LocalDateTime dateCreated);

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

Java@Transactional List findByDocTextLike(String text);

该方法将生成以下查询:

SQLselect 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 ?

这个查询将会失败，错误如下:

Plain Textorg.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子句。对于这种情况，我们可以使用nativequery。在这个查询中，我们必须从LOB存储中获取文本数据，并将其转换为字符串格式。之后，我们可以在LIKE子句中使用它(不要忘记' @Transactional '):

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

现在一切都工作正常。请记住，nativequery可能与其他RDBMS不兼容，并且不会在运行时进行验证。只有在绝对必要的时候才使用。

总结：采用LOB来存储

在***中将大型文本存储为LOB对象的优点和缺点是什么呢?

优点:

***为LOB对象使用了优化的存储方式；可以存储多达4Gb的文本。

缺点:

这里有一个问题：为什么不直接将文本数据存储在表中？我们也来讨论一下这种方式。

方式2：Column Re-Definition

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

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

这使我们能够以“通常”的方式处理长文本。没有事务，native query和JPQL按照预期工作。与LOB类型相比，有一个限制是可存储的最长字符串约为1GB。

当长度小于4GB时，可以使用LOB，但对于大多数用例来说已经足够长了。

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

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

在Hibernate 6中，org.hibernate.type.TextType类被删除了。为了定义存储长文本的列，我们可以这样定义属性:

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

这将在数据库中给出以下列定义:doc_txt varchar(1048576)。它不是TEXT类型，但仍然可以在表中存储大约1Gb的文本。它是***中最大的字符串。

在Hibernate 6中，我们可以按照下面的方式定义docText属性来生成一个包含TEXT数据类型的列:

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

不幸的是，自2022年6月开始，Hibernate 6无法从表中获取数据。它生成TEXT类型的正确表和列定义。将数据从doc_txt列提取到实体属性将会失败。错误文本如下所示:

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

因此，在text /VARCHAR列中存储长文本带来的问题较少。事务、LIKE条件等没有问题。唯一的缺点就是存储大小最多是1Gb。还有其他注意事项吗?

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

SQLcreate 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列的文档实体定义:

Java@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 }

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

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

如果我们尝试执行这个repository方法，将会看到下面的结果:

Plain Textjava.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获取它。下面是对应的代码：

Java//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列。在数据库表中，我们会看到熟悉的画面:

id

data_created

doc_txt

1

2022-06-16 15:28:26.751041

76388

loid

pageno

data

76388

0

This is another text document

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

SQLinsert into document (date_created, doc_txt) values ('2021-10-10', 'This is the document text'); select * from document;

id

data_created

doc_txt

1

2022-06-16 15:28:26.751041

76388

2

2021-10-10 00:00:00

This is the document text

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

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

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

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

JavadocumentRepository.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存储中的文本。

总结：在表中存储长文本

那么，在数据库中将长文本存储为文本列的优点和缺点是什么呢?

优点：

查询将会按照预期工作；不需要单独的事务或nativequery。

缺点：

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

总结：如何在***中存储长文本

1.在大多数情况下，将长文本数据与其他实体数据一起存储在同一个表中应该没问题。它将允许您使用Hibernate和SQL来操作数据。

在Hibernate 5中，使用@Type(type = "org.hibernate.type.TextType")注释JPA实体属性。如果你使用Hibernate 6，使用@Column(name =…， length = length . lob_default)注释用于列定义。注意，使用这种方法时，存储的文本不能超过1Gb。

2.如果您计划存储大量的字符数据(超过1Gb)，那么对JPA实体属性使用@Lob注释。Hibernate

将使用***来对大数据存储进行优化。在使用lob时，有几件事需要考虑。

3.Hibernate文档中有一个很好的建议：请不要仅仅因为你想要一个TEXT列就使用JPA的@Lob注释。@Lob注释的目的不是控制DDL的生成！因此，不要将@Lob实体属性定义与TEXT列数据类型一起使用。

希望这些简单的实用方法可以帮助你使用Hibernate在***中存储文本数据时，避免一些不必要的问题。

译者介绍

赵青窕，社区编辑，从事驱动开发工作。

原文标题：​​How to Store Text in ***: Tips, Tricks, and Traps​​​，作者：Andrey Belyaev​

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。