mybatis语句语法
·
杂技
1.注释写法
<!-- 注释内容 -->
2.判空
user_id != '' and user_id is not null
<if test="userId != null and userId != '' ">
<if test="list != null and list.size > 0">
3.# 和 $ 区别
表名 和排序用 $
4.传递一个参数 加注解@Param("userId")
接口：public int overdueSoonCount(@Param("userId") String userId);
xml: <if test="userId != null and userId != '' ">and pum_user_id = #{userId}</if>
5.特殊符号写法
原符号 < <= > >= & ' "
替换符号 < <= > >= & ' "
6.优惠券
if(pps_ps_is_use_first=1,'首单使用', '直减券' ) AS couponType,
CONCAT(pps_valid_start_time,' - ',pps_valid_end_time) AS validtime,
ELT(pum_use_type,"领取","未领取过期","去使用","已使用","已过期") as useState
字段不等于空
一。if test
select * from achi_evaluation_info
<where>
<if test="deptId != null and deptId != '' "> and unit_name in (select dept_name from sys_dept where dept_id like concat(#{deptId},'%'))</if>
<if test="guid != null and guid != '' "> and guid = #{guid}</if>
<if test='haveParent == "1" '> and (a.oc_parent_id is not null and a.oc_parent_id !='')</if>
<where>
if test判断参数 true false (不要前面的东东) 并且是双等于==不是=
<if test="status == true ">and tp_status = 1</if>
<if test="status == false ">and tp_status = 0</if>
整型
<if test="isEable != null and isEable == 0 ">and a.deleted = 1</if>
<if test="isEable != null and isEable == 1 ">and a.deleted = 0</if>
<if test="status != null ">and a.status = #{status}</if>
数组
<if test="object!=null and object.length>0">
<yourSql>
</if>
判断集合不为空与为空
<if test="userStationIdList == null or userStationIdList.size() == 0"> </if>
<if test="userStationIdList != null and userStationIdList.size() > 0"> </if>
<if test="userCarrierIdList != null and userCarrierIdList.size()>0 ">and a.ocvaq_carrier_id in
<foreach item="item" index="index" collection="userCarrierIdList" open="(" separator="," close=")">
#{item}
</foreach>
</if>
判断日期
<if test="validStartTime != null ">and (a.pu_valid_start_time is not null and DATEDIFF(#{validStartTime},a.pu_valid_start_time) >= 0)</if>
<if test="validEndTime != null "> if test里面判空 去掉字符串and validEndTime != ''
if test外面判空用is not null
查询对象的属性是集合 集合遍历再判断
<if test="stationSelectConditionList!=null"> <!--筛选条件-->
<foreach collection="stationSelectConditionList" item="item">
<if test="item.aupCode == 'StationType' ">
<if test='item.value.contains("1") '>
and (e.ospsr_val like concat(concat("%",#{item.value}),"%") or e.ospsr_val ='2') <!--筛选条件4——是否对外开发-->
</if>
<if test='!item.value.contains("1") '>
and (e.ospsr_val like concat(concat("%",#{item.value}),"%")) <!--筛选条件4——是否对外开发-->
</if>
</if>
<if test="item.aupCode == 'BusinessType' ">
and a.os_carriy_type = #{item.value} <!--筛选条件7——站点属性：直营-->
</if>
<if test="item.aupCode == 'BusinessTime' ">
<!--筛选条件8——营业时间-->
</if>
</foreach>
</if>
判断查询条件包含某个字符串
<if test='item.value.contains("1") '></if>
判断数据库字段是否包含字符串
e.ospsr_val like %#{item.value}%
判断数据库字段是否被字符串包含
find_in_set(e.ospsr_val,#{item.value}) 字符串需要用,号隔开
locate(e.ospsr_val,'0;1;2') > 0 字符串没要求
二。 Like
<if test="unitCode != null and unitCode != '' ">and unit_code like concat(#{unitCode},'%')</if>
name like "%"#{name}"%"
and phone like concat(concat("%",#{phone}),"%")
<bind name="pattern" value="'%'+email+'%'"/>nd email like #{pattern}
三。综合查询 嵌套用 AS taskNum,
'1.单位填报情况管理'
SELECT
IFNULL(unit_name,'合计') AS unit_name,
COUNT(*) AS projectNum,
SUM((SELECT COUNT(DISTINCT guid) FROM achi_task_delivery b WHERE task_state>0 AND unit_code=a.unit_code AND YEAR=a.year)) AS taskNum,
SUM((SELECT COUNT(DISTINCT project_guid) FROM achi_target_declare b WHERE state>0 AND unit_code=a.unit_code AND YEAR=a.year)) AS applyNum,
SUM((SELECT COUNT(DISTINCT project_guid) FROM achi_monitor_declare b WHERE state>0 AND unit_code=a.unit_code AND YEAR=a.year)) AS monitorNum,
SUM((SELECT COUNT(DISTINCT project_guid) FROM achi_self_evaluation_declare b WHERE state>0 AND unit_code=a.unit_code AND YEAR=a.year)) AS evaluationNum
FROM achi_project_information a WHERE EXISTS (SELECT 1 FROM sys_dept b WHERE b.ancestors LIKE '%,110%' AND YEAR='2019' AND STATUS=0 AND dept_id=a.unit_code) GROUP BY unit_name WITH ROLLUP
'2.科室审核情况'
SELECT
IFNULL(unit_name,'合计'),
COUNT(*) AS projectNum,
SUM(( SELECT COUNT(*) FROM achi_project_information WHERE unit_code = a.unit_code )) AS applyNum,
SUM(( SELECT COUNT(*) FROM achi_project_information WHERE unit_code = a.unit_code AND states != '0' AND states != NULL )) AS applyReviewNum,
SUM(( SELECT COUNT(*) FROM achi_monitor_declare WHERE unit_code = a.unit_code )) AS monitorReviewNum,
SUM(( SELECT COUNT(*) FROM achi_evaluation_information WHERE project_guid = a.guid ) ) AS evaluaionReviewNum
FROM achi_project_information a WHERE unit_code IN( SELECT dept_id FROM sys_dept ) AND YEAR ='2019' GROUP BY unit_name WITH ROLLUP
'3.项目审核情况'
SELECT
project_name,
COUNT(*) AS projectNum,
( SELECT COUNT(*) FROM achi_project_information WHERE unit_code = a.unit_code ) AS applyNum,
( SELECT COUNT(*) FROM achi_project_information WHERE unit_code = a.unit_code AND states != '0' AND states != NULL ) AS applyReviewNum,
( SELECT COUNT(*) FROM achi_monitor_declare WHERE unit_code = a.unit_code ) AS monitorReviewNum,
( SELECT COUNT(*) FROM achi_evaluation_information WHERE project_guid = a.guid ) AS evaluaionReviewNum
FROM achi_project_information a WHERE unit_code IN( SELECT dept_id FROM sys_dept ) AND YEAR ='2019' GROUP BY unit_name
'4.双监控 35'
SELECT
unit_name,
project_name,
project_nature,
nature_project_funds,
project_budget_amount,
money,
(SELECT MIN(declare_time) FROM achi_target_declare WHERE d.guid = project_guid) AS declare_time,
(SELECT IFNULL(SUM(pay_quarter) / money, 0) FROM achi_annual_fund_arrangement WHERE project_guid = d.guid) AS rate,
(SELECT IFNULL(MAX(QUARTER), 0) FROM achi_annual_fund_arrangement WHERE project_guid = d.guid) AS QUARTER,
(SELECT e.ac_rate FROM(SELECT project_guid, SUM(actual_score) / SUM(target_score) ac_rate FROM achi_monitor_quarter_target WHERE YEAR = '2019'
AND indicator_one LIKE '产出%'
AND monitor_year = '2019'
GROUP BY project_guid) e WHERE project_guid = d.guid) AS ac_rate
FROM
(SELECT guid, unit_name, project_name, project_nature, nature_project_funds, project_budget_amount, IFNULL(money_country_year + money_province_year + money_city_year + money_bank_year + money_self_year + money_other_year, 0) money FROM achi_project_information a) d
'5条'
SELECT unit_name,
project_name,
project_nature,
nature_project_funds,
project_budget_amount,
money,
(SELECT MIN(declare_time) FROM achi_target_declare WHERE d.guid=project_guid) declare_time,
(SELECT IFNULL(SUM(pay_quarter)/money,0) FROM achi_annual_fund_arrangement WHERE project_guid=d.guid) rate,
(SELECT IFNULL(MAX(QUARTER),0) FROM achi_annual_fund_arrangement WHERE project_guid=d.guid) QUARTER,
(SELECT e.ac_rate FROM (SELECT project_guid,SUM(actual_score)/SUM(target_score) ac_rate FROM achi_monitor_quarter_target WHERE YEAR='2019' AND indicator_one LIKE'产出%' AND monitor_year='2019' GROUP BY project_guid) e WHERE project_guid=d.guid) ac_rate FROM
(SELECT guid,unit_name,project_name,project_nature,nature_project_funds,project_budget_amount,IFNULL(money_country_year+money_province_year+money_city_year+money_bank_year+money_self_year+money_other_year,0) money FROM achi_project_information a WHERE a.year='2019' AND EXISTS (SELECT 1 FROM sys_dept b WHERE b.ancestors LIKE '%,110%' AND STATUS=0 AND dept_id=a.unit_code)) d
四。查询当前数据的上一行数据和下一行数据
<!--上一条审核数据-->
<select id="selectPrevisouProcess2" parameterType="Process" resultMap="ProcessResult">
SELECT * FROM achi_process WHERE flow_id = #{flowId} AND process_step = (SELECT MAX(process_step) FROM achi_process WHERE process_step < #{processStep})
</select>
<!--下一条审核数据-->
<select id="selectNextProcess2" parameterType="Process" resultMap="ProcessResult">
SELECT * FROM achi_process WHERE flow_id = #{flowId} AND process_step = (SELECT MIN(process_step) FROM achi_process WHERE process_step > #{processStep})
</select>
五。循环
<select id="selectWholeDepartmentList" parameterType="com.ruoyi.beforehand.domain.WholeDepartment" resultMap="WholeDepartmentResult">
<include refid="selectWholeDepartmentVo"/>
<where>
<if test="moneyAmount != null "> and money_amount = #{moneyAmount}</if>
<if test="departGuid != null and departGuid != '' "> and depart_guid = #{departGuid}</if>
<if test="departName != null and departName != '' "> and depart_name = #{departName}</if>
<if test="deptId != null and deptId != '' "> and dept_id = #{deptId}</if>
<if test="budgetCode != null and budgetCode != '' "> and budget_code = #{budgetCode}</if>
<if test="projectSpending != null and projectSpending != '' "> and project_spending = #{projectSpending}</if>
<if test="basicSpending != null and basicSpending != '' "> and basic_spending = #{basicSpending}</if>
<if test="yearTargetZt != null and yearTargetZt != '' "> and year_target_zt = #{yearTargetZt}</if>
<if test="year != null "> and year = #{year}</if>
<if test="sysRoleList!=null">
<trim prefix="and (" suffix=")" suffixOverrides="or">
<foreach collection="sysRoleList" item="sysRole">
states=#{sysRole.roleSort} or
</foreach>
</trim>
</if>
<if test="evaluationStates != null and evaluationStates != '' "> and evaluationStates = #{evaluationStates}</if>
</where>
</select>
list循环里面再判断对象属性 站点筛选条件
<if test="stationSelectConditionList!=null">
<trim prefix="and (" suffix=")" suffixOverrides="or">
<foreach collection="stationSelectConditionList" item="item">
<if test="item.aupCode == 'BusinessType' ">
a.os_carriy_type = #{item.value}
</if>
</foreach>
</trim>
</if>
<if test="stationSelectConditionList!=null"> <!--筛选条件-->
<foreach collection="stationSelectConditionList" item="item">
<if test="item.aupCode == 'BusinessType' ">
and a.os_carriy_type = #{item.value} <!--筛选条件7——站点属性：直营-->
</if>
<if test="item.aupCode == 'StationType' ">
and e.ospsr_val = #{item.value} <!--筛选条件4——是否对外开发-->
</if>
</foreach>
</if>
六。mybatis传一个参数和多个参数
一个参数
1.接口
2.xml文件 List<String> selectRoleListByUserId(String userId);
<select id="selectRoleListByUserId" parameterType="string" resultType="string">
<if test="_parameter != null and _parameter != '' "> and b.our_user_id = #{userId}</if>
多个参数
1.使用map：可读性差
2.使用注解（推荐） List<User> = selectByEmailAndSex(@Param("email") String email,@Param("sex")Byte sex)
3.使用javaBean（推荐）
七。in查询
参考链接
1.查询对象 用数组
private String userId;
private String[] ids;
2.xml文件
只有一个参数
<if test="_parameter != null and _parameter.size()>0 ">and board_device_id in
<foreach item="item" index="index" collection="collection" open="(" separator="," close=")">
#{item}
</foreach>
</if>
一个对象的属性
<where>
<if test="userId != null and userId != '' "> and b.oou_id = #{userId}</if>
<if test="ids != null and ids != '' ">
and a.os_id in
<foreach item="item" index="index" collection="ids" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</where>
z
3.postman的传参
不用加分号
八、嵌套查询——一对多 多对一查询
collection 班级对学生
VO类的成员变量名称students students的类型
<collection property="students"
ofType="com.glj.pojo.Student"
column="id"
javaType="ArrayList"
fetchType="lazy"
select="com.glj.mapper.StudentMapper.selectStudentByClazzId">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
</collection>
association 学生对班级
VO类的成员变量名称clazz clazz的类型
<association property="clazz" javaType="com.glj.pojo.Clazz">
<id property="id" column="id"/>
<result property="code" column="code"/>
<result property="name" column="name"/>
</association>
九. resultMap
<resultMap id="BaseResultMap" type="com.nebula.microservice.operationmanage.operation.payment.domain.RefundRecord">
<id column="orr_id" jdbcType="CHAR" property="orrId" />
<result column="orr_order_code" jdbcType="VARCHAR" property="orrOrderCode" />
<result column="orr_refund_amount" jdbcType="INTEGER" property="orrRefundAmount" />
<result column="orr_method" jdbcType="INTEGER" property="orrMethod" />
<result column="orr_cause" jdbcType="VARCHAR" property="orrCause" />
<result column="orr_refund_time" jdbcType="TIMESTAMP" property="orrRefundTime" />
<result column="orr_approval_results" jdbcType="INTEGER" property="orrApprovalResults" />
<result column="orr_approval_comments" jdbcType="VARCHAR" property="orrApprovalComments" />
<result column="orr_approver_id" jdbcType="CHAR" property="orrApproverId" />
<result column="orr_approver_name" jdbcType="VARCHAR" property="orrApproverName" />
<result column="orr_approval_time" jdbcType="TIMESTAMP" property="orrApprovalTime" />
</resultMap>
十. 动态排序
ORDER BY
<choose>
<when test="isRemainFive == true">
isCollect asc,myDistance asc
</when>
<otherwise>
isCollect asc,cityDistance asc
</otherwise>
</choose>
十一. 函数
空处理
ifnull(b.ocod_vin_code,'') as vin, 如果为空，取后面的值；如果不为空，取前面的值
判断数据库字段是否包含字符串
e.ospsr_val like %#{item.value}%
判断数据库字段是否被字符串包含
find_in_set(e.ospsr_val,#{item.value}) 字符串需要用,号隔开
locate(e.ospsr_val,'0;1;2') > 0 字符串没要求
AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。
更多推荐
2
0- 0
已为社区贡献4条内容
所有评论(0)