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