Mybatis框架实现增删改查操作及resultMap结果集映射讲解与实例
POJO 对象输出映射
如果表中的类名与类中的属性名完全相同,mybatis会自动将查询结果封装到POJO对象中. 如果java中使用标准驼峰命名,数据库中使用下划线连接命名,可以开始全局设置实现自动转换
<!-- 开启驼峰命名映射 从数据库命名转java驼峰命名-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
useGeneratedKeys="true"可以返回刚插入数据的主键
KeyColumn=“id” 告知主键列(数据库中的)
KeyProperty=“id” 告知于主键对应的属性(Java中的)
防止IDEA中代码与数据库中相同属性但名称不同而产生的无法传参的问题
<insert id="saveAdmin" parameterType="Admin" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
insert into admin(account,password,sex)values('${account}','${password}','${sex}')
</insert>
接口部分代码
void saveAdmin(Admin admin);
测试运行部分
@Test
public void save(){
Admin admin = new Admin();
admin.setAccount("admin3");
admin.setPassword("111");
admin.setSex("男");
SqlSession sqlSession = MybatisUtil.getSqlSession();
AdminMapper mapper = sqlSession.getMapper(AdminMapper.class);
mapper.saveAdmin(admin);
System.out.println(admin.getId());
sqlSession.commit();
sqlSession.close();
}
更新操作
在更新操作中就不用填写上述操作了,直接填写正常的sql语句即可
<update id="updateAdmin" parameterType="Admin">
update admin set account=#{account},password=#{password},sex=#{sex}where id= #{id}
</update>
接口部分代码
void updateAdmin(Admin admin);
测试运行部分
@Test
public void update(){
Admin admin = new Admin();
admin.setId(13);
admin.setAccount("jim");
admin.setPassword("111");
admin.setSex("男");
SqlSession sqlSession = MybatisUtil.getSqlSession();
AdminMapper mapper = sqlSession.getMapper(AdminMapper.class);
mapper.updateAdmin(admin);
sqlSession.commit();
sqlSession.close();
}
测试结果如下
删除操作
<delete id="deleteAdmin">
delete from admin where id=#{id}
</delete>
接口部分代码
void deleteAdmin(int id);//根据所提供的id锁定主键进行全体删除
测试运行部分
@Test
public void delete(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
AdminMapper mapper = sqlSession.getMapper(AdminMapper.class);
mapper.deleteAdmin(20);
sqlSession.commit();
sqlSession.close();
}
查找操作
方法一: 根据查找主键id进行查找信息
<select id="getAdminById" resultType="com.qn.mybatisPro.model.Admin">
select*from admin where id=#{id}
</select>
接口部分代码
Admin getAdminById(int id);
测试运行部分
@Test
public void find(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
AdminMapper mapper = sqlSession.getMapper(AdminMapper.class);
Admin admin= mapper.getAdminById(18);
System.out.println(admin);
sqlSession.commit();
sqlSession.close();
}
方法二:利用多个条件进行查找
<select id="getAdmin" resultType="com.qn.mybatisPro.model.Admin">
select*from admin where account=#{acc} and sex=#{sex}
</select>
接口部分代码
//传递多个参数 使用注解标签参数绑定@Param("acc") acc向sql中传入名称
Admin getAdmin(@Param("acc") String account,@Param("sex") String sex);
测试运行部分
@Test
public void find1(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
AdminMapper mapper = sqlSession.getMapper(AdminMapper.class);
Admin admin= mapper.getAdmin("admin2","男");
System.out.println(admin);
sqlSession.commit();
sqlSession.close();
}
方法三:通过Map进行多元素查询
<select id="getAdmin1" resultType="Admin">
select*from admin where account=#{acc} and sex=#{sex}
</select>
接口部分代码
Admin getAdmin1(Map<String,Object> map);
测试运行部分
@Test
public void find2(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
AdminMapper mapper = sqlSession.getMapper(AdminMapper.class);
Admin admin= mapper.getAdmin("admin2","男");
System.out.println(admin);
sqlSession.commit();
sqlSession.close();
}
方法四:一次性查询多个符合条件的数据
<select id="getAdminList" resultType="com.qn.mybatisPro.model.Admin">
select*from admin
</select>
接口部分代码
List<Admin> getAdminList();
测试运行部分
@Test
public void find3(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
AdminMapper mapper = sqlSession.getMapper(AdminMapper.class);
List<Admin> admin= mapper.getAdminList();
System.out.println(admin);
sqlSession.commit();
sqlSession.close();
}
方法五:根据主键数查询信息总条数
<select id="getAdminCount" resultType="int">
select count(*) from admin
</select>
接口部分代码
int getAdminCount();//返回有多少条记录
测试运行部分
@Test
public void find4(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
AdminMapper mapper = sqlSession.getMapper(AdminMapper.class);
int count=mapper.getAdminCount();
System.out.println(count);
sqlSession.commit();
sqlSession.close();
}
resultMap结果集映射
特殊情况下,列名与属性名的不一致,会导致再查询过程中值为空值,为了解决此问题我们采用resultmap结果集映射
<resultMap id="adminMap" type="Admin">
<id column="id" property="id"></id>
<result column="account" property="account"></result>
<result column="password" property="password"></result>
<result column="sex" property="sex1"></result>
</resultMap>
<select id="getAdminById" resultMap="adminMap">
select*from admin where id=#{id}
</select>
多表关联处理结果集
resultMap 元素中 association ， collection 元素
association – 复杂类型联合; 许多查询结果合成这个类型
一对一结果映射– association 能引用自身, 或者从其它地方引用collection – 复杂类型集合嵌套结果映射– collection 能引用自身, 或者从其它地方引用多对一与一对多association
关联元素处理“有一个”类型的关系,即一对一关联。它有两种关联方式
嵌套查询：通过执行另外一个 SQL 映射语句来返回预期的复杂类型。
嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集
通过实例夹杂着讲解
首先在Java中创建Employee类
package com.qn.mybatisPro.model;
public class Employee {
private int id;
private String name;
private int age;
private Dept dept;
private Admin admin;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
public Admin getAdmin() {
return admin;
}
public void setAdmin(Admin admin) {
this.admin = admin;
}
@Override
public String toString() {
return "Employee{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", dept=" + dept +
", admin=" + admin +
'}';
}
}
在数据库中建立Employee表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
age INT,
deptId INT,
adminId INT
)
向表中添加数据
再创建Employee接口
public interface EmployeeMapper {
Employee getEmployeeById(int id);
List<Employee> getEmployeeList();
}
再创造接口对应的映射
<?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.qn.mybatisPro.mapper.EmployeeMapper"><!--对应接口地址-->
(1). resutlMap 的 id 属性是 resutlMap 的唯一标识
(2). resutlMap 的 id 属性是映射的 POJO 类
(3). id 标签映射主键，result 标签映射非主键
(4). property 设置 POJO 的属性名称，column 映射查询结果的列名
<resultMap id="empmap" type="Employee">
<id column="id" property="id"></id>
<result column="ename" property="name"></result>
<result column="age" property="age"></result>
<association property="dept" javaType="Dept">
<result column="dname" property="name"></result>
</association>
<association property="admin" javaType="Admin">
<result column="account" property="account"></result>
</association>
</resultMap>
/*(1). 本例的输出映射使用的是 resultMap，而非 resultType
(2). resultMap 引用了 empmap*/
<select id="getEmployeeById" resultMap="empmap">
SELECT emp.id,
emp.name ename,
emp.age,
d.name dname,
a.account
FROM employee emp LEFT JOIN dept d ON emp.deptId=d.id
LEFT JOIN admin a ON emp.adminId=a.id
WHERE emp.id=#{id}
</select>
<select id="getEmployeeList" resultType="com.qn.mybatisPro.model.Employee"></select>
</mapper>
开始测试查找员工信息
package com.qn.mybatisPro.test;
import com.qn.mybatisPro.mapper.EmployeeMapper;
import com.qn.mybatisPro.model.Employee;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.qn.mybatisPro.util.MybatisUtil;
public class TestEmployee {
@Test
public void find(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
EmployeeMapper Mapper=sqlSession.getMapper(EmployeeMapper.class);
Employee employee=Mapper.getEmployeeById(1);
System.out.println(employee.getName());
System.out.println(employee.getDept().getName());
System.out.println(employee.getAdmin().getAccount());
System.out.println(employee);
sqlSession.commit();
sqlSession.close();
}
}
下图为我们所能得到的信息
查找部门及相关信息
首先在Java中创建Dept类
package com.qn.mybatisPro.model;
import java.util.ArrayList;
import java.util.List;
public class Dept {
private int id;
private String name;
private List<Employee> employees;
private Admin admin;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Admin getAdmin() {
return admin;
}
public void setAdmin(Admin admin) {
this.admin = admin;
}
public List<Employee> getEmployees() {
return employees;
}
public void setEmployees(List<Employee> employees) {
this.employees = employees;
}
@Override
public String toString() {
return "Dept{" +
"id=" + id +
", name='" + name + '\'' +
", employees=" + employees +
", admin=" + admin +
'}';
}
}
在数据库中建立Dept表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
adminId INT
)
向表中添加数据
再创建Dept接口
package com.qn.mybatisPro.mapper;
import com.qn.mybatisPro.model.Dept;
import java.util.List;
public interface DeptMapper {
Dept getDeptById(int id);
List<Dept> getDeptList();
}
再创造接口对应的映射
Collection 关联元素处理一对多关联。
• 部门与员工一对多关系• 部门一方，配置多方集合• 员工多方，在多方配置一方• 使用 resultMap 组装查询结果
<mapper namespace="com.qn.mybatisPro.mapper.DeptMapper"><!--对应接口地址-->
<resultMap id="deptmap" type="Dept">
<id column="id" property="id"></id>
<result column="dname" property="name"></result>
<association property="admin" javaType="Admin">
<result column="account" property="account"></result>
</association>
<collection property="employees" javaType="list" ofType="Employee">
<result column="ename" property="name"></result>
</collection>
</resultMap>
<select id="getDeptById" resultMap="deptmap">
SELECT d.id,
d.name dname,
a.account,
emp.name ename
FROM dept d LEFT JOIN admin a ON d.adminId =a.id
LEFT JOIN employee emp ON d.id =emp.deptId
WHERE d.id=#{id}
</select>
<select id="getDeptList" resultMap="deptmap">
SELECT d.id,
d.name dname,
a.account,
emp.name ename
FROM dept d LEFT JOIN admin a ON d.adminId =a.id
LEFT JOIN employee emp ON d.id =emp.deptId
</select>
</mapper>
测试结果
查找部门编号为1的职员姓名
查找各部门与相关所属的人员姓名
扫一扫
6668
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
