Spring Boot and RESTful API(8)MySQL Database

Spring Boot and RESTful API(8)MySQL Database

Official Web Site - MyIbatis and Spring Boot
https://github.com/mybatis/spring-boot-starter/wiki/Quick-Start

pom.xml to support the dependency.
<properties>
    <java.version>1.8</java.version>
    <orika.version>1.5.1</orika.version>
    <myibatis.version>3.4.3</myibatis.version>
    <myibatisstarter.version>1.3.0</myibatisstarter.version>
</properties>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>${myibatisstarter.version}</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>${myibatis.version}</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

Configuration in Properties file application.yaml
spring:
    datasource:
        driverClassName: com.mysql.jdbc.Driver  
        url: jdbc:mysql://xxxxxx:7778/jobs2careers?useUnicode=true&characterEncoding=utf-8  
        username: writer  
        password: xxxxxx
mybatis:
        type-aliases-package: com.sillycat.jobsmonitorapi.domain

POJO class to host the data structure CampaignJobCount.java
package com.sillycat.jobsmonitorapi.domain;

import java.io.Serializable;
import java.util.Date;
import org.apache.commons.lang3.builder.ToStringBuilder;

public class CampaignJobCount implements Serializable {
    private static final long serialVersionUID = -5665717574357320248L;
    private Integer campaignID;
    private Integer liveOld;
    private Integer liveNew;
    private Integer pausedOld;
    private Integer pausedNew;
    private Integer dailyCappedOld;
    private Integer dailyCappedNew;
    private Date countTime;
    …snip...
}

Mapper interface with Annotation to Query Data CampaignJobCountMapper.java
package com.sillycat.jobsmonitorapi.repository;

import java.util.Date;
import java.util.List;
import com.sillycat.jobsmonitorapi.domain.CampaignJobCount;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface CampaignJobCountMapper {

    @Select("SELECT * FROM campaign_job_count_solr_new WHERE campaign_id = #{campaignID}")
    @Results({ @Result(property = "campaignID", column = "campaign_id"),
            @Result(property = "liveOld", column = "count_live_old"),
            @Result(property = "liveNew", column = "count_live_new"),
            @Result(property = "pausedOld", column = "count_paused_old"),
            @Result(property = "pausedNew", column = "count_paused_new"),
            @Result(property = "dailyCappedOld", column = "count_daily_capped_old"),
            @Result(property = "dailyCappedNew", column = "count_daily_capped_new"),
            @Result(property = "countTime", column = "count_time", javaType = Date.class) })
    public List<CampaignJobCount> loadCampaignJobCountByCampaignID(@Param("campaignID") Integer campaignID);

    @Select("SELECT * FROM campaign_job_count_solr_new WHERE campaign_id = #{campaignID} AND count_time > #{queryBackTime}")
    @Results({ @Result(property = "campaignID", column = "campaign_id"),
            @Result(property = "liveOld", column = "count_live_old"),
            @Result(property = "liveNew", column = "count_live_new"),
            @Result(property = "pausedOld", column = "count_paused_old"),
            @Result(property = "pausedNew", column = "count_paused_new"),
            @Result(property = "dailyCappedOld", column = "count_daily_capped_old"),
            @Result(property = "dailyCappedNew", column = "count_daily_capped_new"),
            @Result(property = "countTime", column = "count_time", javaType = Date.class) })
    public List<CampaignJobCount> loadCampaignJobCountByCampaignIDAndDate(@Param("campaignID") Integer campaignID,
            @Param("queryBackTime") Date queryBackTime);
}

Unit Test to cover that MySQL Interface Mapper, CampaignJobCountMapperTest.java
package com.sillycat.jobsmonitorapi.repository;

import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.http.util.Asserts;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.sillycat.jobsmonitorapi.domain.CampaignJobCount;

@RunWith(SpringRunner.class)
@SpringBootTest
public class CampaignJobCountMapperTest {

    @Autowired
    private CampaignJobCountMapper campaignJobCountMapper;

    @Test
    public void testQuery() throws Exception {
        List<CampaignJobCount> jobcounts = campaignJobCountMapper.loadCampaignJobCountByCampaignID(0);
        Asserts.notNull(jobcounts, "result1 is not null");
        System.out.println(jobcounts.size());
        System.out.println(jobcounts.get(0));

        List<CampaignJobCount> jobcountsToday = campaignJobCountMapper.loadCampaignJobCountByCampaignIDAndDate(0,
                yesterday());
        Asserts.notNull(jobcountsToday, "result2 is not null");
        System.out.println(jobcountsToday.size());
        System.out.println(jobcountsToday.get(0));
    }
   
    private Date yesterday() {
        final Calendar cal = Calendar.getInstance();
        cal.add(Calendar.DATE, -1);
        return cal.getTime();
    }

}

Run the Unit Test
>mvn -Dtest=CampaignJobCountMapperTest test

References:
http://blog.netgloo.com/2014/10/27/using-mysql-in-spring-boot-via-spring-data-jpa-and-hibernate/
http://blog.netgloo.com/2014/08/17/use-mysql-database-in-a-spring-boot-web-application-through-hibernate/

http://www.ityouknow.com/springboot/2016/11/06/springboot(%E5%85%AD)-%E5%A6%82%E4%BD%95%E4%BC%98%E9%9B%85%E7%9A%84%E4%BD%BF%E7%94%A8mybatis.html
https://aisensiy.github.io/2017/05/04/spring-mvc-and-mybatis/
http://blog.didispace.com/springbootmybatis/
https://github.com/mybatis/spring-boot-starter/wiki/Quick-Start
http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/