mybatis

springboot中mysql的相关配置，这句话非常重要，批量修改需要添加&allowMultiQueries=true
//分割符号
spring.datasource.url=jdbc:mysql://localhost:3306/bb?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true

1 批量查询，参数类型为List<String>,返回类型为实体类

<select id="get_name_urlBy_fans" resultType="com.demo.demo202111091632.entity.Data_image">
    select name , small_image_url from user where name in
    <foreach item="item" index="index" collection="nameList"
             open="(" separator="," close=")">
             #{item}
    </foreach>
</select>

2 字段更新，参数类型为Map，包含更改字段和条件字段
<update id="update_small_image_urlBy_name" parameterType="Map">
    update user set small_image_url  = #{params.small_image_url} where name = ${params.name}
</update>

3 有条件的全查询，参数类型为Map，返回类型为实体类
<select id="getBy_blogger_fans" parameterType="Map" resultType="com.demo.demo202111091632.entity.Subscribe">
    select * from subscribe where blogger=${params.blogger} and fans = ${params.fans}
</select>

4 模糊查询，参数类型为string，返回类型为实体类
<select  id="search" parameterType="String" resultType="com.demo.demo202111091632.entity.Video">
    select *
    from video where
    name like concat(  '%', #{keywords}, '%' ) or url like concat(  '%', #{keywords}, '%' )
        or classStyle like  concat(  '%', #{keywords}, '%' )
</select>

5 批量增加，参数类型为List<实体类>
<insert id="addMessageMulti" >
    insert into message (id,send_body,receive_body,content,read_status,video_name,style, addtime)
    values
           <foreach collection="list" item="mes" separator=",">
           (#{mes.id},#{mes.send_body}, #{mes.receive_body}, #{mes.content},
            #{mes.read_status}, #{mes.video_name}, #{mes.style}, #{mes.addtime})
           </foreach>
</insert>

6批量修改，参数为List<实体类>

<update id="MultiUpdate">
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update history
          <set>
              progress=#{item.progress}
          </set>
        where username =#{item.username} and video_id =#{item.video_id}
    </foreach>
</update>

7 更新时间，在mybatis写好默认时间为当前时间
<update id="updateBy_id_addtime" parameterType="Integer">
  update history set addtime = date_format(now(), '%Y-%m-%d %H:%i:%s') where id = #{id}
</update>

8 多字段查询，返回值为List<实体类>
<select id="getBy_blogger02" parameterType="String" resultType="com.demo.demo202111091632.entity.Data_sub">
    select fans, each_other from subscribe where blogger = #{blogger}

</select>

9 查询符合条件的数据数量，返回类型为Integer

<select id="getBy_receive_body_read_status" parameterType="String" resultType="Integer">
    select count(*) from message where receive_body = #{receive_body} and read_status = 'no'
</select>

10 把字段更新为默认值yes
<update id="update_read_status" parameterType="Integer" >
    update message set read_status = 'yes' where id = #{id}
</update>

11 根据id删除

<delete id="deleteById" parameterType="Integer">
  delete  from collectiones where id =#{id}
</delete>

12 随机查询十条数据

<select id="build_indexPageVideoList" resultType="com.demo.demo202111091632.entity.Video">
    select *
    from video order by  rand() limit 10
</select>

13 更新操作，再原来的基础上加1

<update id="addCount"> update album set count=count+1 where id=#{id} </update>

14  mybatis更新，转换为sql报错，自动将${params.ordersNum}截的只剩四个字。解决办法： 

把时间改为now(),把状态改为“1”，

<update id="payMoney" parameterType="Map">
    update  orders set money = ${params.money} ,starttime=now(),
    abolish_state="1"
    where orders_num =${params.ordersNum}
</update>

15 动态sql，使用if，参数为map，记住mapper接口文件中，不要用@Param注解，否则if中的参数值会变成必须的

<select id="getList" parameterType="Map" resultType="com.example.fifth.model.Messages">
   select * from messages
      <where>
          <if test="carcode != null and carcode !='' ">and carcode = #{carcode}</if>
          <if test="fromwho !=null  and fromwho !='' ">and fromwho=#{fromwho}</if>
          and type ='1'
      </where>
</select>

16控制台打印sql内容的配置

mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl