MyBatis的复杂查询与动态标签

1、建立实体类

 1 package com.yas.entity;
 2 
 3 import lombok.AllArgsConstructor;
 4 import lombok.Data;
 5 import lombok.NoArgsConstructor;
 6 import lombok.ToString;
 7 
 8 import java.util.Date;
 9 
10 @Data
11 @NoArgsConstructor
12 @AllArgsConstructor
13 @ToString
14 public class SysStaff {
15     private Integer id;
16     private String name;
17     private Integer status;
18     private String remark;
19     private Integer organization_id;
20     private Date gmt_create;
21     private Date gmt_modified;
22 
23     private SysOrganization organization;
24 }
 1 package com.yas.entity;
 2 
 3 import lombok.AllArgsConstructor;
 4 import lombok.Data;
 5 import lombok.NoArgsConstructor;
 6 import lombok.ToString;
 7 
 8 import java.util.Date;
 9 import java.util.List;
10 
11 @Data
12 @NoArgsConstructor
13 @AllArgsConstructor
14 @ToString
15 public class SysOrganization {
16     private Integer id;
17     private String department_name;
18     private Integer status;
19     private String remark;
20     private Date gmt_create;
21     private Date gmt_modified;
22 
23     private List<SysStaff> staffs;
24 }

 

2、建立查询接口:

 1 package com.yas.dao;
 2 
 3 import com.yas.entity.SysStaff;
 4 import org.apache.ibatis.annotations.Mapper;
 5 import org.apache.ibatis.annotations.Param;
 6 
 7 import java.util.List;
 8 
 9 @Mapper
10 public interface SysStaffDAO {
11     //@Select("SELECT * FROM sys_staff s join sys_organization o on s.organization_id=o.id WHERE s.id = #{id}")
12     SysStaff queryStaffById(@Param("id") Integer id);
13 
14     List<SysStaff> queryStaffByName(@Param("name") String name);
15 
16     Integer updateStaff(SysStaff staff);
17 
18     Integer deleteManyStaff(List<Integer> ids);
19 }
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、测试类:

 1 package com.yas;
 2 
 3 import com.yas.dao.SysOrganizationDAO;
 4 import com.yas.dao.SysStaffDAO;
 5 import com.yas.dao.SysUserDAO;
 6 import com.yas.entity.SysOrganization;
 7 import com.yas.entity.SysStaff;
 8 import com.yas.entity.SysUser;
 9 import org.junit.jupiter.api.Test;
10 import org.springframework.beans.factory.annotation.Autowired;
11 import org.springframework.boot.test.context.SpringBootTest;
12 
13 import java.util.ArrayList;
14 import java.util.Date;
15 import java.util.List;
16 
17 @SpringBootTest
18 class MyBatisBootApplicationTests {
19 
20     @Autowired
21     SysStaffDAO sysStaffDAO;
22 
23     @Test
24     void queryStaff() {
25 //        SysStaff staff = sysStaffDAO.queryStaffById(1);
26 //        System.out.println(staff);
27 
28 //        List<SysStaff> staffs = sysStaffDAO.queryStaffByName("");
29 //        for (SysStaff staff : staffs) {
30 //            System.out.println(staff);
31 //        }
32 
33 //        SysStaff staff = new SysStaff(1,"张三",1,"法外狂徒",1,null,null,null);
34 //        sysStaffDAO.updateStaff(staff);
35 
36         List<Integer> list = new ArrayList<Integer>();
37         list.add(14);
38         list.add(15);
39         Integer result = sysStaffDAO.deleteManyStaff(list);
40         System.out.println(result);
41     }
42 
43     @Autowired
44     SysOrganizationDAO sysOrganizationDAO;
45 
46     @Test
47     void queryOrg() {
48         SysOrganization organization = sysOrganizationDAO.queryOrganizationById(4);
49         System.out.println(organization);
50     }
51 }

 

上一篇:《JAVA核心技术卷I》阅读笔记[记录重点,梳理脉络]


下一篇:java将数据同时插入到两张表中