MyBatis实现一对多表查询模式
sql中的多表查询是每一个程序猿（媛）都要掌握的基本功，今天就以一个部门可以有多个员工，一个员工只能有一个部门这种场景，来模拟一对多的多表查询模式。
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 |
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 |
±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
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对象
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 + "]";
}
}
javapackage 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
<?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
<?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>
测试代码
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);
}
}
测试结果
[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]]]测试完毕~~