一、动态SQL的用法

where 1=1 目的解决1.不影响查询的结果 2.做后边的and拼接操作，如果前边条件不成立，where 直接拼接and查询报错。

where和if一般结合使用：
a>若where标签中的if条件都不满足，则where标签没有任何功能，即不会添加where关键字
b>若where标签中的if条件满足，则where标签会自动添加where关键字，并将条件最前方多余的
and去掉
注意：where标签不能去掉条件最后多余的and

  1. where 1 =1;
  2. <where>
  3.             chkoutId=#{id}
  4.             <if test="pName!=null and pName!=''">
  5.                 and filename like concat('%',#{pName},'%')
  6.             </if>
  7.             <if test="checkout != null and checkout != '' and checkout eq'其他'.toString()">
  8.                 and checkout not like concat('%','100','%')
  9.             </if>
  10.             <if test="time != null and time != ''">
  11.                 <![CDATA[and DATE_FORMAT(startTime,'%Y-%m-%d') <= DATE_FORMAT(#{time}, '%Y-%m-%d')]]>
  12.                 <![CDATA[and DATE_FORMAT(endTime,'%Y-%m-%d') >= DATE_FORMAT(#{time}, '%Y-%m-%d')]]>
  13.             </if>
  14. </where>
  1. IFNULL(count(id),0)
  2. IFNULL(sum(dataSize),0)
  3. COUNT(errMsg='存在异常' or NULL)

注意：count在值是NULL是不统计数, (count(‘任意内容’))都会统计出所有记录数

  1. <insert id=" " useGeneratedKeys="true"
  2.         keyProperty="id" parameterType=" ">
  3.         INSERT INTO archive_checkout_detail (
  4.         id,
  5.         <include refid="column" />
  6.         )
  7.         VALUES (
  8.         #{id, jdbcType=INTEGER},  <!-- 主键 -->
  9.         #{filename, jdbcType=VARCHAR},  <!-- 产品文件名称 -->
  10.         #{lv, jdbcType=VARCHAR}, <!-- 数据级别 -->
  11.         #{fileSize, jdbcType=BIGINT}  <!--文件大小（单位：字节） -->
  12.         )
  13. </insert>
  15. <!-- 表字段 ，不包含id -->
  16. <sql id="column">
  17.         chkoutId,
  18.         filename,
  19.         lv,
  20.         fileSize
  21. </sql>
  1. <update id="updateByKey" parameterType=" ">
  2.         UPDATE  表名
  3.         <set>
  4.             <if test="id != null">
  5.                 id = #{id, jdbcType=INTEGER},  <!-- id -->
  6.             </if>
  7.             <if test="checkoutName != null">
  8.                 checkoutName = #{checkoutName, jdbcType=VARCHAR}  
  9.             </if>
  10.         </set>
  11.         WHERE id = #{id}
  12. </update>

注意：

  1. Long selectCount2(@Param("taskIdList") List<Integer> taskIdList);
  2. <select id="selectCount2" resultType="long">
  3.         SELECT IFNULL(count(*),0)
  4.         FROM archive_check ack
  5.         where ack.taskId in (
  6.         <foreach close="" collection="taskIdList" item="taskId"
  7.              open="" separator=",">
  8.              #{taskId}
  9.         </foreach>)
  10. </select>
  12. List<ArchiveCheckPO> selectExeById(@Param("archiveTaskAndCommonlv") ArchiveTaskAndCommonlv archiveTaskAndCommonlv, @Param("taskIdList") List<Integer> taskIdList);
  14. <select id="selectExeById" parameterType="ArchiveTaskAndCommonlv"
  15.         resultMap="BaseResultMap">
  16.         select
  17.         ack.id, ack.fileName, ack.fileDir, ack.chkTime, ack.chkPhase,ack.chkType,ack.errStatus,ack.errMsg
  18.         FROM archive_check ack
  19.         where ack.taskId in (
  20.         <foreach collection="taskIdList" item="taskId" index="index"
  21.              open="(" close=")" separator=",">
  22.              #{taskId}
  23.         </foreach>)
  24.         limit #{archiveTaskAndCommonlv.currentPage},#{archiveTaskAndCommonlv.limit}
  25. </select>
  27. int insertBatch(@Param("tableName") String tableName, @Param("archiveMetaPOList") List<ArchiveMetaPO> archiveMetaPOList);
  28.     <insert id="insertBatch" useGeneratedKeys="true">
  29.         INSERT INTO ${tableName} (
  30.         id,
  31.         <include refid="column" />
  32.         )
  33.         VALUES
  34.         <foreach collection="archiveMetaPOList" item="archiveMetaPO" index="index"
  35.              open="(" close=")" separator=",">
  36.             (
  37.             #{archiveMetaPO.id, jdbcType=INTEGER},  <!-- id -->
  38.             #{archiveMetaPO.archId, jdbcType=VARCHAR},  <!-- 存档编号 -->
  39.             #{archiveMetaPO.taskId, jdbcType=INTEGER}  <!-- 归档任务id，外键（archive_task(id)） -->
  40.             )
  41.         </foreach>
  42.     </insert>
  44. MyBatis传入参数为list、数组、map写法
  45. /**
  46. *传入数组
  47. *
  48. */
  49. public List<Student> findStudentByArray(Integer[] ids);
  51. <select id="findStudentByArray" parameterType="Integer"  resultType="com.ali.entity.Student">
  52.  select id,st_name,st_age from t_student where id in
  53.     <foreach collection="array" item="item_id" open"(" separator=","        close=")">
  54.        #{item_id}
  55.     </foreach>
  56. </select>
  58. /**
  59. *传入哈希
  60. *
  61. */
  62. public List<Map> findStudentByMap(Map<String,Object> ids);
  64. <select id="findStudentByMap" parameterType="Map" resultType="com.ali.entity.Student">
  65.   select id,st_name,st_age from t_student where id in
  66.      <foreach collection="ids" item="item_id" open="(" separator=","     close=")">
  67.          #{item_id}
  68.      </foreach>
  69. </select>
  71. /**
  72. *传入列表
  73. *
  74. */
  75. public List<Student> findStudentByList(List<Integer> ids);
  77. <select id "findStudentByList" parameterType="Integer" resultType="com.ali.entity.Student">
  78.     select id,st_name,st_age from t_student where id in
  79.     <foreach collection="list" item="item_id" open"(" separator=","        close=")">
  80.        #{item_id}
  81.     </foreach>
  82. </select>

在MyBatis框架中：JavaType和ofType的区别：
JavaType用来指定POJO中属性的类型
ofType用来指定映射到List集合中POJO的类型

  1.  public class User {
  2.       private int id;
  3.       private String name;
  4.       private String mobile;
  5.       private List<Post>posts;
  6.   }
  8. <resultMap type="User" id="resultUserMap">
  9.         <result property="id" javaType="int" column="user_id" />
  10.         <result property="name" javaType="string" column="username" />
  11.         <result property="mobile"  column="mobile" />
  12.         <!--javatype指定的是user对象的属性的类型, 而oftype指定的是映射到list集合属性中POJO的类型,本例指的是post类型）-->
  13.         <collection property="posts"  ofType="Post"  javaType="java.util.ArrayList" column="id">
  14.             <id property="id" column="post_id" javaType="int" jdbcType="INTEGER"/>   
  15.             <result property="title" column="title" javaType="string" jdbcType="VARCHAR"/>
  16.             <result property="content" column="content" javaType="string" jdbcType="VARCHAR"/>
  17.         </collection>
  18. </resultMap>
  1.     private int id;
  2.     private String chat_name;
  3.     private int chat_hot;
  4.     private List<chat_son>  son;
  5.     private List<chat_myson>  myson;
  8. <resultMap id="BaseResultMap" type="cn.zcbigdata.mybits_demo.entity.Chat">
  9.         <id column="id" jdbcType="INTEGER" property="id" />
  10.         <result column="chat_name" jdbcType="VARCHAR" property="chat_name" />
  11.         <result column="chat_hot" jdbcType="INTEGER" property="chat_hot" />
  14.         <collection property="son"  column="chat_name" ofType="cn.zcbigdata.mybits_demo.entity.chat_son"  select="select_son">
  15.             <id column="id" jdbcType="INTEGER" property="id" />
  16.             <result column="chat_name" jdbcType="VARCHAR" property="chat_name" />
  17.             <result column="chat_reply" jdbcType="VARCHAR" property="chat_reply" />
  18.             <result column="chat_preson" jdbcType="VARCHAR" property="chat_preson" />
  19.         </collection>
  21.         //property属性里是在java的实体类中的变量名称，即上文List所对应的变量名称
  22.         //column属性是子查询的字段
  23.         //ofType属性，因为此次是要查询返回的是一个集合，所以我们使用ofType
  24.         //select属性主要是选择要执行的sql语句，找到对应的id即可
  25.         <collection property="myson"  column="chat_name" ofType="cn.zcbigdata.mybits_demo.entity.chat_myson"  select="select_myson">
  26.             <id column="id" jdbcType="INTEGER" property="id" />
  27.             <result column="chat_name" jdbcType="VARCHAR" property="chat_name" />
  28.             <result column="chat_myReply" jdbcType="VARCHAR" property="chat_myReply" />
  29.         </collection>
  30.     </resultMap>
  32. <!--    通过话题名称显示，有模糊查询-->
  33.     <select id="select_chatName" resultMap="BaseResultMap" parameterType="String">
  34.         select id,chat_name,chat_hot
  35.         from chat where chat_name like CONCAT('%',#{chat_name},'%')
  36. <!--        limit #{offset},#{limit}-->
  37.     </select>
  39. <!--    他人回复 子查询-->
  40.     <select id = "select_son"  resultType="cn.zcbigdata.mybits_demo.entity.chat_son" parameterType="String">
  41.     select id,chat_reply,chat_person from chat_son where chat_name = #{chat_name}
  42.     </select>
  44. <!--    自己回复 子查询-->
  45.     <select id="select_myson" resultType="cn.zcbigdata.mybits_demo.entity.chat_myson" parameterType="String">
  46.         select id,chat_myReply from chat_myson where chat_name = #{chat_name}
  47.     </select>

二、MyBatis的缓存

三、MyBatis分页插件