Bad SQL Exception grammar while reading values ​​with 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 implementation of 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; } } 

And finally, I got here the DaoImpl class

 //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 values ​​from a result set based on whether a particular column is present in the result set or not. Since I do not get all the columns all the time for my queries.

I get a SQL error with a grammar error when a particular column is missing in the resultSet. For example, when a third query is executed to obtain individual customer names, the result set will contain only the customer name and not the other columns.

That would be really great help. Thank you very much in advance.

Answer 1

If the column numbers are not fixed, you should go with ColumnMapRowMapper based on your implementation, even if you don't need to create a separate concrete RowMapper class (i.e. CustomerDataResponseMapper ), you just need to pass an instance of ColumnMapRowMapper in the request, as follows:

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

Now you must create one method to manage this map, for example

  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 template codes and throws no exceptions if the column name is not on the map (it will just return null if the column name is not on the map)

ColumnMapRowMapper Link:

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

Answer 2

Since you already have 3 separate requests, why not allocate 3 separate RowMapper s, one for each request. Your queries “know” which columns they return, so you can easily create these classes for RowMapper .

If you really need a High-Fidelity solution, you can create an abstract RowMapper database for common parts and 3 subclasses for details specifying a request.

Answer 3

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

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

Answer 4

You can use a generic method that examines the columns of a ResultSet

 @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 the string converter.

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

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

If you decide to ignore a SQLException , at least register it at DEBUG or TRACE if another subtype of SQLException occurs so that it is not lost.

Answer 5

Instead of getting conditionnaly to get columns, you can change your SQL to fit your cartographer, for example, setting another field to an empty string or null (I don’t remember if getString () fails with zero or something kind).

For example, your third query would look like this:

 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 

This way, each query will have the same columns and you won’t have to adapt. This solution is if you really want / cannot change rowMapper (or want to have only one for this object).

But honestly, I would go with an ikketu solution. You have to make a separate cartographer to query thrid (plus, it will not be difficult). Not goign with ORM - this is a choice, but in any case you will have a redundancy problem. I would even add that you should separate part of the logic of your code, these methods seem to do different things (business logic depending on the input and access to the database) this is not very clear (after the third, if you create a method such as "getdistinctName ()" or something else).

Answer 6

Santosh, a quick workaround can pass a flag to your string while it is being passed to jdbcTemplate. I have done this many times to avoid multiple rowmapper. resultSet = (CustomerData) jdbcTemplate.queryForObject (str, new CustomerDataResponseMapper (1), custName, roadNumber);

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

Answer 7

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

Names are matched either directly or by converting a name that separates parts with underscores to the same name using the “camel” case. . Thus, you can use it in any other classes when you want to map directly to the class. Just make sure that the selected fields remain in the target class. And the default constructor or with no argument.

In the 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 you can return the first object or something else.

Answer 8

Based on the logic of your queries, I see that before executing an SQL query, get_rdf_explorer.get_rdf_locomotive_detail can record and there are 3 options for obtaining the necessary (unique) record:

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

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

 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";`