Mybatis的select
2020.02.05 18:42
2020.02.05 18:42
1. Select查询
1.1. 返回List
代码:
接口:
public List<Employee> findAll();
配置:
<select id="findAll" resultType="employee">
select * from employee;
</select>
测试:
/**
* 查询select
*/
@Test
public void test05() throws IOException {
SqlSession sqlSession = getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
// Employee employee = new Employee("TestInsert", "TestInsert@misiai.com", "男");
List<Employee> all = mapper.findAll();
for (Employee employee : all) {
System.out.println("employee = " + employee);
}
sqlSession.commit();//记得提交
}
结论:在配置中返回值类型就是Bean类类型即可,即resultType="employee"
1.2. 封装Map
代码:
接口:
//返回多条记录的map: k就是列名,v就是对应的值
// 告诉以id为键值
@MapKey("id")
public Map<String, Object> findResultsMap();
//返回一条记录的map: k就是列名,v就是对应的值
public Map<String, Object> findResultMapById(Integer id);
配置:
<select id="findResultMapById" resultType="java.util.Map">
select * from employee where id=#{id};
</select>
<select id="findResultsMap" resultType="employee">
select * from employee;
</select>
测试:
Map<String, Object> resultMap = mapper.findResultMapById(4);
System.out.println(resultMap);
// {gender=男, last_name=TestInsert, id=4, email=TestInsert@misiai.com}
Map<String, Object> resultMaps = mapper.findResultsMap();
//{1=Employee{id=1, lastName='Misiai', email='admin@misiai.com', gender='男'},
// 4=Employee{id=4, lastName='TestInsert', email='TestInsert@misiai.com', gender='男'}}
System.out.println(resultMaps);
1.3. ResultMap
简言之就是自定义返回结果有哪些类型,实现高级结果集映射。
代码:
接口:
public Employee findById(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="com.misiai.dao.EmployeePlusMapper">
<!--
id:方便引用,自定义
type:被定义的javaBean
-->
<resultMap id="employeeResult" type="com.misiai.bean.Employee">
<!--
<id />标签,专门用来定义主键
column:数据库对应的列名
property:javaBean中的属性名
-->
<id column="id" property="id"/>
<!--
<result/> 标签,定义其他普通列。主键列也可用result定义,但用<id>定义更好。
-->
<result column="last_name" property="lastName"/>
<!--
其他列不指定,则默认自动封装,但既然都使用resultMap,那么建议都写上。
-->
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</resultMap>
<select id="findById" resultMap="employeeResult">
select *
from employee
where id = #{id};
</select>
</mapper>
Tips:具体请看注释!
测试:
@Test
public void test06() throws IOException {
SqlSession sqlSession = getSqlSession();
EmployeePlusMapper mapper = sqlSession.getMapper(EmployeePlusMapper.class);
Employee byId = mapper.findById(4);
System.out.println("byId = " + byId);
sqlSession.commit();//记得提交
}
结果:
以上只是最简单的应用,更复杂应用:
1.3.1. 场景1:查询关联属性
查询Employee的同时查询员工对应的部门 Employee===Department
Department.java
package com.misiai.bean;
public class Department {
private Integer id;
private String departmentName;
// ...Contructor
// ...getter and setter...
}
新的SQL:
-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`dept_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`last_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`gender` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`dept_id` int(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `empl_dept`(`dept_id`) USING BTREE,
CONSTRAINT `empl_dept` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
配置:
<resultMap id="EmpAndDept" type="com.misiai.bean.Employee">
<id column="id" property="id"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<result column="last_name" property="lastName"/>
<!--
department.id中的department是在bean类定义的属性名称。
-->
<result column="did" property="department.id"/>
<result column="dept_name" property="department.departmentName"/>
</resultMap>
<select id="findEmpAndDept" resultMap="EmpAndDept">
SELECT e.id id,
e.email email,
e.gender gender,
e.last_name last_name,
d.id did,
d.dept_name dept_name
FROM employee e,
department d
WHERE e.dept_id = d.id
AND e.id = 5
</select>
association
上列中级联属性的写法是:
<result column="did" property="department.id"/>
<result column="dept_name" property="department.departmentName"/>
但是还有其他写法:
<resultMap id="EmpAndDept" type="com.misiai.bean.Employee">
<id column="id" property="id"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<result column="last_name" property="lastName"/>
<!--
department.id中的department是在bean类定义的属性名称。
-->
<!--<result column="did" property="department.id"/>-->
<!--<result column="dept_name" property="department.departmentName"/>-->
<!--上面两列等效于:-->
<association property="department" javaType="com.misiai.bean.Department">
<id column="did" property="id"/>
<result column="dept_name" property="departmentName"/>
</association>
</resultMap>
association分步查询:
分步查询就是把一个复杂SQL语句拆分成多个简单的语句。
代码:
-
departmet
mapper:
<?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.misiai.dao.DepartmentMapper"> <select id="findById" resultType="com.misiai.bean.Department"> select id, dept_name departmentName from department where id=#{id}; </select> </mapper>
接口:
package com.misiai.dao; import com.misiai.bean.Department; public interface DepartmentMapper { public Department findById(Integer id); }
-
Employee
mapper:
<!-- EmpAndDeptStep 使用association进行分步查询: 1、先按照员工id查询员工信息 2、根据查询员工信息中的d_id值去部门表查出部门信息 3、部门设置到员工中; --> <resultMap id="EmpAndDeptStep" type="com.misiai.bean.Employee"> <id column="id" property="id"/> <result column="email" property="email"/> <result column="gender" property="gender"/> <result column="last_name" property="lastName"/> <!-- column是要将那列的值传递过去(select要执行的语句中) --> <association column="dept_id" property="department" select="com.misiai.dao.DepartmentMapper.findById" > <!--select:表明当前属性是调用select指定的方法查出的结果--> <id column="dept_id" property="id"/> <result column="dept_name" property="departmentName"/> </association> </resultMap> <select id="findEmpAndDeptStep" resultMap="EmpAndDeptStep"> select * from employee where id=#{id} </select>
接口:
public Employee findEmpAndDeptStep(Integer id);
-
测试代码
@Test public void test06() throws IOException { SqlSession sqlSession = getSqlSession(); EmployeePlusMapper mapper = sqlSession.getMapper(EmployeePlusMapper.class); // Employee byId = mapper.findById(4); // System.out.println("byId = " + byId); // 复杂的resultMap // Employee empAndDept = mapper.findEmpAndDept(5); // System.out.println("empAndDept = " + empAndDept); Employee empAndDeptStep = mapper.findEmpAndDeptStep(6); System.out.println("empAndDeptStep = " + empAndDeptStep); System.out.println(empAndDeptStep.getDepartment()); sqlSession.commit();//记得提交 }
1.3.2. 场景2
根据部门信息查询旗下所有员工(列表collection)
collection查询
联合查询,如果用left join 那么SQL语句是:
SELECT
d.id did,
d.dept_name departmentName,
e.email email,
e.gender gender,
e.last_name lastName
FROM
department d
LEFT JOIN employee e ON d.id = e.dept_id
WHERE
d.id = 1;
mapper/DepartmentMapper.xml
<resultMap id="mapFindEmp" type="com.misiai.bean.Department">
<id column="did" property="id"/>
<result column="departmentName" property="departmentName"/>
<collection property="employees" ofType="Employee">
<id column="id" property="id"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<result column="lastName" property="lastName"/>
</collection>
</resultMap>
<select id="findEmpsById" resultMap="mapFindEmp">
SELECT d.id did,
d.dept_name departmentName,
e.id id,
e.email email,
e.gender gender,
e.last_name lastName
FROM department d
LEFT JOIN employee e ON d.id = e.dept_id
WHERE d.id = #{id};
</select>
com.misiai.dao.DepartmentMapper
List<Employee> findEmpsById(Integer id);
测试:
@Test
public void test08() throws IOException {
SqlSession sqlSession = getSqlSession();
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
List<Employee> empsById = mapper.findEmpsById(1);
System.out.println("empsById = " + empsById);
sqlSession.commit();
}
collection分步查询
代码:
-
Employee
com.misiai.dao.EmployeePlusMapper
List<Employee> findEmployeesByDeptId(Integer id);
该方法是定义在EmployeePlusMapper中的,根据部门id,返回所有在此部门的员工。
对应的mapper.xml
<!--List<Employee> findEmployeesByDeptId(Integer id);--> <select id="findEmployeesByDeptId" resultType="employee"> select * from employee where dept_id=#{id}; </select>
-
Department
接口:
Department findEmployeesByIdStep(Integer id);
mapper:
<resultMap id="mapFindEmployeesStep" type="com.misiai.bean.Department"> <id column="id" property="id"/> <result column="dept_name" property="departmentName"/> <!-- column是要将那列的值传递过去(select要执行的语句中) --> <collection property="employees" column="id" select="com.misiai.dao.EmployeePlusMapper.findEmployeesByDeptId"> </collection> </resultMap> <select id="findEmployeesByIdStep" resultMap="mapFindEmployeesStep"> select * from department where id=#{id}; </select>
测试代码:
@Test public void test08() throws IOException { SqlSession sqlSession = getSqlSession(); DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class); // List<Employee> empsById = mapper.findEmpsById(1); // System.out.println("empsById = " + empsById); Department employeesByIdStep = mapper.findEmployeesByIdStep(1); System.out.println("employeesByIdStep = " + employeesByIdStep); sqlSession.commit(); }
结果:
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6f45df59] ==> Preparing: select * from department where id=?; ==> Parameters: 1(Integer) <== Columns: id, dept_name <== Row: 1, 测试部 ====> Preparing: select * from employee where dept_id=?; ====> Parameters: 1(Long) <==== Columns: id, last_name, email, gender, dept_id <==== Row: 5, Tom, admin1@misiai.com, 男, 1 <==== Total: 1 <== Total: 1
可以看出,是执行了两条语句。
1.4. 补充:鉴别器
discriminator
是定义在resultMap里面的,一种分情况执行不同操作。
了解,这里不多说。
<resultMap id="mapFindEmployeesStep" type="com.misiai.bean.Department">
<discriminator javaType="">
<case value=""></case>
</discriminator>
<id column="id" property="id"/>
<result column="dept_name" property="departmentName"/>
<collection property="employees" column="id" select="com.misiai.dao.EmployeePlusMapper.findEmployeesByDeptId">
</collection>
</resultMap>
本节阅读完毕!
(分享)