Java – error syntax SQL exception when reading value with rowmapper

This is my model class

//Model

    public class CustomerData {

        private String locomotive_id;
        private String customer_name;
        private String road_number;
        private String locomotive_type_code;
        private String in_service_date;
        private String part_number;
        private String emission_tier_type;
        private String airbrake_type_code;
        private String lms_fleet;
        private String aar_road;
        private String locomotive_status_code;

        // Getters and Setters

This is my rowmapper implementation

//RowMapper

    public class CustomerDataResponseMapper implements RowMapper {

    @Override
    public Object mapRow(ResultSet rs,int count) throws sqlException {
        CustomerData customerData = new CustomerData();

        customerData.setLocomotive_id(rs.getString("locomotive_id"));
        customerData.setCustomer_name(rs.getString("customer_name"));
        customerData.setRoad_number(rs.getString("road_number"));
        customerData.setLocomotive_type_code(rs.getString("locomotive_type_code"));
        customerData.setIn_service_date(rs.getString("in_service_date"));
        customerData.setPart_number(rs.getString("part_number"));
        customerData.setEmission_tier_type(rs.getString("emission_tier_type"));
        customerData.setAirbrake_type_code(rs.getString("airbrake_type_code"));
        customerData.setLms_fleet(rs.getString("lms_fleet"));
        customerData.setAar_road(rs.getString("aar_road"));
        customerData.setLocomotive_status_code(rs.getString("locomotive_status_code"));

        return customerData;
    }

}

Finally, I got my daoimpl course here

//DaoImpl
    public String getCustomersData(String locoId,String custName,String roadNumber) {
        CustomerData resultSet = null;
        String str = "";
        if (locoId != null && locoId.length() > 0 && !(locoId.equals("0"))) {
            str = "select   locomotive_id,customer_name,road_number,model_type as locomotive_type_code,to_char(in_service_date,'yyyy-mm-dd') as in_service_date,loco_part_number as part_number,emission_tier_type as emission_tier_type,"
                    + "air_brake_type as airbrake_type_code,lms_fleet,aar_road,locomotive_status_code   from get_rdf_explorer.get_rdf_locomotive_detail  where locomotive_id = ?";
            resultSet = (CustomerData) jdbcTemplate.queryForObject(str,new CustomerDataResponseMapper(),locoId);
        } else if ((custName != null && custName.length() > 0)
                && (roadNumber != null && roadNumber.length() > 0 && roadNumber != "0")) {
            str = "select   locomotive_id,locomotive_status_code   from get_rdf_explorer.get_rdf_locomotive_detail  where customer_name = ? and road_number= ?";
            resultSet = (CustomerData) jdbcTemplate.queryForObject(str,custName,roadNumber);
        } else {
            str = "select distinct customer_name from get_rdf_explorer.get_rdf_locomotive_detail order by customer_name asc";
            resultSet = (CustomerData) jdbcTemplate.queryForObject(str,new CustomerDataResponseMapper());
        }
        return resultSet.toString();
    }

How to conditionally obtain values from the resultset according to whether there are specific columns in the resultset Because I didn't get all the columns through my query

When there is no specific column in the resultset, I receive an SQL error syntax exception For example, when getting and executing a third query with different customer names, there is only customername in the resultset, not other columns

This is really a great help Thank you very much in advance

Solution

If the number of columns is not repaired, you should use columnmaprowmapper according to its implementation. Even if you do not need to create a separate rowmapper specific class (customerdataresponsemapper), you only need to pass an instance of columnmaprowmapper in the query, as shown below:

ColumnMapRowMapper rowMapper = new ColumnMapRowMapper();
List<Map<String,Object>> customerDataList =  jdbcTemplate.query(sql,rowMapper,args);

Now you should create a method to manipulate the map

private CustomerData fillCustomerDataFromMap(List<Map<String,Object>> customerDataList){
            CustomerData customerData = new CustomerData();

            for(Map<String,Object> map: customerDataList ){

               customerData.setColumn(map.get("columnName"));
               customerData.setColumn(map.get("columnName"));
               customerData.setColumn(map.get("columnName"));
               customerData.setColumn(map.get("columnName"));

.........
.........
.........
           }
        return customerData;
    }

This is more readable and removes the boilerplate code. If there is no column name in the map, no exception will be thrown (if there is no column name in the map, only null will be returned)

Reference to columnmaprowmapper:

https://github.com/spring-projects/spring-framework/blob/master/spring-jdbc/src/main/java/org/springframework/jdbc/core/ColumnMapRowMapper.java