mybatis实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<!-- 批量插入 遇到重复key 改为更新 需求设定-->
<insert id="addBatchDepartment" parameterType="java.util.List" >
INSERT INTO department (
branchId,
branchName,
common,
spellId)
VALUES
<foreach collection="list" item="item" index="index" separator="," >
(
#{item.branchId},
#{item.branchName},
#{item.common},
#{item.spellId}
)
</foreach>
ON DUPLICATE KEY UPDATE
branchId = values(branchId)
</insert>

{———-}

1
2
3
4
5
6
7
8
9
10
<insert id="addStudent" parameterType="java.util.Map">
insert into student
(id,name,age)
values
(
#{item.id},
#{item.name},
#{item.age}
)
</insert>

批量

1
2
3
4
5
6
7
8
9
10
11
12
13
<insert id="addStudent" parameterType="java.util.Map">
insert into student
(id,name,age,start_time)
values
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.id},
#{item.name},
#{item.age},
now()
)
</foreach>
</insert>

1
这里写代码片

批量


1
2
3
4
5
6
7
8
9
10
11
12
<update id="updateStudent" parameterType="java.util.Map">
update student
<set>
<if test="userid != null and userid != '' ">
userid=#{userid},
</if>
</set>
where 1=1
<if test="id!= null and id!= '' ">
and id=#{id}
</if>
</update>

批量

1
2
3
4
5
6
7
8
<update id="updateStudent" parameterType="java.util.Map">
update student set state=2 where 1=1
and id in
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>

</update>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<update id="editStudent"  parameterType="java.util.List">  
<foreach collection="list" item="item" index="index" separator=";">
update ware_service
<set>
<if test="item.endTime!=null and item.endTime!=''">
end_time=#{item.endTime},
</if>
<if test="item.redeemcode!=null and item.redeemcode!=''">
redeemcode=#{item.redeemcode}
</if>
</set>
where id=#{item.id}
</foreach>
</update>

批量修改需设置db.properties
新增 &allowMultiQueries=true


1
2
3
4
5
6
7
8
9
10
11
12
 <select id="getAllStudent" resultType=com.mofangge.entity.student" parameterType="java.util.Map">
select
id,
name,
age,
start_time as startTime
from student where 1=1
<if test="userid != null and userid != '' ">
and userid=#{userid}
</if>
and end_time > now()
</select>

批量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<select id="getAllStudents" resultType="com.mofangge.entity.student" parameterType="java.util.Map">
select
id,
name,
age,
start_time as startTime
from student where 1=1 and age in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
and end_time > now()
<if test="userid!=null and userid!=''">
and userid=#{userid}
</if>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="getAllStudent" resultType="com.mofangge.entity.Student" parameterType="java.util.Map">
select
id,
name,
age,
from student where 1=1 and code in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
and end_time > now()
<if test="id!=null and id!=''">
and id=#{id}
</if>
</select>

mybatis大于小于写法

1
2
3
4
5
6
7
8
9
10
11
12
原符号       <        <=      >       >=       &        '        "
替换符号 &lt; &lt;= &gt; &gt;= &amp; &apos; &quot;
例如:sql如下:
create_date_time &gt;= #{startTime} and create_date_time &lt;= #{endTime}

第二种写法(2):
大于等于
<![CDATA[ >= ]]>
小于等于
<![CDATA[ <= ]]>
例如:sql如下:
create_date_time <![CDATA[ >= ]]> #{startTime} and create_date_time <![CDATA[ <= ]]> #{endTime}