Spring Batch之Sample（游标方式读写DB数据表）

最新推荐文章于 2023-07-25 08:21:20 发布

李楚男

最新推荐文章于 2023-07-25 08:21:20 发布

阅读量1.3k

点赞数

分类专栏： springbatch

本文链接：https://blog.csdn.net/XuanYuZuo/article/details/12833311

版权

springbatch 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

在看本篇博客之前，希望您能先到http://xuanmeiku.taobao.com去转转，里面全是真皮炫美酷时尚女鞋，价格实惠！如果你看中了哪一款，可以加我qq1074992674，或者直接通过旺旺联系我！欢迎大家的骚扰！本人诚信经营，绝不做欺骗他人的事情！

前面关于Spring Batch的文章，讲述了SpringBatch对Flat、XML等文件的读写操作。本文将和大家一起讨论Spring Batch对DB的读写操作。Spring Batch对DB数据的读取操作提供两种形式，一种以游标为基础，一条条的读取数据；另外一种是分页的方式读取DB。

工程结构图如下：

1、pom.xml文件的内容主要如下：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>springbatch</groupId>
  <artifactId>springbatch-jdbc</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>springbatch-jdbc</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    <dependency>
			<groupId>org.springframework.batch</groupId>
			<artifactId>spring-batch-core</artifactId>
			<version>2.2.2.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-oxm</artifactId>
			<version>3.2.4.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>c3p0</groupId>
			<artifactId>c3p0</artifactId>
			<version>0.9.1.2</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>3.2.4.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>10.2.0.2.0</version>
		</dependency>
  </dependencies>
</project>

2、数据源c3p0.properties的内容主要如下：

c3p0.driverClass=oracle.jdbc.driver.OracleDriver

c3p0.jdbcUrl=jdbc:oracle:thin:@172.16.27.34:1521:vmdb
c3p0.user=BPPF_BPS
c3p0.password=BPS2012

# pool settings
c3p0.maxPoolSize=20
c3p0.minPoolSize=3
c3p0.initialPoolSize=5

# connection settings
c3p0.acquireIncrement=3
c3p0.acquireRetryAttempts=30
c3p0.acquireRetryDelay=1000
c3p0.checkoutTimeout=30000

# idle settings
c3p0.preferredTestQuery=SELECT SYSDATE FROM DUAL
c3p0.idleConnectionTestPeriod=300
c3p0.maxIdleTime=1800

# cache settings
c3p0.maxStatements=150
c3p0.maxStatementsPerConnection=0

# other settings
c3p0.numHelperThreads=30

3、applicationContext.xml文件内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans  
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd  
http://www.springframework.org/schema/tx  
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd  
http://www.springframework.org/schema/aop  
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd  
http://www.springframework.org/schema/context  
http://www.springframework.org/schema/context/spring-context-2.5.xsd"
	default-autowire="byName">
	<context:component-scan base-package="cn.lichunan.springbatch"></context:component-scan>
	<bean
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
			<value>classpath:c3p0.properties</value>
		</property>
	</bean>
	<!-- 启动job -->
	<bean id="jobLauncher"
		class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
		<property name="jobRepository" ref="jobRepository"></property>
	</bean>
	<!-- 提供持久化操作 -->
	<bean id="jobRepository"
		class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean"></bean>
	<!-- 提供事物管理操作 -->
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager" lazy-init="true">
		<property name="dataSource" ref="dataSource" />
	</bean>
	
	<!-- 数据源的相关配置 -->
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
		destroy-method="close">
		<property name="driverClass" value="${c3p0.driverClass}"></property>
		<property name="jdbcUrl" value="${c3p0.jdbcUrl}"></property>
		<property name="user" value="${c3p0.user}"></property>
		<property name="password" value="${c3p0.password}"></property>
		<property name="maxPoolSize" value="${c3p0.maxPoolSize}"></property>
		<property name="minPoolSize" value="${c3p0.minPoolSize}"></property>
		<property name="initialPoolSize" value="${c3p0.initialPoolSize}"></property>
		<property name="acquireIncrement" value="${c3p0.acquireIncrement}"></property>
		<property name="acquireRetryAttempts" value="${c3p0.acquireRetryAttempts}"></property>
		<property name="acquireRetryDelay" value="${c3p0.acquireRetryDelay}"></property>
		<property name="checkoutTimeout" value="${c3p0.checkoutTimeout}"></property>
		<property name="preferredTestQuery" value="${c3p0.preferredTestQuery}"></property>
		<property name="idleConnectionTestPeriod" value="${c3p0.idleConnectionTestPeriod}"></property>
		<property name="maxIdleTime" value="${c3p0.maxIdleTime}"></property>
		<property name="maxStatements" value="${c3p0.maxStatements}"></property>
		<property name="maxStatementsPerConnection" value="${c3p0.maxStatementsPerConnection}"></property>
		<property name="numHelperThreads" value="${c3p0.numHelperThreads}"></property>
	</bean>

</beans>

Spring Batch对DB基于游标的读取数据操作，是由其核心JdbcCursorItemReader来实现的。一般来说，从DB数据表中读取数据一般有以下几个步骤。首先是DB连接，这些连接DB的基本信息（像DB服务器地址、用户名、密码等信息）由dataSource属性提供，Spring Batch没有提供专门供特殊的类，用的是Spring框架的DataSource。连接上了DB，下面需要关注的就是对DB具体的查询操作了，也就是SQL属性实现，主要是拼接SQL文的一个字符串。有了sql，有可能需要传递参数，sql文参数的一些信息，由preparedStatementSetter属性来满足，具体的实现可以由SpringBatch提供的核心类ListPreparedStatementSetter来设置。连接上了DB，执行了SQL文，最后要关心的就是查询结果的存放问题了，这一点由JdbcCursorItemReader的rowMapper属性来实现。

4、 batch.xml文件内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<bean:beans xmlns="http://www.springframework.org/schema/batch"
	xmlns:bean="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:p="http://www.springframework.org/schema/p" xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:util="http://www.springframework.org/schema/util"
	xsi:schemaLocation="http://www.springframework.org/schema/beans 
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd  
http://www.springframework.org/schema/tx 
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd  
http://www.springframework.org/schema/aop 
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd  
http://www.springframework.org/schema/context 
http://www.springframework.org/schema/context/spring-context-2.5.xsd
http://www.springframework.org/schema/batch 
http://www.springframework.org/schema/batch/spring-batch-2.2.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util.xsd
">
	<bean:import resource="applicationContext.xml" />

	<!-- Job信息的配置 -->
	<job id="ioSampleJob">
		<step id="step1">
			<tasklet>
				<chunk reader="itemReader" processor="itemProcessor" writer="itemWriter" commit-interval="1"></chunk>
			</tasklet>
		</step>
	</job>
	
	<bean:bean id="itemProcessor" class="cn.lichunan.springbatch.processor.ItemProcessor"></bean:bean>
	
	<!-- jdbcItemReader信息配置org.springframework.batch.sample.domain.trade.internal.CustomerCreditRowMapper-->
	<bean:bean id="itemReader"
		class="org.springframework.batch.item.database.JdbcCursorItemReader"
		scope="step">
		<bean:property name="dataSource" ref="dataSource"></bean:property>
		<bean:property name="sql"
			value="select ID,USERID,USERNAME,PASSWORD from T_USER"></bean:property>
		<bean:property name="verifyCursorPosition" value="true"></bean:property>
		<bean:property name="rowMapper">
			<bean:bean class="cn.lichunan.springbatch.rowmapper.UserRowMapper"/>
		</bean:property>
	</bean:bean>

	<!-- jdbcItemWriter信息的配置 -->
	<bean:bean id="itemWriter"
		class="org.springframework.batch.item.database.JdbcBatchItemWriter">
		<bean:property name="dataSource" ref="dataSource"></bean:property>
		<bean:property name="sql"
			value="insert into T_DESTUSER(ID,USERID,USERNAME,PASSWORD,UPDATETIME,UPDATEUSER)values(:id,:userId,:userName,:passWord,:updateTime,:updateUser)"></bean:property>
		<bean:property name="itemSqlParameterSourceProvider">
			<bean:bean class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider"></bean:bean>
		</bean:property>
	</bean:bean>

</bean:beans>

5、本文主要涉及到两个表和其对应的实体类；下面主要把两个表的结构图贴下：

T_USER:

T_DESTUSER:

李楚男

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Spring Batch之Sample（游标方式读写DB数据表）

前面关于Spring Batch的文章，讲述了SpringBatch对Flat、XML等文件的读写操作。本文将和大家一起讨论Spring Batch对DB的读写操作。Spring Batch对DB数据的读取操作提供两种形式，一种以游标为基础，一条条的读取数据；另外一种是分页的方式读取DB。工程结构图如下：1、pom.xml文件的内容主要如下：<project xmlns="ht
复制链接

扫一扫