MyBatis实现一对多表查询模式

sql中的多表查询是每一个程序猿（媛）都要掌握的基本功，今天就以一个部门可以有多个员工，一个员工只能有一个部门这种场景，来模拟一对多的多表查询模式。

代码语言：javascript复制

show create table Emp查看员工表创建语句

------------------------------------------------------------------------------- | emp | CREATE TABLE emp ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, dept_id int(11) DEFAULT NULL, PRIMARY KEY (id), KEY dept_id (dept_id), CONSTRAINT emp_ibfk_1 FOREIGN KEY (dept_id) REFERENCES dept (id) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |

代码语言：javascript复制

show create table dept查看部门表创建语句

±------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Table | Create Table | ±------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | dept | CREATE TABLE dept ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 | ±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

代码语言：javascript复制

select * from  emp

mysql> select * from emp;

±—±-------------±--------
| id | name | dept_id |
±—±-------------±--------
| 1 | 张三 | 1 |
| 2 | 李四 | 2 |
| 3 | 老王 | 3 |
| 4 | 赵四 | 4 |
| 5 | 刘能 | 4 |
| 6 | 迈克尔杰克逊 | 4 |
±—±-------------±--------

select * from dept

mysql> select * from dept;

±—±-------
| id | name |
±—±-------
| 1 | 财务部 |
| 2 | 人事部 |
| 3 | 销售部 |
| 4 | 科技部 |
| 5 | 信息部 |
±—±------- Mybatis实现一对多表查询以员工为角度，在员工bean中保存Dept对象

代码语言：javascript复制

package com.jmy.domain;

public class Emp {

	private int id;
	private String name;
	private Dept dept;
	
	public Emp() {
		super();
	}
	public Emp(int id, String name, Dept dept) {
		super();
		this.id = id;
		this.name = name;
		this.dept = dept;
	}
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Dept getDept() {
		return dept;
	}
	public void setDept(Dept dept) {
		this.dept = dept;
	}
	
	@Override
	public String toString() {
		return "Emp [id="   id   ", name="   name   ", dept="   dept   "]";
	}	
}
java

代码语言：javascript复制

package com.jmy.domain;

import java.util.List;

public class Dept {
	private int id;
	private String name;
	private List<Emp> list;
	
	public Dept() {
		super();
	}
	public Dept(int id, String name, List<Emp> list) {
		super();
		this.id = id;
		this.name = name;
		this.list = list;
	}
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public List<Emp> getList() {
		return list;
	}
	public void setList(List<Emp> list) {
		this.list = list;
	}
	
	@Override
	public String toString() {
		return "Dept [id="   id   ", name="   name   ", list="   list   "]";
	}
}

sqlMapConfig.xml

代码语言：javascript复制

<?xml version="1.0" encoding="UTF-8"?>
<!-- 存放myBatis DTD约束文件的地方 -->
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<!-- 配置数据源 -->
	<environments default="mysqldb">
		<environment id="mysqldb">
			<transactionManager type="JDBC"/>
			<dataSource type="POOLED">
			<property name="driver" value="com.mysql.jdbc.Driver"/>
			<property name="url" value="jdbc:mysql:///mydb1"/>
			<property name="username" value="root"/>
			<property name="password" value="root"/>
			</dataSource>
		</environment>
	</environments>
	<!-- 配置映射文件 -->
	<mappers>
		<mapper resource="EmpMapper.xml"/>
	</mappers>
</configuration>

EmpMapper.xml

代码语言：javascript复制

<?xml version="1.0" encoding="UTF-8"?>
<!-- 描述表Sql与bean映射的关系 -->
<!DOCTYPE mapper
	PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
	"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
	<mapper namespace="com.jmy.EmpMapper">
		<select id="select01" resultMap="resm">
			select emp.id as eid,emp.name as ename,
			dept.id as did,dept.name as dname 
			from emp inner join dept
			on dept.id = emp.dept_id; 
		</select>
		<resultMap type="com.jmy.domain.Emp" id="resm">
			<id column="eid" property="id"/>
			<result column="ename" property="name"/>
			<association property="dept" javaType="com.jmy.domain.Dept">
				<id column="did" property="id"/>
				<result column="dname" property="name"/>
			</association>
		</resultMap>
	</mapper>

测试代码

代码语言：javascript复制

import com.jmy.domain.Emp;

public class Test01 {
	@Test
	public void test01() throws Exception {
		// 1.创建SqlSessionFactory
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().
				build(Resources.getResourceAsStream("sqlMapConfig.xml"));
		
		// 2.创建SqlSession
		SqlSession sqlSession = factory.openSession();
		
		// 3.执行sql
		List<Emp> list = sqlSession.selectList("com.jmy.EmpMapper.select01");
		
		// 4.处理结果集
		System.out.println(list);
	}
}

测试结果

代码语言：javascript复制

[Emp [id=, name=张三, dept=Dept [id=, name=财务部, list=null]], Emp [id=, name=李四, dept=Dept [id=, name=人事部, list=null]], Emp [id=, name=老王, dept=Dept [id=, name=销售部, list=null]], Emp [id=, name=赵四, dept=Dept [id=, name=科技部, list=null]], Emp [id=, name=刘能, dept=Dept [id=, name=科技部, list=null]], Emp [id=, name=迈克尔杰克逊, dept=Dept [id=, name=科技部, list=null]]]

测试完毕~~

auto default key null select

0 人点赞