CODEWITHSUNDEEP
javasqlhibernate
hydertza
hydertza

Reputation: 193

How to escape colon `:` within a native SQL query in Hibernate?

I have a native SQL query that looks like that : 

Query query = session.createSQLQuery("SELECT
        XMLSERIALIZE
        (CONTENT
          XMLELEMENT
          (
            NAME \"ltc:DOAATLTC\",
            XMLATTRIBUTES
            (
              'http://www.edftrading.com/Trade/Common/DoaatLTC' AS \"xmlns:ltc\",
              'http://www.edftrading.com/Trade/Common/DoaatLTCHourlyNomination' AS \"xmlns:ltchnom\"
            ),
            XMLELEMENT ( ... ) FROM ...");

The thing is that Hibernate interprets :DOAATLTC\", , :ltc\", , :ltchnom\", as parameters and expects that we give values query.setString("DOAATLTC\",", ...) , query.setString("ltc\",", ...) , query.setString("ltchnom\",", ...)

But I do not want Hibernate to interpret like that, so I want to escape the colon :.

How to do that ?

Upvotes: 16

Views: 19200

Answers (2)

rogerdpack
rogerdpack

Reputation: 66711

If your colon is a cast like SELECT reltuples::BIGINT then you can rewrite it as a cast(reltuples as BIGINT) to avoid the colons.

ref

Upvotes: 8

Tunaki
Tunaki

Reputation: 137064

In Hibernate, escaping is done with prepending by \. But in Java, you also have to escape \ by another \. So every : needs to be replaced by \\:. Finally, you get:

Query query = session.createSQLQuery("SELECT
    XMLSERIALIZE
    (CONTENT
      XMLELEMENT
      (
        NAME \"ltc\\:DOAATLTC\",
        XMLATTRIBUTES
        (
          'http://www.edftrading.com/Trade/Common/DoaatLTC' AS \"xmlns\\:ltc\",
          'http://www.edftrading.com/Trade/Common/DoaatLTCHourlyNomination' AS \"xmlns\\:ltchnom\"
        ),
        XMLELEMENT ( ... ) FROM ...");

Upvotes: 29

Related Questions