【MyBatis之SQL篇】 基本增删改查 + 动态sql + 批量增删改

mybatis官方

一、基本的增删改查

增删改不用定义resultType，默认返回的int值为执行成功的sql条数

1、insert

int insertUser(User user);

<insert id="insertUser" parameterType="user">
	insert into userinfo(id, userName, passWord, age, sex, phone) values(#{id}, #{userName}, #{passWord}, #{age}, #{sex}, #{phone})
</insert>

2、delete

int deleteUser(int id);

<delete id="deleteUser" parameterType="int">
	delete from userinfo where id = #{id}
</delete>

3、update

int updateUser(int id, String phone);

<update id="updateUser" >
	update userinfo set phone = #{phone} where id = #{id}
</update>

当多个参数时并且参数类型不一，parameterType我们可以不设置或是将多个参数放在map中

int updateUser(Map<String, Object> conditionMap);

<update id="updateUser" parameterType="map" >
	update userinfo set phone = #{phone} where id = #{userId}
</update>

4、select

List<User> queryAllUser();

<select id="queryAllUser" resultType="user">
	select * from userinfo
</select>

二、动态SQL

1、动态查询

根据某一单一条件查询时，我们可以使用choose、when标签（类似switch case）来实现

User queryUser(Integer id, String phone);

<select id="queryUser" resultType="user">
	select id, userName, passWord, age, sex, phone from userinfo
	<where>
	   <choose>
	       <when test="id != null"> <!-- id为Integer类型 -->
	           id = #{id}
	       </when>
	       <when test="phone != null and phone != ''">
	           phone = #{phone}
	       </when>
	   </choose>
	
	 </where>
</select>

使用where 标签的好处在于子元素返回ture情况下才插入 “WHERE” 子句。
而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。所以无论查询条件有无都不会像下边的这种写法导致SQL错误（如果没有where条件满足则最终sql会是“select id, userName, passWord, age, sex, phone from userinfo where”）

<select id="queryUser" resultType="user">
	select id, userName, passWord, age, sex, phone from userinfo
	where
	   <choose>
	       <when test="id != null"> <!-- id为Integer类型 -->
	           id = #{id}
	       </when>
	       <when test="phone != null and phone != ''">
	           phone = #{phone}
	       </when>
	   </choose>
</select>

如果根据多个条件查询我们可以使用if 标签

<select id="queryUser" resultType="user">
	select id, userName, passWord, age, sex, phone from userinfo
	where 1=1
	<if test="age != null">
	    and age = #{age}
	</if>
	<if test="name != null and name != ''">
	    and name = #{phone}
	</if>
	<if test="sex != null and sex != ''">
	    and sex = #{sex}
	</if>
</select>

2、动态更新

<update id="updateUser" parameterType="map" >
	update userinfo
	<set>
	    <if test="phone != null and phone != ''">
	        phone = #{phone}
	    </if>
	</set>
	where id = #{userId}
</update>

3、批量插入

使用mybatis进行批量插入与更新时，必须在配置连接url时指定allowMultiQueries=true

使用foreach标签 ，item是迭代对象的元素,index是迭代索引(可以用来给执行的每条数据进行排序)

int insertBatch(List<User> users);

<insert id="insertBatch" parameterType="list">
	insert into userinfo(id, userName, passWord, age, sex, phone)
	values 
	<foreach collection="list" item="item" separator=",">
	    (#{item.id}, #{item.userName}, #{item.passWord}, #{item.age}, #{item.sex}, #{item.phone})
	</foreach>
</insert>

最终组装的sql，最后一个分隔符会自己去掉

insert into userinfo(id, userName, passWord, age, sex, phone) values(..),(..),(..),(..)

4、批量更新

int updateBatch(List<User> users);

<update id="updateBatch" parameterType="list">
	<foreach collection="list" item="user" separator=";">
	    update userinfo
	    <set>
	        <if test="user.phone != null and user.phone != ''">
	            phone = #{user.phone},
	        </if>
	        <if test="user.userName != null and user.userName != ''">
	            userName = #{user.userName}
	        </if>
	    </set>
	    where id = #{user.id}
	</foreach>
</update>

最终会组装成多条连续的sql ：update userinfo…;update userinfo…

5、批量删除

int deleteBatch(List<Integer> ids);

<delete id="deleteBatch" parameterType="list">
	delete from userinfo where id in
	<foreach collection="list" item="item" open="(" close=")" separator=",">
	    #{item}
	</foreach>
</delete>

最终拼成的sql是: delete from userinfo where id in ( 20,21 ,… )