添加“与ur”或由JdbcPagingItemReader生成的查询中的任何其他前缀 [英] Adding "with ur" or any other prefix in query being generated by JdbcPagingItemReader

问题描述

我正在编写一个java应用程序来从一个表读取数据并将其写入某个文件。我们的表有数百万条记录，我们需要每天阅读
并写入文件。所以，我正在使用读取器的Spring批处理作为JdbcPagingItemReader，因为我想在页面中读取记录。
以下是我的bean defination： -

 < bean id =pagingItemReaderclass =org.springframework。 batch.item.database.JdbcPagingItemReader
 scope =step> 
< property name =dataSourceref =dataSource/> 
< property name =queryProvider> 
< bean 
 class =org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean> 
< property name =dataSourceref =dataSource/> 
< property name =selectClausevalue =select user_id，user_name，address/> 
< property name =fromClausevalue =from mySchema.test/> 
< property name =whereClausevalue =where address<>'/> 
< property name =sortKeyvalue =user_id/> 
< / bean> 
< / property> 
< property name =pageSizevalue =1000/> 
< property name =rowMapper> 
< bean class =com.myCompany.myClient.batch.reader.userVO/> 
< / property> 
< / bean> 
  

Spring内部生成查询： -

  SELECT user_id，user_name，address FROM mySchema.test ORDER BY user_id ASC FETCH FIRST 1000 ROWS ONLY 
  

但是，我正在使用DB2数据库，全局设置在读取期间锁定。所以避免这个，我们在每次查询中使用with ur，我们写。
所以我想要我的查询生成为： -

  SELECT user_id，user_name，address FROM mySchema.test ORDER BY user_id ASC FETCH FIRST 1000 ROWS ONLY with ur 
  

我无法找到这样做。请使用JdbcPagingItemReader，让我知道如何在查询中附加一些额外的部分。

解决方案

JdbcPagingItemReader 允许您设置一个 PagingQueryProvider ;可能使用自定义的 - 而不是使用由 SqlPagingQueryProviderFactoryBean 生成的一个 - 可以解决您的问题

I am writing a java application to read data from one table and writing it in some file. Our table have million of records, which we need to read daily and write in file. So, I am using Spring batch with reader as JdbcPagingItemReader, as I want to read records in pages. Below is my bean defination :-

<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="select user_id, user_name , address" />
        <property name="fromClause" value="from mySchema.test" />
        <property name="whereClause" value="where address <> ''" />
        <property name="sortKey" value="user_id" />
      </bean>
    </property>
    <property name="pageSize" value="1000" />
    <property name="rowMapper">
        <bean class="com.myCompany.myClient.batch.reader.userVO" />
    </property>
</bean>

Spring generates the query internally like this :-

SELECT user_id, user_name , address  FROM mySchema.test ORDER BY user_id ASC FETCH FIRST 1000 ROWS ONLY

However, I am using DB2 database ,with global setting to take lock during the read. So avoid this, we use "with ur" in every query,we write. So I want my query to be generated as :-

   SELECT user_id, user_name , address  FROM mySchema.test ORDER BY user_id ASC FETCH FIRST 1000 ROWS ONLY with ur

I am not able to find anyway to do same. Kindly let me know how to append some extra part in query while using JdbcPagingItemReader.

解决方案

JdbcPagingItemReader allow you to set a PagingQueryProvider; maybe using a custom one - instead of using the one generated by SqlPagingQueryProviderFactoryBean - can solve your problem

这篇关于添加“与ur”或由JdbcPagingItemReader生成的查询中的任何其他前缀的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！