springboot + mybatis 分页实现多对多查询

以前用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 的 有问题留言吧 我第一时间回你