一、select元素
- select标签元素是用来定义查询操作的。
-
id属性:唯一标识符,用来引用这条语句,需要和接口的方法名一致。
-
parameterType属性:参数类型,可以不传,mybatis会根据TypeHandler自动推断。
-
resultType属性:返回值类型,使用别名或者全类名,如果返回的是集合,定义集合中元素的类型。不能和resultMap同时使用。
select元素的属性:
二、resultType属性
- 说明:resultType属性是在select标签中,定义返回值的类型
- 若返回的类型是集合,需要定义集合中元素的类型,例如List和Map集合
示例1:使用select查询标签返回List集合
数据库表:
第一步:在sql映射文件对应的接口中添加方法
package com.csu.marden;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
public interface EmployeeMapper {
//模糊查询返回List集合
public List<Employee>getEmpByLastNameLike(String lastName);
//根据Map查询
public Employee getEmpByMap(Map<String,Object> map);
//根据id和lastName两个字段查询
public Employee getEmpByIdAndLastName(@Param("emp")Employee employee);
//根据id查询数据,并封装成Employee对象
public Employee getEmpById(Integer id);
//添加一个Employee对象
public void addEmp(Employee employee);
//修改一个Employee对象
public void updateEmp(Employee employee);
//根据id,删除一个Employee对象
public void deleteEmpById(Integer id);
}
第二步:在sql映射文件中,编写具体的sql查询语句
注意:select查询语句的返回结果是List集合时,resultType属性应该写集合中元素的类型
<?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.csu.marden.EmployeeMapper">
<!-- 返回List集合 -->
<!-- 如果返回值是一个集合,resultType应该写集合中元素的类型 -->
<select id="getEmpByLastNameLike" resultType="employee" >
select * from tbl_employee where last_name like #{lastName}
</select>
<!-- 多个参数使用Map传入的查询方法 -->
<select id="getEmpByMap" resultType="employee">
select * from tbl_employee where id=#{id} and last_name=#{lastName}
</select>
<!-- 多个参数查询方法 -->
<select id="getEmpByIdAndLastName" resultType="employee">
select * from tbl_employee where id = #{emp.id} and last_name=#{emp.lastName}
</select>
<!-- 单个参数查询方法 -->
<select id="getEmpById" resultType="employee" >
select * from tbl_employee where id = #{id}
</select>
<!--插入方法 -->
<insert id="addEmp" parameterType="employee" useGeneratedKeys="true" keyProperty="id" >
insert into tbl_employee (last_name,email,gender) values (#{lastName},#{email},#{gender})
</insert>
<!-- 更新方法 -->
<update id="updateEmp" parameterType="employee" >
update tbl_employee set last_name=#{lastName},email=#{email},gender=#{gender} where id=#{id}
</update>
<!-- 删除方法 -->
<delete id="deleteEmpById" parameterType="integer">
delete from tbl_employee where id=#{id}
</delete>
</mapper>
第三步:编写测试类
package com.csu.marden;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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 Test {
public static void main(String[] args) throws IOException {
//1.根据xml配置文件(全局配置文件,即mybatis-config.xml)创建一个SqlSessionFactory对象
String resources="mybatis-config.xml";
InputStream inputStream=Resources.getResourceAsStream(resources);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//2.根据SqlSessionFactory创建SqlSession,每个SqlSession代表和数据库的一次会话
SqlSession openSession=sqlSessionFactory.openSession();
try{
//3.获取接口的实现类对象(mybatis会为接口自动创建一个代理对象,代理对象会执行增删改查)
EmployeeMapper mapper=openSession.getMapper(EmployeeMapper.class);
//4.通过接口的实现类对象调用接口的查询方法
List<Employee> result=mapper.getEmpByLastNameLike("%e%");
for(Employee employee : result){
System.out.println(employee);
}
openSession.commit();
}finally{
openSession.close();
}
}
}
示例2:使用select查询标签返回一条记录的Map集合,key值对应列名,value对应相应的取值
第一步:在sql映射文件对应的接口中添加方法
package com.csu.marden;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
public interface EmployeeMapper {
//返回一条记录的map集合,key就是列名,value就是对应的取值
public Map<String,Object> getEmpByIdReturnMap(Integer id);
//模糊查询返回List集合
public List<Employee>getEmpByLastNameLike(String lastName);
//根据Map查询
public Employee getEmpByMap(Map<String,Object> map);
//根据id和lastName两个字段查询
public Employee getEmpByIdAndLastName(@Param("emp")Employee employee);
//根据id查询数据,并封装成Employee对象
public Employee getEmpById(Integer id);
//添加一个Employee对象
public void addEmp(Employee employee);
//修改一个Employee对象
public void updateEmp(Employee employee);
//根据id,删除一个Employee对象
public void deleteEmpById(Integer id);
}
第二步:在sql映射文件中,编写具体的sql查询语句
注意:select查询语句的返回结果是Map时,resultType属性写Map类型(MyBatis对Map类型进行了别名化)
<?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.csu.marden.EmployeeMapper">
<!-- 返回一条记录的Map集合,resultType应该写Map类型 -->
<select id="getEmpByIdReturnMap" resultType="map">
select * from tbl_employee where id=#{id}
</select>
<!-- 返回List集合 -->
<!-- 如果返回值是一个集合,resultType应该写集合中元素的类型 -->
<select id="getEmpByLastNameLike" resultType="employee" >
select * from tbl_employee where last_name like #{lastName}
</select>
<!-- 多个参数使用Map传入的查询方法 -->
<select id="getEmpByMap" resultType="employee">
select * from tbl_employee where id=#{id} and last_name=#{lastName}
</select>
<!-- 多个参数查询方法 -->
<select id="getEmpByIdAndLastName" resultType="employee">
select * from tbl_employee where id = #{emp.id} and last_name=#{emp.lastName}
</select>
<!-- 单个参数查询方法 -->
<select id="getEmpById" resultType="employee" >
select * from tbl_employee where id = #{id}
</select>
<!--插入方法 -->
<insert id="addEmp" parameterType="employee" useGeneratedKeys="true" keyProperty="id" >
insert into tbl_employee (last_name,email,gender) values (#{lastName},#{email},#{gender})
</insert>
<!-- 更新方法 -->
<update id="updateEmp" parameterType="employee" >
update tbl_employee set last_name=#{lastName},email=#{email},gender=#{gender} where id=#{id}
</update>
<!-- 删除方法 -->
<delete id="deleteEmpById" parameterType="integer">
delete from tbl_employee where id=#{id}
</delete>
</mapper>
第三步:编写测试类
package com.csu.marden;
import java.io.IOException;
import java.io.InputStream;
import java.util.Map;
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 Test {
public static void main(String[] args) throws IOException {
//1.根据xml配置文件(全局配置文件,即mybatis-config.xml)创建一个SqlSessionFactory对象
String resources="mybatis-config.xml";
InputStream inputStream=Resources.getResourceAsStream(resources);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//2.根据SqlSessionFactory创建SqlSession,每个SqlSession代表和数据库的一次会话
SqlSession openSession=sqlSessionFactory.openSession();
try{
//3.获取接口的实现类对象(mybatis会为接口自动创建一个代理对象,代理对象会执行增删改查)
EmployeeMapper mapper=openSession.getMapper(EmployeeMapper.class);
//4.通过接口的实现类对象调用接口的查询方法
Map<String,Object> result=mapper.getEmpByIdReturnMap(1);
System.out.println(result);
openSession.commit();
}finally{
openSession.close();
}
}
}
示例3:使用select查询标签返回多条记录的Map集合,Map<Integer,Employee>,key值是这条记录的主键,value值是记录封装后的JavaBean
第一步:在sql映射文件对应的接口中添加方法
注意:在接口中定义方式时,使用@MapKey注解指定返回的Map集合的key值
package com.csu.marden;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;
public interface EmployeeMapper {
//返回多条记录的map集合,Map<Integer,Employee>,其中key值对应这条记录的主键,value值对应这条记录封装后的JavaBean
//使用@MapKey("id")注解,告诉mybatis使用id属性作为Map集合的主键
@MapKey("id")
public Map<Integer,Employee> getEmpByIdLastNameLikeReturnIdMap(String lastName);
//返回多条记录的map集合,Map<String,Employee>,其中key值对应这条记录的主键,value值对应这条记录封装后的JavaBean
//使用@MapKey("lastName")注解,告诉mybatis使用lastName属性作为Map集合的主键
@MapKey("lastName")
public Map<String,Employee> getEmpByIdLastNameLikeReturnLastNameMap(String lastName);
//返回一条记录的map集合,key就是列名,value就是对应的取值
public Map<String,Object> getEmpByIdReturnMap(Integer id);
//模糊查询返回List集合
public List<Employee>getEmpByLastNameLike(String lastName);
//根据Map查询
public Employee getEmpByMap(Map<String,Object> map);
//根据id和lastName两个字段查询
public Employee getEmpByIdAndLastName(@Param("emp")Employee employee);
//根据id查询数据,并封装成Employee对象
public Employee getEmpById(Integer id);
//添加一个Employee对象
public void addEmp(Employee employee);
//修改一个Employee对象
public void updateEmp(Employee employee);
//根据id,删除一个Employee对象
public void deleteEmpById(Integer id);
}
第二步:在sql映射文件中,编写具体的sql查询语句
注意:select查询语句的返回结果是Map时,resultType属性写Map类型(MyBatis对Map类型进行了别名化)
<?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.csu.marden.EmployeeMapper">
<!-- 返回多条记录的Map集合 ,其中id作为map的主键-->
<select id="getEmpByIdLastNameLikeReturnIdMap" resultType="employee">
select * from tbl_employee where last_name like #{lastName}
</select>
<!-- 返回多条记录的Map集合 ,其中lastName作为map的主键-->
<select id="getEmpByIdLastNameLikeReturnLastNameMap" resultType="employee">
select * from tbl_employee where last_name like #{lastName}
</select>
<!-- 返回一条记录的Map集合,resultType应该写Map类型 -->
<select id="getEmpByIdReturnMap" resultType="map">
select * from tbl_employee where id=#{id}
</select>
<!-- 返回List集合 -->
<!-- 如果返回值是一个集合,resultType应该写集合中元素的类型 -->
<select id="getEmpByLastNameLike" resultType="employee" >
select * from tbl_employee where last_name like #{lastName}
</select>
<!-- 多个参数使用Map传入的查询方法 -->
<select id="getEmpByMap" resultType="employee">
select * from tbl_employee where id=#{id} and last_name=#{lastName}
</select>
<!-- 多个参数查询方法 -->
<select id="getEmpByIdAndLastName" resultType="employee">
select * from tbl_employee where id = #{emp.id} and last_name=#{emp.lastName}
</select>
<!-- 单个参数查询方法 -->
<select id="getEmpById" resultType="employee" >
select * from tbl_employee where id = #{id}
</select>
<!--插入方法 -->
<insert id="addEmp" parameterType="employee" useGeneratedKeys="true" keyProperty="id" >
insert into tbl_employee (last_name,email,gender) values (#{lastName},#{email},#{gender})
</insert>
<!-- 更新方法 -->
<update id="updateEmp" parameterType="employee" >
update tbl_employee set last_name=#{lastName},email=#{email},gender=#{gender} where id=#{id}
</update>
<!-- 删除方法 -->
<delete id="deleteEmpById" parameterType="integer">
delete from tbl_employee where id=#{id}
</delete>
</mapper>
第三步:编写测试类
package com.csu.marden;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
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 Test {
public static void main(String[] args) throws IOException {
//1.根据xml配置文件(全局配置文件,即mybatis-config.xml)创建一个SqlSessionFactory对象
String resources="mybatis-config.xml";
InputStream inputStream=Resources.getResourceAsStream(resources);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//2.根据SqlSessionFactory创建SqlSession,每个SqlSession代表和数据库的一次会话
SqlSession openSession=sqlSessionFactory.openSession();
try{
//3.获取接口的实现类对象(mybatis会为接口自动创建一个代理对象,代理对象会执行增删改查)
EmployeeMapper mapper=openSession.getMapper(EmployeeMapper.class);
//4.通过接口的实现类对象调用接口的查询方法
//以id为key值,Employee对象为value值得map集合
Map<Integer,Employee> result1=mapper.getEmpByIdLastNameLikeReturnIdMap("%e%");
Set<Integer> set1=result1.keySet();
for(Iterator<Integer> iter=set1.iterator();iter.hasNext();){
Integer temp=iter.next();
System.out.println(temp+":"+result1.get(temp));
}
System.out.println("--------------------------------------------------------");
//以lastName为key值,Employee对象为value值得map集合
Map<String,Employee> result2=mapper.getEmpByIdLastNameLikeReturnLastNameMap("%e%");
Set<String> set2=result2.keySet();
for(Iterator<String> iter=set2.iterator();iter.hasNext();){
String temp=iter.next();
System.out.println(temp+":"+result2.get(temp));
}
openSession.commit();
}finally{
openSession.close();
}
}
}
三、resultMap属性
当数据表的列名与JavaBean属性名不一致时,自动映射失败。解决方案:
- sql语句使用别名,如数据表中列名为last_name,JavaBean中属性名为lastName,使用select last_name lastName from tbl_employee where id=#{id}
- 若列名与JavaBean属性名符合驼峰命名,则开启驼峰命名法。如A_COLUMN===>aColumn,可以开启自动驼峰命名规则映射功能,mapUnderscoreToCamelCase=true
- 使用resultMap自定义结果集
resultMap(自定义结果映射规则):
- 作用:resultMap标签用来自定义某个JavaBean的封装规则(数据库中查询到的数据集与JavaBean做映射)
-
id属性:唯一标识一个resultMap映射,方便后续引用
-
type属性:自定义规则的JavaBean类型(可以使用JavaBean的全类名,也可以使用别名)
-
id子标签:指定主键列的封装规则(数据表的主键列与JavaBean的属性做映射)
-
result子标签:指定普通列的封装规则(数据表的普通列与JavaBean的属性做映射)
-
子标签中的column属性:数据表的列名
-
子标签中的property属性:JavaBean的属性名
示例1:使用resultMap自定义结果集的映射
第一步:在接口中定义查询方法
package com.csu.marden;
public interface EmployeeMapperPlus {
public Employee getEmpById(Integer id);
}
第二步:在sql映射文件中使用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="com.csu.marden.EmployeeMapperPlus">
<!-- resultMap标签用来自定义某个JavaBean的封装规则 -->
<!-- type属性:自定义规则的Java类型 -->
<!-- id属性:唯一标识一个resultMap映射,方便后续引用-->
<resultMap type="employee" id="MyEmp">
<!-- id子标签指定主键列的封装规则 -->
<!-- column属性指定数据表的哪一列;property属性指定对应的JavaBean属性 -->
<id column="id" property="id"/>
<!-- result子标签指定普通列的封装规则 -->
<!-- column属性指定数据表的哪一列;property属性指定对应的JavaBean属性 -->
<result column="last_name" property="lastName" />
<!-- 其他不指定的列会自动封装,我们要写resultMap就把全部的映射规则都写上 -->
<result column="gender" property="gender"/>
<result column="email" property="email"/>
</resultMap>
<select id="getEmpById" resultMap="MyEmp">
select * from tbl_employee where id=#{id}
</select>
</mapper>
第三步:编写测试类
package com.csu.marden;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
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 Test {
public static void main(String[] args) throws IOException {
//1.根据xml配置文件(全局配置文件,即mybatis-config.xml)创建一个SqlSessionFactory对象
String resources="mybatis-config.xml";
InputStream inputStream=Resources.getResourceAsStream(resources);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//2.根据SqlSessionFactory创建SqlSession,每个SqlSession代表和数据库的一次会话
SqlSession openSession=sqlSessionFactory.openSession();
try{
//3.获取接口的实现类对象(mybatis会为接口自动创建一个代理对象,代理对象会执行增删改查)
EmployeeMapperPlus mapper=openSession.getMapper(EmployeeMapperPlus.class);
//4.通过接口的实现类对象调用接口的查询方法
Employee result=mapper.getEmpById(1);
System.out.println(result);
openSession.commit();
}finally{
openSession.close();
}
}
}
示例2:联合查询-----使用级联属性封装结果集
场景:查询Employee表的同时查询员工对应的部门信息,每个员工都有与之对应的部门信息
数据表:
部门表:
员工表:(d_id是外键,关联部门表的id列)
部门类:
package com.csu.marden;
public class Department {
private Integer id;
private String 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;
}
@Override
public String toString() {
return "Department [id=" + id + ", departmentName=" + departmentName + "]";
}
}
员工类:
package com.csu.marden;
public class Employee {
private Integer id;
private String lastName;
private String email;
private String gender;
private Department dept;
public Employee() {
}
public Employee(Integer id, String lastName, String email, String gender) {
this.id = id;
this.lastName = lastName;
this.email = email;
this.gender = gender;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Department getDept() {
return dept;
}
public void setDept(Department dept) {
this.dept = dept;
}
@Override
public String toString() {
return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
}
}
第一步:在接口中定义关联查询的方法
package com.csu.marden;
public interface EmployeeMapperPlus {
//使用resultMap自定义结果映射
public Employee getEmpById(Integer id);
//关联查询
public Employee getEmpAndDeptById(Integer id);
}
第二步:在sql映射文件中,定义查询语句和resultMap自定义结果映射
注意:由于resultMap封装的是Employee类的映射规则,在子标签中定义数据表的列于JavaBean的属性映射规则。例如,封装级联属性时,数据表中的did列对应Employee类中dept属性的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="com.csu.marden.EmployeeMapperPlus">
<!-- resultMap标签用来自定义某个JavaBean的封装规则 -->
<!-- type属性:自定义规则的Java类型 -->
<!-- id属性:唯一标识一个resultMap映射,方便后续引用-->
<resultMap type="employee" id="MyEmp">
<!-- id子标签指定主键列的封装规则 -->
<!-- column属性指定数据表的哪一列;property属性指定对应的JavaBean属性 -->
<id column="id" property="id"/>
<!-- result子标签指定普通列的封装规则 -->
<!-- column属性指定数据表的哪一列;property属性指定对应的JavaBean属性 -->
<result column="last_name" property="lastName" />
<!-- 其他不指定的列会自动封装,我们要写resultMap就把全部的映射规则都写上 -->
<result column="gender" property="gender"/>
<result column="email" property="email"/>
</resultMap>
<select id="getEmpById" resultMap="MyEmp">
select * from tbl_employee where id=#{id}
</select>
<!-- 联合查询:使用级联属性封装结果集 -->
<resultMap type="employee" id="MyEmpPlus">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
<!-- 封装级联属性 -->
<result column="did" property="dept.id"/>
<result column="dept_name" property="dept.departmentName"/>
</resultMap>
<select id="getEmpAndDeptById" resultMap="MyEmpPlus">
select e.id id, e.last_name last_name, e.gender gender, e.email email, e.d_id did, d.dept_name dept_name
from tbl_employee e,tbl_dept d where e.d_id=d.id and e.id=#{id}
</select>
</mapper>
第三步:编写测试类
package com.csu.marden;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
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 Test {
public static void main(String[] args) throws IOException {
//1.根据xml配置文件(全局配置文件,即mybatis-config.xml)创建一个SqlSessionFactory对象
String resources="mybatis-config.xml";
InputStream inputStream=Resources.getResourceAsStream(resources);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//2.根据SqlSessionFactory创建SqlSession,每个SqlSession代表和数据库的一次会话
SqlSession openSession=sqlSessionFactory.openSession();
try{
//3.获取接口的实现类对象(mybatis会为接口自动创建一个代理对象,代理对象会执行增删改查)
EmployeeMapperPlus mapper=openSession.getMapper(EmployeeMapperPlus.class);
//4.通过接口的实现类对象调用接口的查询方法
Employee result=mapper.getEmpAndDeptById(1);
System.out.println(result);
System.out.println(result.getDept());
openSession.commit();
}finally{
openSession.close();
}
}
}
四、association的理解
作用:
- JavaBean中的属性可能会是一个对象,association用来定义关联查询的对象封装规则。
mybatis中association的两种形式:
- 通过association进行结果集的嵌套(将查询到的数据封装成一个JavaBean对象,然后association元素再引用这个对象作为另一个JavaBean对象的属性)
- 通过association进行分步查询/分段查询(使用一条单独的select语句来查询关联的JavaBean对象,然后在association元素中引用此select语句)
示例1:联合查询-----使用association子标签关联另一个对象的封装规则(嵌套结果集)
association子标签:
- 作用:指定当前封装的JavaBean对象联合的另一个JavaBean对象的封装规则
-
property属性:指定第一个JavaBean对象中的哪个属性是联合的对象
-
javaType属性:指定这个属性对象的类型(不可省略)
-
id子标签:指定主键列的封装规则(数据表的主键列与JavaBean的属性做映射)
-
result子标签:指定普通列的封装规则(数据表的普通列与JavaBean的属性做映射)
第一步:在接口类中编写联合查询的方法
package com.csu.marden;
public interface EmployeeMapperPlus {
//使用resultMap自定义结果映射
public Employee getEmpById(Integer id);
//关联查询
public Employee getEmpAndDeptById(Integer id);
}
第二步:在sql映射文件中,编写联合查询语句和resultMap结果映射规则(使用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="com.csu.marden.EmployeeMapperPlus">
<!-- resultMap标签用来自定义某个JavaBean的封装规则 -->
<!-- type属性:自定义规则的Java类型 -->
<!-- id属性:唯一标识一个resultMap映射,方便后续引用-->
<resultMap type="employee" id="MyEmp">
<!-- id子标签指定主键列的封装规则 -->
<!-- column属性指定数据表的哪一列;property属性指定对应的JavaBean属性 -->
<id column="id" property="id"/>
<!-- result子标签指定普通列的封装规则 -->
<!-- column属性指定数据表的哪一列;property属性指定对应的JavaBean属性 -->
<result column="last_name" property="lastName" />
<!-- 其他不指定的列会自动封装,我们要写resultMap就把全部的映射规则都写上 -->
<result column="gender" property="gender"/>
<result column="email" property="email"/>
</resultMap>
<select id="getEmpById" resultMap="MyEmp">
select * from tbl_employee where id=#{id}
</select>
<!-- 联合查询:使用级联属性封装结果集 -->
<resultMap type="employee" id="MyEmpPlus">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
<!-- 封装级联属性 -->
<result column="did" property="dept.id"/>
<result column="dept_name" property="dept.departmentName"/>
</resultMap>
<resultMap type="employee" id="MyEmpPlus1">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
<!-- 指定Employee对象联合的Department对象 -->
<association property="dept" javaType="department">
<id column="id" property="id"/>
<result column="dept_name" property="departmentName"/>
</association>
</resultMap>
<select id="getEmpAndDeptById" resultMap="MyEmpPlus1">
select e.id id, e.last_name last_name, e.gender gender, e.email email, e.d_id did, d.dept_name dept_name
from tbl_employee e,tbl_dept d where e.d_id=d.id and e.id=#{id}
</select>
</mapper>
第三步:编写测试类
示例2:联合查询-----使用association子标签进行分步查询
流程:
- 先按照员工id查询员工信息
- 根据查询出的员工信息中的d_id值查询部门表中的部门信息
- 将查出的部门信息添加到员工信息中
说明:
- 查询时,首先根据id属性查出全部Employee对象的属性,随后根据Employee对象的d_id属性,在部门表中查询部门信息。使用assocation子标签后,property属性指定当前查询的Department对象属于Employee对象的哪个属性,select属性指定需要调用的查询方法,columu属性指定调用查询方法的传入参数。
分步查询的好处:
- 可以组合已有的方法完成复杂的功能
- 可以使用延迟加载
assocation子标签:
- property属性:指定当前封装的Department对象属于Employee对象的哪个属性。
- select属性:表明当前的属性是调用select指定的方法查出的结果(填写指定的查询方法)。
- column属性:指定将哪一列的值传给select指定的方法。
项目结构:
部门类:
package com.csu.marden;
public class Department {
private Integer id;
private String 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;
}
@Override
public String toString() {
return "Department [id=" + id + ", departmentName=" + departmentName + "]";
}
}
员工类:
package com.csu.marden;
public class Employee {
private Integer id;
private String lastName;
private String email;
private String gender;
private Department dept;
public Employee() {
}
public Employee(Integer id, String lastName, String email, String gender) {
this.id = id;
this.lastName = lastName;
this.email = email;
this.gender = gender;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Department getDept() {
return dept;
}
public void setDept(Department dept) {
this.dept = dept;
}
@Override
public String toString() {
return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
}
}
部门类映射接口:
package com.csu.marden;
public interface DepartmentMapper {
//根据id查询部门信息
public Department getDeptById(Integer id);
}
员工类映射接口:
package com.csu.marden;
public interface EmployeeMapperPlus {
//使用resultMap自定义结果映射
public Employee getEmpById(Integer id);
//关联查询
public Employee getEmpAndDeptById(Integer id);
//关联查询(使用associatino分步查询)
public Employee getEmpByIdStep(Integer id);
}
全局配置文件:(mybatis-config.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>
<properties resource="dbconfig.properties"></properties>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<typeAliases>
<typeAlias type="com.csu.marden.Employee" />
<typeAlias type="com.csu.marden.Department"/>
</typeAliases>
<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>
<!-- 将sql映射文件(EmployeeMapper.xml)注册到全局配置文件(mybatis-config.xml)中 -->
<mappers>
<mapper resource="EmployeeMapper.xml"/>
<mapper resource="EmployeeMapperPlus.xml" />
<mapper resource="DepartmentMapper.xml"/>
</mappers>
</configuration>
部门类对应的sql映射文件:
<?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.csu.marden.DepartmentMapper">
<select id="getDeptById" resultType="department">
select id,dept_name departmentName from tbl_dept where id=#{id}
</select>
</mapper>
员工类对应的sql映射文件:
<?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.csu.marden.EmployeeMapperPlus">
<!-- resultMap标签用来自定义某个JavaBean的封装规则 -->
<!-- type属性:自定义规则的Java类型 -->
<!-- id属性:唯一标识一个resultMap映射,方便后续引用-->
<resultMap type="employee" id="MyEmp">
<!-- id子标签指定主键列的封装规则 -->
<!-- column属性指定数据表的哪一列;property属性指定对应的JavaBean属性 -->
<id column="id" property="id"/>
<!-- result子标签指定普通列的封装规则 -->
<!-- column属性指定数据表的哪一列;property属性指定对应的JavaBean属性 -->
<result column="last_name" property="lastName" />
<!-- 其他不指定的列会自动封装,我们要写resultMap就把全部的映射规则都写上 -->
<result column="gender" property="gender"/>
<result column="email" property="email"/>
</resultMap>
<select id="getEmpById" resultMap="MyEmp">
select * from tbl_employee where id=#{id}
</select>
<!-- 联合查询:使用级联属性封装结果集 -->
<resultMap type="employee" id="MyEmpPlus">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
<!-- 封装级联属性 -->
<result column="did" property="dept.id"/>
<result column="dept_name" property="dept.departmentName"/>
</resultMap>
<resultMap type="employee" id="MyEmpPlus1">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
<!-- 指定Employee对象联合的Department对象 -->
<association property="dept" javaType="department">
<id column="id" property="id"/>
<result column="dept_name" property="departmentName"/>
</association>
</resultMap>
<select id="getEmpAndDeptById" resultMap="MyEmpPlus1">
select e.id id, e.last_name last_name, e.gender gender, e.email email, e.d_id did, d.dept_name dept_name
from tbl_employee e,tbl_dept d where e.d_id=d.id and e.id=#{id}
</select>
<!-- 使用association进行分步查询 -->
<!-- 1.先按照员工id查询员工信息 -->
<!-- 2.根据查询员工信息中的d_id值取部门表中查出部门信息 -->
<!-- 3.将查出的部门信息添加到员工信息中 -->
<resultMap type="employee" id="MyEmpByStep">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
<!-- 使用association子标签定义关联的另一个JavaBean的封装规则 -->
<!-- 流程:使用select指定的方法(传入column指定的这列参数的值)查出对象,并封装给property制定的属性 -->
<!-- select属性:表明当前的属性是调用select指定的方法查出的结果 -->
<!-- column属性:指定将哪一列的值传给这个方法 -->
<association property="dept"
select="com.csu.marden.DepartmentMapper.getDeptById" column="d_id"></association>
</resultMap>
<select id="getEmpByIdStep" resultMap="MyEmpByStep" >
select * from tbl_employee where id=#{id}
</select>
</mapper>
测试类:
package com.csu.marden;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
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 Test {
public static void main(String[] args) throws IOException {
//1.根据xml配置文件(全局配置文件,即mybatis-config.xml)创建一个SqlSessionFactory对象
String resources="mybatis-config.xml";
InputStream inputStream=Resources.getResourceAsStream(resources);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//2.根据SqlSessionFactory创建SqlSession,每个SqlSession代表和数据库的一次会话
SqlSession openSession=sqlSessionFactory.openSession();
try{
//3.获取接口的实现类对象(mybatis会为接口自动创建一个代理对象,代理对象会执行增删改查)
EmployeeMapperPlus mapper1=openSession.getMapper(EmployeeMapperPlus.class);
DepartmentMapper mapper2=openSession.getMapper(DepartmentMapper.class);
//4.通过接口的实现类对象调用接口的查询方法
Employee result=mapper1.getEmpByIdStep(1);
System.out.println(result);
System.out.println(result.getDept());
}finally{
openSession.close();
}
}
}
示例3:联合查询-----使用association子标签进行分步查询,实现延迟加载(按需加载)
背景:
- 示例2中,每次查询Employee对象的时候,Department对象也将一起查询出来。我们需要将部门信息延迟加载,即部门信息在我们使用的时候再去查询。
使用:
- 在上述分步查询的基础上,在全局配置文件中增加两个配置
- <setting name="lazyLoadingEnabled" value="true"/>
- <setting name="aggressiveLazyLoading" value="false"/>
五、collection的理解
- 作用:定义关联集合类型的属性的封装规则。
-
property属性:指定当前集合类型属于JavaBean的哪个元素
-
ofType属性:指定集合里面元素的类型
场景:查询Department表的同时查询部门对应的所有员工信息,每个部门对应多个员工信息
数据表:
部门表:
员工表:
员工类:
package com.csu.marden;
public class Employee {
private Integer id;
private String lastName;
private String email;
private String gender;
private Department dept;
public Employee() {
}
public Employee(Integer id, String lastName, String email, String gender) {
this.id = id;
this.lastName = lastName;
this.email = email;
this.gender = gender;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Department getDept() {
return dept;
}
public void setDept(Department dept) {
this.dept = dept;
}
@Override
public String toString() {
return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
}
}
部门类:
package com.csu.marden;
import java.util.List;
public class Department {
private Integer id;
private String departmentName;
private List<Employee> emps;
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;
}
public List<Employee> getEmps() {
return emps;
}
public void setEmps(List<Employee> emps) {
this.emps = emps;
}
@Override
public String toString() {
return "Department [id=" + id + ", departmentName=" + departmentName + "]";
}
}
示例1:使用collection标签嵌套结果集的方式,定义关联的集合类型元素的封装规则
第一步:在部门类对应的接口中定义查询方法
package com.csu.marden;
public interface DepartmentMapper {
//根据id查询部门信息
public Department getDeptById(Integer id);
//根据id查询部门信息,同时获取部门对应的员工信息
public Department getDeptByIdPlus(Integer id);
}
第二步:在部门类接口对应的sql映射文件中,编写sql语句,以及结果集的封装规则
<?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.csu.marden.DepartmentMapper">
<select id="getDeptById" resultType="department">
select id,dept_name departmentName from tbl_dept where id=#{id}
</select>
<resultMap type="department" id="MyDept">
<id column="did" property="id"/>
<result column="dept_name" property="departmentName"/>
<!-- collection属性定义关联的集合类型的属性的封装规则 -->
<!-- ofType属性:指定集合里面元素的类型 -->
<collection property="emps" ofType="employee">
<!-- 定义这个集合中元素的封装规则 -->
<id column="eid" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
</collection>
</resultMap>
<select id="getDeptByIdPlus" resultMap="MyDept">
select d.id did, d.dept_name dept_name, e.id eid, e.last_name last_name, e.gender gender, e.email email
from tbl_dept d
left join tbl_employee e
on d.id=e.d_id
where d.id=#{id}
</select>
</mapper>
第三步:编写测试类
package com.csu.marden;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
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 Test {
public static void main(String[] args) throws IOException {
//1.根据xml配置文件(全局配置文件,即mybatis-config.xml)创建一个SqlSessionFactory对象
String resources="mybatis-config.xml";
InputStream inputStream=Resources.getResourceAsStream(resources);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//2.根据SqlSessionFactory创建SqlSession,每个SqlSession代表和数据库的一次会话
SqlSession openSession=sqlSessionFactory.openSession();
try{
//3.获取接口的实现类对象(mybatis会为接口自动创建一个代理对象,代理对象会执行增删改查)
EmployeeMapperPlus mapper1=openSession.getMapper(EmployeeMapperPlus.class);
DepartmentMapper mapper2=openSession.getMapper(DepartmentMapper.class);
//4.通过接口的实现类对象调用接口的查询方法
Department result=mapper2.getDeptByIdPlus(1);
System.out.println(result);
System.out.println(result.getEmps());
}finally{
openSession.close();
}
}
}
示例2:使用collection标签分步查询
第一步:在部门类对应的接口中定义查询方法
package com.csu.marden;
public interface DepartmentMapper {
//根据id查询部门信息
public Department getDeptById(Integer id);
//根据id查询部门信息,同时获取部门对应的员工信息
public Department getDeptByIdPlus(Integer id);
//分步查询,使用id查询部门信息,以及对应的员工信息
public Department getDepByIdStep(Integer deptId);
}
第二步:在员工类对应的接口中定义查询方法
package com.csu.marden;
import java.util.List;
public interface EmployeeMapperPlus {
//使用resultMap自定义结果映射
public Employee getEmpById(Integer id);
//关联查询
public Employee getEmpAndDeptById(Integer id);
//关联查询(使用associatino分步查询)
public Employee getEmpByIdStep(Integer id);
//根据部门id查询员工信息
public List<Employee> getEmpsByDeptId(Integer id);
}
第三步:在员工类接口对应的sql映射文件中定义查询语句
<?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.csu.marden.EmployeeMapperPlus">
<!-- resultMap标签用来自定义某个JavaBean的封装规则 -->
<!-- type属性:自定义规则的Java类型 -->
<!-- id属性:唯一标识一个resultMap映射,方便后续引用-->
<resultMap type="employee" id="MyEmp">
<!-- id子标签指定主键列的封装规则 -->
<!-- column属性指定数据表的哪一列;property属性指定对应的JavaBean属性 -->
<id column="id" property="id"/>
<!-- result子标签指定普通列的封装规则 -->
<!-- column属性指定数据表的哪一列;property属性指定对应的JavaBean属性 -->
<result column="last_name" property="lastName" />
<!-- 其他不指定的列会自动封装,我们要写resultMap就把全部的映射规则都写上 -->
<result column="gender" property="gender"/>
<result column="email" property="email"/>
</resultMap>
<select id="getEmpById" resultMap="MyEmp">
select * from tbl_employee where id=#{id}
</select>
<!-- 联合查询:使用级联属性封装结果集 -->
<resultMap type="employee" id="MyEmpPlus">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
<!-- 封装级联属性 -->
<result column="did" property="dept.id"/>
<result column="dept_name" property="dept.departmentName"/>
</resultMap>
<resultMap type="employee" id="MyEmpPlus1">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
<!-- 指定Employee对象联合的Department对象 -->
<association property="dept" javaType="department">
<id column="id" property="id"/>
<result column="dept_name" property="departmentName"/>
</association>
</resultMap>
<select id="getEmpAndDeptById" resultMap="MyEmpPlus1">
select e.id id, e.last_name last_name, e.gender gender, e.email email, e.d_id did, d.dept_name dept_name
from tbl_employee e,tbl_dept d where e.d_id=d.id and e.id=#{id}
</select>
<!-- 使用association进行分步查询 -->
<!-- 1.先按照员工id查询员工信息 -->
<!-- 2.根据查询员工信息中的d_id值取部门表中查出部门信息 -->
<!-- 3.将查出的部门信息添加到员工信息中 -->
<resultMap type="employee" id="MyEmpByStep">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
<!-- 使用association子标签定义关联的另一个JavaBean的封装规则 -->
<!-- 流程:使用select指定的方法(传入column指定的这列参数的值)查出对象,并封装给property指定的属性 -->
<!-- select属性:表明当前的属性是调用select指定的方法查出的结果 -->
<!-- column属性:指定将哪一列的值传给这个方法 -->
<association property="dept"
select="com.csu.marden.DepartmentMapper.getDeptById" column="d_id"></association>
</resultMap>
<select id="getEmpByIdStep" resultMap="MyEmpByStep" >
select * from tbl_employee where id=#{id}
</select>
<!-- 使用collection分段查询 -->
<!-- 由于返回的是List类型的数据,所以resultType中写List中元素的类型 -->
<select id="getEmpsByDeptId" resultType="Employee">
select * from tbl_employee where d_id=#{deptId}
</select>
</mapper>
第四步:在部门类接口对应的sql映射文件中定义查询语句,以及自定义结果封装规则
<?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.csu.marden.DepartmentMapper">
<select id="getDeptById" resultType="department">
select id,dept_name departmentName from tbl_dept where id=#{id}
</select>
<!-- 使用collection标签嵌套结果集 -->
<resultMap type="department" id="MyDept">
<id column="did" property="id"/>
<result column="dept_name" property="departmentName"/>
<!-- collection属性定义关联的集合类型的属性的封装规则 -->
<!-- ofType属性:指定集合里面元素的类型 -->
<collection property="emps" ofType="employee">
<!-- 定义这个集合中元素的封装规则 -->
<id column="eid" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
</collection>
</resultMap>
<select id="getDeptByIdPlus" resultMap="MyDept">
select d.id did, d.dept_name dept_name, e.id eid, e.last_name last_name, e.gender gender, e.email email
from tbl_dept d
left join tbl_employee e
on d.id=e.d_id
where d.id=#{id}
</select>
<!-- 使用collection标签分步查询 -->
<resultMap type="department" id="MyDeptStep">
<id column="id" property="id"/>
<result column="dept_name" property="departmentName"/>
<collection property="emps"
select="com.csu.marden.EmployeeMapperPlus.getEmpsByDeptId" column="id" ></collection>
</resultMap>
<select id="getDepByIdStep" resultMap="MyDeptStep">
select id,dept_name from tbl_dept where id=#{id}
</select>
</mapper>
第五步:编写测试类
package com.csu.marden;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
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 Test {
public static void main(String[] args) throws IOException {
//1.根据xml配置文件(全局配置文件,即mybatis-config.xml)创建一个SqlSessionFactory对象
String resources="mybatis-config.xml";
InputStream inputStream=Resources.getResourceAsStream(resources);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//2.根据SqlSessionFactory创建SqlSession,每个SqlSession代表和数据库的一次会话
SqlSession openSession=sqlSessionFactory.openSession();
try{
//3.获取接口的实现类对象(mybatis会为接口自动创建一个代理对象,代理对象会执行增删改查)
EmployeeMapperPlus mapper1=openSession.getMapper(EmployeeMapperPlus.class);
DepartmentMapper mapper2=openSession.getMapper(DepartmentMapper.class);
//4.通过接口的实现类对象调用接口的查询方法
Department result=mapper2.getDepByIdStep(2);
System.out.println(result);
System.out.println(result.getEmps());
}finally{
openSession.close();
}
}
}
示例3:使用collection子标签进行分步查询,实现延迟加载(按需加载)
背景:
- 示例2中,每次查询Department对象的时候,Employee对象也将一起查询出来。我们需要将员工信息延迟加载,即员工信息在我们使用的时候再去查询。
使用:
- 在上述分步查询的基础上,在全局配置文件中增加两个配置
- <setting name="lazyLoadingEnabled" value="true"/>
- <setting name="aggressiveLazyLoading" value="false"/>
问题1:
-
问题:使用accociation或者collection标签做分步查询时,需要使用column属性传入查询的参数,那么如何将多个参数传入呢?
-
解决:将多列的值封装成map传递,column="{key1=column1,key2=column2}"
示例:
问题2:
-
问题:分步查询可以在association或者collection标签中开启或关闭延迟加载
-
解决:fetchType="eager" (lazy:延迟加载,eager:立即加载)
示例:
总结:
- association标签用来定义单个关联对象
- collection标签用来定义集合类型的关联对象
六、discriminator鉴别器的理解
discriminator鉴别器:mybatis可以使用discriminator鉴别器判断某列的值,然后根据某列的值改变封装的行为。
示例:封装Employee对象时,如果查出的是女生,则把部门信息查询出来,否则部查询;如果是男生,就把last_name这一列的值赋值给email列。
第一步:在员工类对应的接口中定义查询方法
package com.csu.marden;
import java.util.List;
public interface EmployeeMapperPlus {
//使用resultMap自定义结果映射
public Employee getEmpById(Integer id);
//关联查询
public Employee getEmpAndDeptById(Integer id);
//关联查询(使用associatino分步查询)
public Employee getEmpByIdStep(Integer id);
//根据部门id查询员工信息
public List<Employee> getEmpsByDeptId(Integer id);
}
第二步:在员工类接口对应的sql映射文件中,定义鉴别器以及封装规则
<select id="getEmpByIdStep" resultMap="MyEmpDiscriminator" >
select * from tbl_employee where id=#{id}
</select>
<resultMap type="employee" id="MyEmpDiscriminator">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
<!-- column属性: 指定要判断的列名-->
<!-- javaType属性:列值对应的Java类型(写别名即可) -->
<discriminator javaType="string" column="gender">
<!-- 女生的情况 -->
<!-- resultType属性:指定封装的结果类型 -->
<case value="0" resultType="employee">
<association
property="dept"
select="com.csu.marden.DepartmentMapper.getDeptById"
column="d_id">
</association>
</case>
<!-- 男生的情况 -->
<case value="1" resultType="employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="last_name" property="email"/>
</case>
</discriminator>
</resultMap>
第三步:编写测试类