先说问题:在搭建SSM(Spring+SpringMVC+Mybatis)框架,从后台数据库查询出数据提供给前端页面进行分页显示时候,发现分页查询出来的结果在前端页面上显示并不是按照主键 id 排序的,而是先按照关联表id,也就是主表外键的 id 分成不同的部门(我这里是employee 和 department) ,每个部门中再按照 id 顺序显示记录。
这样导致主键 id 为 2的反而跑到了查询结果的后面,或是在插入新的数据,由于排序显示问题,可能不能出现在列表的最后面,导致不容易找到刚刚插入的记录。
其中,我想按照 emp_id(employeeid) 这个主键顺序排列查询的记录,显示到页面上
默认出现的问题如图:
表的关联结构如图:
修改后的效果如图:
解决思路:开始认为是 pageHelper 这个分页插件,在分页的时候对于记录按照某种规则排列了,最后经过测试,mybatis生成逆向工程时候 配置sql 语句 如果不指定 order by XX ,就会按照上述情况排序。其中,实现一对一关联的sql 查询语句有两种写法:
- SELECT * FROM tbl_emp e, tbl_dept d WHERE e.d_id = d.dept_id
- 或
- SELECT* FROM tbl_emp e LEFT JOIN tbl_dept d ON e.d_id = d.dept_id
解决步骤:Mybatis逆向工程配置以及dao层增删改查测试参见:
https://blog.csdn.net/weixin_38533896/article/details/79866813
这里主要是写如何在 mybatis 映射文件 实现一对一关联的sql 并设置order by 属性,排序查询结果
1. mybatis 逆向工程生成的目录结构如下,包括bean 、dao 以及 mapper文件夹下的sql 映射文件
2. 在com.lbc.crud.bean 包中的 Employee.java 中添加Department 属性以及相应的 get 、set 方法
- private Department department;
- public Department getDepartment() {
- return department;
- }
- public void setDepartment(Department department) {
- this.department = department;
- }
3. 在 com.lbc.crud.dao 包中的EmployeeMapper.java 中添加 员工 关联 部门的查询接口
- List<Employee> selectByExampleWithDept(EmployeeExample example);
- Employee selectByPrimaryKeyWithDept(Integer empId);
4. 在mapper 文件夹下 EmployeeMapper.xml 将对应的sql 语句填入,这里直接copy 了整个映射文件select 相关语句
- <?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.lbc.crud.dao.EmployeeMapper">
- <resultMap id="BaseResultMap" type="com.lbc.crud.bean.Employee">
- <id column="emp_id" jdbcType="INTEGER" property="empId" />
- <result column="emp_name" jdbcType="VARCHAR" property="empName" />
- <result column="gender" jdbcType="CHAR" property="gender" />
- <result column="email" jdbcType="VARCHAR" property="email" />
- <result column="d_id" jdbcType="INTEGER" property="dId" />
- </resultMap>
- <resultMap id="WithDeptResultMap" type="com.lbc.crud.bean.Employee">
- <id column="emp_id" jdbcType="INTEGER" property="empId" />
- <result column="emp_name" jdbcType="VARCHAR" property="empName" />
- <result column="gender" jdbcType="CHAR" property="gender" />
- <result column="email" jdbcType="VARCHAR" property="email" />
- <result column="d_id" jdbcType="INTEGER" property="dId" />
- <!-- 指定联合查询出部门字段的封装 -->
- <association javaType="com.lbc.crud.bean.Department" property="department">
- <id column="dept_id" property="deptId" />
- <result column="dept_name" property="deptName" />
- </association>
- </resultMap>
- <sql id="Example_Where_Clause">
- <where>
- <foreach collection="oredCriteria" item="criteria" separator="or">
- <if test="criteria.valid">
- <trim prefix="(" prefixOverrides="and" suffix=")">
- <foreach collection="criteria.criteria" item="criterion">
- <choose>
- <when test="criterion.noValue">
- and ${criterion.condition}
- </when>
- <when test="criterion.singleValue">
- and ${criterion.condition} #{criterion.value}
- </when>
- <when test="criterion.betweenValue">
- and ${criterion.condition} #{criterion.value}
- and
- #{criterion.secondValue}
- </when>
- <when test="criterion.listValue">
- and ${criterion.condition}
- <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
- #{listItem}
- </foreach>
- </when>
- </choose>
- </foreach>
- </trim>
- </if>
- </foreach>
- </where>
- </sql>
- <sql id="Update_By_Example_Where_Clause">
- <where>
- <foreach collection="example.oredCriteria" item="criteria" separator="or">
- <if test="criteria.valid">
- <trim prefix="(" prefixOverrides="and" suffix=")">
- <foreach collection="criteria.criteria" item="criterion">
- <choose>
- <when test="criterion.noValue">
- and ${criterion.condition}
- </when>
- <when test="criterion.singleValue">
- and ${criterion.condition} #{criterion.value}
- </when>
- <when test="criterion.betweenValue">
- and ${criterion.condition} #{criterion.value}
- and
- #{criterion.secondValue}
- </when>
- <when test="criterion.listValue">
- and ${criterion.condition}
- <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
- #{listItem}
- </foreach>
- </when>
- </choose>
- </foreach>
- </trim>
- </if>
- </foreach>
- </where>
- </sql>
- <sql id="Base_Column_List">
- emp_id, emp_name, gender, email, d_id
- </sql>
- <sql id="WithDept_Colume_List">
- e.emp_id, e.emp_name, e.gender, e.email, e.d_id, d.dept_id,
- d.dept_name
- </sql>
- <select id="selectByExampleWithDept" resultMap="WithDeptResultMap">
- select
- <if test="distinct">
- distinct
- </if>
- <include refid="WithDept_Colume_List" />
- from tbl_emp e, tbl_dept d where e.d_id = d.dept_id
- <if test="_parameter != null">
- <include refid="Example_Where_Clause" />
- </if>
- <if test="orderByClause != null">
- order by ${orderByClause}
- </if>
- </select>
- <select id="selectByPrimaryKeyWithDept" resultMap="WithDeptResultMap">
- select
- <include refid="WithDept_Colume_List" />
- from tbl_emp e left join tbl_dept d on e.d_id = d.dept_id
- where emp_id
- = #{empId,jdbcType=INTEGER}
- </select>
- <select id="selectByExample" parameterType="com.lbc.crud.bean.EmployeeExample" resultMap="BaseResultMap">
- select
- <if test="distinct">
- distinct
- </if>
- <include refid="Base_Column_List" />
- from tbl_emp
- <if test="_parameter != null">
- <include refid="Example_Where_Clause" />
- </if>
- <if test="orderByClause != null">
- order by ${orderByClause}
- </if>
- </select>
- <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
- select
- <include refid="Base_Column_List" />
- from tbl_emp
- where emp_id = #{empId,jdbcType=INTEGER}
- </select>
- </mapper>
5. 编写MapperTest.java 进行联合查询的测试,顺带通过employeeExample.setOrderByClause() 方法,给查询结果指定排序顺序,这里是通过emp_id 默认以升序排序
- @Test
- public void testEmpSelectByExampleNull(){
- EmployeeExample employeeExample = new EmployeeExample();
- // 如果不指定order by id,将默认按照其他方式排序查询结果
- employeeExample.setOrderByClause("emp_id");
- List<Employee> listTeammembers =employeeMapper.selectByExampleWithDept(employeeExample);
- for(Employee e : listTeammembers){
- System.out.println(e);
- }
- }
PS: 给sql 提供更多的查询条件在 com.lbc.crud.bean 包的 EmployeeExample.java 中,需要使用更复杂的查询条件,可以在里面寻找合适的方法