【springboot+mybatis+oracle 】where，set，foreach标签使用

使用foreach标签实现oracle批量插入

@Mapper
public interface PfActivityScoreDao {
    String table_name = "PF_ACTIVITY_SCORE ";
    String table_fields = "(OID,PF_PROJECT_NAME,PF_PROJECT_OID,SCORE,CREATE_TIME,PSY_CPR_OID)";
    String table_values = "(#{pd.OID,jdbcType=VARCHAR}," +
            "#{pd.PF_PROJECT_NAME,jdbcType=VARCHAR}," +
            "#{pd.PF_PROJECT_OID,jdbcType=VARCHAR}," +
            "#{pd.SCORE,jdbcType=VARCHAR}," +
            "#{pd.CREATE_TIME,jdbcType=VARCHAR}," +
            "#{pd.PSY_CPR_OID,jdbcType=VARCHAR})";
    String table_values_list = "<if test=\"pd.list != null and pd.list != ''\">  " +
            "select a.* from (" +
            "<foreach collection=\"pd.list\" index=\"index\" item=\"item\" open=\"(\" close=\")\" separator=\"union\">" +
            "select #{item.OID},#{item.PF_PROJECT_NAME},#{item.PF_PROJECT_OID},#{item.SCORE},#{item.CREATE_TIME},#{item.PSY_CPR_OID} from dual" +
            "</foreach>" +
            ")a" +
            "</if>";
    String table_values_list1 = "<if test=\"pd.list != null and pd.list != ''\">  " +
            "select a.* from (" +
            "<foreach collection=\"pd.list\" index=\"index\" item=\"item\" open=\"(\" close=\")\" separator=\",\">" +
            "(#{item.OID},#{item.PF_PROJECT_NAME},#{item.PF_PROJECT_OID},#{item.SCORE},#{item.CREATE_TIME},#{item.PSY_CPR_OID})" +
            "</foreach>" +
            ")a" +
            "</if>";

    String sql_set = "<set> " +
            "<if test=\"pd.PF_PROJECT_NAME != null and pd.PF_PROJECT_NAME != ''\">  PF_PROJECT_NAME = #{pd.PF_PROJECT_NAME,jdbcType=VARCHAR}, </if>" +
            "<if test=\"pd.PF_PROJECT_OID != null and pd.PF_PROJECT_OID != ''\">  PF_PROJECT_OID = #{pd.PF_PROJECT_OID,jdbcType=VARCHAR}, </if>" +
            "<if test=\"pd.SCORE != null and pd.SCORE != ''\">  SCORE = #{pd.SCORE,jdbcType=VARCHAR}, </if>" +
            "</set>" +
            "where OID=#{pd.OID,jdbcType=VARCHAR}";

    @Select("select * from " + table_name + " where IS_DELETED ='1'")
    List<PageData> getAll(PageData pd);

    //    @Insert("insert into " + table_name + table_fields + " values " + table_values)
//    @Insert("<script> insert into " + table_name + table_fields + " values " + table_values_list+ " </script>")
    @Insert("<script> insert into " + table_name + table_fields + table_values_list + " </script>")
    Boolean save(@Param("pd") PageData pd);

    @Delete("delete from " + table_name + " where OID=#{pd.OID,jdbcType=VARCHAR}")
    Boolean delById(@Param("pd") PageData pd);

    @Delete("delete from " + table_name + " where PSY_CPR_OID=#{pd.PSY_CPR_OID,jdbcType=VARCHAR}")
    Boolean delByPsycproid(@Param("pd") PageData pd);

    @Update("<script> update " + table_name + sql_set + " </script>")
    Boolean updateById(@Param("pd") PageData pd);

    @Select("select * from " + table_name + " where OID=#{pd.OID,jdbcType=VARCHAR}")
    PageData getById(@Param("pd") PageData pd);

    @Select("select * from " + table_name + " where PSY_CPR_OID=#{pd.PSY_CPR_OID,jdbcType=VARCHAR}")
    List<PageData> getByPsycproid(@Param("pd") PageData pd);

set，where标签实现按条件修改和查询

@Mapper
public interface PfActivityDao {
    String table_name = "PF_ACTIVITY";
    String table_fields = "(OID,ACTIVITY_NAME,PLAN_TIME,CREATE_TIME,PSY_NAME,CPR_NAME,TEMPLATE_OID,CREATE_DAY,STATUS,IS_DELETED)";
    String table_values = "(#{pd.OID,jdbcType=VARCHAR}," +
            "#{pd.ACTIVITY_NAME,jdbcType=VARCHAR}," +
            "#{pd.PLAN_TIME,jdbcType=VARCHAR}," +
            "#{pd.CREATE_TIME,jdbcType=VARCHAR}," +
            "#{pd.PSY_NAME,jdbcType=VARCHAR}," +
            "#{pd.CPR_NAME,jdbcType=VARCHAR}," +
            "#{pd.TEMPLATE_OID,jdbcType=VARCHAR}," +
            "#{pd.CREATE_DAY,jdbcType=VARCHAR}," +
            "#{pd.STATUS,jdbcType=INTEGER}," +
            "#{pd.IS_DELETED,jdbcType=INTEGER})";
    String sql_set = "<set> " +
            "<if test=\"pd.ACTIVITY_NAME != null and pd.ACTIVITY_NAME != ''\"> ACTIVITY_NAME = #{pd.ACTIVITY_NAME,jdbcType=VARCHAR}, </if>" +
            "<if test=\"pd.PLAN_TIME != null and pd.PLAN_TIME != ''\">  PLAN_TIME = #{pd.PLAN_TIME,jdbcType=VARCHAR}, </if>" +
            "<if test=\"pd.CREATE_TIME != null and pd.CREATE_TIME != ''\">  CREATE_TIME = #{pd.CREATE_TIME,jdbcType=VARCHAR}, </if>" +
            "<if test=\"pd.TEMPLATE_OID != null and pd.TEMPLATE_OID != ''\">  TEMPLATE_OID = #{pd.TEMPLATE_OID,jdbcType=VARCHAR}, </if>" +
            "<if test=\"pd.CREATE_DAY != null and pd.CREATE_DAY != ''\">  CREATE_DAY = #{pd.CREATE_DAY,jdbcType=VARCHAR}, </if>" +
            "<if test=\"pd.STATUS != null \">  STATUS = #{pd.STATUS,jdbcType=INTEGER} </if>" +
            "<if test=\"pd.IS_DELETED != null \">  IS_DELETED = #{pd.IS_DELETED,jdbcType=INTEGER} </if>" +
            "</set>" +
            "where OID=#{pd.OID,jdbcType=VARCHAR}";
    String sql_where = "<where> " +
            "<if test=\"pd.ACTIVITY_NAME != null and pd.ACTIVITY_NAME != ''\"> ACTIVITY_NAME = #{pd.ACTIVITY_NAME,jdbcType=VARCHAR}</if>" +
            "<if test=\"pd.CREATE_DAY != null and pd.CREATE_DAY != ''\"> and CREATE_DAY = #{pd.CREATE_DAY,jdbcType=VARCHAR}</if>" +
            "<if test=\"pd.STATUS != null \"> and STATUS = #{pd.STATUS,jdbcType=INTEGER}</if>" +
            "<if test=\"pd.IS_DELETED != null\"> and IS_DELETED = #{pd.IS_DELETED,jdbcType=INTEGER}</if>" +
            "</where>";

    @Select("select * from " + table_name + " where IS_DELETED ='1' order by PLAN_TIME desc")
    List<PageData> getAll(PageData pd);

    @Insert("insert into " + table_name + table_fields + " values " + table_values)
    Boolean save(@Param("pd") PageData pd);

    @Update("update " + table_name + " set IS_DELETED='0' where OID=#{pd.OID,jdbcType=VARCHAR}")
    Boolean delById(@Param("pd") PageData pd);

    @Update("update " + table_name + " set IS_DELETED='0' where TEMPLATE_OID=#{pd.TEMPLATE_OID,jdbcType=VARCHAR}")
    Boolean delByTemplateoid(@Param("pd") PageData pd);

    @Update("<script> update " + table_name + sql_set + " </script>")
    Boolean updateById(@Param("pd") PageData pd);

    @Select("select * from " + table_name + " where OID=#{pd.OID,jdbcType=VARCHAR}")
    PageData getById(@Param("pd") PageData pd);

    @Select("select * from " + table_name + " where TEMPLATE_OID=#{pd.TEMPLATE_OID,jdbcType=VARCHAR}")
    List<PageData> getByTemplateoid(@Param("pd") PageData pd);

    @Select("<script> select * from " + table_name + sql_where + "</script>")
    List<PageData> getBy(@Param("pd") PageData pd);