1.if标签
1 <select id="queryByNameAndTelephone" parameterType="Customer" 2 resultType="Customer"> 3 SELECT * FROM t_customer 4 WHERE 1=1 5 <if test="name!=null and name!=''"> 6 AND NAME LIKE #{name} 7 </if> 8 <if test="telephone!=null and telephone!=''"> 9 AND telephone LIKE #{telephone} 10 </if> 11 </select>
1 public List<Customer> queryByNameAndTelephone(Customer customer);
1 /** 2 * if标签使用 3 */ 4 @Test 5 public void test1() { 6 SqlSession sqlSession = SessionUtils.getSession(); 7 // getMapper(): 返回指定接口的动态代理的实现类对象 8 CustomerDao dao = sqlSession.getMapper(CustomerDao.class); 9 Customer c = new Customer(); 10 //c.setName("%陈%"); 11 c.setTelephone("%55%"); 12 List<Customer> list = dao.queryByNameAndTelephone(c); 13 for (Customer customer : list) { 14 System.out.println(customer); 15 } 16 sqlSession.commit(); 17 sqlSession.close(); 18 }
2.where标签:自动把第一个条件的and去掉
1 <select id="queryByNameAndTelephone" parameterType="Customer" 2 resultType="Customer"> 3 SELECT * FROM t_customer 4 <!-- <where>:where条件,自动把第一个条件的and去掉 --> 5 <where> 6 <if test="name!=null and name!=''"> 7 AND NAME LIKE #{name} 8 </if> 9 <if test="telephone!=null and telephone!=''"> 10 AND telephone LIKE #{telephone} 11 </if> 12 </where> 13 </select>
3.sql片段
作用:把相同的sql片段抽取出来
1 <!-- sql片段 --> 2 <sql id="customerField"> 3 id,name,gender,telephone 4 </sql>
1 <select id="queryByNameAndTelephone" parameterType="Customer" 2 resultType="Customer"> 3 SELECT 4 <include refid="customerField"/> 5 FROM t_customer 6 <!-- <where>:where条件,自动把第一个条件的and去掉 --> 7 <where> 8 <if test="name!=null and name!=''"> 9 AND NAME LIKE #{name} 10 </if> 11 <if test="telephone!=null and telephone!=''"> 12 AND telephone LIKE #{telephone} 13 </if> 14 </where> 15 </select>
4.foreach标签
1 <delete id="deleteCustomerByIn" parameterType="Customer"> 2 DELETE FROM t_customer WHERE 3 <!-- 4 collection: 需要遍历的属性 5 item: 遍历的变量 6 open: 循环前面的 sql语句 7 close: 循环后面的 sql语句 8 separator:分隔符 9 10 id IN(1,2,3) 11 --> 12 <!-- id为ids数组中的每个元素 --> 13 <foreach collection="ids" item="id" open="id IN (" close=")" separator=","> 14 #{id} 15 </foreach> 16 </delete>
1 public void deleteCustomerByIn(Customer customer);
1 /** 2 * foreach标签 3 */ 4 @Test 5 public void test2() { 6 SqlSession sqlSession = SessionUtils.getSession(); 7 // getMapper(): 返回指定接口的动态代理的实现类对象 8 CustomerDao dao = sqlSession.getMapper(CustomerDao.class); 9 Customer c = new Customer(); 10 Integer[] ids = {23,24,25}; 11 c.setIds(ids); 12 dao.deleteCustomerByIn(c); 13 sqlSession.commit(); 14 sqlSession.close(); 15 }
Customer.java:
1 package cn.sm1234.domain; 2 3 import java.util.Arrays; 4 5 public class Customer { 6 7 private Integer id; 8 private String name; 9 private String gender; 10 private String telephone; 11 12 private Integer ids[]; 13 14 public Integer getId() { 15 return id; 16 } 17 public void setId(Integer id) { 18 this.id = id; 19 } 20 public String getName() { 21 return name; 22 } 23 public void setName(String name) { 24 this.name = name; 25 } 26 public String getGender() { 27 return gender; 28 } 29 public void setGender(String gender) { 30 this.gender = gender; 31 } 32 public String getTelephone() { 33 return telephone; 34 } 35 public void setTelephone(String telephone) { 36 this.telephone = telephone; 37 } 38 public Integer[] getIds() { 39 return ids; 40 } 41 public void setIds(Integer[] ids) { 42 this.ids = ids; 43 } 44 @Override 45 public String toString() { 46 return "Customer [id=" + id + ", name=" + name + ", gender=" + gender + ", telephone=" + telephone + ", ids=" 47 + Arrays.toString(ids) + "]"; 48 } 49 50 51 }