mybatis关联查询

关联查询

一对一

<?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.woniuxy.dao.EmpDao">
  <!--一步查询-->
	<select id="selectEmpByEid" resultMap="empmapper">
		SELECT * FROM dep d ,emp e WHERE d.did=e.did AND eid=#{eid}
	</select>
	
	<resultMap type="Emp" id="empmapper">
		<id column="eid" property="eid"/>
		<result column="ename" property="ename"/>
		对一
		<association property="dep" javaType="Dep">
			<id column="did" property="did"/>
			<result column="dname" property="dname"/>
		</association>
	</resultMap>
	
	
	<select id="selectAllEmps" resultMap="empmapper">
		SELECT * FROM dep d ,emp e WHERE d.did=e.did 
	</select>
	
<!--多步查询-->
	<select id="selectEmpByEid" resultMap="empmapper">
		SELECT * FROM emp WHERE eid=#{eid}
	</select>
	
	<resultMap type="Emp" id="empmapper">
		<id column="eid" property="eid"/>
		<result column="ename" property="ename"/>
		<association property="dep" javaType="Dep" select="selectDepByDid" column="did" />
	</resultMap>
	
	<select id="selectDepByDid" resultType="Dep">
		SELECT * FROM dep WHERE did=#{did}
	</select>
	
	
	<select id="selectAllEmps" resultMap="empmapper">
		SELECT * FROM emp 
	</select>
</mapper>

一对多

实体类

public class Dep {

	private Integer did;
	private String dname;
	//查询部门的时候，要求同时展示部门中的所有员工信息
	//一对多：一个部门有多个员工
	//不用可以不写
	private List<Emp> emps;

    
public class Emp {

	private Integer eid;
	private String ename;
	//一个员工属于一个部门，展示员工的时候同时展示这个员工的部门信息
	//(多对一)一对一
	//外键列在实体类中一般都写成对象,不用可以不查
	private Dep dep;

Dao

package com.woniuxy.dao;

import com.woniuxy.entity.Dep;

public interface DepDao {

	Dep selectDepByDid(int did);
}

映射文件

<?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.woniuxy.dao.DepDao">

	<!-- 使用连接一次出查询，使用resultMap：指定如何将查询的数据装换成想要的类型 -->
	<select id="selectDepByDid" resultMap="depmapper">
		SELECT * FROM dep d,emp e WHERE d.did=e.did AND d.did=#{did}
	</select>
	
	<!-- type:转换的类型 -->
	<resultMap type="Dep" id="depmapper">
		<id  column="did" property="did"/>
		<result  column="dname" property="dname"/>
		<!-- 一对多的映射关系通过collection标签匹配
			property:dep对象中哪个属性是集合
			ofType：集合属性的元素类型
		 -->
		<collection property="emps" ofType="Emp">
			<id column="eid" property="eid"/>
			<result column="ename" property="ename"/>
		</collection>

	</resultMap>
</mapper>

多对一

<?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.woniuxy.dao.EmpDao">

	 <select id="selectEmpByEid" resultMap="empmapper">
		SELECT * FROM dep d ,emp e WHERE d.did=e.did AND eid=#{eid}
	</select>
	
	<resultMap type="Emp" id="empmapper">
		<id column="eid" property="eid"/>
		<result column="ename" property="ename"/>
		对一
		<association property="dep" javaType="Dep">
			<id column="did" property="did"/>
			<result column="dname" property="dname"/>
		</association>
	</resultMap>
	
	
	<select id="selectAllEmps" resultMap="empmapper">
		SELECT * FROM dep d ,emp e WHERE d.did=e.did 
	</select>
</mapper>

多对多

1.通过中间表转化为一对多和多对一