MyBatis映射文件

发表于 | 分类于
字数统计: 1.9k | 阅读时长 ≈ 9

实现增删改查功能

映射文件指导着MyBatis如何进行数据库增删改查
在EmployeeMapper接口中添加增删改查的方法:

1
2
3
4
5
6
7
8
9
10
11
12
package dao;

public interface EmployeeMapper {

    public Employee getEmpById(Integer id);

    public void addEmp(Employee employee);

    public void updateEmp(Employee employee);

    public void deleteEmpById(Integer id);
}

在映射文件Employee.xml添加相应的sql映射：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<?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">
<!-- namespace  名称空间  1.通过 namespace.id 来确定要执行的sql语句
                         2.指定为接口的全类名，用于绑定接口
     id  唯一标识   用于唯一标识一条sql语句
     result  返回值类型
     #{id}  从传递过来的参数中取出id值
-->
<mapper namespace="dao.EmployeeMapper">
<!--public Employee getEmpById(Integer id);-->
    <select id="getEmpById" resultType="bean.Employee">
        select * from employee where id = #{id}
    </select>
<!--public void addEmp(Employee employee);-->
 <!-- parameterType： 参数类型  可以省略
         mysql支持自增主键，自增主键的获取，mybatis也是利用statement.getGenerateKeys()
         useGenerateKey="true":使用自增主键获取主键值策略
         keyProperty:指定对应的主键值类型，也就是mybatis获取到主键后封装给javaBean的哪个属性-->
    <insert id="addEmp" useGeneratedKeys="true" keyProperty="id">
        insert into employee(name,gender,email) values(#{name},#{gender},#{email})
    </insert>


<!--public void updateEmp(Employee employee);-->
    <update id="updateEmp">
        update employee set name=#{name},gender=#{gender},email=#{email} where id=#{id}
    </update>
<!--public void deleteEmpById(Integer id);-->
    <delete  id="deleteEmpById">
        delete from employee where id=#{id}
    </delete>
</mapper>

测试增删改方法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@Test
    public void test3() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession sqlSession = null;
        try {
            sqlSession = sqlSessionFactory.openSession();// 不会自动提交
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
            // 添加
            Employee employee = new Employee(null, "Tom", "1", "tom@126.com");
            mapper.addEmp(employee);
            // 获取自增主键的值
            System.out.println(employee.getId());// 5
            // 修改
            //mapper.updateEmp(new Employee(2,"zyz001","1","zyz001@qq.com"));
            // 删除
            //mapper.deleteEmpById(2);
            // 手动提交
            sqlSession.commit();
        } finally {
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
    }

MyBatis参数处理

单个参数：mybatis不会做特殊处理。    #{参数名}：取出参数
多个参数：mybatis会做特殊处理，多个参数封装成一个map
key：参数param1,param2…
value：传入的参数值
#{param1}或#{0}：取出参数值1
通过注解明确指定封装类型的key：

1
public Employee getEmpByIdAndName(@Param("id")Integer id,@Param("name")String name);

#{指定的key}：取出对应的参数值
#{}与${}的区别：
#{}：是以预编译的形式，将参数设置到sql；防止sql注入
${}：直接拼装到sql中；有安全问题

select元素

1、返回list对象

1
public List<Employee> getEmpsByNameLike(String name);
1
2
3
<select id="getEmpsByNameLike" resultType="bean.Employee">
    select * from employee where name like #{name}
</select>
1
List<Employee> employeeList = mapper.getEmpsByNameLike("%o%");// 查询name含有o的所有记录

2、返回map对象(单条记录)

1
2
// 返回一条记录的map key:列名   value:对应的值
   public Map<String,Object> getEmpByIdReturnMap(Integer id);
1
2
3
<select id="getEmpByIdReturnMap" resultType="map">
     select * from employee where id=#{id}
 </select>
1
2
Map<String,Object> map = mapper.getEmpByIdReturnMap(1);
System.out.println(map);// {gender=1, name=zyz001, id=1, email=zyz001@qq.com}

2、返回map对象(多条记录)

1
2
3
4
// 返回多条记录发map key:主键  value:封装后的bean对象
  // 告诉mybatis封装这个map的时候使用哪个属性作为map的主键
  @MapKey("id")
  public Map<Integer,Employee> getEmpsByNameLikeReturnMap(String name);
1
2
3
<select id="getEmpsByNameLikeReturnMap" resultType="bean.Employee">
       select * from employee where name like #{name};
</select>
1
Map<Integer,Employee> employeeMap = mapper.getEmpsByNameLikeReturnMap("%o%");

resultMap 自定义结果集封装规则

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<!-- resultMap  自定义结果集规则
            id  方便引用
            type 自定义规则的java类型
            -->
<resultMap id="MyEmp" type="bean.Employee">
    <!--  id 指定主键封装规则
             column  指定哪一列
             property  指定对应Javabean的哪一个属性-->
    <id column="id" property="id"/>
    <!-- 定义普通列封装规则 不指定则自动封装       -->
    <result column="name" property="name"></result>
    <result column="gender" property="gender"></result>
    <result column="email" property="email"></result>
</resultMap>
<select id="getEmpById" resultMap="MyEmp">
    select * from employee where id=#{id}
</select>

联合查询：级联属性封装结果集 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<resultMap id="MyEmpAndDept" type="bean.Employee">
        <id column="id" property="id"/>
        <result column="name" property="name"></result>
        <result column="gender" property="gender"></result>
        <result column="email" property="email"></result>
        <result column="dept_id" property="deptId"></result>
        <result column="d_id" property="dept.id"></result>
        <result column="dept_name" property="dept.departmentName"></result>
</resultMap>
<select id="getEmpAndDept" resultMap="MyEmpAndDept">
        SELECT e.id id,e.name name,e.gender gender,e.email email,
        e.dept_id dept_id,d.id d_id,d.dept_name dept_name
        from employee e,dept d WHERE e.dept_id=d.id AND e.id=#{id};
</select>

联合查询：通过association标签封装结果集 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<resultMap id="MyEmpAndDept2" type="bean.Employee">
    <id column="id" property="id"/>
    <result column="name" property="name"></result>
    <result column="gender" property="gender"></result>
    <result column="email" property="email"></result>
    <result column="dept_id" property="deptId"></result>
    <!-- association 可以指定联合的javabean对象
                    property="dept"  指定javabean中的哪个属性是联合的对象
                    javaType  指定联合对象的类型-->
    <association property="dept" javaType="bean.Department">
        <id column="d_id" property="id"/>
        <result column="dept_name" property="departmentName"></result>
    </association>
</resultMap>

分部查询：先查询员工，得到员工信息后再根据员工的部门编号，查询部门信息

1
2
3
public interface DepartmentMapper {
    public Department getDeptById(Integer id);
}

DepartmentMapper.xml

1
2
3
4
5
<mapper namespace="dao.DepartmentMapper">
    <select id="getDeptById" resultType="bean.Department">
        select id,dept_name departmentName from dept where id=#{id}
    </select>
</mapper>

EmployeeMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<!--  分部查询  可以启动延迟加载-->
    <resultMap id="MyEmpByStep" type="bean.Employee">
        <id column="id" property="id"/>
        <result column="name" property="name"></result>
        <result column="gender" property="gender"></result>
        <result column="email" property="email"></result>
        <result column="dept_id" property="deptId"></result>
        <!-- 定义关联对象封装规则
        select 表明当前属性是调用指定的方法后查询出来的结果
        column  指定哪一列的值传给这个方法-->
        <association property="dept"
                     select="dao.DepartmentMapper.getDeptById" column="dept_id">
        </association>
    </resultMap>
    <select id="getEmpByIdStep" resultMap="MyEmpByStep">
        select * from employee where id=#{id}
    </select>

mybatis-config.xml

1
2
3
4
5
6
<setting>
	<!--  开启延迟加载 -->
	<setting name="lazyLoadingEnabled" value="true"/>
	<!-- 按需加载-->
	<setting name="aggressiveLazyLoading" value="false"/>
</setting>

使用时才进行加载：

1
2
Employee empByIdStep = mapper.getEmpByIdStep(1);
System.out.println(empByIdStep.getName());

1
2
Employee empByIdStep = mapper.getEmpByIdStep(1);
System.out.println(empByIdStep);

Collection集合类型&嵌套结果集：
通过部门编号查找所有在该部门的员工，返回一个List员工集合

1
2
3
private Integer id;
private String departmentName;
private List<Employee> emps;
1
2
3
public interface DepartmentMapper {
    public Department getDeptByIdPlus(Integer id);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<resultMap id="MyDept" type="bean.Department">
    <id column="did" property="id"/>
    <result column="dept_name" property="departmentName"/>
    <!-- collection标签用来定义关联集合类型的属性的封装规则
                    ofType  指定集合里面元素的类型 -->
    <collection property="emps" ofType="bean.Employee">
        <id column="eid" property="id"/>
        <result column="name" property="name"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
        <result column="dept_id" property="deptId"/>
    </collection>
</resultMap>
<select id="getDeptByIdPlus" resultMap="MyDept">
    SELECT d.id did ,dept_name,e.id eid,e.`name`,e.gender,e.email,e.dept_id
    FROM dept d LEFT JOIN employee e ON d.id=e.dept_id WHERE d.id=#{id}
</select>
1
2
3
Department deptByIdPlus = mapper.getDeptByIdPlus(1);
System.out.println(deptByIdPlus);
System.out.println(deptByIdPlus.getEmps());

结果集包含List<String> orderIds时

1
2
3
4
5
6
<!-- 一对多，变成list<String> -->
<collection property="orderIds" ofType="String">
    <constructor>
        <arg column="order_Id"/>
    </constructor>
</collection>