select查询的集中情况
1、查询单行数据返回单个对象
public Employee getEmpById(Integer id);
2、查询多行数据返回对象的集合
public List<Employee> getEmpsByNameLike(String name);
<!--如果返回的是一个集合,要挟集合中元素的类型-->
<select id="getEmpsByNameLike" resultType="dao.Employee">
select * from tb_employee where name like #{name}
</select>
@Test
public void test1() throws IOException {
String resource = "mybatis-config.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
List<Employee> like = mapper.getEmpsByNameLike("%e%");
for (Employee employee : like) {
System.out.println(employee);
}
//手动提交数据
openSession.commit();
}finally {
openSession.close();
}
}
3、查询单行数据返回Map集合
//返回一条记录的Map,key就是列名,value就是对应的值
public Map<String,Object> getEmployeeByIdReturnMap(Integer id );
<!-- 查询单行数据返回Map集合 -->
<select id="getEmployeeByIdReturnMap" resultType="map">
select * from tb_employee where id=#{id}
</select>
@Test
public void test1() throws IOException {
String resource = "mybatis-config.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//获取到的sqlSession不会自动提交数据
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Map<String,Object> map = mapper.getEmpsByIdReturnMap(3);
System.out.println(map);
}
openSession.commit();
}finally {
openSession.close();
}
}
‘
4、查询多行数据返回Map集合
//多条记录封装一个Map,Map<integer,Employee>;键就是这条记录的主键,值就是记录封装后的javaBean
@MapKey("id") // 指定使用对象的哪个属性来充当 map 的 key
public Map<Integer,Employee> getEmpsByNameLikeReturnMap(String name);
<!--
使用name作为map的key进行封装
@MapKey("name")
public Map<String,Employee> getEmpsByNameLikeReturnMap(String name);
-->
@Test
public void test1() throws IOException {
String resource = "mybatis-config.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//获取到的sqlSession不会自动提交数据
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
//多条记录封装成一个map
Map<Integer,Employee> map = mapper.getEmpsByNameLikeReturnMap("%r%");
System.out.println(map);
}
//手动提交数据
openSession.commit();
}finally {
openSession.close();
}
}
<select id="getEmpsByNameLikeReturnMap" resultType="dao.Employee">
select * from tb_employee where name like #{name}
</select>
resultType的自动映射
1、autoMappingBehavior默认是PARTIAL,开启自动映射的功能。唯一的要求是列名和javaBean的属性名一致
2、如果autoMappingBehavior设置为null则会取消自动映射
3、数据库字段命名规范,POJO属性符合驼峰命名法,如A_COLUMN aColumn,可 以开启自动驼峰命名规则映射功能,mapUnderscoreToCamelCase=true
resultMap
resultMap自定义映射
映射文件
<?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="dao.EmployeeMapper2">
<!-- 使用resultMap进行自定义映射 -->
<!--
type:自定义规则的java类型
id:唯一id方便使用
-->
<resultMap type="dao.Employee" id="myEmp">
<!-- 指定主键的封装规则
id:定义主键会有底层优化
column:指定哪一列
property:指定对应的javaBean属性
-->
<id column="id" property="id"/>
<!-- 定义普通列封装规则 -->
<result column="last_name" property="lastName"/>
<!-- 其他不指定的列会自动封装,但是建议只要使用了result,就把所有的列的映射规则写上 -->
<result column="sex" property="sex"/>
<result column="email" property="email"/>
</resultMap>
<select id="getEmpById" resultMap="myEmp">
select * from tb2_employee where id=#{id}
</select>
<!-- 使用resultType进行自动映射
<select id="getEmpById" resultType="dao.Employee">
select * from tb2_employee where id=#{id}
</select>
-->
</mapper>
public interface EmployeeMapper2 {
public Employee getEmpById(Integer id);
}
@Test
public void test2() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper2 mapper = openSession.getMapper(EmployeeMapper2.class);
Employee employee = mapper.getEmpById(1);
System.out.println(employee);
}finally {
openSession.close();
}
}
resultMap的关联查询
场景一:
查询Employee的同时查询员工对应的部门
Employee===Department
一个员工有与之对应的部门信息
方式一:级联属性封装结果集(dept.id、dept.departmentName)
package dao;
public class Employee {
private Integer id;
private String lastName;
private String email;
private String sex;
private Department dept;
public Employee() {
super();
}
@Override
public String toString() {
return "Employee [id=" + id + ", name=" + lastName + ", email=" + email + ", sex=" + sex + "]";
}
public Employee(Integer id, String name, String email, String sex) {
super();
this.id = id;
this.lastName = name;
this.email = email;
this.sex = sex;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public Department getDept() {
return dept;
}
public void setDept(Department dept) {
this.dept = dept;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
}
package dao;
public class Department {
private Integer id;
private String departmentName;
@Override
public String toString() {
return "Department [id=" + id + ", departmentName=" + departmentName + "]";
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
}
package dao;
import dao.Employee;
public interface EmployeeMapper {
public Employee getEmpAndDept(Integer id);
}
<?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="dao.EmployeeMapper">
<!-- 场景一:
查询Employee的同时查询员工对应的部门
Employee===Department
一个员工有与之对应的部门信息
id last_name sex d_id did、dept_name(private String departmentName;)
-->
<!-- 方式一:
联合查询:级联属性封装结果集:dept.id
-->
<resultMap type="dao.Employee" id="myDifEmp">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="sex" property="sex"/>
<result column="did" property="dept.id"/>
<result column="dept_name" property="dept.departmentName"/>
</resultMap>
<!-- public Employee getEmpAndDept(Integer id); -->
<select id="getEmpAndDept" resultMap="myDifEmp">
select e.id id,e.last_name last_name,e.sex sex,e.d_id d_id,d.id did,d.dept_name dept_name
from tb2_employee e,tb2_dept d
where e.id=d.id and e.id=#{id}
</select>
</mapper>
<?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>
<properties resource="dbconfig.properties"></properties>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<!-- 添加映射文件的注册 -->
<mappers>
<mapper resource="dao/EmployeeMapper.xml"/>
</mappers>
</configuration>
package test;
import java.io.IOException;
import java.io.InputStream;
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 org.junit.jupiter.api.Test;
import dao.Employee;
import dao.EmployeeMapper;
public class TestEmpDaoImpl {
public SqlSessionFactory getSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
return sqlSessionFactory;
}
@Test
public void test() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee empAndDept = mapper.getEmpAndDept(1);
System.out.println(empAndDept);
System.out.println(empAndDept.getDept());
}finally {
openSession.close();
}
}
}
方式二:使用association来定义关联的单个对象的封装规则
<?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="dao.EmployeeMapper">
<!-- 方式二:使用association来定义关联的单个对象的封装规则 -->
<resultMap type="dao.Employee" id="myDifEmp2">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="sex" property="sex"/>
<association property="dept" javaType="dao.Department">
<id column="did" property="id"/>
<result column="dept_name" property="departmentName"/>
</association>
</resultMap>
<!-- public Employee getEmpAndDept(Integer id); -->
<select id="getEmpAndDept" resultMap="myDifEmp2">
select e.id id,e.last_name last_name,e.sex sex,e.d_id d_id,d.id did,d.dept_name dept_name
from tb2_employee e,tb2_dept d
where e.id=d.id and e.id=#{id}
</select>
</mapper>
resultMap_association 分步查询
public interface EmployeeMapper {
public Employee getEmpByIdStep(Integer id);
}
employeeMapper.xml
<!-- 使用association进行分布查询:
1、先按照员工id查询员工信息
2、根据员工信息中的d_id值去部门表查询部门信息
3、部门信息设置到员工中
-->
<!-- id last_name email sex d_id -->
<resultMap type="dao.Employee" id="myEmpByStep">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="sex" property="sex"/>
<!-- association定义关联对象的封装规则
property :指定关联的对象
select:表明当前属性是调用select指定的方法查处的结果
coulumn:指定那一列的值传给这个方法
流程:使用select指定的方法(传入column指定的这列参数的值)查处对象,并封装给property指定的属性
-->
<association property="dept" select="dao.DepartmentMapper.getDepById" column="d_id">
</association>
</resultMap>
<!-- public Employee getEmpByIdStep(Integer id); -->
<select id="getEmpByIdStep" resultMap="myEmpByStep">
select * from tb2_employee where id=#{id}
</select>
</mapper>
public interface DepartmentMapper {
public Department getDepById(Integer id);
}
<mapper namespace="dao.DepartmentMapper">
<!-- public Department getDepById(Integer id); -->
<select id="getDepById" resultType="dao.Department">
select id,dept_name departmentName from tb2_dept where id=#{id}
</select>
</mapper>
@Test
public void test() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee employee = mapper.getEmpByIdStep(2);
System.out.println(employee);
System.out.println(employee.getDept());
}finally {
openSession.close();
}
}
resultMap_关联查询&延迟加载
可以使用延迟加载来提升查询的效率
使用延迟加载:
Employee–>Dept
每次查询Employee对象的时候,都将一起查询出来
部门信息在使用到的时候再去查询,
分段查询的基础之上在全局配置文件中加上配置
<settings>
<!-- 开启延迟加载 -->
<setting name="lazyloadingEnabled" value="true"/>
<!-- 设置加载的数据是按需还是全部 -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
按需加载
resultMap_关联查询_collection定义关联集合封装规则
public interface DepartmentMapper {
public Department getDepById2(Integer id);
}
DepartmentMapper.xml
<!-- 场景二:
查询部门的时候将部门的信所有员工信息返回
-->
<!-- collection嵌套结果集的方式,定义关联的集合类型元素的封装规则 -->
<!-- public class Department {
private Integer id;
private String departmentName;
private List<Employee> emps;
did dept_name || eid last_name email sex
-->
<resultMap type="dao.Department" id="myDept">
<id column="did" property="id"/>
<result column="dept_name" property="departmentName"/>
<!-- collection定义关联集合类型的属性的封装规则
ofType:指定集合里面元素的类型
-->
<collection property="emps" ofType="dao.Employee">
<!-- 定义这个集合中元素的封装规则 -->
<id column="eid" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="sex" property="sex"/>
</collection>
</resultMap>
<select id="getDepById2" resultMap="myDept">
select d.id did,dept_name dept_name,e.id eid,e.last_name last_name,e.email email,e.sex sex
from tb2_dept d
left join tb2_employee e
on d.id=e.id
where d.id=#{id}
</select>
@Test
public void test2() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
Department department = mapper.getDepById2(1);
System.out.println(department);
System.out.println(department.getEmps());
}finally {
openSession.close();
}
}
resultMap_关联查询_collection分布查询&延迟加载
<!-- collection分布查询&延迟加载 -->
<!-- public class Department {
private Integer id;
private String departmentName;
private List<Employee> emps;
-->
<resultMap type="dao.Department" id="myDeptStep">
<id column="id" property="id"/>
<result column="dept_name" property="departmentName"/>
<collection property="emps" select="dao.EmployeeMapper.getEmpsByDeptId" column="id"></collection>
</resultMap>
<!-- public Department getDepByIdStep(Integer id); -->
<select id="getDepByIdStep" resultMap="myDeptStep">
select id,dept_name from tb2_dept where id=#{id}
</select>
<!-- public List<Employee> getEmpsByDeptId(Integer deptId); -->
<select id="getEmpsByDeptId" resultType="dao.Employee">
select * from tb2_employee where d_id=#{deptId}
</select>
public class Department {
private Integer id;
private String departmentName;
private List<Employee> emps;
public interface DepartmentMapper {
public Department getDepByIdStep(Integer id);
public class Employee {
private Integer id;
private String lastName;
private String email;
private String sex;
private Department dept;
public interface EmployeeMapper {
public List<Employee> getEmpsByDeptId(Integer deptId);
//测试
@Test
public void test2() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
Department department = mapper.getDepByIdStep(1);
System.out.println(department);
System.out.println(department.getEmps());
}finally {
openSession.close();
}
}
resultMap_分步查询多列值&fetchType
<!-- 扩展:多列的值传递过去
将多列的值封装map传递,
column=“{key1=column1,key2=column2}”
fetchType="lazy":表示使用延迟加载
lazy:延迟
eager:立即
-->
<resultMap type="dao.Department" id="myDeptStep">
<id column="id" property="id"/>
<result column="dept_name" property="departmentName"/>
<collection property="emps" select="dao.EmployeeMapper.getEmpsByDeptId" column="{deptId=id}" fetchType="lazy"></collection>
</resultMap>
resultMap_discriminator鉴别器
<!--<discriminator javaType=""></discriminator>
鉴别器:mybatis可以使用discriminator判断某列的值,然后根据某列的值改变封装行为
封装Employee:
如果查出来的是女生,就把部门信息查询出来,否则不查询
如果查询来的是男生,将last_name这一列的值赋值给email
-->
<resultMap type="dao.Employee" id="myEmpDis">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="sex" property="sex"/>
<!--
javaType:列值对应java类型
column:指定判定的列名
-->
<discriminator javaType="string" column="sex">
<!--女生。resultType:指定封装的结果类型 -->
<case value="0" resultType="dao.Employee">
<association property="dept" select="dao.DepartmentMapper.getDepById" column="d_id">
</association>
</case>
<!--男生, 将last_name这一列的值赋值给email-->
<case value="1" resultType="dao.Employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="last_name" property="email"/>
<result column="sex" property="sex"/>
</case>
</discriminator>
</resultMap>
<select id="getEmpByIdStep" resultMap="myEmpDis">
select * from tb2_employee where id=#{id}
</select>
@Test
public void test() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee employee = mapper.getEmpByIdStep(2);
System.out.println(employee);
System.out.println(employee.getDept());
}finally {
openSession.close();
}
}