对象嵌套关联查询一对多List集合查询
mybatis嵌套关联查询如下
由于我的是一对集合查询，所以我有两个类。
@Data
@TableName("tb_user")
public class User {
@TableId(type= IdType.INPUT)
private String id;
@TableField("user_name")
private String username;
private String password;
private String name;
private String email;
private int age;
private ArrayList<Authority> list;
}
权限类
@Data
@TableName
public class Authority {
@TableId(type = IdType.INPUT)
@TableField("aid")
private int id;
@TableId("aname")
private String name;
}测试类
@Test
public void ManyToMany(){
User user = userMapper.selectAuthorityById(1);
ArrayList <Authority> list = user.getList();
System.out.println(user);
for (Authority authority : list) {
System.out.println("所对应权限为" authority.getName());
}
}springboot项目的依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.26</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!--mybatis plus 起步依赖--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.0</version> </dependency>
这下面就是我xml文件里面怎么写的嵌套查询语句
<mapper namespace="com.itheima.mybatisplus.mapper.UserMapper">
<!--返回的对象为authority-->
<resultMap id="authority" type="com.itheima.mybatisplus.domain.User">
<id column="id" property="id"/>
<id column="password" property="password"/>
<id column="age" property="age"/>
<id column="email" property="email"/>
<id column="name" property="name"/>
<id column="user_name" property="username"/>
<collection property="list"
ofType="com.itheima.mybatisplus.domain.Authority">
<id property="id" column="aid"/>
<id property="name" column="aname"/>
</collection>
<select id="selectAuthorityById" parameterType="int" resultMap="authority">
SELECT * FROM
authority a,tb_user t,user_authority ua
WHERE a.aid=ua.authority_id
AND t.id=ua.user_id
AND t.id=#{id}
</select>数据库的配置我就不放了，直接编写就可以了，看会下面这个xml配置就可以了
一对多查询(经典案例)
条件
查询班级表 返回所有学生信息 （一对多问题）
数据库
班级class_info
学生student
代码实现
<!-- 多对一 或者 一对一 --> <!-- <association property=""--> <!-- 一对多 返回集合--> <!- - <collection property=""- ->
实体类ClassInfo.java
@Data
public class ClassInfo {
private Long id;
private String name;
private String nameTest;
private List<Student> studentList;
}ClassInfoMapper.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层某个接口的包的全名称-->
<mapper namespace="com.example.demo.mapper.ClassInfoMapper">
<!-- 查询班级 返回所有学生的信息 一对多-->
<!-- 自定义映射规则-->
<resultMap id="OneToMany" type="com.qcby.zsgc.entity.ClassInfo">
<result column="name" jdbcType="VARCHAR" property="nameTest" />
<collection column="{id1=id,name=name}"
property="studentList"
select="com.example.demo.mapper.StudentMapper.listByClassInfoId"> </collection>
</resultMap>
<select id="listAllWithStudent" resultMap="OneToMany">
select * from class_info
</select>关联StudentMapper.xml中的子查询
<select id="listByClassInfoId" resultType="com.example.demo.entity.Student">
SELECT
*
FROM
student s
where class_info_id = #{id1} or name = #{name}
</select>ClassInfoMapper.java
public interface ClassInfoMapper extends BaseMapper<ClassInfo> {
IPage<ClassInfo> listAllWithStudent(IPage<ClassInfo> page);
}ClassInfoService.java
public interface ClassInfoService extends IService<ClassInfo> {
IPage<ClassInfo> listAllWithStudent(IPage<ClassInfo> page);
}ClassInfoServiceImpl.java
@Service
public class ClassInfoServiceImpl extends ServiceImpl<ClassInfoMapper, ClassInfo> implements ClassInfoService {
@Autowired
private StudentService studentService;
@Override
public IPage<ClassInfo> listAllWithStudent(IPage<ClassInfo> page) {
return this.baseMapper.listAllWithStudent(page);
}
}ClassInfoController.java
@Controller
@RequestMapping("classInfo")
public class ClassInfoController {
@Autowired
private ClassInfoService classInfoService;
@RequestMapping("listAllWithStudent")
@ResponseBody
public IPage<ClassInfo> listAllWithStudent(Integer pageNo,Integer pageSize){
Page<ClassInfo> page = new Page<>(pageNo,pageSize);
return classInfoService.listAllWithStudent(page);
}
}以上为个人经验，希望能给大家一个参考，也希望大家多多支持Devmax。