Translator|赵庆尧评论|孙书娟基于JPA实体定义的DDL生成器是很多开发者的日常工作。在大多数情况下，我们使用Hibernate内置生成器或JPABuddy插件等工具来简化我们的工作，但也有例外，当涉及到在数据库中存储大量数据时，事情会变得有点复杂。用例：存储文档假设一个非空文档对象需要存储在PostgreSQL数据库中。JPA实体代码如下所示：Java:@Entity@Table(name="document")publicclassDocument{@Id@GeneratedValue(strategy=GenerationType.IDENTITY)@Column(name="id",nullable=false)private长号；@Column(name="date_created",nullable=false)privateLocalDateTimedateCreated;@Column(name="doc_txt")privateStringdocText;//为简洁起见省略了getters和setters}问题是：如果我们需要存储很长的文档文本呢？在Java中，字符串数据类型可以容纳大约2Gb的文本数据，但对于上述模型，表列大小将限制为255个字符。那么，我们应该改变什么？方法一：使用LOB存储在关系数据库中，有一种特定的数据类型用于存储大量数据：LOB（LargeOBject）。一旦我们需要在数据库中存储大文本，我们就可以开始定义LOB列。我们需要做的就是用@Lob注解标记docText属性。Java:@Lob@Column(name="doc_txt")privateStringdocText;让我们使用Hibernate为表生成DDL以映射“文档”实体。SQL将是：SQL:createtabledocument(idint8generatedbydefaultasidentity,date_createdtimestampnotnull,doc_txtoid,primarykey(id));可以看到，doc_text列的数据类型是oid。它是什么？在文档中是这样描述的：PostgreSQL提供了两种不同的方式来存储二进制数据。二进制数据可以使用数据类型BYTEA或使用大对象属性存储在表中，它以特殊格式将二进制数据存储在单独的表中，并通过在表中存储OID类型的值来引用该表。在我们的例子中，第二种方法有效。这个单独的表称为pg_largeobject，它存储分为“页”的数据，通常每页2kb，如文档中所述。因此，Hibernate将大文本作为二进制数据存储在单独的表中。这是否意味着我们应该在选择数据时进行额外的连接或在保存数据时进行额外的插入？让我们启用SQL日志记录，创建一个文档实体并使用SpringDataJPA将其保存到数据库中。Java：文档doc=newDocument();doc.setDateCreated(LocalDateTime.of(2020,1,1,10,10));doc.setDocText("这是文档文本");文档保存=documentRepository.save(doc);Hibernate会在控制台显示正常的SQL插入：SQL:insertintodocument(date_created,doc_txt)values(?,?)现在，我们可以通过在控制台执行如下SQL语句来检查数据是否正确存储：SQL:select*fromdocument我们会看到上面命令的结果应该类似于下表：iddata_createddoc_txt12020-01-0110:10:0076388我们在这个表中看不到文档文本，只是在大对象存储对.我们查看pg_largeobject表：SQLselect*frompg_largeobjectwhereloid=76338此时可以看到文档正文。loidpagenodata763880Thisisthedoctext所以Hibernate在后台自动将数据保存到两个表中。现在，我们可以尝试使用Spring数据JPA获取文档数据：JavadocumentRepository.findById(1L).ifPresent(d->System.out.println(d.getDocText()));我们可以在控制台看到如下SQL语句：SQLselectdocument0_.idasid1_0_0_,document0_.date_createdasdate_cre2_0_0_,document0_.doc_txtasdoc_txt3_0_0_fromdocumentdocument0_wheredocument0_.id=?输出应如下所示：PlainTextThisisthedoctextHibernateselectdatafrompg_largetable让我们尝试使用JPQL执行相同的查询。为此，我们创建了一个额外的SpringDataJPA存储库方法并调用它：Java//repository@Query("selectdfromDocumentdwhered.id=?1")OptionalfindByIdIs(Longid);//...//调用documentRepository.findByIdIs(1L).ifPresent(d->System.out.println(d.getDocText()));这种方式将无法完成我们预期的工作：Plaintextorg.springframework.orm.jpa.JpaSystemException:Unabletoaccesslobstream…Causedby:org.hibernate.HibernateException:Unabletoaccesslobstream…Causedby:org.postgresql.util.PSQLException:LargeObjectsmaynotbeusedinauto-commitmode。Hibernate执行额外的数据库读取以获取LOB数据。在自动提交模式下，此读取将在单独的事务中执行。PostgreSQL驱动程序明确禁止它，如上面的错误消息所示。为了解决这个问题，我们需要在事务中执行这样的查询，或者禁用自动提交模式。“CrudRepository”中的SpringDataJPA方法，例如findById()和findAll()，默认在事务中执行。这就是第一个示例中一切正常的原因。当我们使用SpringDataJPA查询方法或JPQL查询时，我们必须像下面的示例一样显式地使用@Transactional。Java@Transactional@Query("selectdfromDocumentdwhered.id=?1")OptionalfindByIdIs(Longid);@TransactionalListfindByDateCreatedIsBefore(LocalDateTimedateCreated);但是禁用自动提交模式似乎比使用@Transactional注释更可取。例如，要在SpringBoot中实现默认连接池（HikariCP），我们需要将spring.datasource.hikari.auto-commit属性设置为false。将文本存储在单独的表中可能会导致其他问题。让我们添加一个存储库方法来使用LIKE子句为docText字段选择文档：Java@TransactionalListfindByDocTextLike(Stringtext);此方法将生成以下查询：SQLselectdocument0_.idasid1_0_,document0_.date_createdasdate_cre2_0_,document0_.doc_txtasdoc_txt3_0_fromdocumentdocument0_wheredocument0_.doc_txtlike?逃脱？此查询将失败并出现以下错误：PlainTextorg.springframework.dao.InvalidDataAccessResourceUsageException：无法提取ResultSet；SQL[不适用]；...Caused:org.postgresql.util.PSQLException:ERROR:functionpg_catalog.like_escape(bigint,charactervarying)doesnotexist提示：没有函数匹配给定的名称和参数类型。您可能需要添加显式类型转换。Hibernate无法生成正确的SQL来处理LOB文本列的LIKE子句。对于这种情况，我们可以使用nativequery。在此查询中，我们必须从LOB存储中获取文本数据并将其转换为字符串格式。之后，我们可以在LIKE子句中使用它（不要忘记'@Transactional'）：Java@Query(value="select*fromdocumentd"+"whereconvert_from(lo_get(doc_txt),'UTF-8')喜欢'%'||?1||'%'",nativeQuery=true)@TransactionalListfindByDocTextLike(Stringtext);现在一切正常。请记住，nativequery可能与其他RDBMS不兼容，并且不会在运行时进行验证。仅在绝对必要时使用。小结：在PostgreSQL中使用LOB将大文本存储为LOB对象的优缺点是什么？优点：PostgreSQL对LOB对象使用优化存储；最多可存储4Gb的文本。缺点：WHERE子句中的某些函数（LIKE、SUBSTRING等）不能在Hibernate中用于LOB文本列。为此，我们需要使用nativeQuery；要使用JPQL或SpringDataJPA存储库查询方法获取文本，我们必须使用@Transactional注释存储库方法或禁用自动提交模式。问题来了：为什么不直接将文本数据存储在表中？让我们也讨论一下这种方法。方法二：重定义列PostgreSQL允许我们将长文本数据存储在特定数据类型（TEXT）的列中。我们可以在注释中指定列定义。Java@Column(name="doc_txt",columnDefinition="text")privateStringdocText;这使我们能够以“通常”的方式处理长文本。没有事务，本机查询和JPQL将按预期工作。相对于LOB类型，最大可以存储的字符串有1GB左右的限制。当长度小于4GB时可以使用LOB，但对于大多数用例来说已经足够长了。这里唯一的问题是硬编码的列定义。为了克服这个问题，我们可以在Hibernate5中使用注解@Type和转换器org.hibernate.type.TextType。与之前的列定义相比，它有一个优势：它不是特定于供应商的。Java@Type(type="org.hibernate.type.TextType")@Column(name="doc_txt")privateStringdocText;在Hibernate6中，删除了org.hibernate.type.TextType类。为了定义存储长文本的列，我们可以这样定义属性：Java@Column(name="doc_txt",length=Length.LOB_DEFAULT)privateStringdocText;这将在数??据库中给出以下列定义：doc_txtvarchar(1048576)。它不是TEXT类型，但您仍然可以在表中存储大约1Gb的文本。它是PostgreSQL中最大的字符串。在Hibernate6中，我们可以如下定义docText属性来生成包含TEXT数据类型的列：Java@JdbcTypeCode(SqlTypes.LONG32VARCHAR)@Column(name="doc_txt")privateStringdocText;不幸的是，从2022年6月开始，Hibernate6无法从表中获取数据。它生成正确的TEXT类型的表和列定义。从doc_txt列中提取数据到实体属性将失败。错误文本如下所示：PlainTextUnknownwrapconversionrequested:[Btojava.lang.String:`org.hibernate.type.descriptor.java.StringJavaType`(java.lang.String)Therefore,storingintext/VARCHARcolumn长文本带来的问题较少。事务、LIKE条件等都没有问题，唯一不足的是存储大小最多1Gb。还有其他的考虑吗？如果我们在数据库中使用带有@Lob注释的TEXT列类型，则可能会出现问题。让我们看看它是如何工作的。首先，我们创建一个表文档并向其中插入一些数据：SQLcreate表文档（idint8默认生成为身份，date_created时间戳不为空，doc_txt文本，主键（id））；insertintodocument(id,date_created,doc_txt)values(1,'2021-10-10','这是文档文本编号1');我们将使用带有@Lob列的文档实体定义：Java@Entity@Table(name="document")publicclassDocument{@Id@GeneratedValue(strategy=GenerationType.IDENTITY)@Column(name="id",nullable=假）私人长号;@Column(name="date_created",nullable=false)privateLocalDateTimedateCreated;@Lob@Column(name="doc_txt")privateStringdocText;//为简洁起见省略了getters和setters如果我们尝试执行这个存储库方法，我们将看到以下结果：PlainTextjava.lang.IllegalStateException:FailedtoexecuteApplication...Causedby:org.hibernate.exception.DataException:couldnotexecutequery...Causedby:org.postgresql.util.PSQLException:Badvaluefortypelong:Thisisthedocumenttextnumber1...我们可以看到Hibernate将@Lob属性值作为一对来处理LOB对象数据的引用数据库表中的TEXT列类型不影响此行为。保存数据呢？让我们先清空表，尝试用@Lob字段保存文档实体并使用SpringDataJPA获取它。下面是相应的代码：Java//SavingDocumentdoc=newDocument();doc.setDateCreated(LocalDateTime.now());doc.setDocText("这是另一个文本文档");文档库.保存（文档）；...//获取documentRepository.findAll().forEach(d->System.out.println(d.getDocText));...//ResultThisisanothertextdocumentSoitlooksliketheentitywiththe@Lobattribute似乎处理TEXT列。在数据库表中，我们看到熟悉的图片：iddata_createddoc_txt12022-06-1615:28:26.75104176388loidpagenodata763880这是另一个文本文档如果我们使用SQL将文档数据插入到表中，然后选择数据，我们会得到如下结果：SQLinsertintodocument(date_created,doc_txt)values('2021-10-10','这是文档文本');select*fromdocument;iddata_createddoc_txt12022-06-1615:28:26.7510417638822021-10-1000:00:00这是文档文本现在我们不能使用Spring数据JPA从数据库中选择数据。选择第二行时，应用程序因类型转换错误而崩溃。让我们在属性中添加@Type注解...Java@Lob@Type(type="org.hibernate.type.TextType")@Column(name="doc_txt")privateStringdocText;并尝试将文档的文本数据打印到应用程序控制台。JavadocumentRepository.findAll().forEach(d->System.out.println(d.getDocText));我们会看到如下输出信息：Hibernate:selectdocument0_.idasid1_0_,document0_.date_createdasdate_cre2_0_,document0_。textastext3_0_fromdocumentdocument0_76388这是文档文本使用@Type注释我们可以选择数据，但是OID引用被转换为文本，因此我们“丢失”了存储在LOB存储中的文本。简介：在表中存储长文本那么在数据库中将长文本存储为文本列的优缺点是什么？优点：查询将按预期工作；不需要单独的事务或nativequery。缺点：存储大小限制为1Gb将@Lob属性定义与TEXT列数据类型混合使用可能会导致意外结果。总结：如何在PostgreSQL中存储长文本1、在大多数情况下，将长文本数据与其他实体数据存储在同一张表中应该没问题。它将允许您使用Hibernate和SQL来操作数据。在Hibernate5中，JPA实体属性使用@Type(type="org.hibernate.type.TextType")注释。如果您使用Hibernate6，请对列定义使用@Column(name=...,length=length.lob_default)注释。请注意，使用此方法时，存储的文本不能超过1Gb。2.如果你打算存储大量的字符数据（超过1Gb），那么对JPA实体属性使用@Lob注解。Hibernate将使用PostgreSQL来优化大数据存储。使用lob时需要考虑一些事项。我们必须在一个事务中执行JPQL查询和SpringDataJPA查询方法，或者显式禁用自动提交模式。要在WHERE条件下使用LOB列，我们可能需要使用nativequery。3.Hibernate文档中有一个很好的建议：请不要仅仅因为你想要一个TEXT列就使用JPA的@Lob注释。@Lob注解的目的不是为了控制DDL的生成！因此，不要将@Lob实体属性定义与TEXT列数据类型一起使用。希望这些简单实用的方法可以帮助大家在PostgreSQL中使用Hibernate存储文本数据时避免一些不必要的问题。译者介绍赵庆尧，社区编辑，从事驱动开发。原标题：HowtoStoreTextinPostgreSQL:Tips,Tricks,andTraps，作者：AndreyBelyaev
