Commons DBUtils Oracle 11.2.0.4 with Java 1.7 Binding parameters SQLException ORA-00942

Question

I'm working with Oracle Database 11.2.0.4 with ojdbc6.jar, and I'm using apache commons dbutils v1.7, with JDK 7. So I'm using the QueryRunner and its method in this function

private <T> List<T> executeQueryAndReturnBeanList(String query, Class<T> className, Object... param) throws SQLException {

     Connection connection = getDBConnectionInstance();

     DbUtils.loadDriver("oracle.jdbc.driver.OracleDriver");

     ResultSetHandler<List<T>> beanListHandler = new BeanListHandler<>(className,new BasicRowProcessor(new GenerousBeanProcessor()));

     QueryRunner runner = new QueryRunner();

     List<T> list = runner.query(connection, query, beanListHandler, param);

     return list;
}

and everything works fine with select query without binding parameters

SELECT * FROM PEOPLE WHERE GRUPPO = 1 AND LANG = 'en_US'

But when I excute this query

String query = "SELECT * FROM PEOPLE WHERE GRUPPO = ? AND LANG = ?";

It gives me this SQL Exception

java.sql.SQLException: ORA-00942: table or view does not exist
 Query: SELECT * FROM PEOPLE WHERE GRUPPO = ? AND LANG = ? Parameters: [1, en_US]
        at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:527)
        at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:391)
        at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:252)
        at mypackage.executeQueryAndReturnBeanList(JDBCFunctions.java:199)

I really don't know why. I tried to use :P1, :P2 or :1, :2 instead of ? to bind parameters but nothing it happens. Any ideas?

Answer 1

Group is a reserved word and cannot be used as a column or table name. Most probably you have a quoted column name such as "GROUP" within the table.

So, need to query as SELECT * FROM PEOPLE WHERE "GROUP" = 1 AND LANG = 'en_US'

Quoted table names should be case sensitive, unlike unquoted ones.

The above one is the basic mistake, while the error(ORA-00942) suggests that your application connects to different schema than the schema in which successfully tested the query.

Answer 2

I finally found the solution. I inserted " on every column and table's name and now it works. For example:

String query = "SELECT * FROM \"PEOPLE\"  WHERE \"GRUPPO\" = ? AND \"LANG\" = ?"