Mybatis高级查询

• ResultMap属性

package com.demo.domain;

import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Integer id;
    private String usernameabc;
    private String passwordabc;
    private Date birthdayab;
    private String sexabc;
    private String addressabc;
}

package com.demo.mapper;

import com.demo.domain.User;

import java.util.List;

public interface UserMapper {

    /*
        根据id查询用户
     */
    public User findUserById(int id);

    /**
     * 查询所有用户
     * @return
     */
    public List findAllResultMap();
}

<?xml version="1.0" encoding="UTF-8" ?>


    
    
        select * from user where id = #{id}
    

    
    
        
        
        
        
        
        
        
        
    

    
    
    
        select * from user
    

• 多条件查询
• 方式一：#{arg0}~#{argn} 或者 #{param1}~#{paramn} 获取参数

public List findByIdAndUsername1(int id, String username);

  select * from user where id = #{param1} and username = #{param2}

• 方式二：注解， @Param()注解获取参数

public List findByIdAndUsername2(@Param("id") int id,@Param("username") String username);

  select * from user where id = #{id} and username = #{username}

• 方式三：pojo对象传递参数

public List findByIdAndUsername3(User user);

  select * from user where id = #{id} and username = #{usernameabc}

• 模糊查询

• 方式一：#{} 在Mybatis中占位符，引用参数值的时会自动添加单引号

public List findByUsernameLike(String username);

  
  select * from user where username like #{username}

List users = mapper.findByUsernameLike("%aa%");

users.forEach(System.out::println);

• 方式二：${} sql原样拼接

  
  select * from user where username like '${value}'

List users = mapper.findByUsernameLike("%aa%");

users.forEach(System.out::println);

Mybatis映射文件深入

• 返回主键

向数据库插入一条记录后，能立即拿到这条记录在数据库的主键值。

• useGeneratedKeys

只适用于主键自增的数据库，mysql和sqlserver支持，oracle不支持。

  insert into user (username, birthday, sex, address) values (#{username}, #{birthday}, #{sex}, #{address})

• selectKey

适用范围更广,支持所有类型的数据库

    
      SELECT LAST_INSERT_ID();
		

		insert into user (username, birthday, sex, address) values (#{username}, #{birthday}, #{sex}, #{address})

动态SQL之：不确定两个都有值

  select * from user /*where 1 = 1*/
    
	
      
        	and id = #{id}
			
			
  				and username = #{username}
			
	

动态SQL之：有值更新；没有值不做处理

    
    
        update user
            
            
                
                    username = #{username},
                
                
                    birthday = #{birthday},
                
                
                    sex = #{sex},
                
                
                    address = #{address},
                
            
                where id = #{id}
    

动态SQL之：数据的循环遍历

• 集合

    
    
        select * from user
        
            
            
                #{id}
            
        
    

• 数组

        select * from user
            
          			
                
                    #{id}
                
            
    

SQL片段

映射文件中可将重复的sql提取出来，使用时用include引用即可，最终达到sql重用的目的。

Mybatis核心配置文件深入

• plugin

• 分页

    
    
      com.github.pagehelper
      pagehelper
      5.3.1
    

    
      com.github.jsqlparser
      jsqlparser
      4.5
    

        // 设置分页参数
        // 参数1: 当前页
        // 参数2: 每页显示的条数
        // 一定要写在调用数据库的上面
        PageHelper.startPage(1, 1);

        List users = mapper.findAllResultMap();
        for (User user : users) {
            System.out.println("user = " + user);
        }

        // 获取分页相关的其它参数
        PageInfo pageInfo = new PageInfo<>(users);
        // 总条数
        long total = pageInfo.getTotal();
        // 总页数
        int pages = pageInfo.getPages();
        // 当前页
        int pageNum = pageInfo.getPageNum();
        // 每页显示长度
        int pageSize = pageInfo.getPageSize();
        // 判断是否第一页
        boolean isFirstPage = pageInfo.isIsFirstPage();
        // 是否最后一页
        boolean isLastPage = pageInfo.isIsLastPage();

Mybatis多表查询

package com.demo.domain;

import lombok.Data;

@Data
public class Orders {
    private Integer id;
    private String ordertime;
    private Double total;
    private Integer uid;
    private User user;
}

package com.demo.domain;

import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Integer id;
    private String username;
    private String password;
    private Date birthday;
    private String sex;
    private String address;
}

• 一对一（多对一）

<?xml version="1.0" encoding="UTF-8" ?>


    
    
        
        
        
        

        
        
            
            
            
            
            
        
    
    
        select * from orders o left join user u on o.uid = u.id
    

• 一对多

<?xml version="1.0" encoding="UTF-8" ?>



    
        
        
        
        
        

        
        
            
            
            
            
        
    
    
        select u.*, o.id oid, o.ordertime, o.total, o.uid from orders o right join user u on o.uid = u.id
    

• 多对多

package com.demo.domain;

import lombok.Data;

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

@Data
public class User {
    private Integer id;
    private String username;
    private String password;
    private Date birthday;
    private String sex;
    private String address;

    // 一个用户多个角色
    private List roleList;
}

package com.demo.domain;

import lombok.Data;

@Data
public class Role {
    private Integer id;
    private String rolename;
    private String roleDesc;
}

<?xml version="1.0" encoding="UTF-8" ?>


    
        
        
        
        
        

        
            
            
            
        
    

    
        select u.*, r.id rid, r.rolename, r.roleDesc from user u left join sys_user_role ur on ur.userid = u.id
                               left join sys_role r on ur.roleid = r.id
    

嵌套查询

将原来多表查询中的联合查询语句拆成单个表的查询，再使用mybatis的语法嵌套在一起。

• 一对一

package com.demo.domain;

import lombok.Data;

@Data
public class Orders {
    private Integer id;
    private String ordertime;
    private Double total;
    private Integer uid;

    private User user;
}

<?xml version="1.0" encoding="UTF-8" ?>



    
        
        
        
        

        
    

    
    
        select * from orders
    

<?xml version="1.0" encoding="UTF-8" ?>


    
    
        select * from user where id = #{id}
    

• 一对多

package com.demo.domain;

import lombok.Data;

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

@Data
public class User {
    private Integer id;
    private String username;
    private String password;
    private Date birthday;
    private String sex;
    private String address;
    private List ordersList;
}

<?xml version="1.0" encoding="UTF-8" ?>


    
    
        
        
        
        
        

        
    

    
        select * from user
    

<?xml version="1.0" encoding="UTF-8" ?>


    
        select * from orders where uid = #{uid}
    

• 多对多

package com.demo.domain;

import lombok.Data;

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

@Data
public class User {
    private Integer id;
    private String username;
    private String password;
    private Date birthday;
    private String sex;
    private String address;
    private List roleList;
}

<?xml version="1.0" encoding="UTF-8" ?>


    
    
        
        
        
        
        

        
    

    
        select * from user
    

<?xml version="1.0" encoding="UTF-8" ?>


    
        select * from sys_role r inner join sys_user_role ur on ur.roleid = r.id
            where ur.userid = #{uid}