Mybatis关联查询

喜欢ヅ旅行 2022-06-05 02:54 270阅读 0赞

**Mybatis关联查询**

演示的数据表为：

![Center][]

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

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

![Center 1][]

示例代码：

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>
    
    	
    	<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>
    		
    		<dependency>
    			<groupId>org.mybatis</groupId>
    			<artifactId>mybatis</artifactId>
    			<version>3.2.3</version>
    		</dependency>
    		
    		<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());
    	}
    }

结果：

![Center 2][]

[Center]: /images/20220605/55ae8d96682b4440bd470546fa5381fc.png
[Center 1]: /images/20220605/2645097dde854359a9c4cd08f5ba56ca.png
[Center 2]: /images/20220605/26355e5d36ee41119fd8bacac3663998.png