ssm复习第三天4. 模糊查询 select * from user where username like '%#{u

4. 模糊查询

package cn.chenmanman.mapper;

import cn.chenmanman.pojo.User;

import java.util.List;

public interface SpecialSqlMapper {
    /**
* <p>通过username来模糊查询</p>
* @param username 用户名
* @return 模糊查询出来的一组用户
* */
    List<User> selectUserForUsername(String username);
}

<?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="cn.chenmanman.mapper.SpecialSqlMapper">

  <select id="selectUserForUsername" resultType="User">
    select * from user where username like '%#{username}%'
  </select>

</mapper>

select * from user where username like '%#{username}%'

此处不可以使用这条sql来进行模糊查询，因为mybatis会将#{username}变为?放入'%%'内。然后预编译设置值时，?属于''内的一部分，因此会报错。

可以使用下面三种方式:

select * from user where username like '%${username}%'

这条使用了${}，因为它是字符串拼接的方式。

select * from user where username like concat('%', #{username}, '%')

这个也可以，因为是sql字符串拼接，会将这仨拼接为一个字符串，这里#{username}会被设置上值。

select * from user where username like "%"#{username}"%"

5. 批量删除

delete from user where user.id in(#{list})

批量删除这里不可以使用#{}，如果使用#{}的方法的话因为是字符串类型，字符串类型的参数会加上单引号。

delete from user where user.id in(${list})

因此可以使用${}

6. 动态设置表名

select * from #{tablename}

这里也不行，原因是字符串类型的参数会加上单引号

所以使用:

select * from ${tablename}

5. ResultMap自定义映射

1. 处理java类属性名与数据库字段名不一致

可以用三种方法来解决名字不一样的问题。

sql查询在select中起别名
在mybatis配置文件中的settings标签中设置mapUnderscoreToCamelCase属性为true，但是前提是java类属性的名字遵守驼峰命名，数据库字段中的名字各单词之间_连接
自定义映射,resultMap

package cn.chenmanman.pojo;

public class Dept {
    private Integer empId;
    private String empName;

    public Dept() {
    }

    public Dept(Integer empId, String empName) {
        this.empId = empId;
        this.empName = empName;
    }

    public Integer getEmpId() {
        return empId;
    }

    public void setEmpId(Integer empId) {
        this.empId = empId;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    @Override
    public String toString() {
        return "Dept{" +
            "empId=" + empId +
            ", empName='" + empName + ''' +
            '}';
    }
}

package cn.chenmanman.pojo;

public class Emp {
    private Integer empId;
    private String empName;
    private Integer age;
    private String gender;
    private Dept dept;

    public Emp() {
    }

    public Emp(Integer empId, String empName, Integer age, String gender, Dept dept) {
        this.empId = empId;
        this.empName = empName;
        this.age = age;
        this.gender = gender;
        this.dept = dept;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "empId=" + empId +
                ", empName='" + empName + ''' +
                ", age=" + age +
                ", gender='" + gender + ''' +
                ", dept=" + dept +
                '}';
    }

    public Integer getEmpId() {
        return empId;
    }

    public void setEmpId(Integer empId) {
        this.empId = empId;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }
}

emp字段:

dept字段:

处理不一致关系的xml:

<?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="cn.chenmanman.mapper.EmpMapper">
  <!-- 自定义映射规则 -->
  <resultMap id="empResultMap" type="emp">
    <!--
    column: 数据库字段列
    property: 实体类属性
    -->
    <!-- 处理主键和实体类属性的映射 -->
    <id column="emp_id" property="empId"/>
    <!-- 处理普通字段和实体类属性的映射 -->
    <result column="emp_name" property="empName"/>
    <result column="age" property="age"/>
    <result column="gender" property="gender"/>
  </resultMap>
  <select id="selectEmpAll" resultMap="empResultMap">
    select * from t_emp
  </select>
</mapper>

2. 处理多对一关系映射

package cn.chenmanman.pojo;

public class Dept {
    private Integer empId;
    private String empName;

    public Dept() {
    }

    public Dept(Integer empId, String empName) {
        this.empId = empId;
        this.empName = empName;
    }

    public Integer getEmpId() {
        return empId;
    }

    public void setEmpId(Integer empId) {
        this.empId = empId;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    @Override
    public String toString() {
        return "Dept{" +
            "empId=" + empId +
            ", empName='" + empName + ''' +
            '}';
    }
}

package cn.chenmanman.pojo;

public class Emp {
    private Integer empId;
    private String empName;
    private Integer age;
    private String gender;
    private Dept dept;

    public Emp() {
    }

    public Emp(Integer empId, String empName, Integer age, String gender, Dept dept) {
        this.empId = empId;
        this.empName = empName;
        this.age = age;
        this.gender = gender;
        this.dept = dept;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "empId=" + empId +
                ", empName='" + empName + ''' +
                ", age=" + age +
                ", gender='" + gender + ''' +
                ", dept=" + dept +
                '}';
    }

    public Integer getEmpId() {
        return empId;
    }

    public void setEmpId(Integer empId) {
        this.empId = empId;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }
}

此处一方为emp，多方为dept。

希望在查询emp的时候能知道这个emp属于哪个dept

有三种解决办法

级联赋值

<?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="cn.chenmanman.mapper.EmpMapper">
    <resultMap id="empResultMap" type="emp">
        <!--
         column: 数据库字段列
         property: 实体类属性
         -->
        <!-- 处理主键和实体类属性的映射 -->
        <id column="emp_id" property="empId"/>
        <!-- 处理普通字段和实体类属性的映射 -->
        <result column="emp_name" property="empName"/>
        <result column="age" property="age"/>
        <result column="gender" property="gender"/>
        <result column="dept_id" property="dept.deptId"/>
        <result column="dept_name" property="dept.deptName"/>
    </resultMap>
    <select id="selectEmpAll" resultMap="empResultMap">
        select * from t_emp emp left join t_dept td on emp.dept_id = td.dept_id
    </select>
</mapper>

使用association

association标签用于处理多对一关系(实际上是用来处理实体类中的实体类属性)。

其中属性property为实体类属性中的属性，javaType为实体类属性的类型。

<?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="cn.chenmanman.mapper.EmpMapper">
  <!-- 自定义映射规则 -->
  <resultMap id="empResultMapOne" type="emp">
    <!--
    column: 数据库字段列
    property: 实体类属性
    -->
    <!-- 处理主键和实体类属性的映射 -->
    <id column="emp_id" property="empId"/>
    <!-- 处理普通字段和实体类属性的映射 -->
    <result column="emp_name" property="empName"/>
    <result column="age" property="age"/>
    <result column="gender" property="gender"/>
  </resultMap>


  <resultMap id="empResultMapTwo" type="emp">
    <!--
    column: 数据库字段列
    property: 实体类属性
    -->
    <!-- 处理主键和实体类属性的映射 -->
    <id column="emp_id" property="empId"/>
    <!-- 处理普通字段和实体类属性的映射 -->
    <result column="emp_name" property="empName"/>
    <result column="age" property="age"/>
    <result column="gender" property="gender"/>
    <result column="dept_id" property="dept.deptId"/>
    <result column="dept_name" property="dept.deptName"/>
  </resultMap>


  <resultMap id="empResultMap" type="emp">
    <!--
    column: 数据库字段列
    property: 实体类属性
    -->
    <!-- 处理主键和实体类属性的映射 -->
    <id column="emp_id" property="empId"/>
    <!-- 处理普通字段和实体类属性的映射 -->
    <result column="emp_name" property="empName"/>
    <result column="age" property="age"/>
    <result column="gender" property="gender"/>
    <association property="dept" javaType="dept">
      <id column="dept_id" property="deptId"/>
      <id column="dept_name" property="deptName"/>
    </association>
  </resultMap>
  <select id="selectEmpAll" resultMap="empResultMap">
    select * from t_emp emp left join t_dept td on emp.dept_id = td.dept_id
  </select>
</mapper>

分步查询

首先查emp查到emp将dept_id传给第二步查询

column: 查询到的字段传给select

select: 下一步的查询方法

<?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="cn.chenmanman.mapper.EmpMapper">
    <!-- 自定义映射规则 -->
    <resultMap id="empResultMapByStep" type="Emp">
        <id column="emp_id" property="empId"/>
        <!-- 处理普通字段和实体类属性的映射 -->
        <result column="emp_name" property="empName"/>
        <result column="age" property="age"/>
        <result column="gender" property="gender"/>
        <association property="dept"
                    column="emp_id"
                    select="cn.chenmanman.mapper.DeptMapper.selectDeptByIdForStepTwo"/>
    </resultMap>
    <select id="selectEmpByIdForStepOne" resultMap="empResultMapByStep">
        select * from t_emp where  emp_id = #{empId}
    </select>
</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="cn.chenmanman.mapper.DeptMapper">
    <select id="selectDeptByIdForStepTwo" resultType="Dept">
        select * from t_dept where dept_id = #{dept_id}
    </select>
</mapper>

3. 延迟加载

在settings标签中设置下面两个属性

lazyLoadingEnabled：延迟加载的全局开关，开启的时候，所有关联独享都会延迟加载。特定关联关系中可以通过设置fetchType属性来覆盖，在此处设置为true

aggressiveLazyLoading:开启时，任一方法的调用都会加载该对象的所有延迟加载属性。否则，每个延迟加载属性会按需加载

将lazyLoadingEnabled设置为true aggressiveLazyLoading设置为false

当在某些地方不希望使用延迟加载属性的话，使用fetchType属性设置为eager

4. 一对多关系映射

一对多关系映射的两种方式

collection标签

在collection标签使用的是ofType集合中所包含的元素类型

<?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="cn.chenmanman.mapper.DeptMapper">
    <resultMap id="deptAndEmpResultMap" type="Dept">
        <id column="dept_id" property="deptId"/>
        <result column="dept_name" property="deptName"/>
        <collection property="empList" ofType="emp">
            <id property="empId" column="emp_id"/>
            <result property="empName" column="emp_name"/>
            <result property="age" column="age"/>
            <result property="gender" column="gender"/>
        </collection>
    </resultMap>
    <select id="selectDeptAndEmpById" resultMap="deptAndEmpResultMap">
        select * from t_dept left join t_emp te on t_dept.dept_id = te.dept_id where te.dept_id = #{dept_id}
    </select>
</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="cn.chenmanman.mapper.DeptMapper">
    <resultMap id="deptAndEmpResultMapByStep" type="Dept">
        <id column="dept_id" property="deptId"/>
        <result column="dept_name" property="deptName"/>
        <collection property="empList"
                    select="cn.chenmanman.mapper.EmpMapper.selectDeptAndEmpByIdForStepTwo"
                    column="dept_id"/>
    </resultMap>
    <!-- 通过查询部门分布查询 -->
    <select id="selectDeptAndEmpByIdForStepOne" resultMap="deptAndEmpResultMapByStep">
        select * from t_dept where dept_id = #{dept_id}
    </select>
</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="cn.chenmanman.mapper.EmpMapper">
    <select id="selectDeptAndEmpByIdForStepTwo" resultType="emp">
        select * from t_emp where dept_id = #{deptId}
    </select>
</mapper>