0

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?

Orifedex
  • 1
  • 2
  • Please, take a look to this post. I think it is a similar issue with parameters incorrectly parsed when using Apache DBUtils: https://stackoverflow.com/questions/30666622/apache-dbutils-changing-column-name-in-update-sql/35033943?noredirect=1#comment60591166_35033943 – Peter Gibbons Nov 29 '19 at 11:49

2 Answers2

0

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • Sorry I change the name of the column name here for privacy. The real name is not GROUP. I update my request – Orifedex Nov 06 '19 at 11:17
  • What do you get by this `select user from dual` from the application ? Is that contained as the username in your connection string? @Orifedex – Barbaros Özhan Nov 06 '19 at 11:24
0

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\" = ?"
Orifedex
  • 1
  • 2