Spring: Generic RowMapper for dynamic queries

Question

I am using SpringBatch to read from Oracle and write to ElasticSearch.

My code works fine for a static query. Example: select emp_id, emp_name from employee_table I have a RowMapper class that displays values ​​from resultSet using the POJO Employee.

My requirement

The request will be entered by the user. Thus, the request may be as follows:

• select emp_id, emp_name from employee_table
• select cust_id, cust_name, cust_age from customer_table
• select door_no, street_name, loc_name, city from address_table
• Similar queries

My questions

• Have a way to dynamically create a POJO according to a user-defined request?
• Will the RowMapper concept work if the request changes, as in my case?
• Is there something like a generic line?

Thank you for your time. Sample code will be highly appreciated.

Answer 1

If you have objects, you need to map them to ...

Consider smoothing your SQL to match your object field names with a custom RowMapper implementation that actually extends BeanWrapperFieldSetMapper

So, if your POJO looks like this:

 public class Employee { private String employeeId; private String employeeName; ... // getters and setters } 

Then your SQL might look like this:

 SELECT emp_id employeeId, emp_name employeeName from employee_table 

Then your wrapped RowMapper will look something like this:

 import org.springframework.jdbc.core.RowMapper import org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper public class BeanWrapperRowMapper<T> extends BeanWrapperFieldSetMapper<T> implements RowMapper<T> { @Override public T mapRow(final ResultSet rs, final int rowNum) throws SQLException { final FieldSet fs = getFieldSet(rs); try { return super.mapFieldSet(fs); } catch (final BindException e) { throw new IllegalArgumentException("Could not bind bean to FieldSet", e); } } private FieldSet getFieldSet(final ResultSet rs) throws SQLException { final ResultSetMetaData metaData = rs.getMetaData(); final int columnCount = metaData.getColumnCount(); final List<String> tokens = new ArrayList<>(); final List<String> names = new ArrayList<>(); for (int i = 1; i <= columnCount; i++) { tokens.add(rs.getString(i)); names.add(metaData.getColumnName(i)); } return new DefaultFieldSet(tokens.toArray(new String[0]), names.toArray(new String[0])); } } 

As an alternative...

If you don’t have a POJO to map, use the ready-made ColumnMapRowMapper to get a map ( Map<String,Object> ) of the column names (call them COL_A, COL_B, COL_C) for the values. Then, if your writer is similar to JdbcBatchItemWriter , you can set your named parameters as:

 INSERT TO ${schema}.TARGET_TABLE (COL_1, COL_2, COL_3) values (:COL_A, :COL_B, :COL_C) 

and then the implementation of ItemSqlParameterSourceProvider might look like this:

 public class MapItemSqlParameterSourceProvider implements ItemSqlParameterSourceProvider<Map<String, Object>> { public SqlParameterSource createSqlParameterSource(Map<String, Object> item) { return new MapSqlParameterSource(item); } } 

Answer 2

To answer your questions:

• Is there a way to dynamically create a POJO based on a user request. Even if it were, I’m not sure how much this will help. For your use case, I would suggest just using Map .
• Will the RowMapper concept work if the request continues to change. If you use Map , you can use column names as keys and column values ​​as values. You can create a RowMapper implementation that can do this.
• Is there something like a generic RowMapper - is there, but it is intended for POJO, so you need to create your own for this.

Answer 3

I found a solution to my problem using Spring ColumnMapRowMapper. Locate the fragment of the xml configuration file. I did not create a POJO class. I succeeded with the map and insert into ES. The name of the card key must match the field names contained in the index.

 <step id="slave" xmlns="http://www.springframework.org/schema/batch"> <tasklet> <chunk reader="pagingItemReader" writer="elasticSearcItemWriter" processor="itemProcessor" commit-interval="10" /> </tasklet> </step> <bean id="pagingItemReader" class="org.springframework.batch.item.database.JdbcPagingItemReader" scope="step"> <property name="dataSource" ref="dataSource" /> <property name="queryProvider"> <bean class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="selectClause" value="*******" /> <property name="fromClause" value="*******" /> <property name="whereClause" value="*******" /> <property name="sortKey" value="*******" /> </bean> </property> <!-- Inject via the ExecutionContext in rangePartitioner --> <property name="parameterValues"> <map> <entry key="fromId" value="#{stepExecutionContext[fromId]}" /> <entry key="toId" value="#{stepExecutionContext[toId]}" /> </map> </property> <property name="pageSize" value="10" /> <property name="rowMapper"> <bean class="org.springframework.jdbc.core.ColumnMapRowMapper" /> </property> </bean> 

And inside my elasticSearcItemWriter class ....

 public class ElasticSearchItemWriter<T> extends AbstractItemStreamItemWriter<T> implements ResourceAwareItemWriterItemStream<T>, InitializingBean { .... .... .... @Override public void write(List<? extends T> items) throws Exception { client = jestClient.getJestClient(); if (items.size() > 0) { for (Object item : items) { @SuppressWarnings("unchecked") Map<String, Object> map = (Map<String, Object>) item; // Asynch index Index index = new Index.Builder(map).index(Start.prop.getProperty(Constants.ES_INDEX_NAME)) .type(Start.prop.getProperty(Constants.ES_INDEX_TYPE)).build(); client.executeAsync(index, new JestResultHandler<JestResult>() { public void failed(Exception ex) { } public void completed(JestResult result) { } }); } } } ..... .... }