Spring Boot and RESTful API(八)MySQL Database
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/
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/