如何将PostgreSQL字符串迁移到文本类型？

Question

我们正在使用一个JPA注解类型，它看起来像这样（groovy代码）：

@Entity
@EqualsAndHashCode
class TextNote extends Serializable {
    @Id Long id
    String text
}

第一次写这个时，我对JPA非常陌生，先写了SQL，然后使注释的类与SQL匹配。通过阅读PostgreSQL，似乎以下是我想要的表格：

CREATE TABLE textnote (
    id bigint NOT NULL,
    text text
);

这样做成功了，我们得到了像这样的表格：

 id  |          text
-----+------------------------
 837 | really long text here

我现在想要做的是将JPA实体纠正为以下内容：

@Entity
@EqualsAndHashCode
class TextNote extends Serializable {
    @Id Long id
    @Lob String text
}

通过添加@Lob注释，JPA提供者（在我的情况下，是Hibernate）可以为我生成DDL，以防我们想要更换数据库。它还可以准确记录我希望文本字段的格式。现在当笔记被创建时，我会看到类似下面的内容：

 id  |          text
-----+------------------------
 837 | 33427

对于新的笔记来说很好，因为当我使用String getText()在代码中读取时，它会返回真正的长文本。老实说，我不知道PostgreSQL是如何实现text类型的，理论上也不需要知道。然而，我们的生产数据库已经存储了许多使用旧代码而没有@Lob注释的笔记。在现有数据库上运行新代码会生成类似这样的问题：

org.springframework.dao.DataIntegrityViolationException: Bad value for type long : not a number this time; SQL [n/a]; nested exception is org.hibernate.exception.DataException: Bad value for type long : not a number this time

对于现有的笔记，是否有一种在 SQL 中迁移旧笔记以正确使用 @Lob 和 text 类型的方法？提前感谢您的帮助。

- Joe

Answer 1

根据Postgres大对象文档，看起来您需要将每个文本块写入文件并单独导入。这不是您应该在SQL中做的事情。

我对JPA一无所知，但@Lob与DDL或切换数据库有什么关系？您完全更改了列的类型；Postgres的text类型有什么问题吗？

---

在这里解决问题，以免在评论中丢失:

真正的问题是@Lob创建了一个Postgres text列，但Hibernate将其视为本地Postgres“大型对象”，它将数据存储在其他位置，并仅在表中留下oid（然后按列类型存储为文本）。 这通常不是您想要的文本。

OP的解决方案是添加@Type(type="org.hibernate.type.StringClobType")来强制Hibernate存储常规文本。

Postgres通常不会将文本存储为五位数字的整数。 :)

- Eevee

---

如果没有该注解，JPA提供程序将生成列类型为varchar的表，这不是我想要的。 @Lob生成PostgreSQL text类型，这正是我想要的。如果我想切换数据库，则希望使用JPA提供程序为我生成DDL。为了做到这一点，必须使用@Lob。明白了吗？ - Joe

你确定@Lob生成的是text吗？无论是文档还是您发布的输出结果都让我觉得它会生成oid——这也是Postgres存储大对象的方式。如果它真的是text，那么你应该只能看到_text_，而不是一个神秘的数字。 - Eevee

我很确定，这是使用@Lob注释输出的表格：gist - Joe

这可能是Hibernate中的bozotic行为。您正在生成一个“text”列，但它被荒谬地视为大对象列；文本应该出现在表中。（postgres绝对不会将常规文本存储为数字。）该文章链接到此SO问题，该问题也存在类似的问题。 - Eevee

Hibernate中bozotic行为的好处。我添加了非标准的Hibernate注释@Type(type="org.hibernate.type.StringClobType")，文本再次以人类可读的方式存储。我想原始问题基于对PostgreSQL的错误假设，所以感谢您的纠正！ :-) - Joe

不是要强迫你，但我想知道你是否能接受这个答案？ :) - Eevee

Answer 2

我使用JPA为我的实体添加注释，并使用Hibernate进行持久化。但我不想在我的实体中添加特定于Hibernate的注释。因此，我有一个包含实体和注释的数据jar文件。然后在我的实现中，我指定了persistence.xml并添加了“CustomTypes.hbm.xml”。该文件是通过persistence.xml中的mapping-file标签自动扫描或添加的。

这个映射文件包含了我想要从基本类型注册表中覆盖的类型。在这种情况下，使用了materialized_clob类型。但我不想这样做，因为当我使用查询工具浏览数据库时，我希望能够直接查看实际内容。所以我加入了：

<typedef name="materialized_clob" class="org.hibernate.type.TextType" />

我希望在不需要在我的数据包中添加特定的注释的情况下，强制使用指定类型来处理所有 clobs。

您可以通过 DEBUG 级别记录 org.hibernate.type.BasicTypeRegistry 来查看映射。

我花了一些时间才解决这个问题，希望这能帮助任何面临相同问题的人。因为这可能也会解决您的问题，所以我认为在这里发布可能是值得的。

- Wytze

Answer 3

我相信现在已经有点晚了，但对于将来遇到同样问题的人。

我也遇到了类似的问题，我的文本列中有旧数据，而这些数据并不是OIDs。当我尝试在升级后的应用程序中使用这些数据时，我也遇到了问题。

Bad value for type long

为了解决这个问题，我创建了这个脚本。也许它能帮助未来的某个人。

我从这篇文章中得到了很大的帮助。

- Tyagi Akhilesh

Answer 4

如果使用Spring，您可以创建一个LocalSessionFactoryBean的子类来注入Hibernate类型覆盖。请参考以下示例：

 public class CustomSessionFactoryBean extends LocalSessionFactoryBean {

    @Override
    protected SessionFactory buildSessionFactory(LocalSessionFactoryBuilder sfb) {
        // To store @Lob annotated Strings in TEXT fields.
        // By default for Postgres generated TEXT column, but stored OID of Postgres LOB object
        sfb.registerTypeOverride(new TextType() {
            @Override
            public String getName() {
                return StandardBasicTypes.MATERIALIZED_CLOB.getName();
            }
        });
        sfb.registerTypeOverride(new NTextType() {
            @Override
            public String getName() {
                return StandardBasicTypes.MATERIALIZED_NCLOB.getName();
            }
        });

        super.buildSessionFactory(sfb);
    }
 }

您不需要使用Hibernate特定的注释@Type，只需用@Lob注释字符串属性，并使用CustomSessionFactoryBean

- vp8106