Very complete response. Thank you Lukas. Now tour plan is clear to me and i
likeit.
Il 17/apr/2015 05:45 PM, "Lukas Eder" <[email protected]> ha scritto:
> 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 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
> [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
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.
