mybatis 之动态sql 增删改查

====================== mybatis的动态SQL ============

---------------------------------------------------------------------------------------------------------
一、什么是动态SQL呢？
就是where后面的条件不是确定的根据用户的选择动态产生的sql就是动态sql。

1.1 动态sql之查询 select

<select id="selectlike" parameterType="map" resultType="app_dynamicSql.User">
select * from t_user
<where>
<if test="pid!=null">
and id = #{pid}
</if>
<if test="pname!=null">
userName like #{pname}
</if>
</where>
</select>
---------------------------------------------------------------------------------------------------------
1.2动态sql之更新 update

update t_user set name = ? password = ?;

<update id="" parameterType="Map">
update t_user
<set>
<if test="pid!=null">
id = #{pid},
</test>
<if test="pname!=null">
name = #{name},
</test>
</set>
where id = #{pid}
</update>
---------------------------------------------------------------------------------------------------------
1.3 动态sql之删除 delete 批量删除

foreach 属性详解
collection 表示参数类型比如是数组或者集合
open 表示以神符号开始
close 表示以什么符号结束
separator 表示分隔符
item 表示要遍历的名称

<delete id="dynaDeleteList">
delete from t_user where id in
<foreach collection="list" open="(" close=")" separator="," item="ids" >
#{ids}
</foreach>
</delete>


<delete id="dynaDeleteArray">
delete from t_user where id in
<foreach collection="参数类型" open="（" close="）" separator="," item="最好是形参名字">
#{最好是形参的名字}
</foreach>
</delete>

---------------------------------------------------------------------------------------------------------

1.4 动态sql之插入 inert
<sql id="key">
<trim suffixOverrides=",">
<if test="id!=null">
id,
</if>
...
</trim>
</sql>
<sql id="value">
<trim suffixOverrides=",">
<if test="id!=null">
#{id},
</if>
...
</trim>
</sql>

<insert id="dynaInsert" parameterType="参数类型">
insert into t_user(<include refid="key"></include>) values(<include refid="value"></include>)
</insert>
---------------------------------------------------------------------------------------------------------

XML配置文件

  1 <?xml version="1.0" encoding="UTF-8" ?>
  2 <!DOCTYPE mapper
  3   PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4   "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5   
  6   <mapper namespace="dyanmicUser">
  7     
  8     <resultMap type="app_dynamicSql.User" id="dynamic_userMap">
  9         <id property="id" column="id"/>
 10         
 11         <result property="userName" column="userName" />
 12         <result property="password" column="password" />
 13     </resultMap>
 14     
 15     <!-- 动态查询Sql -->
 16     <select id="selectlike" parameterType="map" resultType="app_dynamicSql.User"> 
 17             select * from t_user 
 18             <where>
 19                 <if test="pid!=null">
 20                     and id = #{pid}
 21                 </if>
 22                 <if test="pname!=null">
 23                     userName like #{pname}
 24                 </if>
 25             </where>
 26     </select>
 27     
 28     <!-- 动态sql之更新-->
 29     <update id="dynaUpdate" parameterType="Map">
 30         update t_user
 31             <set>
 32                 <if test="pname!=null">
 33                     userName = #{pname},
 34                 </if>
 35                 <if test="ppassword!=null">
 36                     password = #{ppassword},
 37                 </if>
 38             </set>
 39         where id = #{pid}
 40     </update>
 41 
 42     <!-- 动态删除之数组版本 -->
 43     <delete id="dynaDeleteArray">
 44         delete from t_user where id in
 45             <!-- 用于迭代数组元素的标签
 46                 collection表示类型
 47                 open表示开始的符号
 48                 close表示结束符号
 49                 separator表示元素之间的分隔符
 50                 item表示要迭代的数组 属性值可以随便写但是建议与形参名相同
 51                 #{ids} 表示数组中的元素
 52              -->
 53             <foreach collection="array" open="(" close=")" separator="," item="ids" >
 54                 #{ids}
 55             </foreach>
 56     </delete>
 57 
 58     <!-- 动态删除之集合版本 -->
 59     <delete id="dynaDeleteList">
 60         delete from t_user where id in
 61             <foreach collection="list" open="(" close=")" separator="," item="ids" >
 62                 #{ids}
 63             </foreach>
 64     </delete>
 65     
 66     
 67     
 68     <!-- 动态sql之插入 -->
 69     
 70     <!-- sql片段对应的是字段名称 -->
 71     <sql id="key">
 72         <trim suffixOverrides=",">
 73             <if test="id!=null">
 74                 id,
 75             </if>
 76             <if test="userName!=null">
 77                 userName,
 78             </if>
 79             <if test="password">
 80                 password,
 81             </if>
 82         </trim>
 83     </sql>
 84     <!-- sql片段对应的是值 -->
 85     <sql id="value">
 86     <!-- 去掉最后一个逗号 -->
 87         <trim suffixOverrides=",">
 88             <if test="id!=null">
 89                 #{id},
 90             </if>
 91             <if test="userName!=null">
 92                 #{userName},
 93             </if>
 94             <if test="password">
 95                 #{password},
 96             </if>
 97         </trim>
 98     </sql>
 99     
100     
101     <insert id="dynaInsert" parameterType="app_dynamicSql.User">
102         <!-- 引用上面定义的sql片段 -->
103         insert into t_user(<include refid="key"></include>) values(<include refid="value"></include>)
104         
105     </insert>
106 
107   </mapper>
108

UserDao和测试代码实例

  1 package app_dynamicSql;
  2 
  3 import java.util.ArrayList;
  4 import java.util.LinkedHashMap;
  5 import java.util.List;
  6 import java.util.Map;
  7 
  8 import org.apache.ibatis.session.SqlSession;
  9 import org.junit.Test;
 10 
 11 import utils.mybatisUtils;
 12 
 13 public class UserDao {
 14     
 15     /**
 16      * 条件查询所有用户信息
 17      * @param start
 18      * @param size
 19      * @return
 20      * @throws Exception
 21      */
 22     public List<User> findByPage(Integer id,String userName) throws Exception{
 23         
 24         SqlSession sqlSession = null;
 25         List<User> list = new ArrayList<User>();
 26         
 27         try {
 28             
 29             sqlSession = mybatisUtils.getSqlSession();
 30             
 31             Map<String,Object> map = new LinkedHashMap<String,Object>();
 32             map.put("pid", id);
 33             map.put("pname", "%"+userName+"%");
 34             
 35             list = sqlSession.selectList("dyanmicUser.selectlike",map);
 36             
 37         } catch (Exception e) {
 38             e.printStackTrace();
 39         }finally{
 40             //关闭session 断开连接
 41             mybatisUtils.closerSqlSession();
 42         }
 43         return list;
 44     }
 45 
 46     /**
 47      * 条件更新用户信息
 48      * @param userName
 49      * @param password
 50      * @throws Exception
 51      */
 52     public void dynaUpdate(Integer id,String userName,String password) throws Exception{
 53             SqlSession sqlSession = null;
 54             try {
 55                 
 56                 sqlSession = mybatisUtils.getSqlSession();
 57                 
 58                 Map<String,Object> map = new LinkedHashMap<String,Object>();
 59                 map.put("pid", id);
 60                 map.put("pname", userName);
 61                 map.put("ppassword", password);
 62                 
 63                 int count = sqlSession.update("dyanmicUser.dynaUpdate",map);
 64                 
 65                 System.out.println("===========影响"+count+"行========");
 66                 
 67                 //提交事务
 68                 sqlSession.commit();
 69             } catch (Exception e) {
 70                 e.printStackTrace();
 71                 //事务回滚
 72                 sqlSession.rollback();
 73             }finally{
 74                 //关闭session 断开连接
 75                 mybatisUtils.closerSqlSession();
 76             }
 77         }
 78 
 79     
 80 
 81     /**
 82      * 根据ID批量删除  （数组的方式） 
 83      * @param ids
 84      * @throws Exception
 85      */
 86     public void dynaDelete(int... ids) throws Exception{
 87             SqlSession sqlSession = null;
 88             try {
 89                 
 90                 sqlSession = mybatisUtils.getSqlSession();
 91                 
 92                 int count = sqlSession.delete("dyanmicUser.dynaDeleteArray", ids);
 93                 
 94                 System.out.println("===========影响"+count+"行========");
 95                 
 96                 //提交事务
 97                 sqlSession.commit();
 98             } catch (Exception e) {
 99                 e.printStackTrace();
100                 //事务回滚
101                 sqlSession.rollback();
102             }finally{
103                 //关闭session 断开连接
104                 mybatisUtils.closerSqlSession();
105             }
106         }
107     
108     /**
109      * 根据ID批量删除  （集合的方式） 
110      * @param ids
111      * @throws Exception
112      */
113     public void dynaDeleteList(List<Integer> ids) throws Exception{
114             SqlSession sqlSession = null;
115             try {
116                 
117                 sqlSession = mybatisUtils.getSqlSession();
118                 
119                 int count = sqlSession.delete("dyanmicUser.dynaDeleteList", ids);
120                 
121                 System.out.println("===========影响"+count+"行========");
122                 
123                 //提交事务
124                 sqlSession.commit();
125             } catch (Exception e) {
126                 e.printStackTrace();
127                 //事务回滚
128                 sqlSession.rollback();
129             }finally{
130                 //关闭session 断开连接
131                 mybatisUtils.closerSqlSession();
132             }
133         }
134     
135     /**
136      * 动态sql之插入
137      * @param user
138      * @throws Exception
139      */
140     public void dynaInsert(User user) throws Exception{
141         SqlSession sqlSession = null;
142         try {
143             
144             sqlSession = mybatisUtils.getSqlSession();
145             int count = sqlSession.insert("dyanmicUser.dynaInsert", user);
146             
147             System.out.println("===========影响"+count+"行========");
148             
149             //提交事务
150             sqlSession.commit();
151         } catch (Exception e) {
152             e.printStackTrace();
153             //事务回滚
154             sqlSession.rollback();
155         }finally{
156             //关闭session 断开连接
157             mybatisUtils.closerSqlSession();
158         }
159     }
160     
161     
162     
163     
164     @Test
165     public void testFindByPage() throws Exception{
166         UserDao userDao = new UserDao();
167         
168         List<User> list = userDao.findByPage(null,"测试");
169         System.out.println("条件差询结果：");
170         for(User user:list){
171             System.out.println(user);
172         }
173         
174     }
175     
176     @Test
177     public void testDynaUpdate() throws Exception{
178         UserDao userDao = new UserDao();
179 //        userDao.dynaUpdate(1,"测试222","ceshi222");
180         userDao.dynaUpdate(11,null,"222");
181         
182     }
183     
184     @Test
185     public void testDynaDelete() throws Exception{
186         UserDao userdao = new UserDao();
187         userdao.dynaDelete(1,3,5,7,77);
188     }
189     
190     @Test
191     public void testDynaDeleteList() throws Exception{
192         UserDao userdao = new UserDao();
193         
194         List<Integer> list = new ArrayList<Integer>();
195         
196         list.add(2);
197         list.add(4);
198         list.add(6);
199         
200         userdao.dynaDeleteList(list);
201     
202     }
203     
204     @Test
205     public void testDynaInsert() throws Exception{
206         UserDao userdao = new UserDao();
207         userdao.dynaInsert(new User(11,null,null));
208     }
209     
210 }