Jooq custom data type binding of postgres xml (java.sql.SQLXML) to org.w3c.dom.Document

JooqSqlXmlTest.java

package jooqdb;

import static org.jooq.SQLDialect.POSTGRES;
import static org.jooq.impl.DSL.constraint;
import static org.jooq.impl.DSL.field;
import static org.jooq.impl.DSL.name;
import static org.jooq.impl.DSL.table;
import static org.jooq.impl.DSL.using;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.UUID;

import org.cristalise.kernel.persistency.outcome.Outcome;
import org.cristalise.storage.jooqdb.bindings.PostgreSqlXmlBinding;
import org.jooq.DSLContext;
import org.jooq.DataType;
import org.jooq.Field;
import org.jooq.Record;
import org.jooq.SQLDialect;
import org.jooq.Table;
import org.jooq.impl.DefaultDataType;
import org.jooq.impl.SQLDataType;
import org.junit.After;
import org.junit.Before;
import org.junit.Ignore;
import org.junit.Test;
import org.w3c.dom.Document;

import lombok.val;

//@Ignore("Postgres test cannot run on Travis")
public class JooqSqlXmlTest {
    static final String TABLE_NAME = "TEST";
    DSLContext context;

    static DataType<Document> XMLTYPE = new DefaultDataType<Document>(POSTGRES, Document.class, "xml").asConvertedDataType(new PostgreSqlXmlBinding());

    static Table<Record>      TEST  = table(name(TABLE_NAME));
    static Field<UUID>        ID    = field(name("UUID"), UUID.class);
    static Field<Document>    XML   = field(name("XML"),  Document.class);

    @Before
    public void before() throws Exception {
        openPostgres();
        createTable();
    }

    @After
    public void after() {
        dropTable();
        context.close();
    }

    public void openPostgres() throws Exception {
        String userName = "postgres";
        String password = "cristal";
        String url      = "jdbc:postgresql://localhost:5432/integtest";

        Connection conn = DriverManager.getConnection(url, userName, password);
        context = using(conn, SQLDialect.POSTGRES);
    }

    public int createTable() {
        return context.createTableIfNotExists(TEST)
            .column(ID, SQLDataType.UUID.nullable(false))
            .column(XML, XMLTYPE.nullable(true))
            .constraints(constraint("PK_"+TABLE_NAME).primaryKey(ID))
        .execute();
    }

    public int dropTable() {
        return context.dropTableIfExists(TEST).execute();
    }

    public int set(UUID uuid, Document xml) {
        val insertQuery = context.insertQuery(TEST);

        insertQuery.addValue(ID, uuid);
        insertQuery.addValue(XML, xml);

        return insertQuery.execute();
    }

    public Document fetch(UUID uuid) throws Exception {
        Record result = context
                .select().from(TEST)
                .where(ID.equal(uuid))
                .fetchOne();

        return result.get(XML);
    }
    
    @Test
    public void testWithPostgres() throws Exception {
        UUID uuid = UUID.randomUUID();

        assert set(uuid, Outcome.parse("<Outcome/>")) == 1;
        assert fetch(uuid) != null;
    }
}

output.log

14:56:54.363 [main] INFO  org.jooq.Constants - 
                                      
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@  @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@        @@@@@@@@@@
@@@@@@@@@@@@@@@@  @@  @@    @@@@@@@@@@
@@@@@@@@@@  @@@@  @@  @@    @@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@
@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@
@@@@@@@@@@        @@  @  @  @@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@  @@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  Thank you for using jOOQ 3.13.4
                                      
14:56:54.384 [main] DEBUG org.jooq.tools.LoggerListener - Executing query          : create table if not exists "TEST"("UUID" uuid not null, "XML" xml null, constraint "PK_TEST" primary key ("UUID"))
14:56:54.447 [main] DEBUG org.jooq.tools.LoggerListener - Affected row(s)          : 0
14:56:54.976 [main] DEBUG org.jooq.tools.LoggerListener - Executing query          : insert into "TEST" ("UUID", "XML") values (cast(? as uuid), ?::xml)
14:56:55.037 [main] DEBUG org.jooq.tools.LoggerListener - -> with bind values      : insert into "TEST" ("UUID", "XML") values ('c35fc3a1-4bcf-4f58-bfb1-73fce52a2ac6', '<Outcome/>'::xml)
14:56:55.041 [main] TRACE org.jooq.impl.DefaultBinding - Binding variable 1       : c35fc3a1-4bcf-4f58-bfb1-73fce52a2ac6 (class java.util.UUID)
14:56:55.064 [main] DEBUG org.jooq.tools.LoggerListener - Affected row(s)          : 1
14:56:55.423 [main] DEBUG org.jooq.tools.LoggerListener - Executing query          : select * from "TEST" where "UUID" = cast(? as uuid)
14:56:55.424 [main] DEBUG org.jooq.tools.LoggerListener - -> with bind values      : select * from "TEST" where "UUID" = 'c35fc3a1-4bcf-4f58-bfb1-73fce52a2ac6'
14:56:55.424 [main] TRACE org.jooq.impl.DefaultBinding - Binding variable 1       : c35fc3a1-4bcf-4f58-bfb1-73fce52a2ac6 (class java.util.UUID)
14:56:55.535 [main] TRACE org.jooq.tools.LoggerListener - Record fetched           : +------------------------------------+-------------------------------------+
14:56:55.535 [main] TRACE org.jooq.tools.LoggerListener -                          : |UUID                                |XML                                  |
14:56:55.535 [main] TRACE org.jooq.tools.LoggerListener -                          : +------------------------------------+-------------------------------------+
14:56:55.535 [main] TRACE org.jooq.tools.LoggerListener -                          : |c35fc3a1-4bcf-4f58-bfb1-73fce52a2ac6|org.postgresql.jdbc.PgSQLXML@2053d869|
14:56:55.535 [main] TRACE org.jooq.tools.LoggerListener -                          : +------------------------------------+-------------------------------------+
14:56:55.536 [main] DEBUG org.jooq.tools.LoggerListener - Fetched result           : +------------------------------------+-------------------------------------+
14:56:55.536 [main] DEBUG org.jooq.tools.LoggerListener -                          : |UUID                                |XML                                  |
14:56:55.542 [main] DEBUG org.jooq.tools.LoggerListener -                          : +------------------------------------+-------------------------------------+
14:56:55.542 [main] DEBUG org.jooq.tools.LoggerListener -                          : |c35fc3a1-4bcf-4f58-bfb1-73fce52a2ac6|org.postgresql.jdbc.PgSQLXML@2053d869|
14:56:55.542 [main] DEBUG org.jooq.tools.LoggerListener -                          : +------------------------------------+-------------------------------------+
14:56:55.542 [main] DEBUG org.jooq.tools.LoggerListener - Fetched row(s)           : 1
14:56:55.545 [main] DEBUG org.jooq.tools.LoggerListener - Executing query          : drop table if exists "TEST"
14:56:55.564 [main] DEBUG org.jooq.tools.LoggerListener - Affected row(s)          : 0
java.lang.ClassCastException: org.postgresql.jdbc.PgSQLXML cannot be cast to org.w3c.dom.Document
	at jooqdb.JooqSqlXmlTest.fetch(JooqSqlXmlTest.java:111)
	at jooqdb.JooqSqlXmlTest.testWithPostgres(JooqSqlXmlTest.java:119)

PostgreSqlXmlBinding.java

package org.cristalise.storage.jooqdb.bindings;

import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Types;
import java.util.Objects;

import org.cristalise.kernel.persistency.outcome.Outcome;
import org.jooq.Binding;
import org.jooq.BindingGetResultSetContext;
import org.jooq.BindingGetSQLInputContext;
import org.jooq.BindingGetStatementContext;
import org.jooq.BindingRegisterContext;
import org.jooq.BindingSQLContext;
import org.jooq.BindingSetSQLOutputContext;
import org.jooq.BindingSetStatementContext;
import org.jooq.Converter;
import org.jooq.conf.ParamType;
import org.jooq.exception.ConfigurationException;
import org.jooq.impl.DSL;
import org.w3c.dom.Document;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@SuppressWarnings("serial")
public class PostgreSqlXmlBinding implements Binding<Object, Document> {

    @Override
    public Converter<Object, Document> converter() {
        return new Converter<Object, Document>() {
            @Override public Class<Object> fromType() {
                return Object.class; 
            }

            @Override public Class<Document> toType() { 
                return Document.class;
            }

            @Override public Document from(Object xml) {
                if (xml == null) return null;

                try {
                    return Outcome.parse(xml.toString());
                }
                catch (Exception e) {
                    log.error("", e);
                    throw new ConfigurationException(e.getMessage());
                }
            }

            @Override public Object to(Document doc) {
                if (doc == null) return null;

                try {
                    return Outcome.serialize(doc, false);
                }
                catch (Exception e) {
                    log.error("", e);
                    throw new ConfigurationException(e.getMessage());
                }
            }
        };
    }

    /** 
     * Depending on how you generate your SQL, you may need to explicitly distinguish
     * between jOOQ generating bind variables or inlined literals.
     */
    @Override
    public void sql(BindingSQLContext<Document> ctx) throws SQLException {
        // '::' is a postgres specific cast operator
        if (ctx.render().paramType() == ParamType.INLINED) {
            ctx.render().visit(DSL.inline(ctx.convert(converter()).value())).sql("::xml");
        }
        else {
            ctx.render().sql("?::xml");
        }
    }

    /**
     * Registering SQLXML type for JDBC CallableStatement OUT parameters
     */
    @Override
    public void register(BindingRegisterContext<Document> ctx) throws SQLException {
        ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
    }

    /** 
     * Converting the SQLXML to a String value and setting that on a JDBC PreparedStatement
     */
    @Override
    public void set(BindingSetStatementContext<Document> ctx) throws SQLException {
        Object value = ctx.convert(converter()).value();
        ctx.statement().setString(ctx.index(), value == null ? null : Objects.toString(value));
    }

    /** 
     * Getting a String value from a JDBC ResultSet and converting that to a Document
     */
    @Override
    public void get(BindingGetResultSetContext<Document> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.resultSet().getString(ctx.index()));
    }

    /** 
     * Getting a String value from a JDBC CallableStatement and converting that to a Document
     */
    @Override
    public void get(BindingGetStatementContext<Document> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.statement().getString(ctx.index()));
    }

    /** 
     * Setting a value on a JDBC SQLOutput (useful for Oracle OBJECT types)
     */
    @Override
    public void set(BindingSetSQLOutputContext<Document> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }

    /** 
     * Getting a value from a JDBC SQLInput (useful for Oracle OBJECT types)
     */
    @Override
    public void get(BindingGetSQLInputContext<Document> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }
}