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}