java Spring：用于动态查询的通用 RowMapper

Spring: Generic RowMapper for dynamic queries

提问by Andy

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

我正在使用 SpringBatch 从 Oracle 读取并写入 ElasticSearch。

My code works fine for static query. Example: select emp_id, emp_name from employee_tableI have a RowMapper class that maps the values from resultSet with the Employee POJO.

我的代码适用于静态查询。示例： select emp_id, emp_name from employee_table我有一个 RowMapper 类，该类将 resultSet 中的值与 Employee POJO 进行映射。

My requirement is

我的要求是

The query will be inputted by the user. So the query might be as follows

查询将由用户输入。所以查询可能如下

1. select emp_id, emp_name from employee_table
2. select cust_id, cust_name, cust_age from customer_table
3. select door_no, street_name, loc_name, city from address_table
4. Similar queries

1. select emp_id, emp_name from employee_table
2. select cust_id, cust_name, cust_age from customer_table
3. select door_no, street_name, loc_name, city from address_table
4. Similar queries

My questions are

我的问题是

1. Id there a way to dynamically create a POJO according to the query given by the user?
2. Will the RowMapper concept work if the query keeps changing as in my case?
3. Is there something like a generic rowmapper?

1. 有没有办法根据用户给出的查询动态创建 POJO？
2. 如果查询像我的情况一样不断变化，RowMapper 概念是否有效？
3. 有没有类似通用行映射器的东西？

Thanks for your time. Sample code would be much appreciated.

谢谢你的时间。示例代码将不胜感激。

回答by Dean Clark

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

如果您有对象需要映射到...

Consider aliasing your SQL to match your object field names using a custom implementation of RowMapperwhich actually extends BeanWrapperFieldSetMapper

考虑使用RowMapper实际扩展的自定义实现为您的 SQL 设置别名以匹配您的对象字段名称BeanWrapperFieldSetMapper

So if your POJO looks like this:

因此，如果您的 POJO 如下所示：

public class Employee {

    private String employeeId;
    private String employeeName;

    ...
    // getters and setters

}

Then your SQL can look like this:

然后你的 SQL 看起来像这样：

SELECT emp_id employeeId, emp_name employeeName from employee_table

Then your wrapped RowMapperwould look something like this:

然后你的包裹RowMapper看起来像这样：

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

}

Alternatively...

或者...

If you don't have any POJOs to map to, use the out-of-box ColumnMapRowMapperto get get back a map (Map<String,Object>) of column names (let's call them COL_A, COL_B, COL_C) to values. Then if your writer is something like a JdbcBatchItemWriteryou can set your named parameters as:

如果您没有要映射到的任何 POJO，请使用开箱即用的方法将列名（我们称之为 COL_A、COL_B、COL_C）ColumnMapRowMapper的映射 ( Map<String,Object>) 取回值。然后，如果您的编写器类似于 aJdbcBatchItemWriter您可以将命名参数设置为：

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

and then your ItemSqlParameterSourceProviderimplementation could look like so:

然后您的ItemSqlParameterSourceProvider实现可能如下所示：

public class MapItemSqlParameterSourceProvider implements
        ItemSqlParameterSourceProvider<Map<String, Object>> {

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

}

回答by Michael Minella

To answer your questions:

回答您的问题：

1. Is there a way to dynamically create a POJO based on the user's query - Even if there was, I'm not sure how much help it would be. For your use case, I'd suggest just using a Map.
2. Will the RowMapperconcept work if the query keeps changing - If you use a Map, you can use the column names as the keys and the column values as the values. You should be able to create a RowMapperimplementation that can do this.
3. Is there something like a generic RowMapper- There is but it's intended for POJO's so you'd need to create your own for this.

1. 有没有办法根据用户的查询动态创建 POJO - 即使有，我也不确定它会有多大帮助。对于您的用例，我建议只使用Map.
2. RowMapper如果查询不断变化，这个概念是否有效 - 如果您使用 a Map，则可以使用列名作为键，使用列值作为值。您应该能够创建RowMapper可以执行此操作的实现。
3. 有没有类似通用的东西RowMapper- 有，但它是为 POJO 设计的，所以你需要为此创建自己的。

回答by Andy

I found a solution to my problem by using Spring's ColumnMapRowMapper. Please find a snippet from the xml configuration file. I didn't generate any POJO class. I managed with a Mapand inserted the same into ES. The map's key name should match with the field names present in index.

我通过使用 Spring 的ColumnMapRowMapper找到了解决我的问题的方法。请从 xml 配置文件中找到一个片段。我没有生成任何 POJO 类。我用Map管理并将其插入到 ES 中。地图的键名应该与索引中存在的字段名匹配。

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

在我的 elasticSercItemWriter 类中......

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) {
                    }
                });
            }
        }
    }
.....
....
}