How to escape '--' in hibernate createSqlQuery?

Question

The code is as follow:

sql = " select '--', staff_no from staff where staff_no = ?";
Session session2 = HibernateUtil.getCurrentSession();               
Query sqlQuery2 = session2.createSQLQuery(sql);             
sqlQuery2.setParameter(0, "04415");  //error in this line

When run, error

Caused by: org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1

is thrown, I know this is caused by the '--' in sql string, hibernate interpret it as sql comment,

I try to use

"select '\\-\\-', staff_no from ...."

this can run successfully, but the output becomes \-\-, but what I want is -- in output.

So how to escape the '--' in hibernate createSqlQuery?

The problem is caused by the '--' in select clause, if I run

sql = " select '-', staff_no from staff where staff_no = ?";

no problem is found.

Answer 1

As your error message stated:

Remember that ordinal parameters are 1-based! Position: 1

try to use this:

sql = " select \"--\", staff_no from staff where staff_no = ?1";
Session session2 = HibernateUtil.getCurrentSession();               
Query sqlQuery2 = session2.createSQLQuery(sql);             
sqlQuery2.setParameter(1, "04415"); 

According to the documentation:

JPQL-style positional parameters are declared using a question mark followed by an ordinal - ?1, ?2. The ordinals start with 1. Just like with named parameters, positional parameters can also appear multiple times in a query.

Answer 2

Cannot find any method, but use a workaround as follow can work:

sql = " select replace ('- -', ' ', ''), staff_no from staff where staff_no = ?";
Session session2 = HibernateUtil.getCurrentSession();               
Query sqlQuery2 = session2.createSQLQuery(sql);             
sqlQuery2.setParameter(0, "04415");