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
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
