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

Question

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 ?

score 24 · Accepted Answer · answered Aug 18 '15 at 15:25

24

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

answered Aug 18 '15 at 15:25

Tunaki

132,869
46
340
423

Just tried it but it does not work. It says : `ERROR: ORA-00911: caractère non valide` – hydertza Aug 18 '15 at 15:36
No, it's different. Before, it was `Exception in thread "main" org.hibernate.QueryException: Not all named parameters have been set: [ltc", DOAATLTC", ltchnom"]` – hydertza Aug 18 '15 at 15:49
Wait, after thinking a bit. You are certainly right and this error message `ERROR: ORA-00911: caractère non valide` must be a new error message as the precedent `Exception in thread "main" org.hibernate.QueryException: Not all named parameters have been set: [ltc", DOAATLTC", ltchnom"]` has been solved by your solution. – hydertza Aug 18 '15 at 16:03
Well, I fixed the second Error message that had anything to do with the problem of that question. So, I can guarantee that your solution works fine. – hydertza Aug 18 '15 at 16:10

score 5 · Answer 2 · answered Jul 28 '20 at 20:26

5

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

answered Jul 28 '20 at 20:26

rogerdpack

62,887
36
269
388

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

2 Answers2

Linked