注解sql

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
 
import java.util.List;
 
@Mapper
public interface LizzMapper {
    @Select({"<script> select id,name " +
            " from t_lizz" +
            " where id in" +
            " <foreach collection=\"ids\" index=\"index\" item=\"item\" open=\"(\" separator=\",\" close=\")\">" +
            "        #{item}" +
            " </foreach>" +
            "</script>"})
    List<Lizz> selectAllbyList(List<Integer> ids);
 
    @Insert({"<script> insert into t_lizz(id,name)" +
            " values" +
            " <foreach collection=\"datas\" item=\"item\" separator=\",\" >" +
            "        (#{item.id},#{item.name})" +
            " </foreach>" +
            "</script>"})
    insert selectAllbyList(List<Lizz> datas);
 
    @Select({"<script> select id,name " +
            " from t_lizz" +
            " where id in" +
            " <foreach collection=\"ids.split(',')\" index=\"index\" item=\"item\" open=\"(\" separator=\",\" close=\")\">" +
            "        #{item}" +
            " </foreach>" +
             "<if test='state!= null and state!= \"\"' >" +
            " and state= #{state} " +
            "</if> " +
            "</script>"})
    List<AlertRule> selectAllByString(String ids,Integer state);
 
 
    @Select({"<script> select id,name " +
            " from t_lizz" +
            " where " +
            " <choose> "+
            "    <when test='state != null and state!=0 '>" +
            "     and state= #{state} " +
            "    </when> " +
            "    <otherwise>" +
            "     and state= 0 " +
            "    </otherwise> " +
            " </choose> "+
            "</script>"})
    List<AlertRule> selectAllByString(String ids,Integer state);
 
 
}

    @Update({"<script> " +
            "update tb_threed_walk" +
            " set " +
            "<if test = \" content != null and content != '' \">" +
            "  f_sqgd= #{content} " +
            "</if> " +
            "where  f_id=#{id} "+
            "</script>"})
    boolean DelWalk(String id, String content);
 
 @Insert("INSERT INTO tb_threed_walk ( f_id, f_mymc, f_sqgd, f_userid, f_bcsj )
 VALUES ( #{id}, #{roamingName}, #{content}, #{userId},#{dateTime}::date )\n")
    boolean saveWalk(ThreeWalk threeWalk);

    @Select({
            "<script>",
            "select distinct f_dwmc from dm_ayt_ysdzw_dw_f where 1 = 1 and f_dwmc in ",
            "<foreach item = 'item' collection='species' separator=',' open='(' close=')'>",
            "#{item}",
            "</foreach>",
            "</script>"})
    List<String> checkSpecies(List<String> species);

 

        +"<if test='sx != null and sx != \"\" and sx.size>0' >"
             + "and "
            +"i.f_sx in " +
            "<foreach item = 'sn' collection='sx' separator=',' open='(' close= ')'>"+
            "#{sn}"+
            "</foreach>"
             + " </if>"

foreach

SELECT * FROM table
WHERE a IN
<foreach collection = "list" item = "item" open = '(' close = ')' separator = ','>
    #{item.a}
</foreach>
 
//执行Mybatis后的SQL语句示例
SELECT * FROM table
WHERE a IN ('ZBD123','ZBD124','ZBD125','ZBD126')

SELECT * FROM table
WHERE a = #{a}
AND
<foreach collection = "list" item = "item" open = '(' close = ')' separator = 'OR'>
    b = #{item.b} 
    AND c = #{item.c}
    AND d = #{item.d}
</foreach>
 
/*执行Mybatis后的SQL语句示例
 *多条件批量查询用到了SQL语句OR函数，视实际应用环境修改separator参数
 */
SELECT * FROM table
WHERE a = '123'
AND (
    b=123 AND c = true AND d = 'ZBD123' OR
    b=124 AND c = true AND d = 'ZBD124' OR
    b=125 AND c = false AND d = 'ZBD125' OR
    b=126 AND c = true AND d = 'ZBD126'
)