当查询的数据表的字段名与pojo类的属性名一致时,可以用resultType,但sql查询到的字段与pojo的属性名不一致时,则需要使用resultMap将字段名和属性名对应起来。
resultType直接表示返回类型,
resultMap:对外部resultMap的引用,二者不能同时存在。
前期准备,一个employee表,项目的目录结构与之前写的两篇文章一致。
CREATE TABLE employee(
id int(11) NOT NULL auto_increment,
user_name VARCHAR(50) NOT NULL,
age int(6) NOT NULL,
email VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
@Data
public class Employee {
private Integer id;
private String userName;
private Integer age;
private String email;
}
单表查询
EmployeeMapper接口:
public Employee getEmpById(Integer id);
EmployeeMapper.xml映射文件:
<mapper namespace="com.diligentkong.mybatis.dao.EmployeeMapper">
<!--resultMap 自定义某个javaBean的封装规则, type:自定义规则的java类型 id“唯一标识,方便引用-->
<resultMap type="com.diligentkong.mybatis.bean.Employee" id="simpleEmp">
<!-- 指定主键列的封装规则:与下面<result>标签的区别是可以用于提升性能,所以这里用<id>标签来定义主键的映射关系
column:数据库中对应的字段名
property:对应JavaBean 的属性名
-->
<id column="id" property="id"></id>
<!-- 定义普通列封装规则-->
<result column="user_name" property="userName"></result>
<!-- 其他不指定的列会自动封装:我们只要写resultMap就把全部的映射规则都写上。 -->
<result column="age" property="age"></result>
<result column="email" property="email"></result>
</resultMap>
<select id="getEmpById" resultMap="simpleEmp" >
select * from employee where id=#{id};
</select>
</mapper>
测试结果:
public SqlSessionFactory getSqlSeeionFactory() throws IOException {
String resource = "config/mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
return new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void myTest() throws IOException {
// 1.获取sqlSessionFactory对象
SqlSessionFactory sqlSessionFactory = getSqlSeeionFactory();
// 2、获取sqlSession对象
SqlSession openSession = sqlSessionFactory.openSession();
try {
// 3、获取接口的实现类对象
//会为接口自动的创建一个代理对象,代理对象去执行增删改查方法
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee employee = mapper.getEmpById(1);
System.out.println(employee);
openSession.commit();
} finally {
openSession.close();
}
}
}
结果:Employee(id=1, userName=张三, age=20, email=zhangsan.163com)
association 用于一对一,一对多
关联查询:association 用于一对一,一对多的情况
现在我有这样的需求,一个Employee 员工对应一个dept部门,而这个dept部门也有员工(employee) 信息
新建一个dept表,
CREATE TABLE dept(
id int(11) NOT NULL auto_increment,
dept_name VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO dept VALUES(1,"研发部");
INSERT INTO dept VALUES(2,"测试部");
INSERT INTO dept VALUES(3,"市场部");
在employee表中添加dept_id字段,建立外键关系。
alter table employee add column d_id INT(11)
ALTER table employee ADD constraint fk_emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id);
INSERT INTO employee VALUES(1,"张三",20,"zhangsan.163com",1);
INSERT INTO employee VALUES(2,"李四",21,"lisi.163com",1);
INSERT INTO employee VALUES(3,"王五",22,"wangwu.163com",2);
INSERT INTO employee VALUES(4,"赵六",23,"zhaoliu.163com",3);
INSERT INTO employee VALUES(5,"kong",20,"kong.163com",3);
新建Department 类
@Data
public class Department {
private Integer id;
private String deptName;
}
Employee类中新增dept属性
@Data
public class Employee {
private Integer id;
private String userName;
private Integer age;
private String email;
private Department dept;
}
public Employee getEmpAndDept(Integer id);
EmployeeMapper接口:
public Employee getEmpAndDept(Integer id);
EmployeeMapper.xml映射文件:
<resultMap id="MyDeptEmp" type="com.diligentkong.mybatis.bean.Employee">
<id column="id" property="id"></id>
<result column="user_name" property="userName"></result>
<result column="age" property="age"></result>
<result column="email" property="email"></result>
<!-- association可以指定联合的javaBean对象
property="dept":指定哪个属性是联合的对象
javaType:指定这个属性对象的类型[不能省略]
-->
<association property="dept" javaType="com.diligentkong.mybatis.bean.Department">
<id column="id" property="id"/>
<result column="dept_name" property="departmentName"/>
</association>
</resultMap>
<select id="getEmpAndDept" resultMap="MyDeptEmp">
select e.id id, e.user_name user_name, e.age age, e.email email,e.dept_id deptid,
d.id id,d.dept_name dept_name from employee e,dept d where e.dept_id=d.id AND e.id= #{id}
</select>
测试结果:
Employee employee = mapper.getEmpAndDept(1);
System.out.println(employee);
结果:
Employee(id=1, userName=张三, age=20, email=zhangsan.163com, dept=Department(id=1, departmentName=研发部))
collection 用于 一对一 和 一对多
查询部门信息,并查询部门中包含的员工信息,修改pojo
@Data
public class Department {
private Integer id;
private String departmentName;
private List<Employee> emps;
}
DepartmentMapper接口:
public Department getDeptById(Integer id);
DepartmentMapper.xml映射文件
<mapper namespace="com.diligentkong.mybatis.dao.DepartmentMapper">
<resultMap id="MyDept" type="com.diligentkong.mybatis.bean.Department">
<id column="did" property="id"></id>
<result column="dept_name" property="departmentName"></result>
<!--
collection定义关联集合类型的属性的封装规则
ofType:指定集合里面元素的类型
-->
<collection property="emps" ofType="com.diligentkong.mybatis.bean.Employee">
<!-- 定义这个集合中元素的封装规则 -->
<id column="id" property="id"/>
<result column="user_name" property="userName"/>
<result column="age" property="age"/>
<result column="email" property="email"/>
</collection>
</resultMap>
<select id="getDeptById" resultMap="MyDept">
SELECT d.id did,d.dept_name dept_name,
e.id eid,e.user_name user_name,e.age age,e.email email
FROM dept d
LEFT JOIN employee e
ON d.id=e.dept_id
WHERE d.id=#{id}
</select>
</mapper>
测试结果:
DepartmentMapper dept = openSession.getMapper(DepartmentMapper.class);
Department department = dept.getDeptById(1);
System.out.println(department);
结果:
Department(id=1, departmentName=研发部, emps=[Employee(id=null, userName=张三, age=20, email=zhangsan.163com, dept=null), Employee(id=null, userName=李四, age=21, email=lisi.163com, dept=null)])