Mybatis入门（三）：查询操作

• 查询map集合

Map<String, Object> getUserToMap(@Param("id") int id);
 
<select id="getUserToMap" resultType="map"> select * from t_user where id = #{id} </select>

• 查询多个map集合

List<Map<String, Object>> getAllUserToMap();
 
<select id="getAllUserToMap" resultType="map"> select * from t_user </select>

• 查询多个map集合

@MapKey("id") 
Map<String, Object> getAllUserToMap();
 
<select id="getAllUserToMap" resultType="map"> select * from t_user </select>
 
{ 1={password=123456, sex=男, id=1, age=23, username=admin}, 2={password=123456, sex=男, id=2, age=23, username=张三}, 3={password=123456, sex=男, id=3, age=23, username=张三} }

• 模糊查询

List<User> testMohu(@Param("mohu") String mohu);
 
<select id="testMohu" resultType="User"> 
    <!--select * from t_user where username like '%${mohu}%'--> 
    <!--select * from t_user where username like concat('%',#{mohu},'%')--> 
    select * from t_user where username like "%"#{mohu}"%" 
</select>

• 批量删除

mapper.deleteMore("1, 2, 3");
 
int deleteMore(@Param("ids") String ids);
 
<delete id="deleteMore"> delete from t_user where id in (${ids}) </delete>

• 动态表名

List<User> getAllUser(@Param("tableName") String tableName);
 
<select id="getAllUser" resultType="User"> select * from ${tableName} </select>

• 获取自增主键

//useGeneratedKeys：设置使用自增的主键，keyProperty：因为增删改有统一的返回值是受影响的行数，因此只能将获取的自增的主键放在传输的参 数user对象的某个属性中
int insertUser(User user);
 
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id"> 
    insert into t_user values(null,#{username},#{password},#{age},#{sex}) 
</insert>
 

• 字段名和实体类中的属性名不一致，解决方案1

List<Emp> getAllEmp();
 
<select id="getAllEmp" resultMap="Emp">
        select id, emp_name empName, age from t_emp
</select>

• 字段名和实体类中的属性名不一致，解决方案2

# mybatis配置文件中配置如下
    <settings>
        <!--将_自动映射为驼峰，emp_name:empName-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
 
# mapper
List<Emp> getAllEmp();
 
# xml映射
<select id="getAllEmp" resultMap="Emp">
        select id, emp_name, age from t_emp
</select>

• 字段名和实体类中的属性名不一致，解决方案3

# mapper
List<Emp> getAllEmp();
 
# xml映射
    <resultMap id="empResultMap" type="Emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
    </resultMap>
 
<select id="getAllEmp" resultMap="empResultMap">
        select id, emp_name, age from t_emp
</select>

• 处理多对一的映射关系，方式1

# 部门表
public class Dept {
 
    private Integer did;
 
    private String deptName;
 
    //private List<Emp> emps;
 
    @Override
    public String toString() {
        return "Dept{" +
                "did=" + did +
                ", deptName='" + deptName + '\'' +
                //", emps=" + emps +
                '}';
    }
 
    //public List<Emp> getEmps() {
        //return emps;
    //}
 
    //public void setEmps(List<Emp> emps) {
        //this.emps = emps;
    //}
 
    public Integer getDid() {
        return did;
    }
 
    public void setDid(Integer did) {
        this.did = did;
    }
 
    public String getDeptName() {
        return deptName;
    }
 
    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }
 
    public Dept() {
    }
 
    public Dept(Integer did, String deptName) {
        this.did = did;
        this.deptName = deptName;
    }
}
 
# 员工表，添加部门对象作为属性，编写getter方法和setter方法，toString方法
public class Emp implements Serializable {
 
    private Integer eid;
 
    private String empName;
 
    private Integer age;
 
    private String sex;
 
    private String email;
 
    private Dept dept;
 
    @Override
    public String toString() {
        return "Emp{" +
                "eid=" + eid +
                ", empName='" + empName + '\'' +
                ", age=" + age +
                ", sex='" + sex + '\'' +
                ", email='" + email + '\'' +
                ", dept=" + dept +
                '}';
    }
 
    public Dept getDept() {
        return dept;
    }
 
    public void setDept(Dept dept) {
        this.dept = dept;
    }
 
    public Integer getEid() {
        return eid;
    }
 
    public void setEid(Integer eid) {
        this.eid = eid;
    }
 
    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 getSex() {
        return sex;
    }
 
    public void setSex(String sex) {
        this.sex = sex;
    }
 
    public String getEmail() {
        return email;
    }
 
    public void setEmail(String email) {
        this.email = email;
    }
 
    public Emp() {
    }
 
    public Emp(Integer eid, String empName, Integer age, String sex, String email) {
        this.eid = eid;
        this.empName = empName;
        this.age = age;
        this.sex = sex;
        this.email = email;
    }
}
 
Emp getEmpAndDept(@Param("eid") Integer eid);
 
<resultMap id="empAndDeptResultMapOne" type="Emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <result property="dept.did" column="did"></result>
        <result property="dept.deptName" column="dept_name"></result>
</resultMap>
 
<select id="getEmpAndDept" resultMap="empAndDeptResultMapOne">
        select * from t_emp left join t_dept on t_emp.did = t_dept .did where t_emp.eid = #{eid}
</select>

• 处理多对一的映射关系，方式2

Emp getEmpAndDept(@Param("eid") Integer eid);
 
    <resultMap id="empAndDeptResultMapTwo" type="Emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <!--
            association:处理多对一的映射关系
            property:需要处理多对的映射关系的属性名
            javaType:该属性的类型
        -->
        <association property="dept" javaType="Dept">
            <id property="did" column="did"></id>
            <result property="deptName" column="dept_name"></result>
        </association>
    </resultMap>
 
<select id="getEmpAndDept" resultMap="empAndDeptResultMapOne">
        select * from t_emp left join t_dept on t_emp.did = t_dept .did where t_emp.eid = #{eid}
</select>

• 处理多对一的映射关系，方式3

# 部门mapper
Dept getEmpDeptByStep(@Param("did") int did);
# 部门映射文件，根据部门id查询部门信息
<select id="getEmpDeptByStep" resultType="Dept"> 
    select * from t_dept where did = #{did} 
</select>
 
# 员工mapper
Emp getEmpByStep(@Param("eid") int eid);
# 员工映射文件
<resultMap id="empDeptStepMap" type="Emp"> 
    <id column="eid" property="eid"></id> 
    <result column="ename" property="ename"></result> 
    <result column="age" property="age"></result> 
    <result column="sex" property="sex"></result> 
        <!--select：设置分步查询，查询某个属性的值的sql的标识（namespace.sqlId） column：将sql以及查询结果中的某个字段设置为分步查询的条件 -->
        <association property="dept" select="com.atguigu.MyBatis.mapper.DeptMapper.getEmpDeptByStep" column="did"> 
        </association> 
</resultMap> 
 
<select id="getEmpByStep" resultMap="empDeptStepMap"> 
    select * from t_emp where eid = #{eid} 
</select>

• 开启延迟加载

# lazyLoadingEnabled设置为true，aggressiveLazyLoding设置为false，mybatis版本>=3.4.1时默认为true
<settings>
    <setting name="lazyLoadingEnabled" value="true"/>
</settings>

• 一对多映射，方式1

# 部门表，添加员工对象，并添加getter和setter方法，toString方法
public class Dept {
 
    private Integer did;
 
    private String deptName;
 
    private List<Emp> emps;
 
    @Override
    public String toString() {
        return "Dept{" +
                "did=" + did +
                ", deptName='" + deptName + '\'' +
                ", emps=" + emps +
                '}';
    }
 
    public List<Emp> getEmps() {
        return emps;
    }
 
    public void setEmps(List<Emp> emps) {
        this.emps = emps;
    }
 
    public Integer getDid() {
        return did;
    }
 
    public void setDid(Integer did) {
        this.did = did;
    }
 
    public String getDeptName() {
        return deptName;
    }
 
    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }
 
    public Dept() {
    }
 
    public Dept(Integer did, String deptName) {
        this.did = did;
        this.deptName = deptName;
    }
}
 
# mapper
Dept getDeptEmpByDid(@Param("did") int did);
 
# xml，配置返回结果集，根据部门id查询数据
<resultMap id="deptEmpMap" type="Dept"> 
    <id property="did" column="did"></id> 
    <result property="dname" column="dname"></result> 
    <!--ofType：设置collection标签所处理的集合属性中存储数据的类型 --> 
    <collection property="emps" ofType="Emp"> 
        <id property="eid" column="eid"></id> 
        <result property="ename" column="ename"></result> 
        <result property="age" column="age"></result> 
        <result property="sex" column="sex"></result> 
    </collection> 
</resultMap> 
 
<select id="getDeptEmpByDid" resultMap="deptEmpMap"> 
    select dept.*,emp.* from t_dept dept left join t_emp emp on dept.did = emp.did where dept.did = #{did} 
</select>

• 一对多映射，方式2

# 部门mapper
Dept getDeptByStep(@Param("did") int did);
# 部门xml，根据部门id查询数据
<resultMap id="deptEmpStep" type="Dept"> 
    <id property="did" column="did"></id> 
    <result property="dname" column="dname"></result> 
        <collection property="emps" fetchType="eager" select="com.atguigu.MyBatis.mapper.EmpMapper.getEmpListByDid" column="did"></collection> 
</resultMap> 
 
<select id="getDeptByStep" resultMap="deptEmpStep"> 
    select * from t_dept where did = #{did} 
</select>
 
# 员工mapper
List<Emp> getEmpListByDid(@Param("did") int did);
# 员工xml，根据员工id查询数据
<select id="getEmpListByDid" resultType="Emp"> 
    select * from t_emp where did = #{did} 
</select>