PreparedStatement - not binding all the parameters

Question

I'm using Spring-JDBC to execute the SQL and fetch the results.

 SELECT 
       SUM(spend) total_sum   
    FROM TABLE_NAME  
       WHERE   
       sup_id = ?  AND    
      ( ( YEAR = ? AND period IN ( ? ) )   OR     
      ( YEAR = ? AND period IN(?)))

And the bind variables are passed as:

final Object[] paramMap = { "1234", "2010",
                "'01_JAN','02_FEB','03_MAR'", "2009", "'11_NOV','12_DEC'" };

final List<LeadTimeDto> query = getJdbcTemplate().query(sql, paramMap,
                new RowMapper<LeadTimeDto>() {
                    @Override
                    public LeadTimeDto mapRow(final ResultSet resultSet,
                            final int arg1) throws SQLException {
                        final LeadTimeDto leadTimeDto = new LeadTimeDto();
                        final String string = resultSet.getString("total_sum");
                        System.out.println("ltime = " + string);
                        leadTimeDto.setLeadTime(string);
                        return leadTimeDto;
                    }
                });

I'm not sure what is happening here. I've the problem with the binding of the THIRD parameter. If I write the value of the third parameter in the query itself as below it is working.

 SELECT 
       SUM(spend) total_sum   
    FROM TABLE_NAME  
       WHERE   
       sup_id = ?  AND    
      ( ( YEAR = ? AND period IN ( '01_JAN','02_FEB','03_MAR' ) )   OR     
      ( YEAR = ? AND period IN(?)))

If it is the problem with quotes(') then FIFTH param should have also been the issue. But it is binding fine.

I've tried using getNamedParameterJdbcTemplate() as well both with Map and Bean, but no luck.

did you get an exception ? if yes post it pls – Michael Pralow Nov 25 '10 at 08:26 — Michael Pralow, Nov 25 '10 at 08:26
No errors. just that the records are not fetched. – jai Nov 25 '10 at 08:39 — jai, Nov 25 '10 at 08:39

score 2 · Accepted Answer · answered Nov 25 '10 at 08:22

2

Using prepared statement parameters for IN clause is not legal as far as I know. See the following article, which describes some alternatives:

http://www.javaranch.com/journal/200510/Journal200510.jsp#a2

answered Nov 25 '10 at 08:22

Eyal Schneider

22,166
5
47
78

@Schneider: thank you :) it worked. Looks like am not alone: http://stackoverflow.com/questions/178479/alternatives-for-java-sql-preparedstatement-in-clause-issue – jai Nov 25 '10 at 08:53

PreparedStatement - not binding all the parameters

1 Answers1