Xml Type support on querydsl-sql

[dmiorandi]

as discussed in (https://groups.google.com/forum/#!topic/querydsl/wlQhpGe1gjk) i would like to use xml type into querydsl-sql. I'm a little concerned if is better to use as result of an xml field jdbc SQLXML type or a string. First one contains the second b.t.w. Maybe should be nice to have the possibility to choose it in the mapping? What do you think about it?

Here there are some code i used to test behavior. In my code I've forced mapping to String in class generation: (by default output is an Object)

        configuration.register("NM_EVENTI", "MESSAGGIO", String.class);
        configuration.register("NM_EVENTI_FALLBACK", "MESSAGGIO", String.class);

Here there are some tests I've made:

This is about querydsl connection in Oracle that require to_clob(XXXXX) expression to have resultset (otherwise is null). In MSSQL and Postgres this is not necessary it works directly like a standard field.

    @Test
    @Transactional(readOnly=false)
    public void testXml() {

        NmEventiFallback_QSQL nmEventiFallback = NmEventiFallback_QSQL.nmEventiFallback;
        SQLInsertClause ins = queryDslJdbcTemplateViaHibernateSession
                .insertSql(nmEventiFallback);
        ins.columns(
                nmEventiFallback.idTpEvento,
                // nmEventiFallback.data,
                nmEventiFallback.dataNotifica, nmEventiFallback.messaggio,
                nmEventiFallback.nrtentativo, nmEventiFallback.dataTentativo,
                nmEventiFallback.errori).values(0,
        // null,
                null,
                // "<m>test &lt;tttt&gt;</m>",
                "<m>aaa&lt;</m>", 0, null, null);
        long res = ins.execute();
//

//      BooleanExpression a=Expressions.booleanTemplate("to_clob({0})", nmEventiFallback.messaggio);
        Expression<String> expression =SimpleTemplate.create(String.class, "to_clob({0})", nmEventiFallback.messaggio); 
        List<String> resSet = queryDslJdbcTemplateViaHibernateSession
                .getQuery().from(nmEventiFallback).limit(10)
                .list(expression);
        for (String xml : resSet) {
            System.out.println(xml);
        }
    }

This one is about a raw jdbc connection (in oracle) always using string

    @Test
    public void pureJdbcXmlTest() throws ClassNotFoundException, SQLException {

        Class.forName("oracle.jdbc.driver.OracleDriver");
        Class.forName("oracle.xdb.XMLTypeFactory");
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(
                    "URLHERE",
                    "USERHERE", "PASSWORD");

            // 1 INSERT
            String insertTableSQL = "INSERT INTO NM_EVENTI_FALLBACK"
                    + "(ID_TP_EVENTO,MESSAGGIO) VALUES"
                    + "(?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(insertTableSQL);
            preparedStatement.setInt(1,0);
            preparedStatement.setString(2, "<jdbctest>aaaa&lt;bbbb</jdbctest>");
            preparedStatement .executeUpdate();

            // 2 SELECT
            Statement stmt = null;
            String query = "SELECT to_clob(MESSAGGIO) FROM NM_EVENTI_FALLBACK";
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                System.out.println("");
                System.out.println(rs.getString(1));
                System.out.println("");
            }
            System.out.println("");
        }
        catch (Exception ex){
            ex.printStackTrace();
        }
        finally {
            connection.close();
        }
    }

[timowest]

@dmiorandi Could you try the pull request?

If you want to access the two columns via String then the following will work

XMLAsStringType xmlAsString = new XMLAsStringType();
configuration.register("NM_EVENTI", "MESSAGGIO", xmlAsString);
configuration.register("NM_EVENTI_FALLBACK", "MESSAGGIO", xmlAsString);

For SQLXML based usage use this instead

SQLXMLType sqlXml = new SQLXMLType();
configuration.register("NM_EVENTI", "MESSAGGIO", sqlXml);
configuration.register("NM_EVENTI_FALLBACK", "MESSAGGIO", sqlXml);

String based usage might be more useful.

[timowest]

Released in 3.6.0

[starstatus]

When I try to use XMLAsStringType, I get this error: ORA-00932: inconsistent datatypes: expected CLOB got -

When I try to use SQLXMLType, I get: java.lang.String cannot be cast to java.sql.SQLXML

[starstatus]

Looked into both cases, and it seems that I need to construct a new SQLXMLType from a String, which requires a prepared statement. But the statement is only available when I execute the sql. Which means, I basically have to use raw JDBC.

[starstatus]

BTW, in the database, the column is stored as XMLTYPE BINARY XML

[timowest]

How is the column mapped in your Q-type? SQLXML or String?

[starstatus]

I tried both, and it's the same. By the way, if I don't register as XMLAsStringType or SQLXMLType, just map the Q-type as SQLXML or String, I get "ORA-01461: can bind a LONG value only for insert into a LONG column".

[starstatus]

Okay. I figured it out. It's ridiculously nothing about querydsl. Sorry

[dmiorandi]

Hello, I've found an issue on Oracle side. Oracle <=11.x have a character limitation of 4000 bytes. My xml is 20k and cannot be inserted on db because xml is passed as string. To solve this xml have to be passed using SQLXml (that should use CLOB not string), but I've got a string.

My doubt is about XMLAsStringType type. Looking into source it seems that it use SQLXML so it should work passing a CLOB I suppose not string, but in the facts query with more than 4000 bytes xml does not work. I'm a little confused.

Does XMLAsStringType should pass SQLXML type to jdbc driver?

[dmiorandi]

I've found a trick. I set xml field in a preExecute listener so that i've got statement to do it. It works using SQLXML on jdbc query like expected, but this is a nesty trick.

I think this should be done automatically on xml fields mapped as XMLAsStringType but it doens't. Any suggestion? Does in you unit test generate string or SQLXML?

[timowest]

XMLAsStringType might not be the right Type for this use case. Could you post the error message you get?

[dmiorandi]

On oracle 11.x "ORA-01461: can bind a LONG value only for insert into a LONG column-Occurs when querying", just when string is above 4000 chars. On postgres no issue about size using string.

What I excpect is to use string and have mapped on SQLXML type before execute the query. This what I expect from XMLAsStringType, otherwise is not clear to me what XMLAsStringType is meant for.

[timowest]

The name is misleading, maybe it should be renamed to StringAsSQLXMLType, since that is what it does. It maps String to SQLXML on the JDBC level.

I will see what can be done about this.

[dmiorandi]

If I understand you comment XMLAsStringType should do what I need but in my case it doesn't. I've got

• String in QBean and SQLXML in metadata in QBean
• String in bean

like this

configuration.register("isee159_ws_inps_attestazioni", "request", new XMLAsStringType());
configuration.register("isee159_ws_inps_attestazioni", "response", new XMLAsStringType());

that generates

public final StringPath request = createString("request");
public final StringPath response = createString("response");

addMetadata(request, columnMetadata.named("request").withIndex(8).ofType(Types.SQLXML).withSize(2147483647));
addMetadata(response, ColumnMetadata.named("response").withIndex(9).ofType(Types.SQLXML).withSize(2147483647));

so I set a String value on my insert and I expect a SQLXML on jdbc side, but on jdbc side I've got a String, this is my issue. (If I put a breakpoint on SQLXMLType class getValue and setValue were not called)

queryInsert.set(isee159WsInpsAttestazioni_QSQL.request, "<a>LONG MORE THAN 4K HERE</a>");
....
queryInsert.execute();

To make it work I've made this ugly trick, and SQLXML is used on jdbc, but I would like to do in a cleaner way....

        queryInsert.addListener(new XmlSet(new HashMap<String, String>() {
            private static final long serialVersionUID = -1382323144650350371L;
            {
                put(isee159WsInpsAttestazioni_QSQL.response.toString(), "<a>LONG XML HERE</a>");
            }
        }));

public class XmlSet extends SQLListeners {
    HashMap<String, String> xmlValues;

    public XmlSet(HashMap<String, String> xmlValues) {
        this.xmlValues = xmlValues;
    }

    @Override
    public void preExecute(final SQLListenerContext context) {
        if (xmlValues != null) {
            //Connection connection = context.getConnection();
            PreparedStatement preparedStatement = context
                    .getPreparedStatement();
            for (String key : xmlValues.keySet()) {
                XMLAsStringType val = new XMLAsStringType();
                try {
                    val.setValue(preparedStatement, 8, xmlValues.get(key));
                    context.setData(key, val);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        super.preExecute(context);
    }
}

[timowest]

Did you use XMLAsStringType also in the runtime configuration? If not, then it will use StringType to set the values. Unfortunately it is currently necessary to do both, but this part will become simpler with the 4.0.0 release.

[dmiorandi]

I've made a register operation on Configuration object used to run the query, but with no success (Please note that my configuration object is created via spring an is the same across all runtime queries):

This one make work mapping xml as SQLXML, but break normal strings fields. When I use this setting all strings in the query are considered not as StringType but as XMLAsStringType and were printed with no quotes.

configuration.register(new XMLAsStringType());

This one have no effect. Query is created using string on xml fields, and also is not a general solution cause my configuration object should not contain table, columns specific informations cause is shared.

configuration.register("isee159_ws_inps_attestazioni", "request", new XMLAsStringType());

Can you make an example? I can't achieve what you said on runtime side?

PS I think XMLAsStringType should be the default mapping on xml fields. The only limitation is that this type is not available on JDBC v3 (but with java 6,7,8 jdbc 4 is reccomanded so this is no a drammatical limitation, in that cases a differente custom mapping should be used).