用了MybatisPlus之后,感觉自己写sql的机会不多,但是一些多表关联还是需要自己写的。
今天复习一下mybatis中的多表关联查询
建两个简单的表:
emp员工表
dept部门表
员工:部门=多:1
部门:员工=1:多
如果项目中有多对多的关系,需要引入中间表,比如rbac中的用户角色表就是多对多的关系
1.针对多对一的情况:
即员工:部门=多:1
public class EmpDept {
private Integer id;
private String userName;
private Date createDate;
private Integer deptId;
private Dept dept ;
// getter 和setter方法省略
}
方式一:对象属性名.属性
package com.tulingxueyuan.mapper;
import com.tulingxueyuan.pojo.EmpDept;
import java.util.List;
public interface EmpDeptMapper {
List<EmpDept> selectEmpWithDept();
}
<?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.tulingxueyuan.mapper.EmpDeptMapper">
<resultMap id="BaseResultMap" type="com.tulingxueyuan.pojo.EmpDept">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="user_name" jdbcType="VARCHAR" property="userName" />
<result column="create_date" jdbcType="TIMESTAMP" property="createDate" />
<result column="dept_id" jdbcType="INTEGER" property="deptId" />
<!--方式一:对象属性名.属性-->
<result column="d_id" property="dept.id"></result>
<result column="d_name" property="dept.deptName"></result>
</resultMap>
<select id="selectEmpWithDept" resultMap="BaseResultMap">
SELECT
e.id,
e.user_name,
e.dept_id,
d.id as d_id,
d.dept_name as d_name
FROM
emp e
INNER JOIN dept d ON e.dept_id = d.id
</select>
</mapper>
public class SqlTest {
SqlSessionFactory sqlSessionFactory ;
@Before
public void init() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void test01(){
SqlSession sqlSession = sqlSessionFactory.openSession();
EmpDeptMapper mapper = sqlSession.getMapper(EmpDeptMapper.class);
List<EmpDept> empDeptList = mapper.selectEmpWithDept();
System.out.println(empDeptList.size());
System.out.println(empDeptList);
sqlSession.commit();
sqlSession.close();
}
}
方式二:association
1.使用association的第一种写法:指定javaType
association中的property指定多对一种的一
javaType指定类型
<?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.tulingxueyuan.mapper.EmpDeptMapper">
<resultMap id="BaseResultMap" type="com.tulingxueyuan.pojo.EmpDept">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="user_name" jdbcType="VARCHAR" property="userName" />
<result column="create_date" jdbcType="TIMESTAMP" property="createDate" />
<result column="dept_id" jdbcType="INTEGER" property="deptId" />
<!--方式一:对象属性名.属性-->
<!-- <result column="d_id" property="dept.id"></result>-->
<!-- <result column="d_dept_name" property="dept.deptName"></result>-->
<!--方式二-1:association-->
<association property="dept" javaType="com.tulingxueyuan.pojo.Dept">
<result column="d_id" property="id"></result>
<result column="d_dept_name" property="deptName"></result>
</association>
</resultMap>
<select id="selectEmpWithDept" resultMap="BaseResultMap">
SELECT
e.id,
e.user_name,
e.create_date,
e.dept_id,
d.id as d_id,
d.dept_name as d_dept_name
FROM
emp e
INNER JOIN dept d ON e.dept_id = d.id
</select>
</mapper>
2.使用association的第二种写法:指定resultMap
如果不想在association中再写result这种列和属性字段的映射的话,那么就用resultMap来指定一个已经写好的resultMap即可,达到重用的目的。
ps:这时候如果有命名重复的问题,比如emp中有id,dept种也有id,可以用as重命名查询出来的列,之后用columnPrefix="d_"来去掉这个前缀。
<?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.tulingxueyuan.mapper.EmpDeptMapper">
<resultMap id="BaseResultMap" type="com.tulingxueyuan.pojo.EmpDept">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="user_name" jdbcType="VARCHAR" property="userName" />
<result column="create_date" jdbcType="TIMESTAMP" property="createDate" />
<result column="dept_id" jdbcType="INTEGER" property="deptId" />
<!--方式一:对象属性名.属性-->
<!-- <result column="d_id" property="dept.id"></result>-->
<!-- <result column="d_dept_name" property="dept.deptName"></result>-->
<!--方式二-1:association-->
<!-- <association property="dept" javaType="com.tulingxueyuan.pojo.Dept">-->
<!-- <result column="d_id" property="id"></result>-->
<!-- <result column="d_dept_name" property="deptName"></result>-->
<!-- </association>-->
<!--方式二-2:association-->
<association property="dept" columnPrefix="d_"
resultMap="com.tulingxueyuan.mapper.DeptMapper.BaseResultMap">
</association>
</resultMap>
<select id="selectEmpWithDept" resultMap="BaseResultMap">
SELECT
e.id,
e.user_name,
e.create_date,
e.dept_id,
d.id as d_id,
d.dept_name as d_dept_name
FROM
emp e
INNER JOIN dept d ON e.dept_id = d.id
</select>
</mapper>
public class Dept {
private Integer id;
private String deptName;
//省略getter和setter方法
}
那么使用属性名.属性的方式和使用association的两种方式有什么不同呢?
使用association会强制的使我们的查询结果进行多对一,因为mybatis底层是将主键和查出来的对象放入一个类似hashmap的数据结构中,所以没有查emp的id会造成association失效。association是凭借id来组织多对一的结果。这是非常容易忽视的问题。
使用属性名.属性的方式就不会帮助我们多对一,我们查出来多少条数据就是多少条数据。
2.针对一对多的情况:
其实这种情况使我们工作中应用比较多的场景。
比如:级联查询的时候
这里其实用到了collection,这种情况不像是association还可以用属性名.属性代替一下,这个collection只能就自己这么用,哈哈~
重点 代码:
package com.tulingxueyuan.pojo;
import java.util.List;
public class DeptEmp {
private Integer id;
private String deptName;
private List<Emp> emps ;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
public List<Emp> getEmps() {
return emps;
}
public void setEmps(List<Emp> emps) {
this.emps = emps;
}
@Override
public String toString() {
return "DeptEmp{" +
"id=" + id +
", deptName='" + deptName + '\'' +
", emps=" + emps +
'}';
}
}
<?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.tulingxueyuan.mapper.DeptEmpMapper">
<resultMap id="BaseResultMap" type="com.tulingxueyuan.pojo.DeptEmp">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="dept_name" jdbcType="VARCHAR" property="deptName" />
<collection property="emps" columnPrefix="e_"
resultMap="com.tulingxueyuan.mapper.EmpMapper.BaseResultMap"></collection>
</resultMap>
<select id="selectDeptWithEmp" resultMap="BaseResultMap">
SELECT
d.id,
d.dept_name,
e.id as e_id,
e.user_name as e_user_name,
e.create_date as e_create_date,
e.dept_id as e_dept_id
FROM
dept d
INNER JOIN emp e ON d.id = e.dept_id
</select>
</mapper>
<mapper namespace="com.tulingxueyuan.mapper.EmpMapper">
<resultMap id="BaseResultMap" type="com.tulingxueyuan.pojo.Emp">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="user_name" jdbcType="VARCHAR" property="userName" />
<result column="create_date" jdbcType="TIMESTAMP" property="createDate" />
<result column="dept_id" jdbcType="INTEGER" property="deptId" />
</resultMap>
</mapper>
@Test
public void testCollection(){
SqlSession sqlSession = sqlSessionFactory.openSession();
DeptEmpMapper mapper = sqlSession.getMapper(DeptEmpMapper.class);
List<DeptEmp> deptEmpList = mapper.selectDeptWithEmp();
System.out.println(deptEmpList.size());
System.out.println(deptEmpList);
sqlSession.commit();
sqlSession.close();
}
其实collection标签的用法和association标签的属性很多用法是相同的。
ps:
记录mapper.xml中的关于不等于的写法:
重点看注释部分
<select id="selectProductCategoryWithChildren" resultMap="selectProductCategoryWithChildrenMap">
SELECT
t1.id,
t1.`name`,
t2.id AS son_id,
t2.`name` AS son_name
FROM
pms_product_category t1
INNER JOIN pms_product_category t2 ON t1.id = t2.parent_id
and t1.parent_id=0
and t2.parent_id<![CDATA[<>]]>0 <!--或者使用尖括号 但是尖括号会报错 可以使用<> 或者使用<![CDATA[<>]]> 还可以直接使用!= -->
ORDER BY t1.id
</select>