CODEWITHSUNDEEP
spring
Pankaj
Pankaj

Reputation: 3664

Native Query With Spring's JdbcTempate

I'm using spring's JdbcDaoSupport for making data base call. I want to execure native query (sql query) for retrieving data. Do we have any API available in JdbcTemplate for native query? I used queryForObject but it throws exception if there is no data whereas i was expecting it to return back null if it couldn't find data.

Upvotes: 2

Views: 10097

Answers (3)

Abdulaziz Pulatjonov
Abdulaziz Pulatjonov

Reputation: 31

The same happened with me too, it shows 3 rows, expected 1, while in reality it was only one row, I could not get it, and replaced with other code as below:

ProductReportStatsDTO reportStats = new ProductReportStatsDTO();
jdbcSpringManager.getSimpleJdbcTemplate().query(sql.toString(),new RowCallbackHandler() {
            public void processRow(ResultSet rs) {
                try {
                    reportStats.setSerialQty(rs.getLong(1));
                    reportStats.setProductQty(rs.getBigDecimal(2));
                    reportStats.setTotalPrice(rs.getBigDecimal(3));
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        });

Upvotes: 0

maneesh
maneesh

Reputation: 1112

Spring JdbcTemplate's queryForObject method expects your SQL to return exactly one row. If the there are no rows returned or if there are more than 1 row returned it will throw a org.springframework.dao.IncorrectResultSizeDataAccessException. You will have to wrap the call to queryForObject with a try catch block to handle IncorrectResultSizeDataAccessException and return null if the exception is thrown

e.g.

try{
   return jdbcTemplate.queryForObject(...);
}catch(IncorrectResultSizeDataAccessException e){
   return null;
}

Upvotes: 1

nicholas.hauschild
nicholas.hauschild

Reputation: 42849

There are many options available for executing native sql with JdbcTemplate. The linked documentation contains plenty of methods that take native sql, and usually some sort of callback handler, which will accomplish exactly what you are looking for. A simple one that comes to mind is query(String sql, RowCallbackHandler callback).

jdbcTemplate.query("select * from mytable where something > 3", new RowCallbackHandler() {
    public void processRow(ResultSet rs) {
        //this will be called for each row.  DO NOT call next() on the ResultSet from in here...
    }
});

Upvotes: 4

Related Questions