spring batch csv文件导入到mysql数据库

继续学习spring batch , 定义个 spring batch  job, 将数据读入到 mysql 数据库中. 用数据库记录job 任务。所用到的jar包与第一篇文章(http://www.yihaomen.com/article/java/433.htm)相同.

整个工程程序结构如下:


csv 文件格式如下:
程序代码 程序代码

2006-1-13    139,237    37    227.21
2006-2-13    149,582    55    234.71
2006-3-13    457,425    132    211.48
2006-4-13    466,870    141    298.4
2006-5-13    472,385    194    281.35
2006-6-13    438,653    153    273.21
2006-7-13    423,614    155    283.66
2006-8-13    112,449    62    223.89
2006-9-13    126,651    50    297.97
2006-10-13    453,405    135    291.16
2006-11-13    461,418    140    209.42
2006-12-13    436,383    135    258.28


另外在mysql 中设计数据表:

程序代码 程序代码

mysql> desc RAW_REPORT;
+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(11)       | NO   | PRI | NULL    | auto_increment |
| IMPRESSIONS | varchar(200)  | YES  |     | NULL    |                |
| CLICKS      | int(11)       | YES  |     | NULL    |                |
| EARNING     | decimal(10,2) | YES  |     | NULL    |                |
| CreateDDATE | date          | YES  |     | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+
5 rows in set


配置数据库连接
程序代码 程序代码

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
        http://www.springframework.org/schema/jdbc
        http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd">

    <!-- connect to database -->
    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/test" />
        <property name="username" value="root" />
        <property name="password" value="password" />
    </bean>

    <bean id="transactionManager"
        class="org.springframework.batch.support.transaction.ResourcelessTransactionManager" />
    
    <!-- create job-meta tables automatically -->
    <!--
    <jdbc:initialize-database data-source="dataSource">
        <jdbc:script location="org/springframework/batch/core/schema-drop-mysql.sql" />
        <jdbc:script location="org/springframework/batch/core/schema-mysql.sql" />
    </jdbc:initialize-database>
     -->

</beans>



spring batch 核心设置
程序代码 程序代码

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.1.xsd">

    <!-- stored job-meta in database -->
    <bean id="jobRepository"
        class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="transactionManager" ref="transactionManager" />
        <property name="databaseType" value="mysql" />
    </bean>

    <!-- stored job-meta in memory -->
    <!--
    <bean id="jobRepository"
        class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean">
        <property name="transactionManager" ref="transactionManager" />
    </bean>
    -->

    <bean id="jobLauncher"
        class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
        <property name="jobRepository" ref="jobRepository" />
    </bean>

</beans>



job 的定义
程序代码 程序代码

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:batch="http://www.springframework.org/schema/batch"
    xmlns:task="http://www.springframework.org/schema/task"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/batch
        http://www.springframework.org/schema/batch/spring-batch-2.2.xsd
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.1.xsd">

    <bean id="report" class="com.yihaomen.model.Report" scope="prototype" />
    
    <batch:job id="reportJob1">
        <batch:step id="step1">
            <batch:tasklet>
                <batch:chunk reader="cvsFileItemReader" writer="mysqlItemWriter"
                    commit-interval="2">
                </batch:chunk>
            </batch:tasklet>
        </batch:step>
    </batch:job>

    <bean id="cvsFileItemReader" class="org.springframework.batch.item.file.FlatFileItemReader">

        <!-- Read a csv file -->
        <property name="resource" value="classpath:cvs/report.csv" />

        <property name="lineMapper">
            <bean class="org.springframework.batch.item.file.mapping.DefaultLineMapper">

                <!-- split it -->
                <property name="lineTokenizer">
                    <bean
                        class="org.springframework.batch.item.file.transform.DelimitedLineTokenizer">
                        <property name="names" value="date,impressions,clicks,earning" />
                    </bean>
                </property>

                <property name="fieldSetMapper">
                    
                    <!-- return back to reader, rather than a mapped object. -->
                    <!--
                        <bean class="org.springframework.batch.item.file.mapping.PassThroughFieldSetMapper" />
                    -->
                      
                    <!-- map to an object -->
                    <bean
                        class="org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper">
                        <property name="prototypeBeanName" value="report" />
                    </bean>
                    
                </property>

            </bean>
        </property>

    </bean>

    <bean id="mysqlItemWriter"
        class="org.springframework.batch.item.database.JdbcBatchItemWriter">
        <property name="dataSource" ref="dataSource" />
        <property name="sql">
            <value>
            <![CDATA[        
                insert into RAW_REPORT(CreateDDATE,IMPRESSIONS,CLICKS,EARNING) values (:date, :impressions, :clicks, :earning)
            ]]>
            </value>
        </property>
        <!-- It will take care matching between object property and sql name parameter -->
        <property name="itemSqlParameterSourceProvider">
            <bean
                class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" />
        </property>
    </bean>

</beans>



直接运行app 中的main方法,可以得到如下结果:所有的数据都插入到RAW_REPORT表中


源代码下载:
下载文件 spring batch csv 存储到mysql


除非申明,文章均为一号门原创,转载请注明本文地址,谢谢!
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags: springbatch
相关日志:
评论: 0 | 引用: 0 | 查看次数: -
发表评论
昵 称:
密 码: 游客发言不需要密码.
内 容:
验证码: 验证码
选 项:
虽然发表评论不用注册,但是为了保护您的发言权,建议您注册帐号.