Muhammed Zubairuddin Muhammed Zubairuddin - 1 month ago 23
MySQL Question

Spring JDBC Template can't execute Queries containing nested Query in the FROM clause

Spring JDBC Template can't execute Queries containing nested Query in the FROM clause

I am using spring JDBCTemplate to execute queries. And the code uses SqlRowSet, instead of RowMapper or RowExtractor.

Now there seems to be some problem while executing a Query which contains a nested query in the FROM clause. When I test the query in the Workbench or Mysql Console, it works perfectly fine.

Following is the code

JdbcTemplate jdbcTemplate = new JdbcTemplate(txManager.getDataSource());
try {
String sqlQuery = "SELECT profile.user_profile_id, profile.user_id, profile.first_name "
+ "FROM (SELECT user_profile_id, user_id, first_name FROM user_profile WHERE user_id = 1) AS profile";

// The following line is throwing exception
SqlRowSet resultSet = jdbcTemplate.queryForRowSet(sqlQuery);

if (resultSet.next()) {
...... // Put in a POJO
}
} catch(DataAccessException e) {
e.printStackTrace();
// Throw Proper User Defined Exception
}


But, if I change the Query to the following, it works perfectly fine.

String sqlQuery = "SELECT profile.user_profile_id, profile.user_id, profile.first_name "
+ "FROM user_profile AS profile";


NOTE : My database name is titans


I am getting the following exception. It says that table 'titans.*' doesn't exists. It seems absurd to me.

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT prof.user_profile_id, prof.user_id, prof.first_name, prof.last_name FROM (SELECT inner_prof.user_profile_id, inner_prof.user_id, inner_prof.first_name, inner_prof.last_name FROM titans.user_profile AS inner_prof WHERE user_id = 1) AS prof ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'titans.*' doesn't exist
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:456)
at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:501)
at com.titansinc.user.dsp.impl.UserDSP.getUserProfileByUserID(UserDSP.java:188)
at com.titansinc.user.service.UserService.getUserProfileByUserID(UserService.java:36)
at controllers.com.titans.play.controllers.Application.testReadMethod(Application.java:40)
at Routes$$anonfun$routes$1$$anonfun$applyOrElse$6$$anonfun$apply$24.apply(routes_routing.scala:141)
at Routes$$anonfun$routes$1$$anonfun$applyOrElse$6$$anonfun$apply$24.apply(routes_routing.scala:141)
at play.core.Router$HandlerInvokerFactory$$anon$4.resultCall(Router.scala:264)
at play.core.Router$HandlerInvokerFactory$JavaActionInvokerFactory$$anon$15$$anon$1.invocation(Router.scala:255)
at play.core.j.JavaAction$$anon$1.call(JavaAction.scala:55)
at play.GlobalSettings$1.call(GlobalSettings.java:67)
at play.core.j.JavaAction$$anonfun$11.apply(JavaAction.scala:82)
at play.core.j.JavaAction$$anonfun$11.apply(JavaAction.scala:82)
at scala.concurrent.impl.Future$PromiseCompletingRunnable.liftedTree1$1(Future.scala:24)
at scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:24)
at play.core.j.HttpExecutionContext$$anon$2.run(HttpExecutionContext.scala:40)
at play.api.libs.iteratee.Execution$trampoline$.execute(Execution.scala:46)
at play.core.j.HttpExecutionContext.execute(HttpExecutionContext.scala:32)
at scala.concurrent.impl.Future$.apply(Future.scala:31)
at scala.concurrent.Future$.apply(Future.scala:492)
at play.core.j.JavaAction$class.apply(JavaAction.scala:82)
at play.core.Router$HandlerInvokerFactory$JavaActionInvokerFactory$$anon$15$$anon$1.apply(Router.scala:252)
at play.api.mvc.Action$$anonfun$apply$1$$anonfun$apply$4$$anonfun$apply$5.apply(Action.scala:130)
at play.api.mvc.Action$$anonfun$apply$1$$anonfun$apply$4$$anonfun$apply$5.apply(Action.scala:130)
at play.utils.Threads$.withContextClassLoader(Threads.scala:21)
at play.api.mvc.Action$$anonfun$apply$1$$anonfun$apply$4.apply(Action.scala:129)
at play.api.mvc.Action$$anonfun$apply$1$$anonfun$apply$4.apply(Action.scala:128)
at scala.Option.map(Option.scala:145)
at play.api.mvc.Action$$anonfun$apply$1.apply(Action.scala:128)
at play.api.mvc.Action$$anonfun$apply$1.apply(Action.scala:121)
at play.api.libs.iteratee.Iteratee$$anonfun$mapM$1.apply(Iteratee.scala:483)
at play.api.libs.iteratee.Iteratee$$anonfun$mapM$1.apply(Iteratee.scala:483)
at play.api.libs.iteratee.Iteratee$$anonfun$flatMapM$1.apply(Iteratee.scala:519)
at play.api.libs.iteratee.Iteratee$$anonfun$flatMapM$1.apply(Iteratee.scala:519)
at play.api.libs.iteratee.Iteratee$$anonfun$flatMap$1$$anonfun$apply$14.apply(Iteratee.scala:496)
at play.api.libs.iteratee.Iteratee$$anonfun$flatMap$1$$anonfun$apply$14.apply(Iteratee.scala:496)
at scala.concurrent.impl.Future$PromiseCompletingRunnable.liftedTree1$1(Future.scala:24)
at scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:24)
at akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:41)
at akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(AbstractDispatcher.scala:393)
at scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
at scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
at scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
at scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'titans.*' doesn't exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.Util.getInstance(Util.java:360)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446)
at com.mysql.jdbc.Field.getCollation(Field.java:448)
at com.mysql.jdbc.ResultSetMetaData.isCaseSensitive(ResultSetMetaData.java:549)
at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:722)
at com.sun.rowset.CachedRowSetImpl.populate(CachedRowSetImpl.java:639)
at org.springframework.jdbc.core.SqlRowSetResultSetExtractor.createSqlRowSet(SqlRowSetResultSetExtractor.java:79)
at org.springframework.jdbc.core.SqlRowSetResultSetExtractor.extractData(SqlRowSetResultSetExtractor.java:62)
at org.springframework.jdbc.core.SqlRowSetResultSetExtractor.extractData(SqlRowSetResultSetExtractor.java:45)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:446)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
... 44 more

Answer

Looks like queryForRowSet(sqlQuery) method has this bug. I reported the bug on spring JIRA.

An alternative approach would be to use ResultSetExtractor or RowMapper to implement the db call. This implementation doesn't throw any exceptions for the above mentioned queries.

....

String sqlQuery = ".....";
Object[] parameters = { .... };

ITestPOJO resultList = jdbcTemplate.query(sqlQuery, parameters, 
     new ResultSetExtractor<ITestPOJO>() {

        @Override
        public ITestPOJO extractData(ResultSet rs) throws SQLException, DataAccessException {
             // TODO use ResultSet from here 
             // return ITestPOJO object
        }
    });         
...