spring 整合 mybatis+webmagic爬取数据并持

因为最近在爬数据进行分析，数据已经爬好了，但最后还是需要持久化到数据库。因为公司用的持久化框架是mybatis，这里面又不需要mvc的架构，所以只需要spring 和 mybatis进行整合就行了，spring 作为bean容器，mybatis负责orm映射和持久化。

我这边用的是gradle构建工具，下面是我的依赖：

    compile 'us.codecraft:webmagic-core:0.5.3'
    compile('us.codecraft:webmagic-extension:0.5.3')
    compile 'org.seleniumhq.selenium:selenium-java:2.8.0'
    compile group: 'us.codecraft', name: 'webmagic-selenium', version: '0.5.2'
    compile 'com.github.detro:phantomjsdriver:1.2.0'
    testCompile group: 'junit', name: 'junit', version: '4.11'
    compile group: 'com.fasterxml.jackson.core', name: 'jackson-databind', version: '2.8.5'
    compile 'org.springframework:spring-aop:4.2.4.RELEA SE'
    compile 'org.springframework:spring-context:4.2.4.RELEASE'
    compile 'org.springframework:spring-beans:4.2.4.RELEASE'
    compile 'org.springframework:spring-web:4.2.4.RELEASE'
    compile 'org.springframework:spring-webmvc:4.2.4.RELEASE'
    compile 'org.springframework:spring-tx:4.2.4.RELEASE'
    compile 'org.springframework:spring-jdbc:4.2.4.RELEASE'
    compile 'org.springframework:spring-test:4.2.4.RELEASE'
    compile 'mysql:mysql-connector-java:5.1.38'
    compile 'org.mybatis.generator:mybatis-generator-core:1.3.2'
    compile 'org.mybatis:mybatis-spring:1.2.3'
    compile 'org.mybatis:mybatis:3.3.0'
    compile group: 'org.apache.logging.log4j', name: 'log4j-core', version: '2.6.2'
    compile group: 'org.apache.commons', name: 'commons-dbcp2', version: '2.1.1'
    compile group: 'org.projectlombok', name: 'lombok', version: '1.16.10'

接下来是实体类PO（基金）：

@Data
@Builder
public class Fund{
    private int id;
    private String fundCode;
    private String fundName;
    private String dailyGrowthRate;
    private String monthlyGrowthRate;
}

数据库的schema如下：

CREATE TABLE `fund` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fund_code` varchar(255) DEFAULT NULL,
  `fund_name` varchar(255) DEFAULT NULL,
  `daily_growth_rate` varchar(255) DEFAULT NULL,
  `monthly_growth_rate` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11594 DEFAULT CHARSET=utf8;

然后是UserMapper：

public interface FundMapper {
    int insert(Fund fund);
}

然后是业务类UserService：
其中@Service注解配合ComponentScan会把这个类注入Spring容器
@Autowired 是按照类型进行装配

@Service
public class FundService {

    @Autowired
    private FundMapper mapper;

    public int insert(Fund fund){
        return mapper.insert(fund);
    }

}

接下来是UserMapper.xml文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

<mapper namespace="stock.mapper.FundMapper">

    <resultMap id="BaseResultMap" type="stock.po.Fund">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="fund_code" property="fundCode" jdbcType="VARCHAR"/>
        <result column="fund_name" property="fundName" jdbcType="VARCHAR"/>
        <result column="daily_growth_rate" property="dailyGrowthRate" jdbcType="VARCHAR"/>
        <result column="monthly_growth_rate" property="monthlyGrowthRate" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="BaseColumnList">
        id,fund_code,fund_name,daily_growth_rate,monthly_growth_rate
    </sql>

    <insert id="insert" parameterType="stock.po.Fund">
        INSERT INTO fund(
        <include refid="BaseColumnList"/>
        )
        VALUES (
        #{id,jdbcType=INTEGER},
        #{fundCode,jdbcType=VARCHAR},
        #{fundName,jdbcType=VARCHAR},
        #{dailyGrowthRate,jdbcType=VARCHAR},
        #{monthlyGrowthRate,jdbcType=VARCHAR}
        )
    </insert>

</mapper>

接着是mybatis的配置文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <mappers>
        <mapper resource="mapper/FundMapper.xml"/>
    </mappers>

</configuration>

然后就是spring的配置文件：

<?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:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
                        http://www.springframework.org/schema/context
                        http://www.springframework.org/schema/context/spring-context-4.0.xsd
                        http://www.springframework.org/schema/mvc
                        http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd">

    <!-- 加载配置文件 -->
    <context:property-placeholder location="classpath:jdbc.properties"/>

    <context:component-scan base-package="stock.**"/>

    <!-- 数据源，使用dbcp -->
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driver}" /><!-- 这里的name不能直接使用driver，必须是driverClassName -->
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>

    <!-- sqlSessionFactory -->
    <bean id = "sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- 加载mybatis的配置文件 -->
        <property name="configLocation" value="mybatis-config.xml"></property>
        <!-- 数据源 -->
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!-- mapper配置，MapperFactoryBean可以根据mapper接口来生成代理对象 -->
    <bean id="fundMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
        <property name="mapperInterface" value="stock.mapper.FundMapper"/>
        <property name="sqlSessionFactory" ref="sqlSessionFactory"/>
    </bean>


</beans>

其中jdbc.properties的文件如下：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=

然后逻辑代码如下：

public class NewFundProcessor implements PageProcessor {

    private Logger log = LoggerFactory.getLogger(NewFundProcessor.class);

    private ApplicationContext context;

    public NewFundProcessor() {
        context = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
    }

    private FundService fundService;

    private static final String prefix = "https://e.lufunds.com/jijin/allFund?subType=&haitongGrade=&fundGroupId=&currentPage=";
    private static final String suffix = "&orderType=twelve_month_increase_desc&canFixInvest=&searchWord=#sortTab";
    private Site site = Site.me().setRetryTimes(3).setSleepTime(1000).setTimeOut(3000)
            .setUserAgent("Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/53.0.2785.143 Safari/537.36");


    @Override
    public void process(Page page) {
        System.out.println("first ------------------");
        List<String> list = page.getHtml().xpath("div[@class='listTable']/table[@id='fundTable']/tbody/tr").all();

        for (int i = 0; i < list.size(); i++) {

            Html h = new Html(list.get(i).replace("td", "div"));
            String fundCode = h.xpath("//div[1]/text()").get();
            String fundName = h.xpath("//div[2]/a/text()").get();
            String dailyGrowthRate = h.xpath("//div[4]/span/text()").get();
            String monthGrowthRate = h.xpath("//div[5]/span/text()").get();
            String startAmount = h.xpath("//div[10]/text()").get();
            System.out.println("基金代码:" + h.xpath("//div[1]/text()"));
            System.out.println("基金简介:" + h.xpath("//div[2]/a/text()"));
            System.out.println("最新净值:" + h.xpath("//div[3]/p[1]/text()"));
            System.out.println("时间:" + h.xpath("//p[2]/text()"));
            System.out.println("日增长率:" + h.xpath("//div[4]/span/text()"));
            System.out.println("最近一月增长率:" + h.xpath("//div[5]/span/text()"));
            System.out.println("最近三月增长率:" + h.xpath("//div[6]/span/text()"));
            System.out.println("最近一年增长率:" + h.xpath("//div[7]/span/text()"));
            System.out.println("今年增长率:" + h.xpath("//div[8]/span/text()"));
            System.out.println("成立以来增长率:" + h.xpath("//div[9]/span/text()"));
            System.out.println("起投金额:" + h.xpath("//div[10]/text()"));
            fundService = (FundService) context.getBean("fundService");
            Fund fund = new Fund();
            fund.setFundCode(fundCode);
            fund.setFundName(fundName);
            fund.setDailyGrowthRate(dailyGrowthRate);
            fund.setMonthlyGrowthRate(monthGrowthRate);
            int result = fundService.insert(fund);
            System.out.println(result);

            System.out.println("-------");

        }

        System.out.println("size:" + list.size());


    }

    @Override
    public Site getSite() {
        return site;
    }

    public static void main(String[] args) {

        List<String> urls = new ArrayList<String>();
        for (int i = 1; i <= 250; i++) {
            String url = prefix+i+suffix;
            urls.add(url);
        }

        NewFundProcessor processor = new NewFundProcessor();
            Spider.create(processor)
                    .startUrls(urls)
                    .thread(10)
                    .runAsync();

    }
}

执行之后会往数据库插入3000多条基金的数据：

7.png 8.png