以前用ssm就写过了 现在用springboot来写下 当做记笔记吧.
1.环境搭建
mysql 5.0+ 创建三张表
1. user 表
字段: id, username, password,phone ...
2. classroom 表
字段: id , classname ...
3. classroomUser表
字段: id, classId, userId (分别将classId ,userId 设置为外键 分别对应 class表的id 和user表的id)
分析: 多对多的关系是通过额外建立一张表来确立关系的 这里我们用到 classroomUser表
关系:
用户(学生/老师) 可以在多个教室上课.
教室可以有多个用户(学生/老师).
sql语句(两个左外连接) :
例子:查询用户的包括用户的所在的班级名
select u.*, c.id as cid, c.className from user u
left outer join classroomUser cu on u.id = cu.userId
left outer join classroom c on c.id = cu.classId
就可以查询出的数据如下 我的表是写多了几个字段的
表创建好了
打开idea 创建一个springboot 工程
2.进入正题
1. 新建实体类 User.java 与 Classroom.java(注意用到了@Data的注解 不会的百度一下 不想用的自行生成get set 方法)
@Data
public class User {
private Integer id;
private String userName;
private String number;
private String password;
private String phone;
private String sex;
private Integer age;
private String img;
private String studentNumber;
// 多对多
private List<Classroom> classroomList;
}
@Data
public class Classroom {
private Integer id;
private String className;
// 多对多
private List<User> userList;
}
2.新建dao我这里是 mapper ClassroomMapper.java 与 UserMapper.java
@Repository
public interface UserMapper {
// 查
List<User> list();
// 跨表查询 多对多 方法一
User getUserByClassroomId(@Param("id") Integer id);
List<User> getAllUser();
// 多对多另一种写法 方法二
List<User> getAllUser2();
}
@Repository
public interface ClassroomMapper {
// 查
List<Classroom> list();
// 跨表查询 方法一
Classroom getClassroomByUserId(@Param("id") Integer id);
List<Classroom> getAllClassroom();
// 查询班级有多少学生 方法二
List<Classroom> getAllClassroom2();
}
3.service层 UserService.java ClassroomService.java 这里我做了合并 不重要的自行忽略
@Service
public class UserService {
@Autowired
UserMapper userMapper;
public User getUserByClassroomId(@Param("id") Integer id){
return userMapper.getUserByClassroomId(id);
}
public List<User> getAllUser(){
return userMapper.getAllUser();
}
public List<User> getAllUser2(){
return userMapper.getAllUser2();
}
public List<User> list(){
return userMapper.list();
}
}
@Service
public class ClassroomService {
@Autowired
ClassroomMapper classroomMapper;
// 多对多
public Classroom getClassroomByUserId(@Param("id") Integer id){
return classroomMapper.getClassroomByUserId(id);
}
public List<Classroom> getAllClassroom(){
return classroomMapper.getAllClassroom();
}
public List<Classroom> getAllClassroom2(){
return classroomMapper.getAllClassroom2();
}
public List<Classroom> list(){
return classroomMapper.list();
}
}
4.基于 xml 创建 UserMapper.xml 与 ClassrommMapper.xml
<?xml version = "1.0" encoding = "UTF-8"?>
<!DOCTYPE mapper PUBLIC
"-//mybatis.org//DTD com.example.Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.leto.student_system.mapper.UserMapper">
<!--多对多state 方法一-->
<resultMap id="userMap" type="com.letopo.student_system.entity.User">
<result column="id" jdbcType="INTEGER" property="id" />
<result column="userName" jdbcType="VARCHAR" property="userName" />
<result column="number" jdbcType="VARCHAR" property="number" />
<result column="password" jdbcType="VARCHAR" property="password" />
<result column="phone" jdbcType="VARCHAR" property="phone" />
<result column="sex" jdbcType="VARCHAR" property="sex" />
<result column="age" jdbcType="INTEGER" property="age" />
<result column="img" jdbcType="VARCHAR" property="img" />
<result column="studentNumber" jdbcType="VARCHAR" property="studentNumber" />
<association property="classroomList" column="id" select="com.letopo.student_system.mapper.ClassroomMapper.getClassroomByUserId"/>
</resultMap>
<select id="getUserByClassroomId" resultMap="BaseResultMap">
select * from user u,classroomUser cu where u.id=cu.userId and cu.classId = #{id};
</select>
<select id="getAllUser" resultMap="userMap">
select * from user;
</select>
<!--方法二-->
<resultMap id="userMap2" type="com.letopo.student_system.entity.User">
<result column="id" jdbcType="INTEGER" property="id" />
<result column="userName" jdbcType="VARCHAR" property="userName" />
<result column="number" jdbcType="VARCHAR" property="number" />
<result column="password" jdbcType="VARCHAR" property="password" />
<result column="phone" jdbcType="VARCHAR" property="phone" />
<result column="sex" jdbcType="VARCHAR" property="sex" />
<result column="age" jdbcType="INTEGER" property="age" />
<result column="img" jdbcType="VARCHAR" property="img" />
<result column="studentNumber" jdbcType="VARCHAR" property="studentNumber" />
<collection property="classroomList" ofType="com.letopo.student_system.entity.Classroom">
<id column="cid" property="id"></id>
<result column="className" property="className"></result>
</collection>
</resultMap>
<select id="getAllUser2" resultMap="userMap2">
select u.*, c.id as cid, c.className from user u
left outer join classroomUser cu on u.id = cu.userId
left outer join classroom c on c.id = cu.classId
</select>
<!-- 多对多end -->
<select id="list" resultType="com.letopo.student_system.entity.User">
select * from user
</select>
</mapper>
<?xml version = "1.0" encoding = "UTF-8"?>
<!DOCTYPE mapper PUBLIC
"-//mybatis.org//DTD com.example.Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.letopo.student_system.mapper.ClassroomMapper">
<!--多对多state 方法一-->
<resultMap id="classsMap" type="com.letopo.student_system.entity.Classroom">
<result column="id" jdbcType="INTEGER" property="id" />
<result column="className" jdbcType="VARCHAR" property="className" />
<collection property="userList" column="id" select="com.letopo.student_system.mapper.UserMapper.getUserByClassroomId"/>
</resultMap>
<select id="getClassroomByUserId" resultMap="BaseResultMap">
select * from classroom c,classroomUser cu where c.id=cu.classId and cu.userId = #{id};
</select>
<select id="getAllClassroom" resultMap="classsMap">
select * from classroom;
</select>
<!-- 方法二 -->
<resultMap id="classroomMap2" type="com.letopo.student_system.entity.Classroom">
<result column="id" jdbcType="INTEGER" property="id" />
<result column="className" jdbcType="VARCHAR" property="className" />
<collection property="userList" ofType="com.letopo.student_system.entity.User">
<id column="id" property="id"></id>
<result column="userName" property="userName"></result>
<result column="password" property="password"></result>
<result column="phone" property="phone"></result>
</collection>
</resultMap>
<select id="getAllClassroom2" resultMap="classsMap2">
select c.*, u.id as uid, u.userName, u.`password`, u.phone from classroom c
left outer join classroomUser cu on c.id = cu.classId
left outer join user u on u.id = cu.userId
</select>
<!--多对多 end-->
<select id="list" resultType="com.letopo.student_system.entity.Classroom">
select * from classroom
</select>
</mapper>
5.controller UserController.java, ClassroomController.java
/**
* @author jh
* @version 1.0
* @date 2019-10-16 15:20
*/
@RestController
@RequestMapping("/api/user")
// 这里继承个统一异常处理 你们写的时候可以不用 需要的可以咨询我
class UserController extends BaseApiController{
@Autowired
private UserService userService;
// 多对多查 方法1
@CrossOrigin
@GetMapping("/get")
public List<User> get(){
return userService.getAllUser();
}
// 多对多查询 方法2 (加上了分页功能 可以像方法1来写)
@CrossOrigin
@GetMapping("/get2")
public Map<String, Object> get2(@RequestParam(defaultValue = "1") Integer page_num,
@RequestParam(defaultValue = "10") Integer page_size){
PageHelper.startPage(page_num,page_size);
return onDataResp(new MyPageInfo<>(userService.getAllUser2()));
}
// 分页显示,查询用户
@CrossOrigin
@GetMapping("/list")
public Map<String, Object> list(@RequestParam(defaultValue = "1") Integer page_num,
@RequestParam(defaultValue = "10") Integer page_size){
PageHelper.startPage(page_num,page_size);
return onDataResp(new MyPageInfo<>(userService.list()));
}
}
classroomController.java是一样的 我就不写了
运行打开 postman 测试下效果如下
看到这里 你肯定会问 数据不一样啊 哈哈 我的表多建了几个字段嘛 学习的 有两个做参考就好啦
至于 一对多 一对一 思路都是一样的 那就更简单了
总结:
无论是一对一 一对多 多对多 都是一样 只要能写出sql语句 然后用mybatis 的时候 就想办法映射到实体类然后输出就可以了.
这个是基于xml 的 基于注解 等有空了 我再补充了 文章肯定有很多bug 的 有问题留言吧 我第一时间回你