Spring Boot Mybatis 增删改查示例
Mybatis 简介
MyBatis 是一款优秀的持久层框架,它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息,将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。
快速开始
在 pom.xml 文件添加 Mybatis 依赖
使用 MySQL 数据库作为存储。
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
解决 Idea 不识别 Mybatis 的 Mapper.xml 文件问题
<build>
<resources>
<!-- 解决不识别mybatis的Mapper.xml文件问题 -->
<resource>
<directory>src/main/java</directory>
<filtering>false</filtering>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
</resource>
</resources>
</build>
Mybatis 相关配置
application.properties
mybatis.type-aliases-package=com.renguangli.mybatis.bean
mybatis.mapper-locations=classpath:/com/renguangli/mybatis/mapper/xml/*Mapper.xml
mybatis.configuration.map-underscore-to-camel-case=true
spring.datasource.url=jdbc:mysql://localhost:3306/springboot
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
编写 Employee 实体类
public class Employee implements Serializable {
private Integer empNo;
private String firstName;
private String lastName;
private String gender;
@JsonFormat(pattern = "yyyy-MM-dd")
@DateTimeFormat(pattern = "yyyy-MM-dd")
private Date birthDate;
@JsonFormat(pattern = "yyyy-MM-dd")
@DateTimeFormat(pattern = "yyyy-MM-dd")
private Date hireDate;
......
}
编写 EmployeeMapper 接口
public interface EmployeeMapper extends BaseMapper<Employee, Integer>{
List<Employee> listEmployees(@Param("pojo") Employee employee,
@Param("page") Integer page,
@Param("size") Integer size);
Long countEmployee(@Param("pojo") Employee employee);
}
BaseMapper
public interface BaseMapper<T, ID> {
T get(@Param("id") ID id);
List<T> list();
int save(@Param("pojo") T pojo);
int batchSave(@Param("pojos")List<T> pojos);
int delete(@Param("id") ID id);
int batchDelete(@Param("ids") ID[] ids);
int update(@Param("pojo") T pojo);
}
编写 EmployeeMapper.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="com.renguangli.mybatis.mapper.EmployeeMapper">
<select id="listEmployees" resultType="Employee">
select * from employee where 1=1
<if test="pojo.firstName != null and pojo.firstName != ''">
and first_name = #{pojo.firstName}
</if>
<if test="pojo.lastName != null and pojo.lastName != ''">
and last_name = #{pojo.lastName}
</if>
limit #{page},#{size}
</select>
<select id="get" resultType="Employee">
select * from employee where emp_no = #{id}
</select>
<select id="countEmployee" resultType="long">
select count(*) from employee where 1=1
<if test="pojo.firstName != null and pojo.firstName != ''">
and first_name = #{pojo.firstName}
</if>
<if test="pojo.lastName != null and pojo.lastName != ''">
and last_name = #{pojo.lastName}
</if>
</select>
<insert id="save">
insert into employee(emp_no,birth_date,first_name,last_name,gender,hire_date)
values(#{pojo.empNo},#{pojo.birthDate},#{pojo.firstName},#{pojo.lastName},#{pojo.gender},#{pojo.hireDate})
</insert>
<delete id="delete">
delete from employee where emp_no = #{id}
</delete>
<update id="update">
update employee
set last_name = #{pojo.lastName}
where emp_no = #{pojo.empNo}
</update>
</mapper>
编写 Controller
为减少代码量就不写 Service 了
@RestController
public class EmployeeController {
private final EmployeeMapper employeeMapper;
@Autowired
public EmployeeController(EmployeeMapper employeeMapper) {
this.employeeMapper = employeeMapper;
}
@GetMapping("/employees")
public Result listEmployees(Employee employee,
@RequestParam(value = "page", defaultValue = "1") Integer page,
@RequestParam(value = "size", defaultValue = "10") Integer size) {
int offset = (page - 1) * size;
List<Employee> employees = employeeMapper.listEmployees(employee, offset, size);
long count = employeeMapper.countEmployee(employee);
return new Result(employees, count, page, size);
}
@GetMapping("/employee/{empNo}")
public Employee getEmployee(@PathVariable("empNo") Integer empNo) {
Employee employee = employeeMapper.get(empNo);
System.out.println(employee);
return employee;
}
@PostMapping("/employee")
public int saveEmployee(Employee employee) {
employee.setBirthDate(new Date());
employee.setHireDate(new Date());
return employeeMapper.save(employee);
}
@DeleteMapping("/employee/{empNo}")
public int deleteEmployee(@PathVariable("empNo") Integer empNo) {
return employeeMapper.delete(empNo);
}
@PutMapping("/employee")
public int updateEmployee(Employee employee) {
return employeeMapper.update(employee);
}
}
参考资料
本文所有代码放在 Github 上