目录
对象嵌套关联查询一对多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); } }
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。
做猪小侠源码的代理,提供一站式服务
如果你不懂得搭建网站或者服务器,小程序,源码之类的怎么办? 第一通过本站学习各种互联网的技术 第二就是联系客服,我帮帮你搭建(当然要收取部分的费用) 第三成为我们的代理,我们提供整套的服务。