General jdbc/sqlxml support and xpath operator support

[Denis Miorandi]

Hi Lukas,

          I'm quite new to jooq but it seems to me that sqlxml support is missing. In another post you said that is quite difficult to integrate it in jooq and suggested to use somthing like a converter on column.

By now I'got a lot of xml code saved on db and often is managed by sql queries. 

(Use a stored is not an option for me cause I want just tables and no others vendor dependent code on db causa I've got a lot of cross db shared code).

My question is have you in plan to support jdbc sqlxml in the future? I was using querydsl-sql in the past and I've suggested support that functions and was made quite easily.

This is reference to

https://github.com/querydsl/querydsl/issues/1034

In some cases String conversion is ok but there are some limitation (i.e. size) expecially on oracle so use sqlxml (streaming) works fine.

Also what about xpath operator native support?

I mean, do you think queries like the two following one could be supported (near to native way, I know you can write it using expressions ) in the future?

I would like to understand the approach to this kind of data managment on middle-long terms.

Tks 

Denis

T-SQL

WITH XML_Q
AS (
	SELECT TOP (10) NM_Notifiche.ID_Notifica
		,NM_Notifiche.ID_Evento
		,NM_Notifiche.ID_tp_notifica
		,NM_Notifiche.data
		,NM_Notifiche.data_notifica
		,NM_Notifiche.data_evasione
		,NM_Notifiche.nrTentativo
		,NM_Notifiche.note
		,NM_Eventi.ID_Evento AS Expr1
		,NM_Eventi.ID_tp_evento
		,NM_Eventi.data AS Expr2
		,NM_Eventi.data_notifica AS Expr3
		,NM_Eventi.messaggio
		,MAIL.value('subject[1]', 'nvarchar(255)') AS MAIL_SUBJECT
		,MAIL.value('text[1]', 'nvarchar(255)') AS MAIL_TEXT
		,MAIL.value('attachments[1]', 'nvarchar(255)') AS MAIL_ATTACHMENT
		,MAIL.value('idNotifica[1]', 'nvarchar(255)') AS ID_NOTIFICAXML
	FROM NM_Notifiche
	INNER JOIN NM_Eventi ON NM_Notifiche.ID_Evento = NM_Eventi.ID_Evento
	CROSS APPLY [messaggio].nodes('/nmMsgMessage[1]/notifiers[1]/*[idNotifica=sql:column("NM_Notifiche.ID_Notifica")]') AS T_MESSAGGIO(MAIL)
	WHERE NM_Notifiche.ID_EVENTO = 2548
	)
SELECT *
FROM XML_Q
WHERE MAIL_SUBJECT NOT LIKE 'Sql monitor%'
	AND MAIL_SUBJECT NOT LIKE '%SQLException%'

PG

WITH notifiers_xml
AS (
	SELECT e.id_evento
		,n.id_notifica
		,unnest(xpath('/nmMsgMessage/notifiers/*', e.messaggio)) AS notifier_xml
		,unnest(xpath('/nmMsgMessage/notifiers/*/idNotifica/text()', e.messaggio)::TEXT []::INT []) AS id_notifica_xml
	FROM nm_eventi e
	INNER JOIN nm_notifiche n ON e.id_evento = n.id_evento
	WHERE e.id_evento = 7916
	)
SELECT id_evento
	,id_notifica
	,notifier_xml
	,(xpath('/nmMsgMailNotifier/from/text()', notifier_xml)::TEXT []) [1] AS
FROM
	,(xpath('/nmMsgMailNotifier/to/address/text()', notifier_xml)::TEXT []) [1] AS recipients
	,(xpath('/nmMsgMailNotifier/cc/address/text()', notifier_xml)::TEXT []) [1] AS cc
	,(xpath('/nmMsgMailNotifier/bcc/address/text()', notifier_xml)::TEXT []) [1] AS bcc
	,(xpath('/nmMsgMailNotifier/subject/text()', notifier_xml)::TEXT []) [1] AS subject
	,(xpath('/nmMsgMailNotifier/text/text()', notifier_xml)::TEXT []) [1] AS TEXT
FROM notifiers_xml
WHERE id_notifica = id_notifica_xml

[Lukas Eder]

Hi Denis,

Thank you for your enquiry.

XML (much like JSON) in the database is per se a *very* vendor dependent thing. But we're here to help and we can try to make the abstraction over vendors as smooth as possible for you, even before we formally support an XML data type in jOOQ.

On a side note: We have other, paying customers who are very interested in this kind of feature, so we'll make this a high priority for jOOQ 3.7.

Let's have a look at how this could be achieved already with jOOQ 3.5 and 3.6 (due next week) with jOOQ bindings.

Using SQLXML with jOOQ

Much like BLOB and CLOB, JDBC's SQLXML Type doesn't entirely fit into jOOQ's record model, where record attributes are real values, not resource handles to streamable values. However, you can use jOOQ 3.5's org.jooq.Binding types to implement your own binding and expose the JDBC SQLXML type to the outside of the jOOQ Record. Here's an example Binding:

package org.jooq.test.all.bindings;

import java.sql.SQLException;

import java.sql.SQLXML;

import java.sql.SQLXML;

import java.sql.Types;

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;

public class OracleXmlAsSQLXMLBinding implements Binding<Object, SQLXML> {

    /**

     * Generated UID

     */

    private static final long serialVersionUID = 358789452467943117L;

    @Override

    public Converter<Object, SQLXML> converter() {

        return new Converter<Object, SQLXML>() {

            @Override

            public SQLXML from(Object databaseObject) {

                return null; // Not needed in the example

            }

            @Override

            public Object to(SQLXML userObject) {

                return null; // Not needed in the example

            }

            @Override

            public Class<Object> fromType() {

                return Object.class;

            }

            @Override

            public Class<SQLXML> toType() {

                return SQLXML.class;

            }

        };

    }

    @Override

    public void sql(BindingSQLContext<SQLXML> ctx) throws SQLException {

        ctx.render().sql("?");

    }

    @Override

    public void register(BindingRegisterContext<SQLXML> ctx) throws SQLException {

        ctx.statement().registerOutParameter(ctx.index(), Types.SQLXML);

    }

    @Override

    public void set(BindingSetStatementContext<SQLXML> ctx) throws SQLException {

        if (ctx.value() == null)

            ctx.statement().setObject(ctx.index(), null);

        else

            ctx.statement().setSQLXML(ctx.index(), ctx.value());

    }

    @Override

    public void set(BindingSetSQLOutputContext<SQLXML> ctx) throws SQLException {

        if (ctx.value() == null)

            ctx.output().writeObject(null);

        else

            ctx.output().writeSQLXML(ctx.value());

    }

    @Override

    public void get(BindingGetResultSetContext<SQLXML> ctx) throws SQLException {

        ctx.value(ctx.resultSet().getSQLXML(ctx.index()));

    }

    @Override

    public void get(BindingGetStatementContext<SQLXML> ctx) throws SQLException {

        ctx.value(ctx.statement().getSQLXML(ctx.index()));

    }

    @Override

    public void get(BindingGetSQLInputContext<SQLXML> ctx) throws SQLException {

        ctx.value(ctx.input().readSQLXML());

    }

}

You can now configure your code generator to use this binding for all XMLTYPE columns in Oracle (note, there might be some issues as we don't fully recognise SYS.XMLTYPE yet: https://github.com/jOOQ/jOOQ/issues/1112). Roughly:

<customType>

  <name>OracleXmlAsSQLXML</name>

  <type>java.sql.SQLXML</type>

  <binding>org.jooq.test.all.bindings.OracleXmlAsSQLXMLBinding</binding>

</customType>

<forcedType>

  <name>OracleXmlAsSQLXML</name>

  <expression>ORACLE_XML_AS_SQLXML</expression>

</forcedType>

The column in the generated class will now be:

    public final TableField<TExoticTypesRecord, SQLXML> ORACLE_XML_AS_SQLXML = 

         createField("ORACLE_XML_AS_SQLXML", org.jooq.impl.DefaultDataType.getDefaultDataType("XMLTYPE"), this, "", new OracleXmlAsSQLXMLBinding());

I can now run the following test:

@Test

public void testOracleNativeXML() throws Exception {

    TExoticTypes t = T_EXOTIC_TYPES;

    clean(t);

    assertEquals(1,

    create().insertInto(t, t.ID, t.ORACLE_XML_AS_SQLXML)

            .values(1, null)

            .execute());

    SQLXML xml = null;

    try {

        xml = connection.createSQLXML();

        xml.setString("<a><b/></a>");

        assertEquals(1,

        create().insertInto(t, t.ID, t.ORACLE_XML_AS_SQLXML)

                .values(2, xml)

                .execute());

        Result<TExoticTypesRecord> records =

        create().selectFrom(t)

                .orderBy(t.ID)

                .fetch();

        assertEquals(

            asList(1, 2),

            records.stream()

                   .map(TExoticTypesRecord::getId)

                   .collect(toList()));

        assertEquals(

            asList(null, "<a><b/></a>"),

            records.stream()

                   .map(Unchecked.function(

                       (TExoticTypesRecord r) -> r.getOracleXmlAsSqlxml() == null

                           ? null

                           : r.getOracleXmlAsSqlxml().getString().replaceAll("\\s", "")

                   ))

                   .collect(toList()));

    }

    finally {

        JDBCUtils.safeFree(xml);

    }

}

More information about bindings can be found here:

http://www.jooq.org/doc/3.5/manual/code-generation/custom-data-type-bindings/

So, in other words, you can do whatever you want with your SQLXML types in jOOQ, rather easily, once you've set up the binding.

Using native support for the XPath operator

Once you've set up the above binding and applied that to your generated schema, you can create your own DSL templates based on Field<SQLXML> columns. Here's an example that will support Oracle, SQL Server, and PostgreSQL:

public static Field<String> xpath(String xpath, Field<SQLXML> xml) {

    return new CustomField<String>("xpath", SQLDataType.VARCHAR) {

        @Override

        public void accept(Context<?> ctx) {

            switch (ctx.family()) {

                case ORACLE:

                    ctx.visit(DSL.field("extract({0}, {1}).getStringVal()", String.class, xml, inline(xpath)));

                    break;

                case POSTGRES:

                    ctx.visit(DSL.field("(xpath({0}, {1})::text[]) [1]", String.class, inline(xpath), xml));

                    break;
                case ...
            }

        }

    }

}

Summary

I'll summarise by answering your concrete questions:

My question is have you in plan to support jdbc sqlxml in the future?

Yes, we'll add better support for XML and for JSON data types in the future. I cannot promise that we'll explicitly support SQLXML but the solution will be equivalent to SQLXML or better.

 

Also what about xpath operator native support?

Right now, you will have to implement your own Field implementations as shown before. But we will definitively provide our own, standardised XML access methods in the future, which emulate such expressions.

 

I mean, do you think queries like the two following one could be supported (near to native way, I know you can write it using expressions ) in the future?

Yes, the queries can be supported in a near to native way.

 

I would like to understand the approach to this kind of data managment on middle-long terms.

I believe that jOOQ will be the right place to implement the abstraction over the different, vendor-specific XML APIs. We're offering you the tooling for sophisticated templating and AST composition already today. Once we know that customers are interested in this feature even more (which they are), we'll add more focus on developing new improvements in this area.

I hope this helped. I'm more than happy to walk you through other, more concrete examples and integration challenges, should you have any.

Looking forward to hearing from you again,

Lukas

[Denis Miorandi]

Very complete response. Thank you Lukas. Now tour plan is clear to me and i likeit.

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/31XyeqdPtEM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Thanks for the feedback. Yep, I like it too - looking forward to implementing this! :-)

Have a nice weekend,

Lukas