Mybatis动态sql

<where>假如第一个条件为空，会自动处理第一个and

 1 <!-- =====================根据条件查询================== -->
 2      <select id="selectPersonByCondition" parameterType="qc" resultMap="BaseResultMapper">
 3          select * from person_test t
 4          <where>
 5              <if test="name != null">
 6                  t.name like '%${name}%'
 7              </if>
 8              <if test="gender != null">
 9                  and t.gender = #{gender}
10              </if>
11              <if test="birthday != null">
12                  and t.birthday = #{birthday}
13              </if>
14              <if test="address != null">
15                  and t.address like '%${address}%'
16              </if>
17          </where>
18      </select>

<set>会自动解决最后一个,

 1 <update id="updatePerson" parameterType="cn.tx.model.Person">
 2          update person_test p
 3          <set>
 4              <if test="name != null">
 5                  p.name = #{name},
 6              </if>
 7              <if test="gender != null">
 8                  p.gender = #{gender},
 9              </if>
10              <if test="birthday != null">
11                  p.birthday = #{birthday},
12              </if>
13              <if test="address != null">
14                  p.address = #{address},
15              </if>
16          </set>
17          <where>
18              p.id = ${id}
19          </where>
20      </update>

foreach

 1  <!-- map.put("pids",Integer[] ids)
 2          collection:需要遍历的集合
 3          open:开始符号
 4          close:结束符号
 5          item:每一项
 6          separator:分隔符
 7          index:索引
 8       -->
 9      <select id="selectPersonByIn" parameterType="map" resultMap="BaseResultMapper">
10          select * from person_test t where t.id in 
11          <foreach collection="pids" open="(" close=")" item="id" separator="," index="i">
12              #{id}
13          </foreach>
14      </select>

批量插入与批量删除

 1 <insert id="insertBatch" parameterType="map">
 2         
 3         <selectKey keyProperty="personId" order="AFTER" resultType="int">
 4             select LAST_INSERT_ID()
 5         </selectKey>
 6         insert into person (person_id, name, gender, person_addr, birthday)
 7         values
 8         <foreach collection="personList" separator="," item="person">
 9             (#{person.personId}, #{person.name}, #{person.gender}, #{person.personAddr}, #{person.birthday})
10         </foreach>
11     </insert>
 1 @Test
 2     public void insertBatch(){
 3         SqlSession session = sessionFactory.openSession();
 4         List<Person> pList = new ArrayList<Person>();
 5         Map<String,Object> map = new HashMap<String,Object>();
 6         
 7         try {
 8             for(int i = 0; i < 1000008; i++){
 9                 Person p = new Person();
10                 p.setName("黄盖"+i);
11                 p.setGender(1);
12                 p.setPersonAddr("北京"+i);
13                 p.setBirthday(new Date());
14                 pList.add(p);
15                 if(i%100 == 0){//防止内存溢出
16                     map.put("personList", pList);
17                     session.insert("com.rl.mapper.PersonMapper.insertBatch", map);
18                     pList.clear();
19                 }
20             }
21             map.put("personList", pList);
22             session.insert("com.rl.mapper.PersonMapper.insertBatch", map);
23         
24             
25             //数据库的变更都要提交事务
26             session.commit();
27             
28         } catch (Exception e) {
29             e.printStackTrace();
30             session.rollback();
31         }finally{
32             session.close();
33         }
34     }

 

1 <delete id="delete" parameterType="map">
2         delete from user where user_id in 
3         <foreach collection="ids" open="(" close=")" item="userId" separator=",">
4             #{userId}
5         </foreach>
6     </delete>
 1 @Test
 2     public void deleteBatch(){
 3         SqlSession session = sessionFactory.openSession();
 4         List<Integer> idList = new ArrayList<Integer>();
 5         Map<String,Object> map = new HashMap<String,Object>();
 6         
 7         try {
 8             for(int i = 106; i <= 1000113; i++){
 9                 idList.add(i);
10                 if(i%100 == 0){
11                     map.put("ids", idList);
12                     session.delete("com.rl.mapper.PersonMapper.deleteBatch", map);
13                     idList.clear();
14                 }
15             }
16             map.put("ids", idList);
17             session.delete("com.rl.mapper.PersonMapper.deleteBatch", map);
18             //数据库的变更都要提交事务
19             session.commit();
20             
21         } catch (Exception e) {
22             e.printStackTrace();
23             session.rollback();
24         }finally{
25             session.close();
26         }
27     }

 

posted @ 2018-04-25 23:31  cat_fish  阅读(261)  评论(0)  编辑  收藏  举报