Mybatis入门（三）：查询操作

• 查询map集合

Map getUserToMap(@Param("id") int id);

 select * from t_user where id = #{id} 

• 查询多个map集合

List> getAllUserToMap();

 select * from t_user 

• 查询多个map集合

@MapKey("id") 
Map getAllUserToMap();

 select * from t_user 

{ 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 testMohu(@Param("mohu") String mohu);

 
     
     
    select * from t_user where username like "%"#{mohu}"%" 

• 批量删除

mapper.deleteMore("1, 2, 3");

int deleteMore(@Param("ids") String ids);

 delete from t_user where id in (${ids}) 

• 动态表名

List getAllUser(@Param("tableName") String tableName);

 select * from ${tableName} 

• 获取自增主键

//useGeneratedKeys：设置使用自增的主键，keyProperty：因为增删改有统一的返回值是受影响的行数，因此只能将获取的自增的主键放在传输的参 数user对象的某个属性中
int insertUser(User user);

 
    insert into t_user values(null,#{username},#{password},#{age},#{sex}) 

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

List getAllEmp();


        select id, emp_name empName, age from t_emp

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

# mybatis配置文件中配置如下
    
        
        
    

# mapper
List getAllEmp();

# xml映射

        select id, emp_name, age from t_emp

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

# mapper
List getAllEmp();

# xml映射
    
        
        
        
        
        
    


        select id, emp_name, age from t_emp

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

# 部门表
public class Dept {

    private Integer did;

    private String deptName;

    //private List emps;

    @Override
    public String toString() {
        return "Dept{" +
                "did=" + did +
                ", deptName='" + deptName + '\'' +
                //", emps=" + emps +
                '}';
    }

    //public List getEmps() {
        //return emps;
    //}

    //public void setEmps(List 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);


        
        
        
        
        
        
        



        select * from t_emp left join t_dept on t_emp.did = t_dept .did where t_emp.eid = #{eid}

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

Emp getEmpAndDept(@Param("eid") Integer eid);

    
        
        
        
        
        
        
        
            
            
        
    


        select * from t_emp left join t_dept on t_emp.did = t_dept .did where t_emp.eid = #{eid}

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

# 部门mapper
Dept getEmpDeptByStep(@Param("did") int did);
# 部门映射文件，根据部门id查询部门信息
 
    select * from t_dept where did = #{did} 


# 员工mapper
Emp getEmpByStep(@Param("eid") int eid);
# 员工映射文件
 
     
     
     
     
        
         
         
 

 
    select * from t_emp where eid = #{eid} 

• 开启延迟加载

# lazyLoadingEnabled设置为true，aggressiveLazyLoding设置为false，mybatis版本>=3.4.1时默认为true

    

• 一对多映射，方式1

# 部门表，添加员工对象，并添加getter和setter方法，toString方法
public class Dept {

    private Integer did;

    private String deptName;

    private List emps;

    @Override
    public String toString() {
        return "Dept{" +
                "did=" + did +
                ", deptName='" + deptName + '\'' +
                ", emps=" + emps +
                '}';
    }

    public List getEmps() {
        return emps;
    }

    public void setEmps(List 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查询数据
 
     
     
     
     
         
         
         
         
     
 

 
    select dept.*,emp.* from t_dept dept left join t_emp emp on dept.did = emp.did where dept.did = #{did} 

• 一对多映射，方式2

# 部门mapper
Dept getDeptByStep(@Param("did") int did);
# 部门xml，根据部门id查询数据
 
     
     
         
 

 
    select * from t_dept where did = #{did} 


# 员工mapper
List getEmpListByDid(@Param("did") int did);
# 员工xml，根据员工id查询数据
 
    select * from t_emp where did = #{did}