Mybatis笔记 -- 多表关联插入、更新、查询实例

在DeviceModel添加需要关联的实体 DeviceModelAttr

private List<DeviceModelAttr> deviceModelAttr;

DeviceModelMapper.xml 中配置关联实体 DeviceModelAttr

<collection property="deviceModelAttr" ofType="com.industry.txsp.entity.DeviceModelAttr">
	<id property="modelAttrId" column="model_attr_id"/>
	<result property="dmAttrType" column="dm_attr_type"/>
	<result property="attrValue" column="attr_value"/>
	<result property="modelId" column="model_id"/>
</collection>

在DeviceModelServiceImpl 中实现，关联插入，关联查询，关联更新

@Service
public class DeviceModelServiceImpl implements DeviceModelService {
    private static final Logger logger = LoggerFactory.getLogger(DeviceModelService.class);

    @Autowired
    private DeviceModelMapper deviceModelMapper;

    @Autowired
    private DeviceModelAttrMapper deviceModelAttrMapper;

    @Override
    public PageInfo<DeviceModel> query(DeviceModel deviceModel, Integer pageSize, Integer pageNum) {

        if (pageNum == null || pageNum.equals("")) {
            pageNum = 1;
        }
        if (pageSize == null || pageSize.equals("")) {
            pageSize = 10;
        }
        //开启分页
        PageHelper.startPage(pageNum, pageSize);
        List<DeviceModel> list = deviceModelMapper.query(deviceModel);
        logger.info("queryDeviceModel=> effect line count is " + list.size());
        return new PageInfo<>(list);
    }

    @Override
    public DeviceModel findById(String modelId) {
        return deviceModelMapper.findById(modelId);
    }

    @Override
    public int create(DeviceModel deviceModel) {
        IUser user = AdminSecurityUtils.getSecurityUser();
        String uuid = UuidUtil.getRandomUUID();
        deviceModel.setModelId(uuid);
        deviceModel.setCreator(user.getName());
        deviceModel.setCreateId(user.getId());
        deviceModel.setCreateTime(new Date());
        deviceModel.setUpdateTime(new Date());
        deviceModel.setIsDeleted(0);
        for (DeviceModelAttr deviceModelAttr : deviceModel.getDeviceModelAttr()) {
            deviceModelAttr.setModelId(uuid);
            deviceModelAttr.setModelAttrId(UuidUtil.getRandomUUID());
            deviceModelAttrMapper.create(deviceModelAttr);
        }
        return deviceModelMapper.create(deviceModel);
    }

    @Override
    public int update(DeviceModel deviceModel) {
        for(DeviceModelAttr deviceModelAttr : deviceModel.getDeviceModelAttr()){
            deviceModelAttrMapper.update(deviceModelAttr);
        }
        return deviceModelMapper.update(deviceModel);
    }

完整DeviceModelMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.industry.txsp.mapper.DeviceModelMapper">

	<!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.industry.txsp.entity.DeviceModel" id="deviceModelMap">
        <result property="modelId" column="model_id"/>
        <result property="modelName" column="model_name"/>
        <result property="createId" column="create_id"/>
        <result property="creator" column="creator"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="isDeleted" column="is_deleted"/>
		<collection property="deviceModelAttr" ofType="com.industry.txsp.entity.DeviceModelAttr">
			<id property="modelAttrId" column="model_attr_id"/>
			<result property="dmAttrType" column="dm_attr_type"/>
			<result property="attrValue" column="attr_value"/>
			<result property="modelId" column="model_id"/>
		</collection>
    </resultMap>
    
    
    <select id="query" resultMap="deviceModelMap">
		SELECT
		*
		FROM device_model dm,device_model_attr dma
		<where>
			<if test="deviceModel.modelId !=null and deviceModel.modelId !=''">
				AND model_id = #{deviceModel.modelId}
			</if>
			<if test="deviceModel.modelName !=null and deviceModel.modelName !=''">
				AND model_name = #{deviceModel.modelName}
			</if>
			<if test="deviceModel.createId !=null and deviceModel.createId !=''">
				AND create_id = #{deviceModel.createId}
			</if>
			<if test="deviceModel.creator !=null and deviceModel.creator !=''">
				AND creator = #{deviceModel.creator}
			</if>
			<if test="deviceModel.createTime !=null and deviceModel.createTime !=''">
				AND create_time = #{deviceModel.createTime}
			</if>
			<if test="deviceModel.updateTime !=null and deviceModel.updateTime !=''">
				AND update_time = #{deviceModel.updateTime}
			</if>
			<if test="deviceModel.isDeleted !=1">
				AND is_deleted = 0
			</if>
				AND dm.model_id = dma.model_id
		</where>
	</select>
	
	
	<select id="findById" resultMap="deviceModelMap">
	    SELECT
	      *
	    FROM device_model dm,device_model_attr dma
	    WHERE    dm.model_id = #{modelId} and dm.is_deleted  =0  and dm.model_id = dma.model_id
	</select>

	<!--增加-->
  	<insert id="create" parameterType="com.bonc.industry.txsp.entity.DeviceModel">
        INSERT INTO device_model 
        	(model_id,model_name,create_id,creator,create_time,update_time,is_deleted)
        VALUES
        	(#{modelId},#{modelName},#{createId},#{creator},#{createTime},#{updateTime},#{isDeleted})
    </insert>
  	
  	<!--更新-->
  	<update id="update" parameterType="com.bonc.industry.txsp.entity.DeviceModel">
     	UPDATE device_model 
     	SET 
     		<if test="modelId !=null and modelId !=''">
				model_id = #{modelId}
			</if>
	 		<if test="modelName !=null and modelName !=''">
				,model_name = #{modelName}
			</if>
	 		<if test="createId !=null and createId !=''">
				,create_id = #{createId}
			</if>
	 		<if test="creator !=null and creator !=''">
				,creator = #{creator}
			</if>
	 		<if test="createTime !=null">
				,create_time = #{createTime,jdbcType=TIMESTAMP}
			</if>
	 		<if test="updateTime !=null">
				,update_time = #{updateTime,jdbcType=TIMESTAMP}
			</if>
	 		<if test="isDeleted !=null and isDeleted !=''">
				,is_deleted = #{isDeleted}
			</if>
	      	WHERE    model_id = #{modelId}          </update>
</mapper>