mybatis 的 select 和update 方法汇总

1. select

1.1 实体查询

//  mapper ：
 List<InfItemEntityVO> pageQueryByExample(InfItemEntityVO infItemEntityVO);
//   xml:
 <!-- 分页模糊查询 -->
  <select id="pageQueryByExample" resultType="com.anji.allways.business.inf.vo.InfItemEntityVO">
  	select
  	<include refid="Base_Column_List"></include>
  	from wms_inf_item
  	where 1=1
  	<if test="itemCode != null and itemCode != ''">
		AND item_code = #{itemCode}
	</if>
  	<if test="itemName != null and itemName != ''">
		AND item_name = #{itemName}
	</if>
	order by create_time desc 
    LIMIT #{pageNum},#{pageRows}
  </select>
// 说明 ： 参数类型可以不填，返回的对象是实体或者vo

2. 字段查询 参数是Map  返回List<Map>

	/**
	 * 
	 * <pre>
	 * 跨服务查询零件属性
	 * </pre>
	 *
	 * @param id
	 * @return
	 */
	List<Map<String, Object>> queryItemPropertyByCon(Map<String, Object> map);

//  mapper.xml
<!-- 通过条件查询零件属性 -->
	<select id="queryItemPropertyByCon" parameterType="java.util.Map"
		resultType="java.util.Map">
		SELECT
			id AS id,
			item_id AS itemId,
			is_repack AS isRepack,
			role_id AS roleId,
			role_name AS roleName,
			inv_max_qty invMaxQty,
			item_name itemName,
			picking_name pickingName,
			whse_id whseId,
			force_loc forceLoc
		FROM
		wms_item_property
		<where>
			<foreach collection="map" item="value" index="key" separator="and">
				${key} = #{value}
			</foreach>
		</where>
	</select>

3. 参数map  返回对象list

// serviceImpl
   /**
     * @param code
     * @param orgId
     * @return
     * @see com.anji.allways.business.auth.service.ItemPropertyService#queryByCodeList(java.lang.String, java.lang.Long)
     */
    @Override
    public List<ItemProperty> queryByCodeList(String code, Long orgId) {
        Map<String,Object> map = new HashMap<String, Object>();
        map.put("item_code",code);
        map.put("org_id", orgId);
        map.put("is_del", DeleteStatusE.USEFUL.getValue());
        return itemPropertyMapper.queryByCodeList(map);
    }
// mapper.xml
	<!-- 通过零件代码查询零件属性 -->
	<select id="queryByCodeList" parameterType="java.util.Map" resultType="com.anji.allways.business.auth.entity.ItemProperty">
		SELECT
      	 *
		FROM
		wms_item_property
		<where>
			<foreach collection="map" item="value" index="key" separator="and">
				${key} = #{value}
			</foreach>
		</where>
	</select>

 

4. 批量更新

    <update id="updateBatchSetYoyAndMon">
        UPDATE work_eff_stati
        set YOY_ON_TIME_WORK =
        <foreach collection="result" item="item" separator=" " open="case id" close="end ,">
            when #{item.id} then #{item.yoyOnTimeWork}
        </foreach>
        MOM_ON_TIME_WORK =
        <foreach collection="result" item="item" separator=" " open="case id" close="end">
            when #{item.id} then #{item.momOnTimeWork}
        </foreach>
        where id in
        <foreach collection="result" item="item" separator="," open="(" close=")">
           #{item.id}
        </foreach>


    </update>