Using EclipseLink JPA to store XML type to PostgreSQL

Question

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;
    }
}

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

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!

Answer 1

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

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.

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.

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)

See the "implicit: no" ?

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.

Answer 2

You could potentially use a Converter to convert between String and Postgres XML type.

Otherwise, you can extend your PostgreSQLPlatform to support the type. (see, DatabasePlatform.setParameterValueInDatabaseCall(), DatabasePlatform.getCustomModifyValueForCall() and DatabasePlatform.shouldUseCustomModifyForCall()).

I would suggest you also log a bug if you can't get it to work. There should be an easy way to do this.