一、三级联动(以省份-城市-考场为例)
1)表设计
省份和城市存在同一张表,parentId为0代表省,parentId不为零代表对应省份下的城市。
package com.example.jiakao.pojo.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import javax.persistence.*;
@Entity
@Data
@TableName("plate_region")
@Table(name="plate_region")
public class PlateRegionPo {
@Id
@TableId(type = IdType.AUTO)
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column( columnDefinition = "int4" )
private Integer id;
@Column( columnDefinition = "varchar(64)" )
private String name;
@Column( columnDefinition = "varchar(64)" )
private String plate;
@Column( columnDefinition = "int4 default 0 " )
private Integer parentId;
}
考场表,parentId对应城市id
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import javax.persistence.*;
@Entity
@Data
@TableName("exam_place")
@Table(name="exam_place")
public class ExamPlacePo {
@Id
@TableId(type = IdType.AUTO)
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column( columnDefinition = "bigint" )
private Long id;
@Column( columnDefinition = "varchar(64)" )
private String name;
@Column( columnDefinition = "int4" )
private Integer parentId;
}
2)定义返回类型vo
package com.example.jiakao.pojo.vo.list;
import lombok.Data;
import java.util.List;
@Data
public class ProvinceVo {
private Long id;
private String name;
private String plate;
private List<CityVo> children;
}
package com.example.jiakao.pojo.vo.list;
import lombok.Data;
import java.util.List;
@Data
public class CityVo {
private Long id;
private String name;
private String plate;
private List<ExamPlaceVo> children;
}
package com.example.jiakao.pojo.vo.list;
import lombok.Data;
@Data
public class ExamPlaceVo {
private Long id;
private String name;
}
3)mapper.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.example.jiakao.mapper.ExamPlacePoMapper">
<resultMap id="selectRegionExamPlaces" type="com.example.jiakao.pojo.vo.list.ProvinceVo">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="plate" property="plate"></result>
<collection property="children" ofType="com.example.jiakao.pojo.vo.list.CityVo">
<id column="city_id" property="id"></id>
<result column="city_name" property="name"></result>
<result column="city_plate" property="plate"></result>
<collection property="children" ofType="com.example.jiakao.pojo.vo.list.ExamPlaceVo">
<id column="exam_id" property="id"></id>
<result column="exam_name" property="name"></result>
</collection>
</collection>
</resultMap>
<select id="listRegionExamPlaces" resultMap="selectRegionExamPlaces">
select
p.id,
p.name,
p.plate,
c.id city_id,
c.name city_name,
c.plate city_plate,
e.id exam_id,
e.name exam_name
from plate_region p
join plate_region c on c.parent_id = p.id
join exam_place e on e.parent_id = c.id
where p.parent_id = 0
</select>
</mapper>
4)查询结果
{
"code": 200,
"message": "成功",
"data": [
{
"id": 2,
"name": "湖北",
"plate": "鄂",
"children": [
{
"id": 3,
"name": "武汉",
"plate": "A",
"children": [
{
"id": 1,
"name": "hfgg"
},
{
"id": 5,
"name": "hgi"
}
]
}
]
},
{
"id": 1,
"name": "广东",
"plate": "粤",
"children": [
{
"id": 7,
"name": "深圳",
"plate": "B",
"children": [
{
"id": 6,
"name": "hgiG"
}
]
},
{
"id": 8,
"name": "广州",
"plate": "A",
"children": [
{
"id": 7,
"name": "hgU"
},
{
"id": 8,
"name": "hgUU"
}
]
}
]
}
]
}
5)注意点
join、left join、right join三者的区别简单概括:join取两张表的交集,只保留有交集的条目;left join以join之前的表为主表,无论有没有交集,主表条目都存在;right join以join之后的表为主表。
二、关于带有模糊查询以及分页的联表查询处理(以考场-考试科目为例)
1)vo定义,用于保存数据时传参,与po对象字段对应,只忽略po对象中不需要用户操作的字段。
package com.example.jiakao.pojo.vo.req.save;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
@Data
@ApiModel("保存考场信息的参数")
public class ExamPlaceReqVo {
@ApiModelProperty(value = "考场id")
private Long id;
@ApiModelProperty(value = "考场名称", required = true)
@NotBlank(message = "名称不能为空")
private String name;
@ApiModelProperty(value = "考场所属城市id", required = true)
@NotNull(message = "所属城市不能为空")
private Integer cityId;
@ApiModelProperty(value = "考场所属省份id", required = true)
@NotNull(message = "所属省份不能为空")
private Integer provinceId;
}
package com.example.jiakao.pojo.vo.req.save;
import com.example.jiakao.common.validator.BoolNumber;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import javax.validation.constraints.Min;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
@Data
@ApiModel("保存课程信息")
public class ExamCourseReqVo {
@ApiModelProperty("课程id")
private Long id;
@ApiModelProperty(value = "课程名称", required = true)
@NotBlank(message = "课程名不能为空")
private String name;
@ApiModelProperty(value = "课程类型",required = true)
@NotNull(message = "课程类型不能为空")
@BoolNumber(message = "课程类型值为0或1")
private Short type;
@ApiModelProperty(value = "课程价格",required = true)
@NotNull(message = "课程价格不能为空")
@Min(value = 0, message = "价格不能为负数")
private Double price;
@ApiModelProperty(value = "课程所属考场",required = true)
@NotNull(message = "所属考场不能为空")
private Long examPlaceId;
}
返回列表的vo定义,用于构建返回结果实体。
package com.example.jiakao.pojo.vo.list;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
@Data
@ApiModel("课程信息返回值")
public class ExamCourseVo {
@ApiModelProperty("课程id")
private Long id;
@ApiModelProperty(value = "课程名称")
private String name;
@ApiModelProperty(value = "课程类型")
private Short type;
@ApiModelProperty(value = "课程价格")
private Double price;
@ApiModelProperty(value = "课程所属考场")
private Long examPlaceId;
@ApiModelProperty(value = "考场所属省份")
private Integer provinceId;
@ApiModelProperty(value = "考场所属城市")
private Integer cityId;
}
2)Service和Mapper
因为我们使用的两张表中都有name字段,因此我们在构造QueryWrapper时需要明确标出时哪一张表的name字段,因此这里使用QueryWrapper而不能使用LambdaQueryWrapper。
package com.example.jiakao.service;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.jiakao.common.enhance.MpPage;
import com.example.jiakao.common.enhance.MpQueryWrapper;
import com.example.jiakao.mapper.ExamCourseMapper;
import com.example.jiakao.pojo.entity.ExamCoursePo;
import com.example.jiakao.pojo.vo.json.PageVo;
import com.example.jiakao.pojo.vo.list.ExamCourseVo;
import com.example.jiakao.pojo.vo.req.query.KeyWordReqVo;
import org.springframework.stereotype.Service;
@Service
public class ExamCourseService extends ServiceImpl<ExamCourseMapper, ExamCoursePo> {
public PageVo<ExamCourseVo> listCoursesPage(KeyWordReqVo query) {
MpPage<ExamCourseVo> mpPage= new MpPage<>(query);
MpQueryWrapper<ExamCourseVo> wrapper = new MpQueryWrapper<>();
wrapper.likes(query.getKeyword(),"c.name");
baseMapper.selectCourses(mpPage,wrapper);
return mpPage.buildVo();
}
} }
}
package com.example.jiakao.mapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.example.jiakao.common.enhance.MpPage;
import com.example.jiakao.pojo.entity.ExamCoursePo;
import com.example.jiakao.pojo.vo.list.ExamCourseVo;
import org.apache.ibatis.annotations.Param;
//官方推荐前缀Constants.WRAPPER => ew
public interface ExamCourseMapper extends BaseMapper<ExamCoursePo> {
public MpPage<ExamCourseVo> selectCourses(MpPage<ExamCourseVo> page,
@Param(Constants.WRAPPER) QueryWrapper wrapper);
}
当我们在Mapper的方法中传入和返回的类型都是IPage接口的实现时,Mybatis-plus会自动帮我们分页,也就是在查询语句后拼接limit。但是查询条件不会帮我们自动实现,需要我们手动调用QueryWrapper类型的customSqlSegment获取SQL语句然后拼接到xml中。注意使用${}进行拼接,而不是#{},$是直接替换,而#会进行预编译。
当然我们也可以在有需要时对除BaseMapper泛型的实体以外的自定义实体构造LambdaQueryWrapper,此时我们需要增加afterPropertiesSet配置。
public class MyBatisPlusConfig implements InitializingBean {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL));
return interceptor;
}
/**
* 拥有lambda cache的实体类,才能使用LambdaQueryWrapper<Entity>
* 默认情况下只有BaseMapper<Entity>中泛型的entity才拥有lambda cache
* 其他可以通过TableInfoHelper手动添加lambda cache
*/
@Override
public void afterPropertiesSet() throws Exception {
MapperBuilderAssistant assistant = new MapperBuilderAssistant(new MybatisConfiguration(),"");
TableInfoHelper.initTableInfo(assistant, ExamCourseVo.class);
}
}
3)Mapper.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.example.jiakao.mapper.ExamCourseMapper">
<resultMap id="selectCourses" type="com.example.jiakao.pojo.vo.list.ExamCourseVo">
</resultMap>
<!-- ${wrapper.customSqlSegment} 获取自定义SQL-->
<select id="selectCourses" resultMap="selectCourses">
select
c.id,
c.name,
c.exam_place_id,
c.price,
c.type,
e.city_id,
e.province_id
from exam_course c
left join exam_place e on c.exam_place_id = e.id
${ew.customSqlSegment}
</select>
</mapper>