Mybatis关联查询

Mybatis关联查询

演示的数据表为：

部门和员工属于一对多关系。接下来演示的是根据员工编号查询员工信息并关联查询所在部门信息，查询所有部门信息并关联查询该部门的所有员工信息。

演示项目结构如下（Maven项目）：

示例代码：

1、配置文件 mybatis_cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<!--数据源 -->
	<environments default="myconn">
		<environment id="myconn">
			<!-- 事务管理者 -->
			<transactionManager type="JDBC"></transactionManager>
			<!-- 数据库连接参数 -->
			<dataSource type="POOLED">
				<property name="driver" value="org.gjt.mm.mysql.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/db" />
				<property name="username" value="root" />
				<property name="password" value="root" />
			</dataSource>
		</environment>
	</environments>
	<!-- 引入实体映射文件 -->
	<mappers>
		<mapper resource="cn/sz/hcq/pojo/Emp.xml" />
		<mapper resource="cn/sz/hcq/pojo/Dept.xml" />
	</mappers>
</configuration>

2、部门接口  IDeptDAO.java

package cn.sz.hcq.dao;

import java.util.List;

import cn.sz.hcq.pojo.Dept;

public interface IDeptDAO {
	/**
	 * 查询所有部门的信息， 并查询该部门的所有员工的信息
	 * 
	 * @return
	 */
	public List<Dept> findAllDept();
}

3、部门接口实现类 DeptDAOImpl.java

package cn.sz.hcq.dao.impl;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import cn.sz.hcq.dao.IDeptDAO;
import cn.sz.hcq.factory.MybatisSqlSessionFactory;
import cn.sz.hcq.pojo.Dept;

public class DeptDAOImpl implements IDeptDAO {

	public List<Dept> findAllDept() {
		SqlSession sqlSession = null;
		try {
			sqlSession = MybatisSqlSessionFactory.getMySqlSession();
			return sqlSession.selectList("cn.sz.hcq.pojo.Dept.findAllDept");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MybatisSqlSessionFactory.closeSqlSession();
		}
		return null;
	}
}

4、员工接口 IEmpDAO.java

package cn.sz.hcq.dao;

import java.util.List;

import cn.sz.hcq.pojo.Emp;

public interface IEmpDAO {
	/**
	 * 查询所有的员工并包含部门信息
	 * 
	 * @return
	 */
	public List<Emp> findAllEmps();

	/**
	 * 根据员工编号查询员工信息并包括部门信息
	 * 
	 * @param empno
	 * @return
	 */
	public Emp findAllByEmpno(Integer empno);

}

5、员工接口实现类 EmpDAOImpl.java

package cn.sz.hcq.dao.impl;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import cn.sz.hcq.dao.IEmpDAO;
import cn.sz.hcq.factory.MybatisSqlSessionFactory;
import cn.sz.hcq.pojo.Emp;

public class EmpDAOImpl implements IEmpDAO {

	public List<Emp> findAllEmps() {
		return null;
	}

	public Emp findAllByEmpno(Integer empno) {
		SqlSession sqlSession = null;

		try {
			sqlSession = MybatisSqlSessionFactory.getMySqlSession();
			return sqlSession.selectOne("cn.sz.hcq.pojo.Emp.findEmpByEmpno",
					empno);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MybatisSqlSessionFactory.closeSqlSession();
		}
		return null;
	}
}

6、生成session数据库连接工具类 MybatisSqlSessionFactory.java

package cn.sz.hcq.factory;

import java.io.IOException;
import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MybatisSqlSessionFactory {
	// 配置文件
	private static final String RESOURCE = "mybatis_cfg.xml";
	private static Reader reader = null;
	private static SqlSessionFactoryBuilder builder = null;
	private static SqlSessionFactory factory = null;
	// 可以在同一个线程范围内，共享一个对象
	private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();

	// 静态代码块（类加载的时候执行一次）
	static {
		try {
			reader = Resources.getResourceAsReader(RESOURCE);
			builder = new SqlSessionFactoryBuilder();
			factory = builder.build(reader);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static SqlSession getMySqlSession() {
		// 从本地线程中获取session连接
		SqlSession sqlSession = threadLocal.get();
		// 连接为空则创建连接，并将该连接添加到本地线程中去
		if (sqlSession == null) {
			if (factory == null) {
				rebuildFactory();
			}
			sqlSession = factory.openSession();
		}
		threadLocal.set(sqlSession);
		return sqlSession;
	}

	// 创建工厂
	public static void rebuildFactory() {
		try {
			reader = Resources.getResourceAsReader(RESOURCE);
			builder = new SqlSessionFactoryBuilder();
			factory = builder.build(reader);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	// 关闭连接
	public static void closeSqlSession() {
		SqlSession sqlSession = threadLocal.get();
		if (sqlSession != null) {
			// 关闭session
			sqlSession.close();
		}
		// 同时将本地线程中置为null（防止用户再次调用时出现空的session）
		threadLocal.set(null);
	}

}

7、部门实体类 Dept.java

package cn.sz.hcq.pojo;

import java.io.Serializable;
import java.util.List;

public class Dept implements Serializable {
	private Integer deptno;
	private String dname;
	private String loc;
	
	private List<Emp> emps;

	public Integer getDeptno() {
		return deptno;
	}

	public void setDeptno(Integer deptno) {
		this.deptno = deptno;
	}

	public String getDname() {
		return dname;
	}

	public void setDname(String dname) {
		this.dname = dname;
	}

	public String getLoc() {
		return loc;
	}

	public void setLoc(String loc) {
		this.loc = loc;
	}

	public List<Emp> getEmps() {
		return emps;
	}

	public void setEmps(List<Emp> emps) {
		this.emps = emps;
	}

}

8、部门映射文件 Dept.xml

<?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="cn.sz.hcq.pojo.Dept">
	<!-- 根据部门编号查询部门信息 -->
	<select id="findDeptByDeptno" parameterType="java.lang.Integer"
		resultType="cn.sz.hcq.pojo.Dept">
		select deptno,dname,loc from dept where deptno=#{deptno}
	</select>

	<!-- 查询所有的部门信息，并查询该部门的所有的员工信息（关联查询） -->
	<select id="findAllDept" resultMap="deptinfo">
		select deptno,dname,loc from
		dept
	</select>

	<resultMap type="cn.sz.hcq.pojo.Dept" id="deptinfo">
		<id property="deptno" column="DEPTNO"></id>
		<result property="dname" column="DNAME" />
		<result property="loc" column="LOC" />
		<!-- 查询该部门的所有的员工信息 -->
		<collection property="emps" column="DEPTNO" javaType="java.util.ArrayList"
			ofType="cn.sz.hcq.pojo.Emp" select="cn.sz.hcq.pojo.Emp.findEmpByDeptno"></collection>
	</resultMap>
</mapper>

9、员工实体类 Emp.java

package cn.sz.hcq.pojo;

import java.io.Serializable;
import java.util.Date;

public class Emp implements Serializable {
	private Integer empno;
	private String ename;
	private String job;
	private Integer mgr;
	private Date hiredate;
	private Double sal;
	private Double comm;
	private Dept dept;

	public Integer getEmpno() {
		return empno;
	}

	public void setEmpno(Integer empno) {
		this.empno = empno;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public Integer getMgr() {
		return mgr;
	}

	public void setMgr(Integer mgr) {
		this.mgr = mgr;
	}

	public Date getHiredate() {
		return hiredate;
	}

	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}

	public Double getSal() {
		return sal;
	}

	public void setSal(Double sal) {
		this.sal = sal;
	}

	public Double getComm() {
		return comm;
	}

	public void setComm(Double comm) {
		this.comm = comm;
	}

	public Dept getDept() {
		return dept;
	}

	public void setDept(Dept dept) {
		this.dept = dept;
	}

}

10、员工映射文件 Emp.xml 

<?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="cn.sz.hcq.pojo.Emp">
	<!-- 根据部门编号查询员工 -->
	<select id="findEmpByDeptno" parameterType="java.lang.Integer"
		resultType="cn.sz.hcq.pojo.Emp">
		select empno,ename,job,mgr,hiredate,sal,comm,deptno from
		emp
		where deptno=#{deptno}
	</select>
	<!-- 根据员工编号查询员工信息，并查询出他所在部门信息 （关联查询） -->
	<select id="findEmpByEmpno" parameterType="java.lang.Integer"
		resultMap="empinfo">
		select empno,ename,job,mgr,sal,comm,hiredate,deptno from emp
		where
		empno=#{empno}
	</select>

	<!-- 自己封装结果 type : 表示我最终要封装到什么类型的对象中 id : 自定义，起名字，方便调用 -->
	<resultMap type="cn.sz.hcq.pojo.Emp" id="empinfo">
		<id column="EMPNO" property="empno" />
		<result column="ENAME" property="ename" />
		<result column="JOB" property="job" />
		<result column="MGR" property="mgr" />
		<result column="HIREDATE" property="hiredate" />
		<result column="SAL" property="sal" />
		<result column="COMM" property="comm" />
		<!-- 查询出部门信息 -->
		<association property="dept" column="DEPTNO"
			select="cn.sz.hcq.pojo.Dept.findDeptByDeptno"></association>
	</resultMap>

</mapper>

11、pom.xml  maven配置文件

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>cn.sz.hcq.pro</groupId>
	<artifactId>Mybatis_03</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>
	<dependencies>
		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.2.3</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.39</version>
		</dependency>
	</dependencies>
</project>

12、测试类

package cn.sz.hcq.test;

import java.util.Iterator;
import java.util.List;

import cn.sz.hcq.dao.IDeptDAO;
import cn.sz.hcq.dao.IEmpDAO;
import cn.sz.hcq.dao.impl.DeptDAOImpl;
import cn.sz.hcq.dao.impl.EmpDAOImpl;
import cn.sz.hcq.pojo.Dept;
import cn.sz.hcq.pojo.Emp;

public class DeptTest {
	public static void main(String[] args) {
		// 查询所有部门，并关联查询出该部门的所有员工
		IDeptDAO deptDAO = new DeptDAOImpl();
		List<Dept> depts = deptDAO.findAllDept();
		for (int i = 0; i < depts.size(); i++) {
			Dept dept = depts.get(i);
			System.out.println(dept.getDeptno() + "," + dept.getDname() + ","
					+ dept.getLoc());
			System.out.println("================");
			List<Emp> emps = dept.getEmps();
			Iterator<Emp> it = emps.iterator();
			while (it.hasNext()) {
				Emp emp = it.next();
				System.out.println(emp.getEname() + "," + emp.getJob());
			}
		}
		// 查询工号为7788的员工，并关联查询他的部门信息
		System.out.println("*************");
		IEmpDAO empDAO = new EmpDAOImpl();
		Emp emp = empDAO.findAllByEmpno(7788);
		System.out.println(emp.getEname() + "," + emp.getJob() + ","
				+ emp.getDept().getDname());
	}
}

结果：