mybatis的注解开发之三种动态sql

参考资料：https://www.cnblogs.com/guoyafenghome/p/9123442.html

1. 使用xml脚本控制

@Select("<script>select * from user <if test=\"id !=null \">where id = #{id} </if></script>")    
public List<User> findUserById(User user);

类似与在xml文件中进行配置，可读性较差

2. 使用内部类生成动态sql

@Mapper  
public interface MybatisDao {  
    //使用UserDaoProvider类的findUserById方法来生成sql  
    @SelectProvider(type = UserDaoProvider.class, method = "findUserById")  
    public List<User> findUserById(User user);  
      
    class UserDaoProvider {  
        public String findUserById(User user) {  
            String sql = "SELECT * FROM user";  
            if(user.getId()!=null){  
                sql += " where id = #{id}";  
            }  
            return sql;  
        }  
    }
}

通过字符串拼接sql，同时增改删也有对应的@InsertProvider、@UpdateProvider、@DeleteProvider

3. 使用结构化sql

public String findUserById(User user) {      
    return new SQL(){{      
        SELECT("id,name");      
        SELECT("other");      
        FROM("user");      
        if(user.getId()!=null){      
            WHERE("id = #{id}");      
        }      
        if(user.getName()!=null){      
            WHERE("name = #{name}");      
        }      
    //从这个toString可以看出，其内部使用高效的StringBuilder实现SQL拼接      
    }}.toString();      
} 

通过SQL类的结构化sql方法拼接sql，最后通过StringBuilder实现拼接

需要注意：结构化SQL从mybaits3.5.2版本开始支持LIMIT和OFFSET，需要对应的mybaits-spring-boot-starter版本不小于2.1.0

<!-- 引入mybatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>

可以通过LIMIT和OFFSET实现分页

@SelectProvider(type = OrganizationDaoProvider.class, method = "findAll")
    @Results({
        @Result(property = "organizationNo", column = "organization_no"),
        @Result(property = "organizationName", column = "organization_name"),
        @Result(property = "taxFreeType", column = "tax_free_type"),
        @Result(property = "taxFreeValue", column = "tax_free_value"),
        @Result(property = "taxFreeRate", column = "tax_free_rate"),
    })
List<Organization> findAll(int limit, int offset);

class OrganizationDaoProvider {
    public String findAll(int limit, int offset) {
        return new SQL(){{
            SELECT("*");
            SELECT("bin_to_uuid(organization_id) as organizationId");
            FROM("organization");
            if(limit > 0) {
                LIMIT(limit);
            }
            if(offset > 0) {
                OFFSET(offset);
            }
        }}.toString();
    }
}

dao调用

public List<Organization> list(int pageNum, int pageSize) {
    int limit = pageSize;
    int offset = (pageNum - 1) * pageSize;
    return organizationMapper.findAll(limit, offset);
}