Not all variable bound Exception Using JDBC template

Question

I am using jbcTemplate and I am encountered ORA-01008 exception.

package com.awzpact.prayas.service;

import com.awzpact.uam.dao.BaseJdbcTemplate;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Service;

/**
 *
 * @author zia.khan
 */
@Service
public class PayCodeDetailListService extends BaseJdbcTemplate {

    public List<Map<String, Object>> searchPayCodeByempCode(String tabSuffix, String empCode, String yyyyMm) {
        MapSqlParameterSource param = new MapSqlParameterSource();
        String tableName = "Salary_detail_report_082018";
        String query = "SELECT "
                + " DISTINCT PAY_CODE, "
                + " PAY_CODE_DESC, "
                + " AMOUNT, "
                + " row_number() over (Order by EMP_CODE ) AS ROW_NUM "
                + " FROM " + tableName
                + " WHERE EMP_CODE=:EMP_CODE "
                + " AND YYYYMM=:YYYYMM "
                + " AND PAY_CODE NOT IN (997,998,999) "
                + " ORDER BY PAY_CODE ASC ";
        param.addValue("empCode", empCode);
        param.addValue("YYYYMM", yyyyMm);
        List<Map<String, Object>> employees = queryForList(query);
        if (employees != null && !employees.isEmpty()) {
            for (Map<String, Object> employee : employees) {
                for (Iterator<Map.Entry<String, Object>> it = employee.entrySet().iterator(); it.hasNext();) {
                    Map.Entry<String, Object> entry = it.next();
                    String key = entry.getKey();
                    Object value = entry.getValue();
                    System.out.println(key + " = " + value);
                }
            }
        }
        return employees;
    }
}

This line List<Map<String, Object>> employees = queryForList(query); throws and exception.

In addition I also want to know, the result returned after execution of query can be directly bind into the POJO.

Simple meaning, My query will returning limited number of columns, like 10,13,15 50,90 etc.

Now I don't want to implement mapper, because there are 95 records and for limited records i need to write big mapper and each time i also need to check the null values while mapping the row.

I want to to directly bind the result in the domain.

so after R&D i came up with this solution and while testing i am encountering this, kindly suggest me the best solution.

You have `param.addValue("empCode", empCode)` but the actual parameter in your SQL query is called `:EMP_CODE`. Fix this, and maybe the error will go away. — Tim Biegeleisen, Oct 10 '18 at 10:29
Omg, how i missed that, thanks so much Tim, can you give me best suggestion regarding my requirement, please go through the question description after my code. — user9634982, Oct 10 '18 at 10:31
I don't know JDBCTemplate well enough to comment on the second part of your question. In general, if you need dozens of columns in your result set, then be prepared to do some work on the JDBC side. If you want truly _automatic_ mapping, then consider using something like Hibernate/JPA, which can automatically map a record to a Java class (entity). — Tim Biegeleisen, Oct 10 '18 at 10:33
I do agree with you Tim, but i am working on real industry project and we are using JdbcTemplate, project is alreayd stable and complex, so it is not an easy task to move on ORM, yes we will surely plan to implement the same in the next upcoming projects. — user9634982, Oct 10 '18 at 10:36
Off the top of my head, you could write a single template which gets _every_ column in a given table. Then, just reuse it throughout your code, and only access in the map those keys (columns) which you really want to use. Obviously, this approach is wasteful from a latency point of view, as you always would be selecting every column. But, it does get around the problem of having to write new mappers for each query. — Tim Biegeleisen, Oct 10 '18 at 10:37
Tim can u demonstrate little bit programmatic so it will be helpful for me. — user9634982, Oct 10 '18 at 10:43

score 0 · Answer 1 · answered Oct 10 '18 at 11:53

In addition to Tim's answer, I want to add that your code doesn't put param variable into the query. You have to put param map into the query somehow. If we are talking about Spring JdbcTemplate class, it has a huge number of different methods, e.g. you can do something like:

jdbcTemplate.query(
    "select id from your_table_here where first_column = ? and second_column = ?",
    new Object[] {
        firstParameter, secondParameter
    },
    new int[] {
        VARCHAR, VARCHAR // types of parameters in same order
    },
    new SingleColumnRowMapper<>()   // here you can use your own mapper
);

Not all variable bound Exception Using JDBC template

1 Answers1