Mybatis-动态sql 注解方式实现

@Repository
public interface OilpirceDao {
//@SelectProvider为例，它主要包含两个注解属性，其中，type表示工具类，method表示工具类的某个方
//法（用于返回具体的SQL语句）。

    @SelectProvider(type= OilpirceDao.OilpirceDaoSql.class, method="findAll")
    List<OilpriceData> findAll(JSONObject params);

    @SelectProvider(type= OilpirceDao.OilpirceDaoSql.class, method="findAllCount")
    Integer findAllCount(JSONObject params);

    @SelectProvider(type= OilpirceDao.OilpirceDaoSql.class, method="downloadData")
    List<OilpriceData> downloadData(JSONObject params);

    @Select("select * from HV_SINOPEC_GAS_PRICE_modify where province = #{province} and PERIOD_ID = #{periodId} and isdel ='0'")
    List<OilpriceData> judgeOilDataExist(OilpriceData oilpriceData);



    @Insert("insert into HV_SINOPEC_GAS_PRICE_modify (PID,PROVINCE,GAS_92,GAS_95,GAS_98,CHECHAI_0,LNG,L_CNG,CNG,CHAI_ZYK,GAS_92_2,GAS_95_2,GAS_98_2,CHECHAI_0_2,PERIOD_ID,CREATE_BY,CREATE_DATE) " +
            "values (sunhao.seq_gas_pid.nextval,#{province},#{gas92},#{gas95},#{gas98},#{chechai0},#{lng},#{lCng},#{cng},#{chaiZyk},#{gas922},#{gas952},#{gas982},#{chechai02},#{periodId},#{createBy},sysdate)")
    int addOilpriceData(OilpriceData oilpriceData);



    @Update("update HV_SINOPEC_GAS_PRICE_modify set GAS_92=#{gas92},GAS_95=#{gas95},GAS_98=#{gas98},CHECHAI_0=#{chechai0}," +
            " LNG=#{lng},L_CNG=#{lCng},CNG=#{cng},CHAI_ZYK=#{chaiZyk},GAS_92_2=#{gas922},GAS_95_2=#{gas952},GAS_98_2=#{gas982},CHECHAI_0_2=#{chechai02},EDIT_BY=#{editBy},EDIT_DATE=#{editDate} where pid =#{pid}")
    void updateOilPirceData(OilpriceData oilpriceData);



    @Update("update HV_SINOPEC_GAS_PRICE_modify set status=#{status},release_Time = to_date(#{time},'yyyy-mm-dd hh24:mi:ss') where PERIOD_ID = #{periodId}")
    void updateOilePirceStatus(String status,String time,String periodId);


    @Update("update HV_SINOPEC_GAS_PRICE_modify set isdel='1',edit_by=#{editBy},edit_date=sysdate where pid=#{pid}")
    void delOilPrice(int pid,String editBy);




    //查找下个周期
    @Select(" select to_char(b.START_DATE,'yyyy\"年\"mm\"月\"dd\"日\"')||'-'||to_char(b.END_DATE,'yyyy\"年\"mm\"月\"dd\"日\"') as time,b.PERIOD_ID from hv_SINOPEC_GAS_period b where b.PERIOD_ID =(select a.PERIOD_ID+1 from hv_SINOPEC_GAS_period a where  sysdate between a.START_DATE and a.END_DATE)\n")
    List<GasperiodData> findNextPeriod();

    //查找所有周期
    @Select(" select to_char(a.START_DATE,'yyyy\"年\"mm\"月\"dd\"日\"')||'-'||to_char(a.END_DATE,'yyyy\"年\"mm\"月\"dd\"日\"') as time,a.PERIOD_ID from hv_SINOPEC_GAS_period a  ")
    List<GasperiodData> findAllPeriod();

    //查找当前和下个周期
    @Select("select to_char(a.START_DATE,'yyyy\"年\"mm\"月\"dd\"日\"')||'-'||to_char(a.END_DATE,'yyyy\"年\"mm\"月\"dd\"日\"') as time,a.PERIOD_ID from hv_SINOPEC_GAS_period a where sysdate between START_DATE and END_DATE\n" +
            "union\n" +
            " select to_char(b.START_DATE,'yyyy\"年\"mm\"月\"dd\"日\"')||'-'||to_char(b.END_DATE,'yyyy\"年\"mm\"月\"dd\"日\"') as time,b.PERIOD_ID from hv_SINOPEC_GAS_period b where b.PERIOD_ID in (select a.PERIOD_ID+1 from hv_SINOPEC_GAS_period a where  sysdate between a.START_DATE and a.END_DATE)")
    List<GasperiodData> findNowAndNextPeriod();


    //查找当前周期之后的所有周期
    @Select("select to_char(b.START_DATE,'yyyy\"年\"mm\"月\"dd\"日\"')||'-'||to_char(b.END_DATE,'yyyy\"年\"mm\"月\"dd\"日\"') as time,b.PERIOD_ID from  hv_SINOPEC_GAS_period b where b.PERIOD_ID > ( select PERIOD_ID from hv_SINOPEC_GAS_period  a where  sysdate between a.START_DATE and a.END_DATE)")
    List<GasperiodData> findNextPeriodAll();

    //判断某个周期是否已经录入数据
    @Select("select * from HV_SINOPEC_GAS_PRICE_modify where PERIOD_ID = #{period} and isdel='0'")
    List<OilpriceData> judgeDataByPeriod(String period);

    //查找当前周期的所有数据
    @Select("select b.* from hv_SINOPEC_GAS_PRICE_modify b where b.PERIOD_ID =( select PERIOD_ID from hv_SINOPEC_GAS_period  a where  sysdate between a.START_DATE and a.END_DATE) and isdel = '0'")
    List<OilpriceData> findNowPeriodOilData();


    //查找某个周期有多少个省还未录入

    @Select("  select  to_char(wmsys.wm_concat(to_char(province_name))) as provinceName from sinopec_research_province where province_id not in(  select province from HV_SINOPEC_GAS_PRICE_modify where period_id=#{periodId})")
    String getNoAddProvince(String periodId);


    @Insert("insert into HV_SINOPEC_GAS_job (job_id,period_id,job_date,create_date) values (sunhao.seq_gas_job_id.nextval,#{periodId},to_date(#{time},'yyyy-mm-dd hh24:mi:ss'),sysdate)")
    int addGasJob(String periodId,String time);


    class OilpirceDaoSql{
        public String baseSql(JSONObject params){
            String sql = "select a.*," +
                    "  (select p.province_name  from sinopec_research_province p where a.province=p.province_id)as provinceName," +
                    "  (select to_char(hp.START_DATE,'yyyy\"年\"mm\"月\"dd\"日\"')||'-'||to_char(hp.END_DATE,'yyyy\"年\"mm\"月\"dd\"日\"') as time from HV_SINOPEC_GAS_period hp where a.period_id=hp.period_id)time, " +
                    " (case a.status when 'N' then '未发布' when 'Y' then '已发布' else '其它' end)releaseStatus " +
                    "  from HV_SINOPEC_GAS_PRICE_modify a where isdel='0'\n";


            String periodId = params.getString("periodId");

            if(StringUtils.isNotBlank(periodId)){
                sql += " and PERIOD_ID = #{periodId}";
            }

            String province = params.getString("province");

            if(StringUtils.isNotBlank(province)){
                sql += " and province = #{province}";
            }
            sql += " order by a.create_date desc";

            return sql;

        }

        public String downloadData(JSONObject params){
            String sql = "select   to_char(create_date,'yyyy-mm-dd hh24:mi:ss')createDateNew,to_char(release_time,'yyyy-MM-dd')releaseTimeNew,a.* from ( "+baseSql(params) +"  )a ";
            return  sql;
        }


        public String findAll(JSONObject params){
            String sql = "select tt.* from(select t.*,rownum r from (" + baseSql(params)
                    + ") t) tt where tt.r > #{start} and tt.r <= #{end}";
            return sql;

        }

        public String findAllCount(JSONObject params) {
            String sql = "select count(*) from (" + baseSql(params) + ") t";
            return sql;
        }



    }





}