mybatis 批量操作笔记

1.批量删除

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

2.批量增加

  <insert id="insertUserByList">
    insert into user (username, password, phone, birthday) values
       <foreach collection="list" separator="," item="u">
         (#{u.username}, #{u.password}, #{u.phone}, #{u.birthday})
       </foreach>
  </insert>

3.联表查询

  <resultMap id="selectUserByNameAndPhoneAndPasswd" type="com.javasm.entity.User">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="username" jdbcType="VARCHAR" property="username" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="phone" jdbcType="CHAR" property="phone" />
    <result column="birthday" jdbcType="TIMESTAMP" property="birthday" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="create_by" jdbcType="INTEGER" property="createBy" />
    <result column="update_by" jdbcType="INTEGER" property="updateBy" />
    <collection property="orderList" ofType="com.javasm.entity.Order" column="id">
      <id column="order_id" property="orderId"/>
      <result column="user_id" property="userId"/>
      <result column="user_name" property="userName"/>
      <result column="pay_type" property="payType"/>
      <result column="total_price" property="totalPrice"/>
      <result column="order_state" property="orderState"/>
      <result column="storage_id" property="supplierId"/>
      <result column="storage_name" property="storageName"/>
      <result column="supplier_id" property="supplierId"/>
      <result column="deleted" property="deleted"/>
    </collection>
  </resultMap>


  <select id="findUserByLike" resultMap="BaseResultMap">
    select DISTINCT u.*, o.*
    from `user` u
    left join `order` o  on o.user_id = u.id
    left join order_detail od on o.order_id = od.order_id
    left join goods g on od.goods_id = g.id
    <where>
      <if test="userName!= null and userName!=''">
        and u.username like concat('%',concat(#{userName}, '%'))
      </if>
      <if test="password!= null and password!=''">
        and u.password like concat('%',concat(#{password}, '%'))
      </if>
      <if test="phone!= null and phone!=''">
        and u.phone like concat('%',concat(#{phone}, '%'))
      </if>
      <if test="goodsName!= null and goodsName!=''">
        and g.goods_name like concat('%',concat(#{goodsName}, '%'))
      </if>
      <if test="description!= null and description!=''">
        and g.description like concat('%',concat(#{description}, '%'))
      </if>
    </where>
  </select>

4.二次查询一对多关系

  <resultMap id="BaseResultMap" type="com.javasm.entity.User">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="username" jdbcType="VARCHAR" property="username" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="phone" jdbcType="CHAR" property="phone" />
    <result column="birthday" jdbcType="TIMESTAMP" property="birthday" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="create_by" jdbcType="INTEGER" property="createBy" />
    <result column="update_by" jdbcType="INTEGER" property="updateBy" />
    <collection property="orderList" ofType="com.javasm.entity.Order" column="id" select="com.javasm.mapper.OrderMapper.selectList"/>
  </resultMap>



    <select id="selectById" resultMap="BaseResultMap">
      select *
      from user
      where id = #{id};
    </select>

5.二次查询一对一关系

  <resultMap id="BaseResultMap" type="com.javasm.entity.OrderDetail">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="order_id" jdbcType="INTEGER" property="orderId" />
    <result column="goods_id" jdbcType="INTEGER" property="goodsId" />
    <result column="quantity" jdbcType="INTEGER" property="quantity" />
    <result column="cost" jdbcType="DECIMAL" property="cost" />
    <association property="goods" javaType="com.javasm.entity.Goods" column="goods_id" select="com.javasm.mapper.GoodsMapper.selectByPrimaryKey"/>
  </resultMap>


    <select id="selectById" resultMap="BaseResultMap">
      select *
      from order_detail
      where id = #{id};
    </select>