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>
批量删除：
接口：
@PostMapping( "/remove") @ResponseBody public AjaxResult remove(String[] ids) { return toAjax(heziBrandPerformanceService.deleteHeziBrandPerformanceByIds(ids)); }
配置文件：
<delete id="deleteByIds" parameterType="String"> DELETE FROM electric_fence WHERE id in <foreach collection="array" index="index" item="id" open="(" separator="," close=")"> #{id} </foreach> </delete>
foreach元素的属性主要有item，index，collection，open，separator，close。
- item：集合中元素迭代时的别名，该参数为必选。
- index：在list和数组中,index是元素的序号，在map中，index是元素的key，该参数可选
- open：foreach代码的开始符号，一般是(和close=")"合用。常用在in(),values()时。该参数可选
- separator：元素之间的分隔符，例如在in()的时候，separator=","会自动在元素中间用“,“隔开，避免手动输入逗号导致sql错误，如in(1,2,)这样。该参数可选。
- close: foreach代码的关闭符号，一般是)和open="("合用。常用在in(),values()时。该参数可选。
- collection: 要做foreach的对象，作为入参时，List对象默认用"list"代替作为键，数组对象有"array"代替作为键，Map对象没有默认的键。当然在作为入参时可以使用@Param("keyName")来设置键，设置keyName后，list,array将会失效。 除了入参这种情况外，还有一种作为参数对象的某个字段的时候。举个例子：如果User有属性List ids。入参是User对象，那么这个collection = "ids".如果User有属性Ids ids;其中Ids是个对象，Ids有个属性List id;入参是User对象，那么collection = "ids.id"
在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况 下，该属性的值是不一样的，主要有一下3种情况：
1. 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
<select id="countByUserList" resultType="_int" parameterType="list"> select count(*) from users <where> id in <foreach item="item" collection="list" separator="," open="(" close=")" index=""> #{item.id, jdbcType=NUMERIC} </foreach> </where> </select>
List<User> users = new ArrayList<User>(); int count = mapper.countByUserList(users);
2. 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
<select id="dynamicForeach2Test" resultType="Blog"> select * from t_blog where id in <foreach collection="array" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </select>
int[] ids = new int[] {1,3,6,9}; List blogs = blogMapper.dynamicForeach2Test(ids);
3. 如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了
<select id="dynamicForeach3Test" resultType="Blog"> select * from t_blog where title like "%"#{title}"%" and id in <foreach collection="ids" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </select>
final List ids = new ArrayList(); ids.add(1); ids.add(2); ids.add(3); Map params = new HashMap(); params.put("ids", ids); params.put("title", "中国"); List blogs = blogMapper.dynamicForeach3Test(params);
查询表中的二级项
查询表中否有子机构的所有机构 ( 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>
关联查询
<resultMap id="BaseResultMap" type="com.bo.ExpensesReceiptsBo" extends="com.model.dao.ExpensesReceiptsMapper.BaseResultMap"> <!-- 根据台账ID查询附件 --> <collection property="expensesReceiptsFileBos" column="id" select="com.dao.ExpensesReceiptsFileDao.selectExpensesReceiptsFiles"></collection> </resultMap>
