实训任务步骤
-
实体类:Product.java
-
数据库脚本工作(schema.sql、data.sql)
-
创建Mapper接口(包含相关的关联关系Mapper接口)
-
完成*Mapper.xml的sql封装(包含相关的关联关系Mapper映射器)
-
完成服务层接口定义(
*Servic
)和接口实现(*ServiceImpl
) -
完成Controller实现
-
本实训涉及关联关系,需要特别注意对应绑定和传参
-
完成测试验证
实施步骤及过程
实体类
-
产品实体
package demo.mybatis.entity;
import lombok.Data;
import java.util.List;
/*产品类*/
@Data
public class Product {
private int id;
private String name;
private Brand brand;
private List<Category> categories;
}
-
分类实体
package demo.mybatis.entity;
import lombok.Data;
/*分类业务类*/
@Data
public class Category {
private int id;
private String name;
}
数据库准备
schema.sql数据表结构准备
drop table if exists product;
CREATE TABLE product (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL comment '产品名称',
brand_id INT NOT NULL comment '关联品牌'
);
-- 产品——分类关联表(多对多关系)
drop table if exists product_category;
CREATE TABLE product_category (
product_id INT NOT NULL,
category_id INT NOT NULL,
PRIMARY KEY (product_id, category_id)
);
data.sql种子数据准备
insert into brand(name) values('华为');
insert into brand(name) values('苹果');
insert into brand(name) values('荣耀');
insert into brand(name) values('小米');
insert into Category(name) values('手机');
insert into Category(name) values('笔记本电脑');
insert into Category(name) values('智能穿戴设备');
insert into Category(name) values('家电');
insert into Product(name,brand_id) values('HUAWEI Mate 60 Pro',1);
insert into Product(name,brand_id) values('HUAWEI Mate 60 Pro+',1);
insert into Product(name,brand_id) values('HUAWEI Mate X5',1);
insert into Product_Category(product_id,category_id) values (1,1);
insert into Product_Category(product_id,category_id) values (2,1);
insert into Product_Category(product_id,category_id) values (3,1);
DAO层Mapper接口
通过MyBatis的@Mapper注解定义接口
-
分类Mapper接口
package demo.mybatis.mapper;
import demo.mybatis.entity.Category;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface CategoryMapper {
Category getCategoryById(Integer id);
List<Category> getAllCategory();
int addCategory(Category entity);
int updateCategory(Category entity);
int deleteCategory(Integer id);
Category findCategoriesByProductId(Integer productId);
}
-
关联关系ProductCategory类Mapper接口
-
通过基于哈希表的方式完成关联表数据的K:V对应,完成数据传参及赋值
package demo.mybatis.mapper;
import org.apache.ibatis.annotations.Mapper;
import java.util.Map;
@Mapper
public interface ProductCategoryMapper {
int addProductCategory(Map<String,Object> map);
int deleteProductCategory(Map<String,Object> map);
}
-
产品Mapper接口
package demo.mybatis.mapper;
import demo.mybatis.entity.Product;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface ProductMapper {
Product getProductByID(Integer id);
List<Product> getAllProducts();
int addProduct(Product entity);
int updateProduct(Product entity);
int deleteProduct(Integer id);
}
Mapper映射器
-
通过namespace,完成与DAO接口映射
-
通过id,完成与DAO接口方法映射,并将SQL语句与方法进行绑定
-
通过#实现防SQL注入
-
通过resultMap完成结果映射
-
通过resultType指定查询结果的数据类型,MyBatis会自动将结果映射为JavaBean中的属性
-
通过association标签完成一对一关系
-
通过collection标签完成一对多关系
关联表ProductCategory映射器
<?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="demo.mybatis.mapper.ProductCategoryMapper">
<insert id="addProductCategory" parameterType="java.util.Map">
insert into product_category(product_id,category_id) values (#{productId},#{categoryId})
</insert>
<delete id="deleteProductCategory" parameterType="java.util.Map">
delete from product_category where product_id=#{productId}
</delete>
</mapper>
Product映射器代码
<?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="demo.mybatis.mapper.ProductMapper">
<!--品牌表的resultMap-->
<resultMap id="brandResultMap" type="demo.mybatis.entity.Brand">
<id property="id" column="brand_id"/>
<result property="name" column="brand_name"/>
</resultMap>
<!--分类表的resultMap-->
<resultMap id="categoryResultMap" type="demo.mybatis.entity.Category">
<id property="id" column="category_id"/>
<result property="name" column="category_name"/>
</resultMap>
<!--产品表的resultMap-->
<resultMap id="productResultMap" type="demo.mybatis.entity.Product">
<id property="id" column="id"/>
<result property="name" column="name"/>
<!--插入品牌的关联查询-->
<association property="brand" javaType="demo.mybatis.entity.Brand" resultMap="brandResultMap" />
<!--插入分类信息的多对多关联查询-->
<collection property="categories" column="id" ofType="demo.mybatis.entity.Category" select="findCategoriesByProductId" />
<!--<collection property="categories" ofType="demo.mybatis.entity.Category" resultMap="categoryResultMap" />-->
</resultMap>
<!--获取所有学生的SQL查询-->
<select id="getAllProducts" resultMap="productResultMap">
SELECT p.id, p.name, b.id AS brand_id, b.name AS brand_name, c.id as category_id, c.name as category_name
FROM product p INNER JOIN brand b ON p.brand_id = b.id
left join product_category pc on pc.product_id=p.id
left join category c on pc.category_id=c.id
</select>
<select id="getProductByID" resultMap="productResultMap">
SELECT p.id, p.name, b.id AS brand_id, b.name AS brand_name, c.id as category_id, c.name as category_name
FROM product p INNER JOIN brand b ON p.brand_id = b.id
left join product_category pc on pc.product_id=p.id
left join category c on pc.category_id=c.id
where p.id=#{id}
</select>
<select id="findCategoriesByProductId" resultType="demo.mybatis.entity.Category">
select c.* from product_category pc
join category c on pc.category_id=c.id
where pc.product_id=#{id}
</select>
<insert id="addProduct">
/*获取自动生成的主键值*/
<selectKey resultType="java.lang.Integer" keyProperty="id" order="AFTER">
select LAST_INSERT_ID()
</selectKey>
insert into product(name,brand_id) values (#{name},#{brand.id});
</insert>
<update id="updateProduct">
update product set name=#{name},brand_id=#{brand.id} where id=#{id}
</update>
<delete id="deleteProduct">
delete from product where id=#{id}
</delete>
</mapper>
服务层
-
定义服务接口(
*Service.java
) -
完成服务接口实现类(
*ServiceImpl.java
),构造函数自动注入DAO层
服务接口定义
-
Product服务接口定义
package demo.mybatis.service;
import demo.mybatis.entity.Product;
import java.util.List;
public interface ProductService {
List<Product> getAllProducts();
Product getProductById(Integer id);
int addProduct(Product entity);
int updateProduct(Product entity);
int deleteProduct(Integer id);
}
Product服务接口实现
package demo.mybatis.service.impl;
import demo.mybatis.entity.Category;
import demo.mybatis.entity.Product;
import demo.mybatis.mapper.ProductCategoryMapper;
import demo.mybatis.mapper.ProductMapper;
import demo.mybatis.service.ProductService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Service
public class ProductServiceImpl implements ProductService {
ProductMapper mapper;
ProductCategoryMapper pcMapper;
@Autowired
public ProductServiceImpl(ProductMapper mapper,ProductCategoryMapper pcMapper){
this.mapper=mapper;
this.pcMapper=pcMapper;
}
@Override
public List<Product> getAllProducts() {
return mapper.getAllProducts();
}
@Override
public Product getProductById(Integer id) {
return mapper.getProductByID(id);
}
@Override
public int addProduct(Product entity) {
int result=mapper.addProduct(entity);
for(Category category:entity.getCategories()){
Map<String,Object> map=new HashMap<>();
map.put("productId",entity.getId());
map.put("categoryId",category.getId());
pcMapper.addProductCategory(map);
}
return result;
}
@Override
public int updateProduct(Product entity) {
Map<String,Object> map=new HashMap<>();
map.put("productId",entity.getId());
pcMapper.deleteProductCategory(map);
for(Category category:entity.getCategories()){
map=new HashMap<>();
map.put("productId",entity.getId());
map.put("categoryId",category.getId());
pcMapper.addProductCategory(map);
}
return mapper.updateProduct(entity);
}
@Override
public int deleteProduct(Integer id) {
Product entity=mapper.getProductByID(id);
Map<String,Object> map=new HashMap<>();
map.put("productId",entity.getId());
pcMapper.deleteProductCategory(map);
int result=mapper.deleteProduct(id);
return result;
}
}
控制层实现
-
通过@RequestMapping完成URL路径映射
-
构造函数自动注入Service层
-
HTTP方法映射(@GetMapping、@PostMapping、@PutMapping、@DeleteMapping等)
-
@GetMapping:处理GET请求,用于向Server获取数据
-
@PostMapping:处理POST请求,用于向Server提交数据,一般用于新增数据
-
@PutMapping:和POST请求相同,用于向Server提交数据,一般用于更新数据
-
@DeleteMapping:处理DELETE请求,用于向Server删除数据
-
@PathVariable:URL绑定占位符,完成URL和入口参数的绑定
-
@RequestBody:接收调用方传递给后端的JSON字符串中的数据,一般使用POST方式完成数据提交。
-
@RequestBody和@RequestParam可以同时使用,区别是@RequestBody只能有一个,@RequestParam可以有多个
package demo.mybatis.controller;
import demo.mybatis.entity.Product;
import demo.mybatis.service.ProductService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
@RequestMapping("/product")
public class ProductController {
ProductService service;
@Autowired
public ProductController(ProductService service){
this.service=service;
}
@GetMapping("/list")
public List<Product> getAll(){
return service.getAllProducts();
}
@GetMapping("/{id}")
public Product getById(@PathVariable("id")Integer id){
return service.getProductById(id);
}
@PostMapping("/insert")
public int insert(@RequestBody Product entity){
return service.addProduct(entity);
}
@PutMapping("/edit/{id}")
public int edit(@PathVariable("id")Integer id,@RequestBody Product entity){
entity.setId(id);
return service.updateProduct(entity);
}
@DeleteMapping("/delete/{id}")
public int delete(@PathVariable("id")Integer id){
return service.deleteProduct(id);
}
}