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 }