Mybatis SqlProvider用法详解

Mybatis3中增加了使用注解来配置Mapper的新特性，这次主要介绍其中几个@Provider的使用方式，分别是：@SelectProvider、@UpdateProvider、@InsertProvider和@DeleteProvider。

这种写法的好处是不用编写任何xml文件，支持动态sql及多表联查，非常方便

用法简介

1.@对应的注解名(type=类名.class,method="方法名"):通过调用Class上的这个方法来获得sql语句

2.public 方法的返回值 方法名(参数类型 参数);

@SelectProvider用法及动态sql拼接

//mapper层定义
@Mapper
public interface FootMatch310Mapper {
    @SelectProvider(type = FootMatch310SqlProvider.class, method = "findAll")
    List<FootMatch310> findAll(Map<String, Object> params);  

    @SelectProvider(type = FootMatch310SqlProvider.class, method = "queryInfo")
    List<FootMatch310> queryInfo(Map<String, Object> params);
}


//SqlProvider层 动态sql拼接用法
public class FootMatch310SqlProvider {
    //使用map进行参数传参
    public String findAll(Map<String, Object> params) {
        return new SQL() {{
            SELECT("*");
            FROM("FOOT_MATCH_310");
            //相等查
            if (params.get("searchLeague") != null && 
                !"".equals(params.get("searchLeague"))) {
                WHERE("league = #{searchLeague}");
            }
            //模糊查
            if (params.get("searchSquare") != null &&    
                !"".equals(params.get("searchSquare"))) {
                WHERE("square = LIKE CONCAT('%', #{searchSquare}, '%')");
            }
        }}.toString();
    }

    public String queryInfo(Map<String, Object> params){
        return new SQL() {{
            SELECT("distinct f.*");
            FROM("FOOT_MATCH_310 AS f");
            // JOIN 内连接 LEFT_OUTER_JOIN 左连接   RIGHT_OUTER_JOIN 右连接
            LEFT_OUTER_JOIN("FOOT_MATCH_310_ASIAN_COM  AS fc  ON f.competition_id = 
                             fc.competition_id");
             //查区间
            if (params.get("initCalculationValue1") != null && 
                !"".equals(params.get("initCalculationValue1"))) {
                // 将用户输入转换为一个范围
                int input = Integer.parseInt((String)      params.get("initCalculationValue1"));
                params.put("lowerBound1", input);
                params.put("upperBound1", input + 1);
                WHERE("fc.init_calculation_value1 BETWEEN #{lowerBound1} AND #
                      {upperBound1}");
            }
          
    }}.toString();
}

@InsertProvider用法

//mapper层
@Mapper
public interface FootMatch310Mapper {
  @InsertProvider(type = FootMatch310SqlProvider.class, method = "save") 
  public void save(Map<String, Object> params);
}

//SqlProvider层
public class FootMatch310SqlProvider {
    public String save(Map<String, Object> params) {
        return new SQL() {{
            INSERT_INTO("FOOT_MATCH_310");
            //多个写法.
            INTO_COLUMNS("league", "square");
            INTO_VALUES("#{league}", "#{square}");

            //条件写法.
            if (params.get("league") != null && 
                !"".equals(params.get("league"))) {
                VALUES("league", "#{league}");
            }
            if (params.get("square") != null && 
                !"".equals(params.get("square"))) {
                VALUES("square", "#{square}");
            }

        }}.toString();
    }
}

@UpdateProvider用法

//mapper层
@Mapper
public interface FootMatch310Mapper {
    @UpdateProvider(type=FootMatch310SqlProvider.class, method="update")
    int update(@Param("id") int id, @Param("name") String name);
}

//SqlProvider层
public class FootMatch310SqlProvider {
    public String update(Map<String, Object> params) {
        return new SQL(){{
            UPDATE("FOOT_MATCH_310");
            if(params.get("league") != null && 
                !"".equals(params.get("league"))) {
                SET("league = #{league}");
            }
            WHERE("id = #{id}");
        }}.toString();
    }
}

@DeleteProvider用法

//mapper层
@Mapper
public interface FootMatch310Mapper {
    @DeleteProvider(type=FootMatch310SqlProvider.class, method="delete")
    int delete(@Param("id") int id);
}

public class FootMatch310SqlProvider {
    public String delete(Map<String, Object> params) {
        return new SQL(){{
            DELETE_FROM("FOOT_MATCH_310");
            WHERE("id = #{id}");
        }}.toString();
    }
}