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 ?

Tunaki · Accepted Answer · 2015-08-18 15:25:52Z

29

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, 2015 at 15:25

Tunaki

138k46 gold badges370 silver badges443 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

hydertza Over a year ago

Just tried it but it does not work. It says : ERROR: ORA-00911: caractère non valide

hydertza Over a year ago

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 Over a year ago

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 Over a year ago

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.

Lewis Munene Over a year ago

If you used a text block to write your query, this solution will not work since a \ that's not at the end of the line is not a valid escape character in text blocks. You's have to go with @rogerdpack's approach

rogerdpack · Accepted Answer · 2020-07-28 20:26:49Z

8

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, 2020 at 20:26

rogerdpack

67.7k40 gold badges291 silver badges410 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related