使用EclipseLink JPA将XML类型存储到PostgreSQL

本文介绍了使用EclipseLink JPA将XML类型存储到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.