xml 特殊字符可以适用转义字符代替
< | < | 小于号 |
> | > | 大于号 |
& | & | 和 |
' | ’ | 单引号 |
" | " | 双引号 |
其他方法:用 <![CDATA[ ]]> 节,将sql包括起来, 在两者之间嵌入不想被解析程序解析的原始数据,解析器不对CDATA区中的内容进行解析,而是 将这些数据原封不动地交给下游程序处理。
通用字段,不推荐使用 SELECT * FROM
<sql id="Base_Column_List" >
pai_id, create_date, modify_date, area_name, up_area_id, floor, last_floor, remark,
area_code, area_index, name_en, name_jm, post_code, district_number, sort_name, sel_tag,
ass_key, ass_date, area_name_sync_cus
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >
SELECT
<include refid="Base_Column_List" />
FROM tb_im_verbal_trick_info
WHERE id = #{id,jdbcType=VARCHAR}
</select>
通用条件查询 commonSql
<where>
<if test='userStatus != null'> <!-- 包装类型,不要添加 !='' 这样的判断 -->
and user_status = #{userStatus, jdbcType=INTEGER}
</if>
<if test="modifyTime != null" >
modify_time = #{modifyTime,jdbcType=TIMESTAMP}
</if>
<if test='userPhone != null and userPhone != ""'> <!-- 模糊查询 -->
and user_phone like '%${userPhone}%'
</if>
<if test='userPhone != null and userPhone != ""'> <!-- 更安全的模糊查询 -->
and user_phone like CONCAT('%',#{userPhone,jdbcType=VARCHAR},'%')
</if>
<if test='searchVal != null and searchVal != ""'> <!-- 通用多字段模糊查询 -->
and ( real_name like '%${searchVal}%' or name like '%${searchVal}%' )
</if>
<if test='createTime!= null'> <!-- TIMESTAMP类型,不要添加 !='' 这样的判断 -->
and create_time= #{createTime, jdbcType=TIMESTAMP}
</if>
<if test='startTime != null and startTime != ""'> <!-- 通用时间范围查询 -->
and DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s') >= date_format(#{startTime},'%Y-%m-%d %H:%i:%s') </if>
<if test='endTime != null and endTime != ""'>
and DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{endTime, jdbcType=VARCHAR}
</if>
</where>
<select id="queryUserInfoBo" parameterType="com.juchiwang.sys.bo.ImVerbalTrickInfoBo" resultMap="BaseResultMap">
SELECT
*
FROM
tb_im_info
<include refid="commonSql"></include>
</select>
查询
批量查询
<select id="selectUserList" parameterType="SysUser" resultMap="SysUserResult">
SELECT * FROM sys_user
<where>
<if test="loginName != null and loginName != ''">
AND login_name like concat('%', #{loginName}, '%')
</if>
<if test="status != null and status != ''">
AND status = #{status}
</if>
<if test="phonenumber != null and phonenumber != ''">
AND phonenumber like concat('%', #{phonenumber}, '%')
</if>
<if test="beginTime != null and beginTime != ''"><!-- 开始时间检索 -->
AND date_format( create_time,'%y%m%d') >= date_format(#{beginTime},'%y%m%d')
</if>
<if test="endTime != null and endTime != ''"><!-- 结束时间检索 -->
AND date_format( create_time,'%y%m%d') <= date_format(#{endTime},'%y%m%d')
</if>
</where>
</select>
主键查询
接口:
@GetMapping("/getInfo/{id}")
public CommonResult selectUserInfo (@PathVariable(value = "id", required = true) String id) {
ElectricFence result = electricFenceService.getInfo(id);
return CommonResult.success(result);
}
Mapper:
UserInfo selectUserInfo(String id);
配置文件:
<select id="selectUserInfo" parameterType="java.lang.String" resultMap="BaseResultMap">
SELECT * FROM user_info
WHERE id = #{id} <!-- 这里的#{}中的内容要和mapper方法的参数名一样才行
</select>
根据状态统计查询
<select id="selectTabNum" parameterType="com.juchiwang.msc.bo.MscBaseFeedbackBo" resultMap="BaseResultMap">
SELECT
IFNULL(SUM(1),0) all_num
IFNULL(SUM(case when clear_state = 0 then 1 else 0 end),0) clearStateNo, <!-- 有欠款 -->
IFNULL(SUM(case when back_state = 0 then 1 else 0 end),0) backStateNo, <!-- 未回款 -->
IFNULL(SUM(case when back_state = 1 and clear_state = 0 then 1 else 0 end),0) backStateHalf, <!-- 部分回款 -->
FROM
msc_base_feedback
</select>
查询数据是否已经存在
<select id="queryApPerformRole2Count" parameterType="com.juchiwang.bo.ApPerformRoleBo" resultType="java.lang.Integer">
SELECT
count(*)
FROM
ap_perform_role
WHERE
del_state = 0
</select>
增加
<insert id="insert" parameterType="com.qm.business.model.LogLogin">
insert into log_login (id, ip, account,
result, reason, time,
mac_address, user_id, log_out_time
)
values (#{id,jdbcType=BIGINT}, #{ip,jdbcType=VARCHAR}, #{account,jdbcType=VARCHAR},
#{result,jdbcType=VARCHAR}, #{reason,jdbcType=VARCHAR}, #{time,jdbcType=TIMESTAMP},
#{macAddress,jdbcType=VARCHAR}, #{userId,jdbcType=BIGINT}, #{logOutTime,jdbcType=TIMESTAMP}
)
</insert>
<insert id="insertSelective" parameterType="com.qm.business.model.LogLogin">
insert into log_login
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="ip != null">
ip,
</if>
<if test="account != null">
account,
</if>
<if test="result != null">
result,
</if>
<if test="reason != null">
reason,
</if>
<if test="time != null">
time,
</if>
<if test="macAddress != null">
mac_address,
</if>
<if test="userId != null">
user_id,
</if>
<if test="logOutTime != null">
log_out_time,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=BIGINT},
</if>
<if test="ip != null">
#{ip,jdbcType=VARCHAR},
</if>
<if test="account != null">
#{account,jdbcType=VARCHAR},
</if>
<if test="result != null">
#{result,jdbcType=VARCHAR},
</if>
<if test="reason != null">
#{reason,jdbcType=VARCHAR},
</if>
<if test="time != null">
#{time,jdbcType=TIMESTAMP},
</if>
<if test="macAddress != null">
#{macAddress,jdbcType=VARCHAR},
</if>
<if test="userId != null">
#{userId,jdbcType=BIGINT},
</if>
<if test="logOutTime != null">
#{logOutTime,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
更新
<update id="updateByPrimaryKey" parameterType="com.qm.business.model.LogLogin">
update log_login
set ip = #{ip,jdbcType=VARCHAR},
account = #{account,jdbcType=VARCHAR},
result = #{result,jdbcType=VARCHAR},
reason = #{reason,jdbcType=VARCHAR},
time = #{time,jdbcType=TIMESTAMP},
mac_address = #{macAddress,jdbcType=VARCHAR},
user_id = #{userId,jdbcType=BIGINT},
log_out_time = #{logOutTime,jdbcType=TIMESTAMP}
where id = #{id,jdbcType=BIGINT}
</update>
<update id="updateByPrimaryKeySelective" parameterType="com.qm.business.model.LogLogin">
update log_login
<set>
<if test="ip != null">
ip = #{ip,jdbcType=VARCHAR},
</if>
<if test="account != null">
account = #{account,jdbcType=VARCHAR},
</if>
<if test="result != null">
result = #{result,jdbcType=VARCHAR},
</if>
<if test="reason != null">
reason = #{reason,jdbcType=VARCHAR},
</if>
<if test="time != null">
time = #{time,jdbcType=TIMESTAMP},
</if>
<if test="macAddress != null">
mac_address = #{macAddress,jdbcType=VARCHAR},
</if>
<if test="userId != null">
user_id = #{userId,jdbcType=BIGINT},
</if>
<if test="logOutTime != null">
log_out_time = #{logOutTime,jdbcType=TIMESTAMP},
</if>
</set>
where id = #{id,jdbcType=BIGINT}
</update>
删除
删除:
Mapper:
void deleteByIds(Long id);
配置文件:
<delete id="deleteHeziBrandPerformanceById" parameterType="Long">
DELETE FROM hezi_brand_performance WHERE id = #{id} <!-- 这里的#{}中的内容要和mapper方法的参数名一样才行 -->
</delete>
批量删除-单参数(Array)
Mapper:
int count = mapper.countByUserList(Long[] userIds);
配置文件:
<select id="countByUserList" resultType="_int" parameterType="list">
select count(*) from users
<where>
id in
<foreach collection="array" item="userId" open="(" separator="," close=")">
#{userId}
</foreach>
</where>
</select>
批量删除-单参数(List)
Mapper:
List<User> users = new ArrayList<User>();
int count = mapper.countByUserList(users);
配置文件:
<select id="countByUserList" resultType="_int" parameterType="list">
select count(*) from users
<where>
id in
<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
#{item}
</foreach>
<!--
<foreach item="item" collection="list" separator="," open="(" close=")" index="">
#{item.id, jdbcType=NUMERIC}
</foreach>
-->
</where>
</select>
批量删除-多参数:
RouteParam 字段:
private List<Long> routeIds;
Mapper:
List<RouteResult> list(RouteParam routeParam);
配置文件:
<foreach collection="routeIds" item="id" index="index" open="(" separator="," close=")">
#{id}
</foreach>
查询表中的二级项
查询表中否有子机构的所有机构 ( parent_id = id )
<select id="querySubDept" parameterType="SysDeptSelectBo" resultMap="BaseResultMap">
SELECT
one.id,
one.dept_name
FROM sys_dept one
WHERE
one.id in (SELECT parent_id FROM sys_dept WHERE parent_id in
<foreach collection="delBoIds" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
and del_state = 0
)
</select>
统计登录的前一天为止,过去6个月内创建的车辆
<select id="listVehicleTrend" resultType="com.qm.business.vehicle.client.model.VehicleTrendDto">
select
DATE_FORMAT(v.create_time, '%Y-%m') as date, sum(1) as num
from
vehicle v
where
v.del_flag != '1'
and v.create_time < date_sub(curdate(), interval 1 day)
and v.create_time > date_add(curdate(), interval -6 MONTH)
GROUP BY date
</select>