java – 使用rowmapper读取值时出现错误语法SQL异常

发布时间：2020-12-14 19:37:34 所属栏目：Java 来源：网络整理

导读：这是我的Model类 //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; pr

这是我的Model类

//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

这是我的RowMapper实现

//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;
    }

}

最后,我在这里得到了我的DaoImpl课程

//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();
    }

如何根据resultSet中是否存在特定列来有条件地从resultSet获取值.因为我没有通过我的查询得到所有列.

当resultSet中不存在特定列时,我收到SQL错误语法异常.例如,当获取执行不同客户名称的第三个查询时,在resultSet中只有customerName,而不是其他列.

这真的是一个很大的帮助.非常感谢提前.

解决方法

如果没有修复列数,那么你应该根据它的实现使用ColumnMapRowMapper,即使你不需要创建单独的RowMapper具体类(即CustomerDataResponseMapper),你只需要在查询中传递ColumnMapRowMapper的实例,如下所示：

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

现在你应该创建一个方法来操作这个地图

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;
    }

这更具可读性并删除样板代码,如果map中不存在列名,则不会抛出任何异常(如果map中不存在列名,则只返回null)

ColumnMapRowMapper的引用：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!