Wednesday, October 5, 2011

Calling stored procedure from JdbcTemplate

I am using JdbcTemplate to make database queries, like:
List<Map<String,Object>> rows = jdbcTemplate.queryForList("select * from rows");
Recently I had to replace SQL selects with stored procedures, but after doing so, I had exception:
java.sql.SQLException: The SQL statement must not contain a procedure call or parameter markers.
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1297)
        at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:35)
        at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:440)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:455)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:463)
        at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:494)
As I found out finally, when queryForList is called without parameters it cann't call stored procedures, so to do it you just need to add empty parameter list:
List<Map<String,Object>> tables = jdbcTemplate.queryForList("{call getRows() }", new Object[] {});

No comments:

Post a Comment