mybatis查询相关操作

1.查询的对象中带有集合的数据（有相同字段需要取别名，不然属性会被最外层属性值覆盖）

<resultMap id="baseResultMap" type="com.hx.zhdx.domain.entity.base.model.business.UserInfoManage">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="department" property="department"/>
    <result column="post_certification" property="postCertification"/>
    <result column="ability_embodiment" property="abilityEmbodiment"/>
    <result column="type" property="type"/>
    <result column="create_user" property="createUser"/>
    <result column="create_user_id" property="createUserId"/>
    <result column="create_time" property="createTime"/>
    <collection property="intelligenceList" resultMap="intelligenceListMap" />
    <collection property="programList" resultMap="programListMap" />
</resultMap>

<resultMap id="intelligenceListMap" type="com.hx.zhdx.domain.entity.base.model.business.Intelligence">
    <result column="intelligence_name" property="intelligenceName"/>
    <result column="start_date" property="startDate"/>
    <result column="end_date" property="endDate"/>
    <collection property="comFile" resultMap="comFileMap"/>
</resultMap>

<resultMap id="programListMap" type="com.hx.zhdx.domain.entity.base.model.business.UserProgram">
    <result column="pro_name" property="proName"/>
    <result column="pro_info" property="proInfo"/>
    <result column="pro_ability" property="proAbility"/>
</resultMap>

<resultMap id="comFileMap" type="com.hx.zhdx.domain.entity.base.model.business.ComFile">
    <result column="c_id" property="id"/>
    <result column="original_name" property="originalName"/>
    <result column="file_path" property="filePath"/>
</resultMap>

<select id="getViewInfoManage" resultMap="baseResultMap" parameterType="java.lang.Long">
SELECT
    fui.id,
   fui.name,
   fui.department,
   fui.post_certification,
   fui.ability_embodiment,
   fui.type,
   fui.create_time,
   fi.intelligence_name,
   fi.start_date,
   fi.end_date,
   fp.pro_ability,
   fp.pro_info,
   fp.pro_name,
   cf.id AS c_id,
   cf.original_name,
   cf.file_path
FROM
   famf_user_info fui
   LEFT JOIN famf_user_intelligence fuin ON fuin.user_id = fui.id
   LEFT JOIN famf_intelligence fi ON fi.id = fuin.intelligence_id
   LEFT JOIN famf_user_pro fup ON fup.user_id = fui.id
   LEFT JOIN famf_pro fp ON fp.id = fup.pro_id
   LEFT JOIN famf_file_intelligence ffi ON ffi.intelligence_id = fi.id
   LEFT JOIN com_file cf ON cf.id = ffi.file_id
   WHERE fui.id=#{id}
   AND fui.is_del='0'
    </select>

2.更新语句操作

<update id="updateUser" parameterType="com.hx.zhdx.dao.model.user.UserInfo">
   UPDATE
   user
   <set>
      <if test="staffAccount!=null">
         staff_account=#{staffAccount},
      </if>
      <if test="name!=null">
         name=#{name},
      </if>
      <if test="sex!=null">
         sex=#{sex},
      </if>
      <if test="telphone!=null">
         telphone=#{telphone},
      </if>
      <if test="state!=null">
         state=#{state},
      </if>
      <if test="staffId!=null">
         staff_id=#{staffId},
      </if>
      <if test="city!=null">
         city=#{city},
      </if>
      <if test="area!=null">
         area=#{area},
      </if>
      <if test="sysCode!=null">
         sys_code=#{sysCode},
      </if>
      <if test="blocCode!=null">
         bloc_code=#{blocCode},
      </if>
      <if test="oaCode!=null">
         oa_code=#{oaCode},
      </if>
      <if test="govPrechangeId!=null">
         gov_prechange_id=#{govPrechangeId},
      </if>
      <if test="oaId!=null">
         oa_id=#{oaId},
      </if>
      <if test="mssId!=null">
         mss_id=#{mssId},
      </if>
      <if test="delFlag!=null">
         del_flag=#{delFlag},
      </if>
      <if test="email!=null">
         email=#{email},
      </if>

      <if test="updateTime!=null">
         update_time=#{updateTime}
      </if>
   </set>
       where staff_code=#{staffCode}
</update>

3.插入语句

<insert id="addUsersInfo" parameterType="com.hx.zhdx.dao.model.user.MUser">
    INSERT INTO user(staff_code,staff_account,name,sex,telphone,state,staff_id,city,area,sys_code,bloc_code,
    oa_code,gov_prechange_id,create_time,del_flag,oa_id,mss_id,version,email) VALUES (#{staffCode},#{staffAccount},#{name},#{sex},#{telphone},#{state},#{staffId},#{city},#{area},#{sysCode},#{blocCode},#{oaCode},#{govPrechangeId},
    #{createTime},#{delFlag},#{oaId},#{mssId},#{version},#{email})

</insert>

4.插入集合数据

<insert id="insertUserList" parameterType="java.util.List">
   insert into user_info(staff_code,channel_id,channel_name,city,bss_channel_id,bill_channel_id) values
   <foreach collection="list" separator="," item="item">
   (#{item.staffCode},#{item.channelId},#{item.channelName},#{item.city},#{item.bssChannelId}, #{item.billChannelId})
    </foreach>
</insert>

5.更新符合条件的集合

void batchDeleteStrategicDevelopment(@Param("list")List<Long> ids, @Param("userId")Integer userId);

<update id="batchDeleteStrategicDevelopment" parameterType="java.util.List">
    update test_strategic_development set del_flag=1, update_user = #{userId}
    where row_id in
    <foreach collection="list" separator="," open="(" close=")" item="rowId">
        #{rowId}
    </foreach>
</update>

6.删除集合数据

Integer delUserMessage(@Param("userId")Integer userId, @Param("list")List<Integer> list);

<delete id="delUserMessage" parameterType="java.util.Map">
    DELETE FROM zqzt_user_message WHERE user_id=#{userId,jdbcType=INTEGER} AND message_id IN (
    <foreach collection="list" item="item" index="index" separator="," >
        #{item,jdbcType=INTEGER}
    </foreach>
    )
</delete>