一、本文摘要
- SpringBoot中引入MyBatis;
- MyBatis的分页插件PageHelper的使用;
- SpringBoot中进行单元测试;
- mybatis-generator的使用。
二、准备一个Java项目
- 目录结构
src
├─main
│ ├─java
│ │ └─com
│ │ └─yale
│ │ ├─controller
│ │ ├─dao
│ │ ├─entity
│ │ ├─service
│ │ └─utils
│ └─resources
│ └─mapper
└─test
└─java
└─com
└─yale
├─dao
└─service
- 初始pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<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>com.yale</groupId>
<artifactId>SBMybatis</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.3.RELEASE</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-rest</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
</dependencies>
</project>
三、引入MyBatis
- 在pom.xml文件添加依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
- 在application.properties中设置数据库信息
#驱动名称
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#数据库链接地址
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=true
#登录用户名
spring.datasource.username=root
#登录密码
spring.datasource.password=root
- 在主配置类中配置@MapperScan注解,用来指定dao包目录
package com.yale;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.yale.dao")
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
- 在application.properties中设置*Mapper.xml扫描地址
#mapper扫描地址
mybatis.mapper-locations=classpath:/mapper/*Mapper.xml
四、使用mybatis-generator
- MySQL数据库中创建一张student表,就四个字段:id,name,age,birth
CREATE TABLE `student` (
`id` int(10) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`birth` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- 在pom.xml文件中添加依赖和插件
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.2</version>
</dependency>
<build>
<plugins>
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.2</version>
<configuration>
<!-- mybatis用于生成代码的配置文件 -->
<configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
<verbose>true</verbose>
<overwrite>true</overwrite>
</configuration>
</plugin>
</plugins>
</build>
- 在resources目录下创建generatorConfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!--导入属性配置-->
<!--<properties resource="generator.properties"></properties>-->
<!--指定特定数据库的jdbc驱动jar包的位置-->
<classPathEntry location="D:\cyy\.m2\repository\mysql\mysql-connector-java\5.1.41\mysql-connector-java-5.1.41.jar"/>
<context id="default" targetRuntime="MyBatis3">
<!-- optional,旨在创建class时,对注释进行控制 -->
<commentGenerator>
<property name="suppressDate" value="true"/>
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!--jdbc的数据库连接 -->
<jdbcConnection
driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://127.0.0.1:3306/test"
userId="root"
password="root">
</jdbcConnection>
<!-- 非必需,类型处理器,在数据库类型和java类型之间的转换控制-->
<javaTypeResolver>
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!-- Model模型生成器,用来生成含有主键key的类,记录类 以及查询Example类
targetPackage 指定生成的model生成所在的包名
targetProject 指定在该项目下所在的路径
-->
<javaModelGenerator targetPackage="com.yale.entity"
targetProject="src/main/java">
<!-- 是否允许子包,即targetPackage.schemaName.tableName -->
<property name="enableSubPackages" value="false"/>
<!-- 是否对model添加 构造函数 -->
<property name="constructorBased" value="true"/>
<!-- 是否对类CHAR类型的列的数据进行trim操作 -->
<property name="trimStrings" value="true"/>
<!-- 建立的Model对象是否 不可改变 即生成的Model对象不会有 setter方法,只有构造方法 -->
<property name="immutable" value="false"/>
</javaModelGenerator>
<!--Mapper映射文件生成所在的目录 为每一个数据库的表生成对应的SqlMap文件 -->
<sqlMapGenerator targetPackage="mapper"
targetProject="src/main/resources">
<property name="enableSubPackages" value="false"/>
</sqlMapGenerator>
<!-- 客户端代码,生成易于使用的针对Model对象和XML配置文件 的代码
type="ANNOTATEDMAPPER",生成Java Model 和基于注解的Mapper对象
type="MIXEDMAPPER",生成基于注解的Java Model 和相应的Mapper对象
type="XMLMAPPER",生成SQLMap XML文件和独立的Mapper接口
-->
<javaClientGenerator targetPackage="com.yale.dao"
targetProject="src/main/java" type="XMLMAPPER">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<table tableName="student" domainObjectName="Student"
enableCountByExample="false" enableUpdateByExample="false"
enableDeleteByExample="false" enableSelectByExample="false"
selectByExampleQueryId="false">
</table>
</context>
</generatorConfiguration>
- 使用maven命令:mvn mybatis-generator:generate -e ,自动生成三个文件:Student.java, StudentMapper.java, StudentMapper.xml。
五、使用MyBatis的分页插件PageHelper
- 在pom.xml文件添加依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.6</version>
</dependency>
- 在主配置类添加一个Bean: PageHelper
package com.yale;
import com.github.pagehelper.PageHelper;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import java.util.Properties;
@SpringBootApplication
@MapperScan("com.yale.dao")
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
//配置mybatis的分页插件pageHelper
@Bean
public PageHelper pageHelper() {
PageHelper pageHelper = new PageHelper();
Properties properties = new Properties();
properties.setProperty("offsetAsPageNum", "true");
properties.setProperty("rowBoundsWithCount", "true");
properties.setProperty("reasonable", "true");
properties.setProperty("dialect", "mysql"); //配置mysql数据库的方言
pageHelper.setProperties(properties);
return pageHelper;
}
}
- 在utils目录下创建PageBean.java
package com.yale.utils;
/**
* 分页bean
*/
import java.util.List;
public class PageBean<T> {
// 当前页
private Integer currentPage = 1;
// 每页显示的总条数
private Integer pageSize = 10;
// 总条数
private Integer totalNum;
// 是否有下一页
private Integer isMore;
// 总页数
private Integer totalPage;
// 开始索引
private Integer startIndex;
// 分页结果
private List<T> items;
public PageBean() {
super();
}
public PageBean(Integer currentPage, Integer pageSize, Integer totalNum) {
super();
this.currentPage = currentPage;
this.pageSize = pageSize;
this.totalNum = totalNum;
this.totalPage = (this.totalNum+this.pageSize-1)/this.pageSize;
this.startIndex = (this.currentPage-1)*this.pageSize;
this.isMore = this.currentPage >= this.totalPage?0:1;
}
public Integer getCurrentPage() {
return currentPage;
}
public void setCurrentPage(Integer currentPage) {
this.currentPage = currentPage;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getTotalNum() {
return totalNum;
}
public void setTotalNum(Integer totalNum) {
this.totalNum = totalNum;
}
public Integer getIsMore() {
return isMore;
}
public void setIsMore(Integer isMore) {
this.isMore = isMore;
}
public Integer getTotalPage() {
return totalPage;
}
public void setTotalPage(Integer totalPage) {
this.totalPage = totalPage;
}
public Integer getStartIndex() {
return startIndex;
}
public void setStartIndex(Integer startIndex) {
this.startIndex = startIndex;
}
public List<T> getItems() {
return items;
}
public void setItems(List<T> items) {
this.items = items;
}
}
- 在StudentMapper接口中添加两个方法:findAll()和countAll()。
package com.yale.dao;
import com.yale.entity.Student;
import java.util.List;
public interface StudentMapper {
/********** generator auto create ************/
int deleteByPrimaryKey(Integer id);
int insert(Student record);
int insertSelective(Student record);
Student selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(Student record);
int updateByPrimaryKey(Student record);
/************* self add ***************/
List<Student> findAll();
int countAll();
}
- 在StudentMapper.xml也要添加相应sql
<?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="com.yale.dao.StudentMapper" >
<resultMap id="BaseResultMap" type="com.yale.entity.Student" >
<constructor >
<idArg column="id" jdbcType="INTEGER" javaType="java.lang.Integer" />
<arg column="name" jdbcType="VARCHAR" javaType="java.lang.String" />
<arg column="age" jdbcType="INTEGER" javaType="java.lang.Integer" />
<arg column="birth" jdbcType="DATE" javaType="java.util.Date" />
</constructor>
</resultMap>
<sql id="Base_Column_List" >
id, name, age, birth
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from student
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from student
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.yale.entity.Student" >
insert into student (id, name, age,
birth)
values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER},
#{birth,jdbcType=DATE})
</insert>
<insert id="insertSelective" parameterType="com.yale.entity.Student" >
insert into student
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="name != null" >
name,
</if>
<if test="age != null" >
age,
</if>
<if test="birth != null" >
birth,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=INTEGER},
</if>
<if test="name != null" >
#{name,jdbcType=VARCHAR},
</if>
<if test="age != null" >
#{age,jdbcType=INTEGER},
</if>
<if test="birth != null" >
#{birth,jdbcType=DATE},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.yale.entity.Student" >
update student
<set >
<if test="name != null" >
name = #{name,jdbcType=VARCHAR},
</if>
<if test="age != null" >
age = #{age,jdbcType=INTEGER},
</if>
<if test="birth != null" >
birth = #{birth,jdbcType=DATE},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.yale.entity.Student" >
update student
set name = #{name,jdbcType=VARCHAR},
age = #{age,jdbcType=INTEGER},
birth = #{birth,jdbcType=DATE}
where id = #{id,jdbcType=INTEGER}
</update>
<!-- self add -->
<select id="findAll" resultMap="BaseResultMap">
select <include refid="Base_Column_List" /> from student
</select>
<select id="countAll" resultType="int">
select count(1) from student
</select>
</mapper>
- 在Service中进行分页查询: findStudentByPage
package com.yale.service;
import com.github.pagehelper.PageHelper;
import com.yale.dao.StudentMapper;
import com.yale.entity.Student;
import com.yale.utils.PageBean;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service
public class StudentService {
@Resource
private StudentMapper studentMapper;
public Student getStudentById(Integer id){
return studentMapper.selectByPrimaryKey(id);
}
public List<Student> findStudentByPage(int currentPage, int pageSize){
//设置分页信息,分别是当前页数和每页显示的总记录数【记住:必须在mapper接口中的方法执行之前设置该分页信息】
PageHelper.startPage(currentPage, pageSize);
List<Student> studentList = studentMapper.findAll();
int totalSize = studentMapper.countAll();
PageBean<Student> pageData = new PageBean<Student>(currentPage, pageSize, totalSize);
pageData.setItems(studentList);
return pageData.getItems();
}
}
六、SpringBoot中的单元测试
- 在\src\test\java\com\yale\dao目录下,新建一个StudentMapperTest类
package com.yale.dao;
import com.yale.entity.Student;
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 java.util.Calendar;
import java.util.Date;
@RunWith(SpringRunner.class)
@SpringBootTest
public class StudentMapperTest {
@Autowired
private StudentMapper studentMapper;
@Test
public void testInsert(){
System.out.println("学生插入测试开始...");
Student originStu = new Student(10,"test",20,new Date());
Student tempStu = null;
Calendar calendar = Calendar.getInstance();
for(int i = 0; i < 10; i++){
tempStu = new Student();
tempStu.setId(originStu.getId() + i);
tempStu.setName(originStu.getName() + i);
tempStu.setAge(originStu.getAge() + i);
calendar.setTime(originStu.getBirth());
calendar.add(Calendar.DAY_OF_MONTH, i);
tempStu.setBirth(calendar.getTime());
studentMapper.insert(tempStu);
}
System.out.println("学生插入测试结束...");
}
}
-
运行测试方法,可以看到控制台输出"学生插入测试开始…“和"学生插入测试结束…”,然后数据库中也可以看到多了10条数据。
-
在\src\test\java\com\yale\service目录下,新建一个StudentServiceTest类
package com.yale.service;
import com.yale.entity.Student;
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 java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
public class StudentServiceTest {
@Autowired
private StudentService studentService;
@Test
public void testPage(){
System.out.println("分页测试开始...");
List<Student> students = studentService.findStudentByPage(1, 5);
for(Student student : students){
System.out.println("id:"+student.getId()+",name:"+student.getName()
+",age:"+student.getAge()+",birth:"+student.getBirth());
}
System.out.println("分页测试结束...");
}
}
- 运行方法后,控制台输出:
分页测试开始...
id:1,name:aaa,age:11,birth:Thu Mar 07 00:00:00 CST 2019
id:10,name:test0,age:20,birth:Fri Mar 08 00:00:00 CST 2019
id:11,name:test1,age:21,birth:Sat Mar 09 00:00:00 CST 2019
id:12,name:test2,age:22,birth:Sun Mar 10 00:00:00 CST 2019
id:13,name:test3,age:23,birth:Mon Mar 11 00:00:00 CST 2019
分页测试结束...