1、一对一查询
简单理解:一个员工属于一个部门,查询该员工所属部门即可
1、建表语句
-- springboot.dept definition
-- 部门表
CREATE TABLE `dept` (
`did` int(11) NOT NULL AUTO_INCREMENT,
`dname` varchar(50) DEFAULT NULL,
`loc` varchar(50) DEFAULT NULL,
PRIMARY KEY (`did`)
) ENGINE=InnoDB AUTO_INCREMENT=912 DEFAULT CHARSET=utf8;
-- springboot.emp definition
-- 员工表
CREATE TABLE `emp` (
`id` int(11) NOT NULL,
`ename` varchar(50) DEFAULT NULL,
`gender` varchar(10) DEFAULT NULL,
`job_id` int(11) DEFAULT NULL,
`mgr` int(11) DEFAULT NULL,
`joindate` varchar(20) DEFAULT NULL,
`salary` decimal(7,2) DEFAULT NULL,
`bonus` decimal(7,2) DEFAULT NULL,
`dept_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `emp_jobid_ref_job_id_fk` (`job_id`),
KEY `emp_deptid_ref_dept_id_fk` (`dept_id`),
CONSTRAINT `emp_deptid_ref_dept_id_fk` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`did`),
CONSTRAINT `emp_jobid_ref_job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、pom文件中添加以下依赖
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<!--mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.18</version>
<scope>provided</scope>
</dependency>
</dependencies>
<build>
<!--maven由于他的约定大于配置,我们之后可以能遇到我们写的配置文件,无法被导出或者生效的问题-->
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
</build>
3、mybatis工具类
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
InputStream inputStream = null;
try {
//在代码运行时就读取文件获取sqlsession对象
String resource = "Mybatis-config.xml";
inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 既然有了 SqlSessionFactory,顾名思义,我们可以从中获得 SqlSession 的实例。
* SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。
* 你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句
*/
public static SqlSession getSqlSessionFactory() {
return sqlSessionFactory.openSession();
}
}
4、实体类:
emp
import lombok.Data;
@Data
public class Emp {
/**
* 社员id
*/
private Integer id;
/**
* 社员姓名
*/
private String ename;
/**
* 社员性别
*/
private String gender;
/**
* 工作id
*/
private Integer job_id;
/**
* 上级领导
*/
private Integer mgr;
/**
* 入职时间
*/
private String joindate;
/**
* 工资
*/
private Double salary;
/**
* 奖金
*/
private Double bonus;
/**
* 工号
*/
private Integer dept_id;
private Dept dept;
}
dept
import lombok.Data;
@Data
public class Dept {
private Integer did;
private String dname;
private String loc;
//一个部门多个员工
private List<Emp> emps;
}
DeptMapper
public interface DeptMapper {
/**
*方法暂时可以先不写
*/
}
EmpMapper
public interface EmpMapper {
/**
*查询单个员工及其部门的全部信息
*/
Emp getEmpAndDeptList(int id);
}
对应的xml文件
DeptMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.kexun.dao.DeptMapper">
</mapper>
EmpMapper.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="com.kexun.dao.EmpMapper">
<!-- 一对一 -->
<resultMap id="empDept" type="emp">
<result column="id" property="id"/>
<result column="ename" property="ename"/>
<result column="gender" property="gender"/>
<result column="job_id" property="join_id"/>
<result column="mgr" property="mgr"/>
<result column="joindate" property="joindate"/>
<result column="salary" property="salary"/>
<result column="bonus" property="bonus"/>
<result column="dept_id" property="dept_id"/>
<association property="dept" javaType="com.kexun.entity.Dept">
<result column="did" property="did" />
<result column="dname" property="dname" />
<result column="loc" property="loc" />
</association>
</resultMap>
<select id="getEmpAndDeptList" parameterType="int" resultMap="empDept">
select *
from springboot.dept d,
springboot.emp e
where d.did = e.dept_id
and e.id = #{id}
</select>
</mapper>
在mabatis核心配置文件中绑定映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://www.mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!--起别名-->
<typeAliases>
<typeAlias type="com.kexun.entity.Dept" alias="dept"/>
</typeAliases>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/*****?useSSL=false"/>
<property name="username" value="****"/>
<property name="password" value="****"/>
</dataSource>
</environment>
</environments>
<!--映射文件-->
<mappers>
<mapper resource="com/kexun/dao/DeptMapper.xml"/>
<mapper resource="com/kexun/dao/EmpMapper.xml"/>
</mappers>
</configuration>
编写测试类
public class AppTest {
/**
* 社员部门一对一
*/
@Test
public void three() {
SqlSession sqlSession = MybatisUtils.getSqlSessionFactory();
Emp empAndDeptList = sqlSession.getMapper(EmpMapper.class).getEmpAndDeptList(1002);
System.out.println(empAndDeptList);
sqlSession.close();
}
}
运行结果:
2、多对一
从员工角度:多个员工属于同一个部门·
EmpMapper
public interface EmpMapper {
/**
*查询单个员工及其部门的全部信息
*/
Emp getEmpAndDeptList(int id);
/**
*查询一个部门的全部信息所有员工
*/
List<Emp> getAll();
}
EmpMapper.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="com.kexun.dao.EmpMapper">
<!-- 一对一 -->
<resultMap id="empDept" type="emp">
<result column="id" property="id"/>
<result column="ename" property="ename"/>
<result column="gender" property="gender"/>
<result column="job_id" property="join_id"/>
<result column="mgr" property="mgr"/>
<result column="joindate" property="joindate"/>
<result column="salary" property="salary"/>
<result column="bonus" property="bonus"/>
<result column="dept_id" property="dept_id"/>
<association property="dept" javaType="com.kexun.entity.Dept">
<result column="did" property="did" />
<result column="dname" property="dname" />
<result column="loc" property="loc" />
</association>
</resultMap>
<!--一对一连表查询-->
<select id="getEmpAndDeptList" parameterType="int" resultMap="empDept">
select
<include refid="baseEmp"/>
from springboot.dept d,
springboot.emp e
where d.did = e.dept_id
and e.id = #{id}
</select>
<!--多对一连表查询-->
<select id="getAll" resultMap="empDept">
select
<include refid="baseEmp"/>
from springboot.dept d,
springboot.emp e
where d.did = e.dept_id
</select>
</mapper>
在mabatis核心配置文件中绑定映射文件
<!--绑定映射文件-->
<mappers>
<!--通过类去注册 注意保证dao接口和mapper.xml文件在同一包下,不然无法生效-->
<mapper resource="com/kexun/dao/StudentMapper.xml"/>
<mapper resource="com/kexun/dao/TeacherMapper.xml"/>
</mappers>
编写测试类
public class AppTest {
/**
* 测试Emp表的多对一方法
*/
@Test
public void dfrgd() {
SqlSession sqlSession = MybatisUtils.getSqlSessionFactory();
List<Emp> all = sqlSession.getMapper(EmpMapper.class).getAll();
for (Emp emp : all) {
System.out.println(emp);
}
sqlSession.close();
}
}
运行结果:
3、一对多查询
部门角度:(可以简单理解为)在一对一基础上查询某个部门所有员工信息
注:沿用之前的数据表
DeptMapper
public interface DeptMapper {
/**
*查询部门的全部员工信息
*/
Dept getDeptEmp(int id);
}
EmpMapper
public interface EmpMapper {
/**
*查询单个员工及其所属部门的全部信息
*/
Emp getEmpAndDeptList(int id);
}
对应xml文件
DeptMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.kexun.dao.DeptMapper">
<!--一对多-->
<select id="getDeptEmp" parameterType="int" resultMap="baseMap">
select *
from springboot.dept d,
springboot.emp e
where d.did = e.dept_id
and d.did = #{did}
</select>
<resultMap id="baseMap" type="dept">
<result column="did" property="did"/>
<result column="dname" property="dname"/>
<result column="loc" property="loc"/>
<collection property="emps" ofType="emp">
<result column="id" property="id"/>
<result column="ename" property="ename"/>
<result column="gender" property="gender"/>
<result column="job_id" property="job_id"/>
<result column="mgr" property="mgr"/>
<result column="joindate" property="joindate"/>
<result column="salary" property="salary"/>
<result column="bonus" property="bonus"/>
<result column="dept_id" property="dept_id"/>
</collection>
</resultMap>
EmpMapper.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="com.kexun.dao.EmpMapper">
<!-- 一对一 -->
<resultMap id="empDept" type="emp">
<result column="id" property="id"/>
<result column="ename" property="ename"/>
<result column="gender" property="gender"/>
<result column="job_id" property="join_id"/>
<result column="mgr" property="mgr"/>
<result column="joindate" property="joindate"/>
<result column="salary" property="salary"/>
<result column="bonus" property="bonus"/>
<result column="dept_id" property="dept_id"/>
<association property="dept" javaType="com.kexun.entity.Dept">
<result column="did" property="did" />
<result column="dname" property="dname" />
<result column="loc" property="loc" />
</association>
</resultMap>
<select id="getEmpAndDeptList" parameterType="int" resultMap="empDept">
select *
from springboot.dept d,
springboot.emp e
where d.did = e.dept_id
and e.id = #{id}
</select>
</mapper>
编写测试类
public class AppTest {
/**
* 社员部门一对多
*/
@Test
public void method3() {
SqlSession sqlSession = MybatisUtils.getSqlSessionFactory();
Dept deptEmp = sqlSession.getMapper(DeptMapper.class).getDeptEmp(10);
System.out.println(deptEmp);
sqlSession.close();
}
}
运行结果: