- 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>