Mybatis 常用操作

批量更新

<update id="updateBatch"  parameterType="java.util.List">  
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update course
        <set>
            name=${item.name}
        </set>
        where id = ${item.id}
    </foreach>      
</update>

批量插入

 1 <!-- 批量插入生成的兑换码 -->
 2      <insert id ="insertCodeBatch" parameterType="java.util.List" >
 3             <selectKey resultType ="java.lang.Integer" keyProperty= "id"
 4                  order= "AFTER">
 5                 SELECT LAST_INSERT_ID()
 6             </selectKey >
 7            insert into redeem_code
 8            (bach_id, code, type, facevalue,create_user,create_time)
 9            values
10             <foreach collection ="list" item="reddemCode" index= "index" separator =",">
11                 (
12                 #{reddemCode.batchId}, #{reddemCode.code},
13                 #{reddemCode.type},
14                 #{reddemCode.facevalue},
15                 #{reddemCode.createUser}, #{reddemCode.createTime}
16                 )
17             </foreach >
18      </insert >

Mybatis in操作的几种方式
其中collection 为 mapper里面的param("")里面的参数 遍历的单个对象 为item的值,当查询的参数有多个时,有两种方式可以实现，一种是使用@Param("xxx")进行参数绑定，另一种可以通过Map来传参数。

List<User> selectByIdSet(@Param("name")String name, @Param("ids")String[] idList);
 
<select id="selectByIdSet" resultMap="BaseResultMap">
	SELECT
	<include refid="Base_Column_List" />
	from t_user
	WHERE  name=#{name,jdbcType=VARCHAR} and id IN
	<foreach collection="ids" item="id" index="index"
			 open="(" close=")" separator=",">
	  #{id}
	</foreach>

Map传参

Map<String, Object> params = new HashMap<String, Object>(2);
params.put("name", name);
params.put("idList", ids);
mapper.selectByIdSet(params);
<select id="selectByIdSet" resultMap="BaseResultMap">  
     select  
     <include refid="Base_Column_List" />  
     from t_user where 
     name = #{name}
     and ID in  
     <foreach item="item" index="index" collection="idList" open="(" separator="," close=")">  
      #{item}  
     </foreach>  

如果参数的类型是Array,则在使用时，collection属性要必须指定为 array

List<User> selectByIdSet(String[] idList);
 
<select id="selectByIdSet" resultMap="BaseResultMap">
	SELECT
	<include refid="Base_Column_List" />
	from t_user
	WHERE id IN
	<foreach collection="array" item="id" index="index" open="(" close=")" separator=",">
	  #{id}
	</foreach>
</select>

单个参数时 如果参数的类型是List, 则在使用时，collection属性要必须指定为 list

List<User> selectByIdSet(List idList);
 
<select id="selectByIdSet" resultMap="BaseResultMap">
	SELECT
	<include refid="Base_Column_List" />
	from t_user
	WHERE id IN
	<foreach collection="list" item="id" index="index" open="(" close=")" separator=",">
	  #{id}
	</foreach>
</select>

java 参数是set

void findByids(Set<Integer> wtids);

<select id="findByids" resultMap="BaseResultMap">
	SELECT
	<include refid="Base_Column_List" />
	from t_user
	WHERE id IN
        <foreach collection="wtids" item="id" index="index" open="(" close=")" separator=",">
           #{id}
        </foreach>
</select>

原文地址:https://www.cnblogs.com/pojo/p/14294071.html

Mybatis if, set, where 动态sql和sql片段的使用

choose (when, otherwise)

HashMap hashMap = new HashMap();
hashMap.put("title","java");
//        hashMap.put("author","自己");
hashMap.put("views", 1000);

<select id="queryBlogChoose" parameterType="map" resultType="Blog">
    select * from blog
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
                author = #{author}
            </when>
            <otherwise>
                views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

foreach

• collection：遍历对象
• item：每一项
• index：索引
• open：开头
• separator：分隔符
• close：结尾

int[] array = new int[]{10, 5000, 9999};
List<Integer> list = new ArrayList<>();
for (int i : array) {
    list.add(i);
}

<select id="getBlogIn" parameterType="list" resultType="Blog">
    select * from blog
    <where>
        <if test="list != null and list.size() > 0">
            views in
            <foreach collection="list" item="id" index="index" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </where>
</select>

sql片段

• sql: 抽取的片段
• include:引用的标签

<sql id="if-title-author">
    <if test="title != null">
        title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</sql>

<select id="queryBlogIf" parameterType="map" resultType="Blog">
    select * from blog
    <where>
        <include refid="if-title-author"/>
    </where>
</select>