Trying to do SELECT statement with parameters using Jdbctemplate spring-CodePudding

This is the error I get when making a request with traderCode params:

org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [SELECT * FROM tblCustomer WHERE TraderCode = :traderCode;]; The index 1 is out of range.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.

The parameters are provided in Postman: Screenshot

The full Get Request:

    @GetMapping
    public ResponseEntity<List<Customer>> getCustomers(@RequestParam(required = false) String traderCode, String compCode) {
        try {
            List<Customer> customers = new ArrayList<Customer>();
            JdbcTemplate template = getDataSource(compCode);
            if (traderCode == null){
                customers = template.query("SELECT * FROM tblCustomer;",
                        new BeanPropertyRowMapper<Customer>(Customer.class));
                //customerRepository.findAll().forEach(customers::add);
            } else {
                customers = template.query("SELECT * FROM tblCustomer WHERE TraderCode = :traderCode;",
                        new BeanPropertyRowMapper<Customer>(Customer.class),
                        new MapSqlParameterSource()
                                .addValue("traderCode", traderCode));
                //customerRepository.findByTraderCode(traderCode).forEach(customers::add);
            }

            if (customers.isEmpty())
                return new ResponseEntity<>(HttpStatus.NO_CONTENT);

            return new ResponseEntity<>(customers, HttpStatus.OK);
        } catch (Exception e) {
            System.out.print(e);
            return  new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

CodePudding user response：

You should edit the source code as below.

    @GetMapping
    public ResponseEntity<List<Customer>> getCustomers(@RequestParam(required = false) String traderCode, String compCode) {
        try {
            List<Customer> customers = new ArrayList<Customer>();
            JdbcTemplate template = getDataSource(compCode);
            if (traderCode == null || traderCode == ""){
                customers = template.query("SELECT * FROM tblCustomer",
                        new BeanPropertyRowMapper<Customer>(Customer.class));
               
            } else {
                customers = template.query("SELECT * FROM tblCustomer WHERE TraderCode = :TraderCode",
                        new MapSqlParameterSource().addValue("TraderCode", traderCode),
                        new BeanPropertyRowMapper<Customer>(Customer.class));
            }

            if (customers.isEmpty())
                return new ResponseEntity<>(HttpStatus.NO_CONTENT);

            return new ResponseEntity<>(customers, HttpStatus.OK);
        } catch (Exception e) {
            System.out.print(e);
            return  new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }