Xml Type support on querydsl-sql #1034

Closed
dmiorandi opened this Issue Nov 7, 2014 · 16 comments

Comments

Projects
None yet
3 participants
@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 timowest added this to the 3.6.0 milestone Nov 8, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 8, 2014

Member

@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.

Member

timowest commented Nov 8, 2014

@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.

@Shredder121 Shredder121 closed this in #1037 Nov 16, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 30, 2014

Member

Released in 3.6.0

Member

timowest commented Nov 30, 2014

Released in 3.6.0

@aug828

This comment has been minimized.

Show comment
Hide comment
@aug828

aug828 Jan 31, 2015

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

aug828 commented Jan 31, 2015

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

@aug828

This comment has been minimized.

Show comment
Hide comment
@aug828

aug828 Jan 31, 2015

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.

aug828 commented Jan 31, 2015

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.

@aug828

This comment has been minimized.

Show comment
Hide comment
@aug828

aug828 Jan 31, 2015

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

aug828 commented Jan 31, 2015

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

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Feb 1, 2015

Member

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

Member

timowest commented Feb 1, 2015

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

@aug828

This comment has been minimized.

Show comment
Hide comment
@aug828

aug828 Feb 1, 2015

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".

aug828 commented Feb 1, 2015

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".

@aug828

This comment has been minimized.

Show comment
Hide comment
@aug828

aug828 Feb 2, 2015

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

aug828 commented Feb 2, 2015

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

@dmiorandi

This comment has been minimized.

Show comment
Hide comment
@dmiorandi

dmiorandi Feb 23, 2015

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?

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

This comment has been minimized.

Show comment
Hide comment
@dmiorandi

dmiorandi Feb 23, 2015

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?

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

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Feb 23, 2015

Member

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

Member

timowest commented Feb 23, 2015

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

@dmiorandi

This comment has been minimized.

Show comment
Hide comment
@dmiorandi

dmiorandi Feb 23, 2015

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.

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

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Feb 23, 2015

Member

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.

Member

timowest commented Feb 23, 2015

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

This comment has been minimized.

Show comment
Hide comment
@dmiorandi

dmiorandi Feb 23, 2015

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);
    }
}

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

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Feb 23, 2015

Member

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.

Member

timowest commented Feb 23, 2015

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

This comment has been minimized.

Show comment
Hide comment
@dmiorandi

dmiorandi Feb 24, 2015

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).

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).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment