Mybatis：在sqlBuilder中动态的生成sql语句

浏览数：123 / 时间：2015年06月12日

最近项目当中使用到MyBatis 动态语句生成的功能，我使用的是sqlBuilder，话不多说，直接上代码，StockMarketProvider.java：

public String selectByStockIdSql(){
        BEGIN();
        SELECT("*");
        FROM(TABLE_NAME);
        WHERE("stock_id = #{0}");
        return SQL();
    }
    
    public String updateByStockIdSql(){
        BEGIN();
        UPDATE(TABLE_NAME);
        SET("jkp=#{jkp}");
        SET("zsp=#{zsp}");
        SET("zgj=#{zgj}");
        SET("zdj=#{zdj}");
        SET("ztj=#{ztj}");
        SET("dtj=#{dtj}");
        SET("hsl=#{hsl}");
        SET("zf=#{zf}");
        SET("syl=#{syl}");
        SET("sjl=#{sjl}");
        SET("cjl=#{cjl}");
        SET("cje=#{cje}");
        SET("zsz=#{zsz}");
        SET("ltsz=#{ltsz}");
        SET("mr1=#{mr1}");
        SET("mr2=#{mr2}");
        SET("mr3=#{mr3}");
        SET("mr4=#{mr4}");
        SET("mr5=#{mr5}");
        SET("mc1=#{mc1}");
        SET("mc2=#{mc2}");
        SET("mc3=#{mc3}");
        SET("mc4=#{mc4}");
        SET("mc5=#{mc5}");
        SET("mr1_num=#{mr1Num}");
        SET("mr2_num=#{mr2Num}");
        SET("mr3_num=#{mr3Num}");
        SET("mr4_num=#{mr4Num}");
        SET("mr5_num=#{mr5Num}");
        SET("mc1_num=#{mc1Num}");
        SET("mc2_num=#{mc2Num}");
        SET("mc3_num=#{mc3Num}");
        SET("mc4_num=#{mc4Num}");
        SET("mc5_num=#{mc5Num}");
        SET("status=#{status}");
        SET("dqj=#{dqj}");
        SET("bhz=#{bhz}");
        SET("bhl=#{bhl}");
        WHERE("stock_id=#{stockId}");
        return SQL();
    }
    //SELECT * FROM market AS a WHERE a.stock_id IN (SELECT id FROM stock AS b WHERE b.`type`=1) 
    //AND a.`status`=0 ORDER BY bhl DESC LIMIT 10;
    public String selectByOrderAtLimitSql(Map<String,Object> params){
        String orderby = (String)params.get("orderby");//使用Params注解，当中的名称作为key        BEGIN();
        SELECT("*");
        FROM(TABLE_NAME+" AS a");
        WHERE("a.stock_id IN (SELECT id FROM stock AS b WHERE b.type=1) and a.status=0");
        String order="DESC";
        if("bhl".equals(orderby)){
            order="DESC";
          ORDER_BY("bhl");
        }else if("hsl".equals(orderby)){
            order="ASC";
            ORDER_BY("hsl");
        }else if("zf".equals(orderby)){
            order="DESC";
            ORDER_BY("zf");
        }
        return SQL()+" "+order+" limit #{limit}";
    }
    
    public String selectDiefuListSql(){
        BEGIN();
        SELECT("*");
        FROM(TABLE_NAME+" AS a");
        WHERE("a.stock_id IN (SELECT id FROM stock AS b WHERE b.type=1) AND a.status=0 AND a.bhl<0 ");
        ORDER_BY("a.bhl");
        return SQL()+" limit #{0}";//我还没有查到怎样使用limit所以干脆直接加到后面了
    }

对应的StockMarketMapper.java 文件：

package com.oliver.mapper.inter;

import java.util.List;

import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.SelectKey;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;

import com.oliver.db.sql.StockMarketProvider;
import com.oliver.models.StockMarket;

public interface IStockMarketMapper {
    
    @InsertProvider(type=StockMarketProvider.class,method="insertStockMarketSql")
    @SelectKey(keyProperty="id",keyColumn="id", before = false, resultType = int.class, statement = { "SELECT LAST_INSERT_ID() AS ID" })
    public void insertStockMarket(StockMarket stockMarket);
    
    @SelectProvider(type=StockMarketProvider.class,method="selectByStockIdSql")
    @Results(value={
            @Result(id=true,property="id",column="id"),
            @Result(property="dqj",column="dqj"),
            @Result(property="bhz",column="bhz"),
            @Result(property="bhl",column="bhl"),
            @Result(property="jkp",column="jkp"),
            @Result(property="zsp",column="zsp"),
            @Result(property="zgj",column="zgj"),
            @Result(property="zdj",column="zdj"),
            @Result(property="ztj",column="ztj"),
            @Result(property="dtj",column="dtj"),
            @Result(property="hsl",column="hsl"),
            @Result(property="zf",column="zf"),
            @Result(property="syl",column="syl"),
            @Result(property="sjl",column="sjl"),
            @Result(property="cjl",column="cjl"),
            @Result(property="cje",column="cje"),
            @Result(property="zsz",column="zsz"),
            @Result(property="ltsz",column="ltsz"),
            @Result(property="mr1",column="mr1"),
            @Result(property="mr2",column="mr2"),
            @Result(property="mr3",column="mr3"),
            @Result(property="mr4",column="mr4"),
            @Result(property="mr5",column="mr5"),
            @Result(property="mc1",column="mc1"),
            @Result(property="mc2",column="mc2"),
            @Result(property="mc3",column="mc3"),
            @Result(property="mc4",column="mc4"),
            @Result(property="mc5",column="mc5"),
            @Result(property="mr1Num",column="mr1_num"),
            @Result(property="mr2Num",column="mr2_num"),
            @Result(property="mr3Num",column="mr3_num"),
            @Result(property="mr4Num",column="mr4_num"),
            @Result(property="mr5Num",column="mr5_num"),
            @Result(property="mc1Num",column="mc1_num"),
            @Result(property="mc2Num",column="mc2_num"),
            @Result(property="mc3Num",column="mc3_num"),
            @Result(property="mc4Num",column="mc4_num"),
            @Result(property="mc5Num",column="mc5_num"),
            @Result(property="status",column="status"),
            @Result(property="stockId",column="stock_id")
    })
    public StockMarket selectByStockId(int stockId);
    
    @UpdateProvider(type=StockMarketProvider.class,method="updateByStockIdSql")
    public void update(StockMarket stockMarket);

    @SelectProvider(type=StockMarketProvider.class,method="selectByOrderAtLimitSql")
    @Results(value={
            @Result(id=true,property="id",column="id"),
            @Result(property="dqj",column="dqj"),
            @Result(property="bhz",column="bhz"),
            @Result(property="bhl",column="bhl"),
            @Result(property="jkp",column="jkp"),
            @Result(property="zsp",column="zsp"),
            @Result(property="zgj",column="zgj"),
            @Result(property="zdj",column="zdj"),
            @Result(property="ztj",column="ztj"),
            @Result(property="dtj",column="dtj"),
            @Result(property="hsl",column="hsl"),
            @Result(property="zf",column="zf"),
            @Result(property="syl",column="syl"),
            @Result(property="sjl",column="sjl"),
            @Result(property="cjl",column="cjl"),
            @Result(property="cje",column="cje"),
            @Result(property="zsz",column="zsz"),
            @Result(property="ltsz",column="ltsz"),
            @Result(property="mr1",column="mr1"),
            @Result(property="mr2",column="mr2"),
            @Result(property="mr3",column="mr3"),
            @Result(property="mr4",column="mr4"),
            @Result(property="mr5",column="mr5"),
            @Result(property="mc1",column="mc1"),
            @Result(property="mc2",column="mc2"),
            @Result(property="mc3",column="mc3"),
            @Result(property="mc4",column="mc4"),
            @Result(property="mc5",column="mc5"),
            @Result(property="mr1Num",column="mr1_num"),
            @Result(property="mr2Num",column="mr2_num"),
            @Result(property="mr3Num",column="mr3_num"),
            @Result(property="mr4Num",column="mr4_num"),
            @Result(property="mr5Num",column="mr5_num"),
            @Result(property="mc1Num",column="mc1_num"),
            @Result(property="mc2Num",column="mc2_num"),
            @Result(property="mc3Num",column="mc3_num"),
            @Result(property="mc4Num",column="mc4_num"),
            @Result(property="mc5Num",column="mc5_num"),
            @Result(property="status",column="status"),
            @Result(property="stockId",column="stock_id")
    })
    public List<StockMarket> selectByOrderAtLimit(@Param("orderby")String orderBy, @Param("limit")int limit);

    @SelectProvider(type=StockMarketProvider.class,method="selectDiefuListSql")
       @Results(value={
               @Result(id=true,property="id",column="id"),
               @Result(property="dqj",column="dqj"),
               @Result(property="bhz",column="bhz"),
               @Result(property="bhl",column="bhl"),
               @Result(property="jkp",column="jkp"),
               @Result(property="zsp",column="zsp"),
               @Result(property="zgj",column="zgj"),
               @Result(property="zdj",column="zdj"),
               @Result(property="ztj",column="ztj"),
               @Result(property="dtj",column="dtj"),
               @Result(property="hsl",column="hsl"),
               @Result(property="zf",column="zf"),
               @Result(property="syl",column="syl"),
               @Result(property="sjl",column="sjl"),
               @Result(property="cjl",column="cjl"),
               @Result(property="cje",column="cje"),
               @Result(property="zsz",column="zsz"),
               @Result(property="ltsz",column="ltsz"),
               @Result(property="mr1",column="mr1"),
               @Result(property="mr2",column="mr2"),
               @Result(property="mr3",column="mr3"),
               @Result(property="mr4",column="mr4"),
               @Result(property="mr5",column="mr5"),
               @Result(property="mc1",column="mc1"),
               @Result(property="mc2",column="mc2"),
               @Result(property="mc3",column="mc3"),
               @Result(property="mc4",column="mc4"),
               @Result(property="mc5",column="mc5"),
               @Result(property="mr1Num",column="mr1_num"),
               @Result(property="mr2Num",column="mr2_num"),
               @Result(property="mr3Num",column="mr3_num"),
               @Result(property="mr4Num",column="mr4_num"),
               @Result(property="mr5Num",column="mr5_num"),
               @Result(property="mc1Num",column="mc1_num"),
               @Result(property="mc2Num",column="mc2_num"),
               @Result(property="mc3Num",column="mc3_num"),
               @Result(property="mc4Num",column="mc4_num"),
               @Result(property="mc5Num",column="mc5_num"),
               @Result(property="status",column="status"),
               @Result(property="stockId",column="stock_id")
       })
    public List<StockMarket> selectDiefuList(int limit);
}