mybatis映射文件 select

package com.lzr.dao;

import com.lzr.bean.Employee;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

/**
 * @author GGBond
 * @create 2021-05-22-19:34
 */
public interface EmployeeMapper {

    //多条记录封装一个map：Map:键是这条记录的主键，值是记录封装后的javabean
    //告诉mybatis封装这个map的时候使用哪个属性作为map的key
    @MapKey("id")
    public Map getEmpByLastNameLikeReturnMap(String lastName);

    //返回一条记录的map：key就是列名，值就是对应的值
    public Map getEmpByIdReturnMap(Integer id);

    public List getEmpsByLastNameLike(String lastName);


}

<?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">

    
    
    
        select * from tbl_employee where last_name like #{lastName}
    

    
    
        select * from tbl_employee where id=#{id}
    

    
    
    
        select * from tbl_employee where last_name like #{lastName}
    

 @Test
    public void test04() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //1.获取到的SqlSession不会自动提交数据
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
//            List like = mapper.getEmpsByLastNameLike("%e%");
//            for (Employee employee:like) {
//                System.out.println(employee);
//            }
//            Map map = mapper.getEmpByIdReturnMap(1);
//            System.out.println(map);
            Map map = mapper.getEmpByLastNameLikeReturnMap("%r%");
            System.out.println(map);
        }finally {
            openSession.close();
        }
    }

package com.lzr.bean;

import org.apache.ibatis.type.Alias;

/**
 * @author GGBond
 * @create 2021-05-22-18:17
 */
@Alias("emp")
public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private String gender;
    private Department dept;

    public Employee() {
        super();
    }

    public Employee(Integer id, String lastName, String email, String gender) {
        this.id = id;
        this.lastName = lastName;
        this.email = email;
        this.gender = gender;
    }

    public Department getDept() {
        return dept;
    }

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

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getGender() {
        return gender;
    }

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

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                ", gender='" + gender + '\'' +
                '}';
    }
}

package com.lzr.bean;

/**
 * @author GGBond
 * @create 2021-05-25-10:06
 */
public class Department {

    private Integer id;
    private String departmentName;

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", departmentName='" + departmentName + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }
}

package com.lzr.dao;

import com.lzr.bean.Employee;

/**
 * @author GGBond
 * @create 2021-05-25-9:45
 */
public interface EmployeeMapperPlus {

    public Employee getEmpById(Integer id);

    public Employee getEmpAndDept(Integer id);

}

<?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">

    
    
    
        
        
        
        
        
        
        
    
    
    
    
        select * from tbl_employee where id=#{id}
    

    
    
    
        
        
        
        
        
    
    
    
        select e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,d.id did,d.dept_name dept_name
        from tbl_employee e,tbl_dept d where e.d_id=d.id and e.id=#{id}
    

@Test
    public void test05() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
//            Employee empById = mapper.getEmpById(1);
//            System.out.println(empById);
            Employee empAndDept = mapper.getEmpAndDept(1);
            System.out.println(empAndDept);
            System.out.println(empAndDept.getDept());
        }finally {
            openSession.close();
        }


    }

<?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">

    
    
    
        
        
        
        
        
        
        
    
    
    
    
        select * from tbl_employee where id=#{id}
    

    
    
    
        
        
        
        
        
    
    
    
        
        
        

        
        
            
            
        
    
    
    
        select e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,d.id did,d.dept_name dept_name
        from tbl_employee e,tbl_dept d where e.d_id=d.id and e.id=#{id}
    


    
    
        
        
        
        
        
        
        column="d_id">

        
    
    
    
        select * from tbl_employee where id=#{id}
    

    
    

    
    
    
        select * from tbl_employee where d_id=#{deptId}
    

    

package com.lzr.bean;

import java.util.List;

/**
 * @author GGBond
 * @create 2021-05-25-10:06
 */
public class Department {

    private Integer id;
    private String departmentName;
    private List emps;

    public List getEmps() {
        return emps;
    }

    public void setEmps(List emps) {
        this.emps = emps;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", departmentName='" + departmentName + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }
}

package com.lzr.dao;

import com.lzr.bean.Employee;

import java.util.List;

/**
 * @author GGBond
 * @create 2021-05-25-9:45
 */
public interface EmployeeMapperPlus {

    public Employee getEmpById(Integer id);

    public Employee getEmpAndDept(Integer id);

    public Employee getEmpByIdStep(Integer id);

    public List getEmpsByDeptId(Integer deptId);

}

package com.lzr.dao;

import com.lzr.bean.Department;

/**
 * @author GGBond
 * @create 2021-05-25-14:58
 */
public interface DepartmentMapper {

    public Department getDeptById(Integer id);

    public Department getDeptByIdPlus(Integer id);

    public Department getDeptByIdStep(Integer id);
}

<?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">


    
    
        select id,dept_name departmentName from tbl_dept
        where id=#{id}
    

    
    
    
    
        
        
        
        
            
            
            
            
            
        
    
    
    
        select d.id did,d.dept_name dept_name,e.id eid,
        e.last_name last_name,e.email email,e.gender gender
        from tbl_dept d
        left join tbl_employee e
        on d.id=e.id
        where d.id=#{id}
    


    
        
        
        
        column="{deptId=id}" fetchType="eager">

        
    
    
    
        select id,dept_name departmentName from tbl_dept
        where id=#{id}
    

    

@Test
    public void test05() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
            /*Employee empById = mapper.getEmpById(1);
              System.out.println(empById);*/
            /*Employee empAndDept = mapper.getEmpAndDept(1);
            System.out.println(empAndDept);
            System.out.println(empAndDept.getDept());*/
            Employee employee = mapper.getEmpByIdStep(1);
            System.out.println(employee.getLastName());
            System.out.println(employee.getDept());
        }finally {
            openSession.close();
        }


    }
    @Test
    public void test06() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
//            Department department = mapper.getDeptByIdPlus(1);
//            System.out.println(department);
//            System.out.println(department.getEmps());
            Department deptByIdStep = mapper.getDeptByIdStep(1);
            System.out.println(deptByIdStep);
            System.out.println(deptByIdStep.getEmps());
        }finally {
            openSession.close();
        }
    }