CODEWITHSUNDEEP
sqlspringjdbctemplate
sanvica
sanvica

Reputation: 99

Spring jdbcTemplate query always returns null irrespective of data in database

I need to fetch a sum value from database based on date range. I tried using Spring jdbcTemplate in the following ways. But it doesn't return anything. 

public void getTotal(String from, string toDate){
    String totalSql="select  sum(b.finalAmount) as total from example a, example b "+ 
                "where a.created >= TO_TIMESTAMP(:fromDate, 'MM-DD-YYYY') AND a.created < TO_TIMESTAMP(:toDate, 'MM-DD-YYYY hh24:mi:ss') "+
                "and a.tradein_id=b.tradein_id";

    List<Integer> checkAmt =  jdbcTemplate.query(sql, new RowMapper<Integer>()   {

        @Override
        public Integer mapRow(ResultSet rs, int rowNum) throws SQLException
        {
            int check = rs.getInt("TOTAL");
            return check;

        }
    }, fromDate,toDate);


    int checkAmount = jdbcTemplate.queryForObject(
            totalSql, new Object[]{fromDate, toDate},Integer.class);
}

When I hardcode the fromDate and toDate in query, it works fine. I assume there is something wrong with the select parameters I am sending in.

Both from date and todate are String values from front end of the format 08/09/2016.

Upvotes: 2

Views: 6336

Answers (1)

Nathan Hughes
Nathan Hughes

Reputation: 96454

The SQL is using named parameters but the code is sending a list of arguments. Either use a NamedParameterJdbcTemplate and change how you're passing in arguments, or use a JdbcTemplate and change the SQL to use the ? placeholder instead of named arguments.

If you use NamedParameterJdbcTemplate, you have to refer to the parameters by name in the SQL, and you have to provide names when passing in the arguments. Put them in a map, like this (from the spring-jdbc documentation):

public int countOfActorsByFirstName(String firstName) {

    String sql = "select count(*) from T_ACTOR where first_name = :first_name";

    SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);

    return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
}

Alternatively you can provide the arguments like this:

Map args = new HashMap();
args.put("fromDate", fromDate);
args.put("toDate", toDate);
jdbcTemplate.queryForObject(sql, args, Integer.class);

If you don't want to use named parameters, change the SQL to look like

String totalSql= "select sum(b.finalAmount) as total from example a, example b "+ 
"where a.created >= TO_TIMESTAMP(?, 'MM-DD-YYYY') AND a.created < TO_TIMESTAMP(?, 'MM-DD-YYYY hh24:mi:ss') "+
"and a.tradein_id=b.tradein_id"

and leave the rest alone.

Upvotes: 3

Related Questions