Invalid Column Name Exception in Spring-Hibernate Application

Question

I am working on a Spring Hibernate application which uses Oracle 11G database. I have a table from which I want to select three columns and display it in a jsp page. Now, one of the columns has data in the format- abc+def+xyz. I have to display "abc", "def" and "xyz" in 3 different columns in my jsp page. For that, I have written the following query in my DAO:

SQLQuery query=sessionFactory.getCurrentSession().createSQLQuery("SELECT TABLE_ID, REGEXP_SUBSTR(TABLE_UID, '[^+]+', 1, 3) AS FIRST_COLUMN, REGEXP_SUBSTR(TABLE_UID, '[^+]+', 1, 2) AS SECOND_COLUMN, REGEXP_SUBSTR(TABLE_UID, '[^+]+', 1, 1) AS THIRD_COLUMN, USERID FROM TABLE_TABLE1 WHERE USERID='abhi'");

The names of my columns are- TABLE_ID, TABLE_UID, USERID and the name of my table is- TABLE_TABLE1. When I run this, I'm getting-

org.hibernate.exception.GenericJDBCException: could not execute query] with root cause
java.sql.SQLException: Invalid column name.

But the same query when executed in SQL, is giving expected output. I don't understand why I'm getting this discrepancy

Please describe `TABLE_TABLE1` and paste the result here. Need to check on datatype too — Utsav, Nov 25 '15 at 05:28
Also run the statement this sql statements separately in the database and see do you really get any error. `SELECT TABLE_ID, REGEXP_SUBSTR(TABLE_UID, '[^+]+', 1, 3) AS FIRST_COLUMN, REGEXP_SUBSTR(TABLE_UID, '[^+]+', 1, 2) AS SECOND_COLUMN, REGEXP_SUBSTR(TABLE_UID, '[^+]+', 1, 1) AS THIRD_COLUMN, USERID FROM TABLE_TABLE1 WHERE USERID='abhi'` — Utsav, Nov 25 '15 at 05:32
@Utsav TABLE_TABLE1 contains a lot of columns. It's actually a pretty huge table. I'll give you the information about relevant columns: TABLE_ID- Number (19) TABLE_UID- Varchar2(100 Byte) USER_ID- VARCHAR2(100 BYTE) In my POJO class, the variables mapped to these columns are all String, except for the one mapped to TABLE_ID, which is int. The SQL Statement gives me the desired output when run separately in the DB. — Abhineet Kumar, Nov 25 '15 at 05:34
before that please run the sql statement separately in database and see if it throws the error. — Utsav, Nov 25 '15 at 05:38
@Abhineet: If you are using hibernate then column name in select query must be same like you have mentioned it in mapping file. Then and then only hibernate can map your column with your POJO — Viraj Dhamal, Nov 25 '15 at 08:40
@Viraj: Yes. It is the same. But since, I'm splitting one column into three, do I need to have three different entities mapped to it? — Abhineet Kumar, Nov 25 '15 at 09:14
Please turn on [SQL logging in Hibernate](http://stackoverflow.com/questions/8490617/enable-hibernate-logging) and compare the differences between the executed query and your original query. — Tobias Liefke, Nov 25 '15 at 12:15

score 0 · Answer 1 · answered Nov 25 '15 at 06:36

0

I am suspecting [ or ] characters making the issue(not sure).The other way around you can try using place holders

String queryString= "SELECT TABLE_ID, REGEXP_SUBSTR(TABLE_UID, ?, 1, 3) AS FIRST_COLUMN, REGEXP_SUBSTR(TABLE_UID, ?, 1, 2) AS SECOND_COLUMN, REGEXP_SUBSTR(TABLE_UID, ?, 1, 1) AS THIRD_COLUMN, USERID FROM TABLE_TABLE1 WHERE USERID= ?";
SQLQuery query = getSession().createSQLQuery(queryString);
query.setString(0, "[^+]+");
query.setString(1, "[^+]+");
query.setString(2, "[^+]+");
query.setString(3, "abhi");

answered Nov 25 '15 at 06:36

Tom Sebastian

3,373
5
29
54

No. we're back to "Invalid Column" now – Abhineet Kumar Nov 25 '15 at 06:58
Are you using any hbm file or mapping entity for this table? – Tom Sebastian Nov 25 '15 at 07:07
I'm mapping the entities to my POJO class using Annotations. – Abhineet Kumar Nov 25 '15 at 07:19

Tom Sebastian · Answer 2 · 2015-11-25T05:47:27.203

-1

It might be because you didn't escape special characters.If so use org.apache.commons.lang.StringEscapeUtils .It has one method:

public static String escapeSql(String str);

Escapes the characters in a String to be suitable to pass to an SQL query.Please see java doc

In your case it can be:

String escaped = StringEscapeUtils.escapeSql("SELECT TABLE_ID, REGEXP_SUBSTR(TABLE_UID, '[^+]+', 1, 3) AS FIRST_COLUMN, REGEXP_SUBSTR(TABLE_UID, '[^+]+', 1, 2) AS SECOND_COLUMN, REGEXP_SUBSTR(TABLE_UID, '[^+]+', 1, 1) AS THIRD_COLUMN, USERID FROM TABLE_TABLE1 WHERE USERID='abhi'");
SQLQuery query=sessionFactory.getCurrentSession().createSQLQuery(escaped );

edited Nov 25 '15 at 05:47

answered Nov 25 '15 at 05:32

Tom Sebastian

3,373
5
29
54

org.hibernate.exception.SQLGrammarException: could not execute query] with root cause java.sql.SQLException: ORA-00907: missing right parenthesis – Abhineet Kumar Nov 25 '15 at 06:01
It doesn't help. The reason is- All the single quotes in my query are somehow changed to two single quotes after using escapeSql and that in turn is giving the aforementioned exception. – Abhineet Kumar Nov 25 '15 at 06:16
@Tom I think you missed the point of `escapeSQL`. Please check the example in its Javadoc and compare it with your solution. – Tobias Liefke Nov 25 '15 at 12:09

Invalid Column Name Exception in Spring-Hibernate Application

2 Answers2