1.新增完了,再次点击新增如何让其调用修改方法而不是再新增一条数据
<selectKey resultType="java.lang.String" keyPerperty="param.id">
select last_insert_id()as ID FROM DUAL
<selectKey/>
2.sql语句中进行日期的比较
(select DATE_ADD(date(sysdate()),INTERVAL 30 DAY)<=date(rectifi_deadline)) as over30days
按照系统时间往后延期30天,和完成期限进行比较 如果成立则值为1 如果不成立值为0
3.某个单位只能查其子单位的信息
企事业单位只能查本单位
<if test="param.authority==1">
and r.create_unit=#{param.loginUnit,jdbcType=VARCHAR}
</if>
监管局能查其下属单位
<if test="param.authority==2">
and r.create_unit IN (
SELECT ID
FROM e_unit u
WHERE u.parent_unit=#{param.loginUnit,jdbcType=VARCHAR}
AND status = 1
)
and r.status in (1,2,3)
</if>
管理局能查询下属监管局的下属单位
<if test="param.authority==2">
and r.create_unit IN (
SELECT ID
FROM e_unit u
WHERE u.parent_unit IN
(select id from e_unit
where parent_unit = # {param.loginUnit,jdbcType=VARCHAR}
AND status = 1 )
<if test ="param.industryDector ! = null and param.industrySerctor ! =''">
AND SECTOR =#{param.industrySector}
AND status=1
)
and r.status in (1,2,3)
</if>
再补充一个按照特殊规定排序的问题
<if test =‘’param.orderField != null and param.orderField != ''“>
order by ${param.orderField} ${param.orderDirection}
</if>
<if test =‘’param.orderField == null and param.orderField == ''“>
order by o.code_order desc,r.create_date desc ,r.id desc
</if>
4.数据库中和日期相关的函数
select查询语句中 查出的结果按照指定的格式 date_format(字段名,‘%Y-%m-%d’);
insert添加语句中 string类型的值转化为时间格式 str_to_date(#{传入的值},‘%Y-%m-%d’);
返回日期 date( '符合日期格式的数据')
5.mybatis中 关于foreach的使用
select * from table where 1=1
<if test ="role != null">
<foreach close =") " collection="role" index="index" item="item" opean ="AND r.ROLE_NAME IN (" separator =",")>
#{item}
</foreach>
</if>
<!--批量新增-->
<insert id="addGutyUnitBatch" paramterType="com.easp.vo.event.DutyUnitVo">
insert into E_DUTY_UNIT (
...
...
)
select
t.duty,
t.type,
#{map.eventId},
#{map.createBy},
#{map.createBy},
sysdate()
from (
<foreach collection ="map.list" item="item" separator ="UNION ALL" >
SELECT
#{item.dutyDept} dutyDept,
#{item.type} type
FROM DUAL
</foreach>
)t
</inset>