批量添加
<insert id="addUserBatch">
INSERT INTO fx_user_base(
id,
identitykey,
password,
status,
phone,
name
)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.id,jdbcType=VARCHAR},
#{item.identitykey,jdbcType=VARCHAR},
#{item.password,jdbcType=VARCHAR},
#{item.status,jdbcType=INTEGER},
#{item.phone,jdbcType=VARCHAR},
#{item.name,jdbcType=VARCHAR}
)
</foreach>
</insert>
根据list查询
<select id="getLv2List" resultType="com.ifeixiu.media.pojo.Department">
SELECT
d.id,
d.name,
d.parent_id,
d.level,
d.create_time,
d.update_time,
d.source
FROM
fx_department d
left join network_user_role ur on ur.department_id=d.id
left join network_role r on ur.role_id=r.id
WHERE
1=1
<if test="keyword!= null and keyword!=''">
and d.name like CONCAT('%',#{keyword},'%')
</if>
<if test="depIdList!= null">
and d.id in
<foreach collection="depIdList" item="item" index="index" open="(" separator="," close=")">
#{item, jdbcType=VARCHAR}
</foreach>
</if>
<if test="type==1">
and r.role_type=5
</if>
group by d.id
</select>
抽离公共 sql
<sql id="Base_Column_List">
id,
`name`,
parent_id,
`level`,
create_time,
update_time
</sql>
<select id="getDepartmentById" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"></include>
FROM fx_department
WHERE id = #{id}
</select>
复杂 resultMap
<resultMap id="BaseResultMap" type="com.ifeixiu.media.packaged.PkgMediaCheck">
<id column="id" property="id"/>
<result column="type" property="type"/>
<result column="create_time" property="createTime"/>
<result column="operator_time" property="operatorTime"/>
<association property="department" resultMap="DepartmentMap"/>
</resultMap>
<resultMap id="DepartmentMap" type="com.ifeixiu.media.packaged.PkgDepartment">
<result column="department_id" property="id"/>
<result column="department_name" property="name"/>
<result column="department_parent_id" property="parentId"/>
<association property="parent" resultMap="parentDepartmentMap"/>
</resultMap>
<resultMap id="parentDepartmentMap" type="com.ifeixiu.media.packaged.PkgDepartment">
<result column="parent_department_id" property="id"/>
<result column="parent_department_name" property="name"/>
</resultMap>
条件查询
SELECT
mn.id,
mn.title,
mn.content,
mn.type,
mn.notice_type,
mn.issuing_time,
mn.create_time,
mn.create_user,
mn.update_time,
mn.update_user,
mn.is_top,
mn.sort,
mn.is_delete
FROM
media_notice mn
WHERE
1=1
<if test="view.type!=null">
and mn.type = #{view.type}
</if>
<if test="view.isDelete!=null">
and mn.is_delete = #{view.isDelete}
</if>
<if test="view.noticeType!=null">
and mn.notice_type = #{view.noticeType}
</if>
<if test="view.startTime != null">
and mn.issuing_time between #{view.startTime} and #{view.endTime}
</if>
<if test="view.title!= null and view.title!=''">
and mn.title like CONCAT('%',#{view.title},'%')
</if>
case when...then...else...end...用法
<select id="getGroupList" resultType="com.dobell.research.mybatis.entities.vo.GroupInfoVO">
SELECT
cu.unit_name groupName,
cu.unit_code groupCode,
cu.third_party_id,
ut.role_id,
ut.head_image,
role.`COMMENT` roleName,
ut.job_type,
job.`COMMENT` jobName,
cu.create_time,
cuc.`level` `level`,
CASE
WHEN ut.role_id = 8 THEN
1 ELSE ut.role_id
END sort
FROM
common_user_to_unit ut
LEFT JOIN common_user u ON ut.user_code = u.user_code
LEFT JOIN common_unit cu ON ut.group_code = cu.unit_code
LEFT JOIN common_unit_control cuc on cuc.group_code = cu.unit_code
LEFT JOIN ( SELECT type, VALUE, COMMENT FROM sys_dictionary WHERE type = 13 )
role ON role.VALUE= ut.role_id
LEFT JOIN ( SELECT type, VALUE, COMMENT FROM sys_dictionary WHERE type = 5 )
job ON job.`value` = ut.job_type
WHERE
cu.`status` = 1
AND (ut.is_delete = 1 OR ut.role_id = 8)
AND ut.role_id IN ( 4, 5, 6, 7, 8 )
AND (ut.student_type != 0 OR ut.role_id = 8)
AND u.user_code = #{userCode}
GROUP BY
ut.group_code
ORDER BY sort
</select>