使用EclipseLink JPA将XML类型存储到PostgreSQL [英] Using EclipseLink JPA to store XML type to PostgreSQL

问题描述

我是JPA的新手，我对如何使用JPA将XML类型存储到PostgreSQL有疑问.我只是在扩展现有的POJO，以将一个额外的XML字段持久存储到列中. POJO看起来像这样(我故意省略了许多其他列以使其更短，在添加此额外的XML类型之前，此POJO可以通过JPA很好地保留在PostgreSQL中).

I am new to JPA, and I have a question on how to use JPA to store XML type to PostgreSQL. I am simply expanding an existing POJO to include persisting an additional XML field to a column. The POJO looks something like this (I purposely left out a bunch of other columns to make this shorter, before adding this additional XML type, this POJO can persist into PostgreSQL just fine via JPA).

public class Finding implements Serializable {

private static final long serialVersionUID = -5814053129578212916L;
    ...
    @Column(name = "PLUGIN_TEXT_XML")
    private String pluginTextXML;

    public void setPluginText(String pluginText) {
        this.pluginText = pluginText;
    }
    public String getPluginTextXML() {
            return pluginTextXML;
    }
}

当我尝试保留此POJO时，我收到了PSQLException

When I try to persist this POJO, I received a PSQLException

Caused by: org.postgresql.util.PSQLException: ERROR: column "plugin_text_xml" is of type xml but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 668

我一直在尝试在线查找如何在JPA中映射XML类型，但是没有运气.如果有人可以帮我忙或者将我指向在线站点，以便我可以阅读更多内容，那将是很大的帮助！谢谢您的阅读！

I have been trying to look online on how to map a XML type in JPA but no luck. If anyone can give me a hand or point me to a site online so I can read up more on it then it would be a great help! Thank you for reading!

推荐答案

PostgreSQL对像json，xml等类似文本类型之间的隐式强制转换非常讨厌.

PostgreSQL is annoyingly strict about implicit casts between text-like types like json, xml, etc.

严格正确的解决方案是在使用JDBC设置参数时使用setObject(the_string, java.sql.Types.SQLXML).在ORM层中很难做到这一点，而且许多ORM似乎无法直接理解SQL/XML类型.

The strictly correct solution is to use setObject(the_string, java.sql.Types.SQLXML) when setting the parameter with JDBC. This is difficult through ORM layers, and many ORMs don't seem to understand the SQL/XML types directly.

您可以告诉PostgreSQL通过修改系统目录来允许从text到xml的隐式转换.这不是理想的方法，但是会起作用.

You can tell PostgreSQL to permit the cast from text to xml to be made implicitly by modifying the system catalogs. It isn't ideal, but it'll work.

在psql中，您可以使用\dC xml将猫列出到xml:

In psql, you can use \dC xml to list cats to xml:

                               List of casts
    Source type    |    Target type    |      Function      |   Implicit?   
-------------------+-------------------+--------------------+---------------
 character         | xml               | xml                | no
 character varying | xml               | xml                | no
 text              | xml               | xml                | no
 xml               | character         | (binary coercible) | in assignment
 xml               | character varying | (binary coercible) | in assignment
 xml               | text              | (binary coercible) | in assignment
(6 rows)

请参阅隐式:否"?

您需要使它们可分配转换.由于它们是内置类型，因此您无法为其CREATE CAST进行更改，因此必须直接更新目录，更改pg_cast表.

You need to make them assignment-convertable. Since they're built-in types you can't CREATE CAST for them, you have to update the catalogs directly, changing the pg_cast table.

UPDATE pg_cast SET castcontext = 'a' 
FROM pg_cast c 
INNER JOIN pg_type srctype ON (c.castsource = srctype.oid)
INNER JOIN pg_type dsttype on (c.casttarget = dsttype.oid) 
WHERE pg_cast.oid = c.oid 
  AND srctype.typname IN ('text','varchar') 
  AND dsttype.typname = 'xml';

处理系统目录可能会产生意想不到的效果.在这种情况下，我认为进行此更改是相当安全的，但仍使系统的内部混乱.后果自负.

Messing with the system catalogs can have unexpected effects. In this case I believe it is fairly safe to make this change, but it's still messing with the system's innards. Proceed at your own risk.

这篇关于使用EclipseLink JPA将XML类型存储到PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！