MyBatis的复杂查询与动态标签

1、建立实体类

package com.yas.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.Date;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class SysStaff {
    private Integer id;
    private String name;
    private Integer status;
    private String remark;
    private Integer organization_id;
    private Date gmt_create;
    private Date gmt_modified;

    private SysOrganization organization;
}

package com.yas.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.Date;
import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class SysOrganization {
    private Integer id;
    private String department_name;
    private Integer status;
    private String remark;
    private Date gmt_create;
    private Date gmt_modified;

    private List<SysStaff> staffs;
}

2、建立查询接口：

package com.yas.dao;

import com.yas.entity.SysStaff;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface SysStaffDAO {
    //@Select("SELECT * FROM sys_staff s join sys_organization o on s.organization_id=o.id WHERE s.id = #{id}")
    SysStaff queryStaffById(@Param("id") Integer id);

    List<SysStaff> queryStaffByName(@Param("name") String name);

    Integer updateStaff(SysStaff staff);

    Integer deleteManyStaff(List<Integer> ids);
}

package com.yas.dao;

import com.yas.entity.SysOrganization;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface SysOrganizationDAO {
    SysOrganization queryOrganizationById(@Param("id") Integer id);
}

3、Mapper映射文件：

StaffDAOMapper.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="com.yas.dao.SysStaffDAO">
    <resultMap id="staff_org" type="sysstaff">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="status" property="status"></result>
        <result column="remark" property="remark"></result>
        <result column="organization_id" property="organization_id"></result>
        <result column="gmt_create" property="gmt_create"></result>
        <result column="gmt_modified" property="gmt_modified"></result>

        <association property="organization" javaType="SysOrganization">
            <id column="oid" property="id"></id>
            <result column="department_name" property="department_name"></result>
        </association>
    </resultMap>

    <select id="queryStaffById" resultMap="staff_org">
        SELECT s.id,s.name,s.status,s.remark,s.organization_id,s.gmt_create,s.gmt_modified,o.id as oid,o.department_name FROM sys_staff s join sys_organization o on s.organization_id=o.id WHERE s.id = #{id}
    </select>

    <!-- 抽取重复的sql -->
    <sql id="basic">
        select * from sys_staff
    </sql>
    <select id="queryStaffByName" resultType="SysStaff">
        <include refid="basic"></include>
    <!-- where 会自动去除 条件语句中以and or开头 -->
<!--        <where>-->
<!--            <if test="name != ''">-->
<!--                name = #{name}-->
<!--            </if>-->
<!--        </where>-->
        <trim prefix="where" prefixOverrides="or|and">
            <if test="name != ''">
                `name` = #{name}
            </if>
        </trim>
    </select>


    <update id="updateStaff" parameterType="SysStaff">
        update sys_staff

<!--        <set>-->
<!--            <if test="name != null">-->
<!--                name = #{name},-->
<!--            </if>-->
<!--            <if test="remark!=null">-->
<!--                remark = #{remark}-->
<!--            </if>-->
<!--        </set>-->
<!--        <where>-->
<!--            id = #{id}-->
<!--        </where>-->

        <trim prefix="set" prefixOverrides=",">
            <if test="name != null">
                `name` = #{name},
            </if>
            <if test="remark!=null">
                remark = #{remark}
            </if>
        </trim>
        <trim prefix="where" prefixOverrides="or|and">
            id = #{id}
        </trim>
    </update>

    <delete id="deleteManyStaff" parameterType="java.util.List">
        delete from sys_staff where id in
        <foreach collection="list" open="(" close=")" item="sid" separator=",">
            #{sid}
        </foreach>
    </delete>
</mapper>

OrganizationDAOMapper.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="com.yas.dao.SysOrganizationDAO">
    <resultMap id="org_staff" type="SysOrganization">
        <id column="id" property="id"></id>
        <result column="department_name" property="department_name"></result>
        <result column="status" property="status"></result>
        <result column="remark" property="remark"></result>
        <result column="gmt_create" property="gmt_create"></result>
        <result column="gmt_modified" property="gmt_modified"></result>

        <collection property="staffs" ofType="SysStaff">
            <id column="sid" property="id"></id>
            <result column="name" property="name"></result>
        </collection>
    </resultMap>

    <select id="queryOrganizationById" resultMap="org_staff">
        SELECT o.id,o.department_name,o.status,o.remark,o.gmt_create,o.gmt_modified,s.id AS sid,s.name
        FROM sys_organization o JOIN sys_staff s
        ON o.id = s.organization_id
        WHERE o.id=#{id}
    </select>
</mapper>

4、测试类：

package com.yas;

import com.yas.dao.SysOrganizationDAO;
import com.yas.dao.SysStaffDAO;
import com.yas.dao.SysUserDAO;
import com.yas.entity.SysOrganization;
import com.yas.entity.SysStaff;
import com.yas.entity.SysUser;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@SpringBootTest
class MyBatisBootApplicationTests {

    @Autowired
    SysStaffDAO sysStaffDAO;

    @Test
    void queryStaff() {
//        SysStaff staff = sysStaffDAO.queryStaffById(1);
//        System.out.println(staff);

//        List<SysStaff> staffs = sysStaffDAO.queryStaffByName("");
//        for (SysStaff staff : staffs) {
//            System.out.println(staff);
//        }

//        SysStaff staff = new SysStaff(1,"张三",1,"法外狂徒",1,null,null,null);
//        sysStaffDAO.updateStaff(staff);

        List<Integer> list = new ArrayList<Integer>();
        list.add(14);
        list.add(15);
        Integer result = sysStaffDAO.deleteManyStaff(list);
        System.out.println(result);
    }

    @Autowired
    SysOrganizationDAO sysOrganizationDAO;

    @Test
    void queryOrg() {
        SysOrganization organization = sysOrganizationDAO.queryOrganizationById(4);
        System.out.println(organization);
    }
}