Bad grammar SQL Exception while reading the values using rowmapper

Question

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

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

}

And finally, I got my DaoImpl class 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,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 customer_name = ? and road_number= ?";
            resultSet = (CustomerData) jdbcTemplate.queryForObject(str, new CustomerDataResponseMapper(), 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 can I conditionally get the values from the resultSet based on whether a particular column is present in the resultSet or not. As I am not getting all the columns all the time through my queries.

I am getting SQL bad grammar exception when specific column is not present in resultSet. For example when the third query to get distinct customer names get executed, in the resultSet only customerName would be there, but not the other columns.

It would be really a great help. Thanks a lot in advance.

Welcome to Stack Overflow! Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, _a specific problem or error_ and _the shortest code necessary_ to reproduce it **in the question itself**. Questions without a clear problem statement are not useful to other readers. See: [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). — Joe C, Jul 16 '17 at 20:46
@JoeC As it is myproject specific code, I can't post the complete code. — Santosh Anantharamaiah, Jul 16 '17 at 20:48
Well what about the _specific problem or error_? Not to mention the _**shortest** code necessary_? — Joe C, Jul 16 '17 at 20:49
@JoeC Understood your point.. Thanks a lot for that. Please see the updated question. Let me know if I have to add any further details. — Santosh Anantharamaiah, Jul 16 '17 at 20:55
Make several methods `CustomerData findByLocId(String locId)` and so on. — Joop Eggen, Jul 16 '17 at 21:05
@JoopEggen I had this in my mind, but I am wondering, is there a better way of doing the same. As I got multiple methods like this. — Santosh Anantharamaiah, Jul 16 '17 at 21:10
Depends, but you do not want usages like `find(..., null, ..., null)`. — Joop Eggen, Jul 16 '17 at 21:36
The method getCustomersData() should return List not String. In this way you should manage the last query that is completely different. Move it to separate method returning List and you will not need RowMapper at all. — Andriy Slobodyanyk, Jul 27 '17 at 11:12
Have a look at https://stackoverflow.com/questions/37016843/spring-generic-rowmapper-for-dynamic-queries — Yusuf Kapasi, Aug 01 '17 at 06:37
Hi, @ Suntosh Anantharamaiah you can use ColumnMapRowMapper for your situation for more detail please refer my answer given below — Irfan Bhindawala, Aug 01 '17 at 13:26

score 6 · Answer 1 · answered Jul 27 '17 at 06:06

6

Since you already have 3 separate queries why not have 3 separate RowMappers, one for each query. Your queries "know" what columns they return, so you can easily create those classes for RowMapper.

If you really want High-Fidelity solution you could create abstract base RowMapper for common parts and 3 subclasses for parts specifig to the query.

answered Jul 27 '17 at 06:06

ikettu

1,203
12
17

Let's consider there are only few column names different in each query, wouldn't this be against to DRY principle. Repeating the same piece of code at 3 different places. – Santosh Anantharamaiah Jul 27 '17 at 18:44
2

You could use common superclass for common parts. But in my opinion DRY is good principle but not to be enforced everywhere. Here you have 3 separate queries which most probably will evolve to be more different each other. These are simple classes and it's much readable code code with different RowMappres than trying to create some automatic magic which no-one can understand afterwards. – ikettu Jul 28 '17 at 04:52

aaguilera · Answer 2 · 2020-07-19T20:38:41.797

1

My advice is to split your getCustomersData into three different methods. If you definitely want to ignore this advice, the quick and dirty solution is to protect the rs.getString(...) calls inside your rowMapper. Something like this:

try {
    customerData.setLocomotive_id(rs.getString("locomotive_id"));
} catch (SQLException e) {
    // ignore this exception. DO NOT TRY THIS AT HOME!
}

edited Jul 19 '20 at 20:38

answered Jul 26 '17 at 06:55

aaguilera

1,080
10
27

https://stackoverflow.com/questions/3599861/how-can-i-determine-if-the-column-name-exist-in-the-resultset# this is what I closely following. Using exception for this scenario may not be the right choice right? – Santosh Anantharamaiah Jul 26 '17 at 08:28
Of course, as I wrote, this is a "quick and dirty" solution. You'd better refactor your design. – aaguilera Jul 27 '17 at 08:42
Hi, @aaguilera this solution is costlier and time consuming you can read more about how slow java exceptions are [here](http://www.fuwjax.com/how-slow-are-java-exceptions/) – Irfan Bhindawala Aug 01 '17 at 13:22

isah · Answer 3 · 2017-07-26T14:26:52.600

1

You can use a generic method which investigates the ResultSet's columns

@SuppressWarnings("unchecked")
public <T> T getColIfPresent(ResultSet rs, String columnName) throws SQLException {
    ResultSetMetaData metaData = rs.getMetaData();
    for (int i = 1; i <= metaData.getColumnCount(); i++) {
        if (columnName.equals(metaData.getColumnName(i))) {
            return (T) rs.getObject(columnName);
        }
    }
    return null;// not present
}

Then, in row mapper.

customerData.setLocomotive_id(getColIfPresent(rs, "locomotive_id"));
...

This has O(n*m) complexity where n - the number of columns checked, m - the number of columns returned in ResultSet.

If you choose to ignore the SQLException, at least log it in DEBUG or TRACE level in case a different subtype of SQLException occurs, so that it's not lost.

edited Jul 26 '17 at 14:26

answered Jul 26 '17 at 13:49

isah

5,221
3
26
36

I completely agree to your point, but it definitely impacts performance right? As it has to check for the columns then come back and assign values. – Santosh Anantharamaiah Jul 26 '17 at 15:41
I suggest storing the investigated columns in row mapper's private attribute as `Set columns`. Then populate this `Set` with `ResultSet`'s columns on first row, the other rows will just do a contains check `if(columns.contains(columnName)){..}` check on this set, which is O(1). This way columns are determined only once per query, not for every row. – isah Jul 26 '17 at 16:18

score 1 · Answer 4 · answered Jul 27 '17 at 08:40

Rather than conditionnaly getting columns, you could modify your SQL to match your mapper, like setting other field to empty string or null (I don't remmember if getString() crashes on null or something).

For example your third query would look like:

select distinct customer_name, null as "locomotive_id",'' as "road_number", null as model_type, [etc.] from get_rdf_explorer.get_rdf_locomotive_detail order by customer_name asc

So each query would have the same columns and you don't have to adapt. This is the solution if you d'ont really want/can't change the rowMapper (or want to have only one for this object).

But honestly I would go with ikketu's solution. You should make a separate mapper for the thrid query (plus, it wouldn't be complicated). Not goign with an ORM is a choice but you'll have redundancy problem anyway. I would even add that you should separate some of the logic in your code, this methods seems to be doing different thing (business logic depending on input, and database access) it's not very clear (after the third if, create a method like "getdistinctName()" or something).

score 1 · Answer 5 · answered Jul 31 '17 at 05:37

Santosh, a quick workaround could be passing a flag to your rowmapper while supplying it to jdbcTemplate. I've done so many times to avoid multiple rowmapper. resultSet = (CustomerData) jdbcTemplate.queryForObject(str, new CustomerDataResponseMapper(1), custName, roadNumber);

For the above changes, you need to overload constructor with the default one. Then you need to use your flag i.e. instance variable in mapRow() method to handle each situation separately.

Zico · Answer 6 · 2017-07-31T09:54:54.667

1

You can use BeanPropertyRowMapper which will directly map field names of target class. Here is the javadoc.

The names are matched either directly or by transforming a name separating the parts with underscores to the same name using "camel" case. So, you can use it any other classes whenever you want to map directly to a class. Just have to make sure selected fields are remain in target class. And a default or no-arg constructor.

Following example to get CustomerData using BeanPropertyRowMapper

RowMapper<CustomerData> mapper = new BeanPropertyRowMapper<>(CustomerData.class);
List<CustomerData> result = jdbc.query("your query string...", mapper, query_args...);

So, then you can return first object or whatsoever.

edited Jul 31 '17 at 09:54

answered Jul 31 '17 at 08:06

Zico

2,349
2
22
25

hi, @zico i don't think that this will helpful for undecided number of columns – Irfan Bhindawala Aug 01 '17 at 13:24
@IrfanBhindawala What do you mean by undecided number of column? Though `BeanPropertyRowMapper ` automatically map selected columns with the properties of target class. If he select one field and present it in the targeted class which having more than one property then it will just bind(call default setter) only one field. – Zico Aug 01 '17 at 13:38
how `BeanPropertyRowMapper` will decide which setter of the class to call and what do you mean by default setter – Irfan Bhindawala Aug 01 '17 at 13:59
1

@IrfanBhindawala you can get all info in documentation. Link provided in answer – Zico Aug 01 '17 at 14:18
Thanks @Zic for the reference – Irfan Bhindawala Aug 02 '17 at 13:23

score 0 · Answer 7 · answered Jul 31 '17 at 07:45

Based on the logic of your queries i see that before executing sql query in get_rdf_explorer.get_rdf_locomotive_detail there are some records and 3 options of getting of necessary (unique) record are possible:

by locomotive_id
by customer_name and road_number
ANY record (all records must have same customer_name, else SQL distinct without any conditions return more than 1 row)

So, in the 3rd option you can get any 1 record with all attributes equal to NULL and NOT NULL customer_name value:

str = "select null as locomotive_id, customer_name, null as road_number,     
<other attributes> from get_rdf_explorer.get_rdf_locomotive_detail where 
rownum = 1";`

score 0 · Accepted Answer · answered Aug 01 '17 at 13:09

If numbers of columns are not fix then you should go with ColumnMapRowMapper based on its implementation even you do not require to create separate concrete class of RowMapper (i.e. CustomerDataResponseMapper ) you just need to pass instance of ColumnMapRowMapper in query as given below:

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

Now you should create one method to manipulate this map like

    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 remove the boilerplate codes and not throw any exception if column name is not present in map (it will simply returns null if column name is not present in map)

Reference of ColumnMapRowMapper :

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

Perfect, this is what I was looking for. Is there any working example? — Santosh Anantharamaiah, Aug 01 '17 at 13:48
Hi, @ Santosh Anantharamaiah this should work without any doubt because we only used class of spring jdbc library that's it. — Irfan Bhindawala, Aug 01 '17 at 13:57

Bad grammar SQL Exception while reading the values using rowmapper

8 Answers8