Saving and Loading XML to PostgreSQL via jOOQ

By Adrian Smith11 Jun 2019200 words1 mins to read

To use XML Java DOM objects with PostgreSQL's XML datatype, do the following.

Create your table with the XML datatype: (You now have a table where the x column can be an XML document, or can be null.)

CREATE TABLE foo (xml_column XML);

Add the following to your Maven dependencies:

<dependency>
    <groupId>com.databasesandlife</groupId>
    <artifactId>java-common</artifactId>
    <version>8.13.0</version>
</dependency>

Thereafter, tell the jOOQ generation process that any PostgreSQL columns of the type XML should be converted to/from Java DOM Element objects thus:

<forcedType>
    <userType>org.w3c.dom.Element</userType>
    <converter>com.databasesandlife.util.jooq.PostgresXmlDomElementBinding</converter>
    <types>XML</types>   <!-- DB types to match -->
</forcedType>

Regenerate your sources with mvn generate-sources and now you can write code such as:

import org.w3c.dom.Element;

// Get an XML DOM "Element"
Element element = DomParser.from("<input/>");

// Set the value in your record
FooRecord r = new FooRecord();
r.setXmlColumn(element);

// Fetch the record
FooRecord fetched = ....;
Element e = fetched.getXmlColumn();

This article was written by Adrian Smith on 11 Jun 2019

Follow me: Facebook | Twitter | LinkedIn | Email

More on: PostgreSQL | jOOQ