今天了解了通过SpringBoot整合MyBatis的框架 自己写一个博客复习一波
任务:创建两张表:
员工表(员工姓名, 员工的年龄, 员工性别, 部门id, id , 创建时间, 更新时间)
部门表(id, 创建时间, 更新时间, 部门名称)
执行每张表的CRUD
自定义关联表查询 自定义业务场景
思路 : 首先通过mysql数据库创建两张表
CREATE DATABASE ead;
CREATE TABLE employees (
id INT(10) PRIMARY KEY AUTO_INCREMENT,
empName VARCHAR(50),
empAge INT(10),
empSex VARCHAR(10),
depId INT(10),
createBy DATE,
updateBy DATE
);
CREATE TABLE departments (
id INT(10) PRIMARY KEY AUTO_INCREMENT,
depName VARCHAR(50),
createBy DATE,
updateBy DATE
);
INSERT INTO employees(id,empName,empAge,empSex,depId,createBy,updateBy) VALUES(1001,'李白',18,'男',1,NOW(),NULL);
SELECT * FROM employees;
INSERT INTO departments(id,depName,createBy,updateBy) VALUES(1,'刺客',NOW(),NULL);
SELECT * FROM departments;
创建一个数据库名为:ead 分别创建两张表 雇员和部门 并为两张表各插入一条语句(执行该代码后,查询出的结果表如下)
雇员表
id | empName | empAge | createBy | empSex | depId | updateBy |
---|---|---|---|---|---|---|
1001 | 李白 | 18 | 2020-08-18 | 男 | 1 | (null) |
部门表
id | depName | createBy | updateBy |
---|---|---|---|
1 | 刺客 | 2020-08-18 | (NULL) |
表创建完成之后,打开idea创建一个SpringBoot开始整合MyBatis
进入idea选择Spring Initializr 点击下一步(Next)
输入自己的group和Artifact Java版本选择8或者以上 点击下一步
选择这两个依赖点击下一步,其他依赖可以直接进入后在pom文件中导入
直接点击finish 进入idea
进入后页面如下,我们需要整合mybatis框架,需要Mybatis和Mysql的jar包
我们在pom.xml文件的dependencys中添加以下两个依赖
<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>
<scope>runtime</scope>
</dependency>
依赖添加完成 我们在项目的src/main/java/com/pzy/ead下: 我们通过数据库表分别创建出每张表的 [实体类(entity),数据访问层(dao|mapper),业务逻辑层(Service),控制层(Controller)]
创建完成后表结构如下
在实体类表中根据数据库表字段创建字段信息
employees表实体类
package com.pzy.ead.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employees {
private Integer id;
private String empName;
private Integer empAge;
private String empSex;
private Integer depId;
private Date createBy;
private Date updateBy;
}
Departments表实体类
package com.pzy.ead.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Departments {
private Integer id;
private String depName;
private Date createBy;
private Date updateBy;
}
创建实体类使用了lombok依赖
通过注解实现set,get方法以及无参构造和有参构造,减少了代码的构成
注:使用lombok时,需要先下载它的插件,否则可能无法使用
实体类创建完成后,打开resources文件 将application.propeties删除,我们使用application.yml文件进行springboot的配置 (配置mybatis和数据源)
分别在application.yml和application-dev.yml中添加如下代码
application.yml
spring:
profiles:
active: dev
application-dev.yml
server:
port: 9000
spring:
datasource:
username: pzy
password: 123
url: jdbc:mysql://localhost:3306/ead?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
mapper-locations: classpath:mapper/*Mapper.xml
type-aliases-package: com.pzy.ead.entity
创建完成两个yml文件后 我们根据application-dev的mybatis配置的mapper路径中创建两张数据库表的Mapper.xml文件
创建后在他们的命名空间中添加他们各自映射的Mapper的java文件路径,并分别写入一个查询语句测试一下
EmployeesMapper.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd">
<mapper namespace="com.pzy.ead.mapper.EmployeesMapper">
<select id="selectAllEmployees" resultType="Employees">
SELECT id,empName,empAge,empSex,depId,createBy,updateBy FROM employees;
</select>
</mapper>
DepartmentMapper.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd">
<mapper namespace="com.pzy.ead.mapper.DepartmentsMapper">
<select id="selectAllDepartments" resultType="Departments">
SELECT id,depName,createBy,updateBy FROM departments;
</select>
</mapper>
创建文件后我们在他们各自的Mapper的java文件中创建出这两个方法 并通过注解层层映射出来
DepartmentsMapper内代码
package com.pzy.ead.mapper;
import com.pzy.ead.entity.Departments;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
@Mapper
public interface DepartmentsMapper {
List<Departments> selectAllDepartments();
}
EmployeesMapper内代码
package com.pzy.ead.mapper;
import com.pzy.ead.entity.Employees;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
@Mapper
public interface EmployeesMapper {
List<Employees> selectAllEmployees();
}
在Mapper层中分别给两个接口方法名称与Mapper.xml文件处的一致
并添加注解使Service层能够自动创建此类
Service层
DepartmentsService
package com.pzy.ead.service;
import com.pzy.ead.entity.Departments;
import com.pzy.ead.mapper.DepartmentsMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class DepartmentsService {
@Autowired
DepartmentsMapper departmentsMapper;
public List<Departments> findAllDepartments(){
return departmentsMapper.selectAllDepartments();
}
}
EmployeesService
package com.pzy.ead.service;
import com.pzy.ead.entity.Employees;
import com.pzy.ead.mapper.EmployeesMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class EmployeesService {
@Autowired
EmployeesMapper employeesMapper;
public List<Employees> findAllEmployees(){
return employeesMapper.selectAllEmployees();
}
}
在Service中使用Autowired自动导入他们各自的Mapper对象,并在Service中添加注解Service以便于他们的控制器层的调用
控制层
DepartmentsController
package com.pzy.ead.controller;
import com.pzy.ead.entity.Departments;
import com.pzy.ead.service.DepartmentsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class DepartmentsController {
@Autowired
private DepartmentsService departmentsService;
@RequestMapping("/findDep")
public List<Departments> findAll(){
return departmentsService.findAllDepartments();
}
}
EmployeesController
package com.pzy.ead.controller;
import com.pzy.ead.entity.Employees;
import com.pzy.ead.service.EmployeesService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class EmployeesController {
@Autowired
private EmployeesService employeesService;
@RequestMapping("/findEmp")
public List<Employees> findAll(){
return employeesService.findAllEmployees();
}
}
最后在我们的项目启动处添加一个Mapper的扫描
@MapperScan(“com.pzy.ead.mapper”) 括号内是我们的Mapper包的路径 表示直接扫描我们的Mapper包内的所有接口 并与Mapper.xml文件进行绑定
package com.pzy.ead;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.pzy.ead.mapper")
public class EadApplication {
public static void main(String[] args) {
SpringApplication.run(EadApplication.class, args);
}
}
运行项目 测试是否成功:
项目运行,通过我们设置的9000端口号与我们在控制器中设置的RequestMapping内的路径查询
测试成功 返回了我们需要的List集合格式
按照格式创建出两张表的CRUD
Mapper
DepartmentsMapper
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd">
<mapper namespace="com.pzy.ead.mapper.DepartmentsMapper">
<select id="selectAllDepartments" resultType="Departments">
SELECT id,depName,createBy,updateBy FROM departments;
</select>
<select id="selectDepartments" resultType="Departments">
SELECT id,depName,createBy,updateBy FROM departments WHERE id = #{id};
</select>
<insert id="insertDepartments" parameterType="Departments">
INSERT INTO departments(depName,createBy) VALUES(#{depName},now());
</insert>
<delete id="deleteDepartments">
DELETE FROM departments WHERE id = #{id}
</delete>
<update id="updateDepartments" parameterType="Departments">
UPDATE departments
<set>
<if test="depName!=null and depName!=''">
depName = #{depName},
</if>
updateBy = now()
</set>
WHERE id = #{id}
</update>
</mapper>
EmployeesMapper
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd">
<mapper namespace="com.pzy.ead.mapper.EmployeesMapper">
<select id="selectAllEmployees" resultType="Employees">
SELECT id,empName,empAge,empSex,depId,createBy,updateBy FROM employees;
</select>
<select id="selectEmployees" resultType="Employees">
SELECT id,empName,empAge,empSex,depId,createBy,updateBy FROM employees WHERE id = #{id};
</select>
<insert id="insertEmployees" parameterType="Employees">
INSERT INTO employees(empName,empAge,empSex,depId,createBy) VALUES(#{empName},#{empAge},#{empSex},#{depId},now());
</insert>
<delete id="deleteEmployees">
DELETE FROM employees WHERE id = #{id}
</delete>
<update id="updateEmployees" parameterType="Employees">
UPDATE employees
<set>
<if test="empName!=null and empName!=''">
empName = #{empName},
</if>
<if test="empAge!=null">
empAge = #{empAge},
</if>
<if test="empSex!=null and empSex!=''">
empSex = #{empSex},
</if>
<if test="depId!=null">
depId = #{depId},
</if>
updateBy = now()
</set>
where id = #{id}
</update>
</mapper>
Mapper Java代码
DepartmentsMapper
package com.pzy.ead.mapper;
import com.pzy.ead.entity.Departments;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
@Mapper
public interface DepartmentsMapper {
List<Departments> selectAllDepartments();
Departments selectDepartments(@Param("id") Integer id);
Integer insertDepartments(Departments emp);
Integer deleteDepartments(@Param("id") Integer id);
Integer updateDepartments(Departments emp);
}
EmployeesMapper
package com.pzy.ead.mapper;
import com.pzy.ead.entity.Employees;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
@Mapper
public interface EmployeesMapper {
List<Employees> selectAllEmployees();
Employees selectEmployees(@Param("id") Integer id);
Integer insertEmployees(Employees emp);
Integer deleteEmployees(@Param("id") Integer id);
Integer updateEmployees(Employees emp);
}
Service
DepartmentsService
package com.pzy.ead.service;
import com.pzy.ead.entity.Departments;
import com.pzy.ead.mapper.DepartmentsMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class DepartmentsService {
@Autowired
DepartmentsMapper departmentsMapper;
public List<Departments> findAllDepartments(){
return departmentsMapper.selectAllDepartments();
}
public Departments findDepartments(Integer id){
return departmentsMapper.selectDepartments(id);
}
public Integer addDepartments(Departments emp){
return departmentsMapper.insertDepartments(emp);
}
public Integer removeDepartments(Integer id){
return departmentsMapper.deleteDepartments(id);
}
public Integer modifyDepartments(Departments emp){
return departmentsMapper.updateDepartments(emp);
}
}
EmployeesService
package com.pzy.ead.service;
import com.pzy.ead.entity.Employees;
import com.pzy.ead.mapper.EmployeesMapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class EmployeesService {
@Autowired
EmployeesMapper employeesMapper;
public List<Employees> findAllEmployees(){
return employeesMapper.selectAllEmployees();
}
public Employees findEmployees(Integer id){
return employeesMapper.selectEmployees(id);
}
public Integer addEmployees(Employees emp){
return employeesMapper.insertEmployees(emp);
}
public Integer removeEmployees(Integer id){
return employeesMapper.deleteEmployees(id);
}
public Integer modifyEmployees(Employees emp){
return employeesMapper.updateEmployees(emp);
}
}
控制层Controller
DepartmentsController
package com.pzy.ead.controller;
import com.pzy.ead.entity.Departments;
import com.pzy.ead.service.DepartmentsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class DepartmentsController {
@Autowired
private DepartmentsService departmentsService;
@RequestMapping("/findDep")
public List<Departments> findAll(){
return departmentsService.findAllDepartments();
}
@RequestMapping("/findDep/{id}")
public Departments findEmp(@PathVariable Integer id){
return departmentsService.findDepartments(id);
}
@RequestMapping("/addDep")
public Integer createEmp(@RequestParam("depName") String depName){
Departments dep = new Departments();
dep.setDepName(depName);
return departmentsService.addDepartments(dep);
}
@RequestMapping("/modifyDep")
public Integer modifyEmp(@RequestParam(value = "depName",required = false) String depName,@RequestParam("id") Integer id){
Departments dep = new Departments();
dep.setId(id);
if(depName!=null) {
dep.setDepName(depName);
}
return departmentsService.modifyDepartments(dep);
}
@RequestMapping("/deleteDep/{id}")
public Integer removeEmp(@PathVariable Integer id){
return departmentsService.removeDepartments(id);
}
}
EmployeesController
package com.pzy.ead.controller;
import com.pzy.ead.entity.Employees;
import com.pzy.ead.service.EmployeesService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class EmployeesController {
@Autowired
private EmployeesService employeesService;
@RequestMapping("/findEmp")
public List<Employees> findAll(){
return employeesService.findAllEmployees();
}
@RequestMapping("/findEmp/{id}")
public Employees findEmp(@PathVariable Integer id){
return employeesService.findEmployees(id);
}
@RequestMapping("/addEmp")
public Integer createEmp(@RequestParam("depId") Integer depId,
@RequestParam("empAge") Integer empAge,
@RequestParam("empName") String empName,
@RequestParam("empSex") String empSex){
Employees emp = new Employees();
emp.setDepId(depId);
emp.setEmpAge(empAge);
emp.setEmpName(empName);
emp.setEmpSex(empSex);
return employeesService.addEmployees(emp);
}
@RequestMapping("/modifyEmp/{id}")
public Integer modifyEmp1(@RequestParam(value = "depId",required = false) Integer depId,
@RequestParam(value = "empAge",required = false) Integer empAge,
@RequestParam(value = "empName",required = false) String empName,
@RequestParam(value = "empSex",required = false) String empSex,
@PathVariable("id") Integer id){
Employees emp = new Employees();
emp.setId(id);
emp.setDepId(depId);
emp.setEmpAge(empAge);
emp.setEmpName(empName);
emp.setEmpSex(empSex);
return employeesService.modifyEmployees(emp);
}
@RequestMapping("/deleteEmp/{id}")
public Integer removeEmp(@PathVariable Integer id){
return employeesService.removeEmployees(id);
}
}