Hibernate error while update

Question

Possible Duplicate:
Using Hibernate query : colon gets treated as parameter / escaping colon

We are trying to write a native query. When we try to update a particular column value that contains a ": " ,we get an exception.

This is the generated query

  UPDATE MY_TABLE SET DESCRIPTION = "Test 01 : ABC",LAST_UPDATE_TS =
  CURRENT_TIMESTAMP ,LAST_UPDATE_USER_ID = 111 WHERE MY_ID =123

I think the problem lies in the DESCRIPTION data, which contains ': '

We get this error

 nested exception is org.hibernate.QueryException:Space is not allowed after 
 parameter prefix ':' ' UPDATE 
 MY_TABLE SET DESCRIPTION = "MY Test 01 : ABC",LAST_UPDATE_TS =
 CURRENT_TIMESTAMP, LAST_UPDATE_USER_ID = 111 WHERE MY_ID =123'

This is the Java code that is responsible for the query :

 StringBuffer strQry = new StringBuffer(" UPDATE MY_TABLE SET ");

          if(myForm.getDescription() != null){
                 if(flag == 1){
                       strQry.append(",");
                 }
                 strQry.append(" DESCRIPTION = \"" + myForm.getDescription().trim()+ "\" ");
                 flag = 1;

Any idea why this error is occurring and how we can get around it ?

the data containing ':' is in myForm.getDescription(). Are you saying the we need to edit this data ? — user2434, Nov 11 '12 at 15:44

NimChimpsky · Answer 1 · 2012-11-11T15:48:28.567

1

UPDATE MY_TABLE SET DESCRIPTION = "Test 01 \\: ABC",LAST_UPDATE_TS =
  CURRENT_TIMESTAMP ,LAST_UPDATE_USER_ID = 111 WHERE MY_ID =123

add forward slashes.

The colon is used to specify named parameters so you have to escape it(and then escape the escape).

edited Nov 11 '12 at 15:48

answered Nov 11 '12 at 15:41

NimChimpsky

46,453
60
198
311

Err It doesn't even compile in java with single forward slash – Amit Deshpande Nov 11 '12 at 15:42

score 0 · Answer 2 · answered Nov 11 '12 at 17:27

You need to parameterize your sql. ":" is a special character that hibernate uses to detect named parameters. So, hibernate throws an exception when trying to parse your query.
You could parameterize your sql shown below.

   StringBuffer strQry = new StringBuffer(" UPDATE MY_TABLE SET ");

          if(myForm.getDescription() != null){
                 if(flag == 1){
                       strQry.append(",");
                 }
                 strQry.append(" DESCRIPTION = :description");
                 flag = 1;
....

   if(myForm.getDescription() != null){
        session.setString("description", description)
   }

Parameterizing your query is important for two other reasons -

Avoid sql injections.
Performance gains because of pre-compilation of your sql.

dimas · Answer 3 · 2012-11-11T18:01:45.267

I think problem in you case occurs because Hibernate thinks that you are using named parameter (symbol ":" is a special keyword used for this). Try to define description, last_update_user_id and my_id as named parameter for your query. So your code will look like:

String queryText = "UPDATE MY_TABLE SET DESCRIPTION = :description,LAST_UPDATE_TS =
  CURRENT_TIMESTAMP, LAST_UPDATE_USER_ID = :lastUpdateUserID WHERE MY_ID = :myId";
Query query = session.createSQLQuery(queryText);
query.setParameter("description", yourDescription);
query.setParameter("lastUpdateUserID", yourLastUpdateUserID);
query.setParameter("myId", yourMyId);
...

In my example I define whole query in one place, but you may build it by parts like in your code snippet:

 StringBuffer strQry = new StringBuffer(" UPDATE MY_TABLE SET ");

 if(myForm.getDescription() != null){
     if(flag == 1){
           strQry.append(",");
     }
     strQry.append(" DESCRIPTION = :description ");
     flag = 1;
 } 
...

EDIT

In you conditions may be even better to use numeric parameters (?1) in query. Example:

 List<Object> parametersList = new LinkedList<Object>();
 int parameterPosition = 1;

 StringBuffer strQry = new StringBuffer(" UPDATE MY_TABLE SET ");

 if(myForm.getDescription() != null){
     if(parameterPosition > 1){
           strQry.append(",");
     }
     strQry.append(" DESCRIPTION = ?");
     strQry.append(parameterPosition);
     strQry.append(" ");

     parametersList.add(myForm.getDescription().trim());
     parameterPosition ++;
 } 
...

int position = 1;
for (Object parameter: parametersList) {
     query.setParameter(position, parameter);
     position++;
}

An least somehow like this).

Hibernate error while update

3 Answers3