mybatis 配置文件mysql的常用语句写法总结

                    mybatis 配置文件mysql常用语句写法总结
1.添加语句
   <insert id="insert" parameterType="com.baidu.bean.AppAccountBean">
  insert into 表名
    (account,pass,operate,create_date,remark)
  values(
    #{account,jdbcType=VARCHAR},
    #{password,jdbcType=VARCHAR},
    #{operater,jdbcType=VARCHAR},
    now(),
    #{remarks,jdbcType=VARCHAR}
    )
 </insert>
2.批量添加语句
<insert id="insert" parameterType="java.util.List">
     insert into  表名(
             delete_power,
             create_date,
             update_date,
             remarks,
             permission_group_code
             )
           values
     <foreach  collection="list" item="item" index="index" separator =",">
  (
  #{item.deletePower},
  (select now() as createDate from dual),
  (select now() as updateDate from dual),
  #{item.remarks},
  #{item.permissionGroupCode}
  
  )
  </foreach>
 </insert>
3.删除语句
<delete id="delete" parameterType="String">
   delete  from 表名 where cs_admin_name = #{adminName}
 </delete>
4.批量删除语句
<delete id="delete" parameterType="java.util.List">
    delete from
          表名
    where
           cs_admin_name
           in
           <foreach open="(" close=")" collection="list" item="item" index="index" separator=",">
           #{item.adminName}
           </foreach>
 </delete>
5.修改语句
 <update id="update" parameterType="com.baidu.pojo.HttpProjectPojo" > 
        UPDATE
         表名
        SET
         <if test="state !=null and state !=''">
               STATE = #{state} ,
           </if>
           <if test="isPaid !=null and isPaid !=''">
               IS_PAID = #{isPaid} ,
           </if>
         UPDATE_DATE = sysdate()
        WHERE
         1=1
         <if test="proNumber !=null and proNumber !=''">
            AND PRO_NUMBER = #{proNumber} 
         </if>
    </update>
6.批量修改语句
 <update id="Update" parameterType="java.util.List" > 
        <foreach collection="list" item="item" index="index" separator=";"> 
         update
          表名
         set
          <![CDATA[material_num = material_num + #{item.materielNum} , ]]>
          update_date = now()
         where
          pro_number = #{item.proNumber} 
          and
          material_code = #{item.materielCode} 
        </foreach> 
    </update>
7.查询语句
<select id="select" parameterType="java.util.Map" resultType="com.baidu.pojo.MaterialOrderPojo">
           select
             mo.id,
             date_format(mo.create_date,'%Y-%m-%d %H:%i:%s') as createDate,
                date_format(mo.update_date,'%Y-%m-%d %H:%i:%s') as updateDate,  
    mo.remark as remarks,     
    mo.state as state
     from
         表名 mo
     where
       date_format(trunc(mo.create_date),'%Y-%m-%d') = date_format(trunc(now()),'%Y-%m-%d')
     </select>
8.查询表里面的下一个自增的id
 <select id="selectNextId" resultType="java.lang.Integer">
  select AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_NAME = '表名';
 </select>
9.in子查询语句
<select id="select" parameterType="java.util.Map" resultType="com.baidu.pojo.PackageDetailedPojo">
    select
      d.id as id,
    b.id as packageId,
    b.package_no as packageNo,
    d.material_num  as materialNum
  from
   表1 d
  left join
   表2 b
  on
   b.package_no=d.package_no
  left join
         表3 mp
  on
   mp.material_code = d.material_code
  where
   d.state = '1'
  <if test="packageId != null and packageId != ''">
  and  b.id in
   <foreach item="item" index="index" collection="packageId" open="(" separator="," close=")"> 
      #{item,jdbcType=INTEGER} 
    </foreach>
  </if>
 </select>
10.嵌套查询（一对多关联查询）
<select id="select" parameterType="java.util.Map" resultMap="materialOrderMap">
  select  a.*,
   oa.card_no as orderCardNo,
   oa.integral_type as integralType,
   pa.city_code as cityCode,
            pr.name as provinceName
   from (
            select mo.id,
                      mo.package_info as packageInfo,
                      date_format(mo.create_date, '%Y-%m-%d %H:%i:%s') as createDate,
                      date_format(mo.update_date, '%Y-%m-%d %H:%i:%s') as updateDate,
                      mo.state as state
                  from
                   表1 mo
                  where 1 = 1
                   and mo.state = '1'
                       <if test="createTime != null and createTime != '' ">
           <![CDATA[  and  date_format(mo.create_date,'%Y-%m-%d') >= #{createTime} ]]>
           </if>
           <if test="endTime != null and endTime != '' ">
           <![CDATA[  and  date_format(mo.create_date,'%Y-%m-%d') <= #{endTime} ]]>
           </if>
        ) a
    left join
     表2 oa
             on
              a.orderno = oa.order_no
             left join
    表3 pa
    on
    a.orderno = pa.order_no
    left join
     表4 ci
    on ci.city_code=pa.city_code
    left join
    表5 pr
    on pr.code=ci.province_code
             order by
                a.createdate desc
     </select>
 
  <resultMap type="com.baidu.pojo.MaterialOrderPojo" id="materialOrderMap">
  <id property="id" column="id"/>
  <result property="packageInfo" column="package_info"/>
  <result property="state" column="state"/>
  <result property="createDate" column="create_date"/>
  <result property="updateDate" column="update_date"/>
  <result property="cityCode" column="city_code"/>
  <collection property="orderCardInfo" ofType="com.baidu.pojo.OrderCardPojo">
   <result property="orderCardNo" column="oa.card_no"/>  
   <result property="orderIntegral" column="oa.integral"/>  
  </collection>
 </resultMap>
11. 模糊查询
<select id="select" parameterType="java.util.Map" resultMap="com.baidu.pojo.PackageDetailedPojo">
     select * from 表名 v  where  v.FCUSTOMERNAME like CONCAT(CONCAT('%', #{fcustomername}), '%') 
 </select>
12.if + where 的条件判断
 <select id="select" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity">  
    SELECT ST.STUDENT_ID,  
           ST.STUDENT_NAME,    
           ST.PLACE_ID  
      FROM 表名 ST   
    <where>  
        <if test="studentName !=null ">  
            ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')  
        </if>   
    </where>    
  </select>  
13.if + set 的更新语句
<update id="update" parameterType="liming.student.manager.data.model.StudentEntity">  
    UPDATE 表名  
    <set>  
        <if test="studentName != null and studentName != '' ">  
            STUDENT_TBL.STUDENT_NAME = #{studentName},  
        </if>  
        <if test="studentSex != null and studentSex != '' ">  
            STUDENT_TBL.STUDENT_SEX = #{studentSex},  
        </if>    
    </set>  
    WHERE STUDENT_TBL.STUDENT_ID = #{studentId};      
  </update> 
14.if+   trim代替where
<select id="select" resultMap="resultMap_studentEntity">  
    SELECT ST.STUDENT_ID,  
           ST.STUDENT_NAME,  
           ST.PLACE_ID  
      FROM 表名 ST   
    <trim prefix="WHERE" prefixOverrides="AND|OR">   
        <if test="studentBirthday != null ">  
            AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}  
        </if>   
    </trim>     
  </select> 
15.if +   trim代替set
 <update id="update" parameterType="liming.student.manager.data.model.StudentEntity">  
    UPDATE 表名 
    <trim prefix="SET" suffixOverrides=",">  
        <if test="studentName != null and studentName != '' ">  
            STUDENT_TBL.STUDENT_NAME = #{studentName},  
        </if>  
        <if test="studentSex != null and studentSex != '' ">  
            STUDENT_TBL.STUDENT_SEX = #{studentSex},  
        </if>  
    </trim>  
    WHERE STUDENT_TBL.STUDENT_ID = #{studentId}  
</update> 
16.choose (when, otherwise)
<select id="getStudentList_choose" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity">  
    SELECT ST.STUDENT_ID,  
           ST.STUDENT_NAME,   
           ST.PLACE_ID  
      FROM 表名 ST   
    <where>  
        <choose>  
            <when test="studentSex != null and studentSex != '' ">  
                AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}  
            </when >  
            <otherwise>  
            </otherwise>  
        </choose>  
    </where>    
</select>  
17.foreach 参数为array示例的写法
<select id="getStudentListByClassIds_foreach_array" resultMap="resultMap_studentEntity">  
    SELECT ST.STUDENT_ID,  
           ST.PLACE_ID  
      FROM 表名 ST  
      WHERE ST.CLASS_ID IN   
     <foreach collection="array" item="classIds"  open="(" separator="," close=")">  
        #{classIds}  
     </foreach>  
</select>  
18.foreach 参数为list示例的写法
<select id="getStudentListByClassIds_foreach_list" resultMap="resultMap_studentEntity">  
    SELECT ST.STUDENT_ID,    
           ST.PLACE_ID  
      FROM 表名 ST  
      WHERE ST.CLASS_ID IN   
     <foreach collection="list" item="classIdList"  open="(" separator="," close=")">  
        #{classIdList}  
     </foreach>  
</select>  

 

最后PS：
   1）<![CDATA[   ]]>是XML语法。在CDATA内部的所有内容都会被解析器忽略。如果文本包含了很多的"<"字符 <=和"&"字符——就象程序代码一样，那么最好把他们都放到CDATA部件中。
   2）Mysql中获取当前时间（年月日 时分秒）有NOW()、SYSDATE()。   获取当前的日期（年月日） CURDATE()。   获取当前的时间（时分秒）CURTIME()。