MyBatis动态SQL标签

 

1.if标签

在if标签内写上相应的条件就可以拼接SQL语句

2.where标签

where标签自动生成和删除where   还能删除where后的第一个and 

 <select id="getCustomer" resultType="com.itlike.domain.Customer">
        select * from `customer`
        <!--where标签自动生成和删除where    还能删除where后第一个and-->
        <where>
            <if test="name != null and name != ''">
                `cust_name`=#{name}
            </if>
            <if test="profession != null and profession !=''">
                and `cust_profession`=#{profession}
            </if>
        </where>
    </select>

测试类：

List<Customer> customer = mapper.getCustomer2("李白", "刺客");

发送到数据库的SQL语句：

==>  Preparing: select * from `customer` WHERE `cust_name`=? and `cust_profession`=? 
==> Parameters: 李白(String), 刺客(String)

List<Customer> customer = mapper.getCustomer2(null, "刺客");

 ==>  Preparing: select * from `customer` WHERE `cust_profession`=? 
==> Parameters: 刺客(String)

如果将and的位置改变，放在第一个条件之后，那么where不会去除and  

 <select id="getCustomer2" resultType="com.itlike.domain.Customer">
        select * from `customer`
        <!--where标签自动生成和删除where    还能删除where后第一个and-->
        <where>
            <if test="name != null and name != ''">
                `cust_name`=#{name} and
            </if>
            <if test="profession != null and profession !=''">
                   `cust_profession`=#{profession}
            </if>
        </where>
    </select>

测试类：

List<Customer> customer = mapper.getCustomer2("李白", null);

SQL: select * from `customer`                   WHERE `cust_name`=? and        报错

因此一般在使用where标签时，都将and放在条件的前面，而不会放在条件的后面 

3.trim标签 

prefix： 设置前缀，在第一个条件之前加一个前缀
prefixOverrides：条件前覆盖  把第一个条件之前的某个字段变成空
suffix：设置后缀，在最后一个条件之后加一个后缀
suffixOverrides：条件后覆盖  把最后一个条件之后的某个字段变成空

 <select id="getCustomer" resultType="com.itlike.domain.Customer">
        select * from `customer`
       <trim prefix="where" prefixOverrides="and" suffix="abc" suffixOverrides="and">
            <if test="name != null and name != ''">
                and `cust_name`=#{name}
            </if>
            <if test="profession != null and profession !=''">
                and `cust_profession`=#{profession} and
            </if>
       </trim>
    </select>

测试类：

 List<Customer> customer = mapper.getCustomer("李白", "刺客");

输出的SQL：

select * from `customer`     where  `cust_name`=?     and `cust_profession`=?  abc

4.choose标签

choose     只有第一个条件符合了就会执行     后面的条件即使符合了也不会执行
when
otherwise

 <select id="getCustomer" resultType="com.itlike.domain.Customer">
        select * from `customer`
       <where>
           <choose>
               <when test="profession != null and profession !=''">
                   `cust_profession`=#{profession}
               </when>
               <when test="name != null and name != ''">
                   `cust_name`=#{name}
               </when>
               <otherwise>
                   1=1
               </otherwise>
           </choose>

       </where>
    </select>

测试类：

 public void test1(){
        SqlSession sqlSession = MyBatisUtils.openSession();
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        List<Customer> customer = mapper.getCustomer("李白", "刺客")
    }

输出SQL：==>  Preparing: select * from `customer` WHERE `cust_profession`=? 
                  ==> Parameters: 刺客(String)

 public void test1(){
        SqlSession sqlSession = MyBatisUtils.openSession();
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        List<Customer> customer = mapper.getCustomer("李白", null);
    }

输出SQL：==>  Preparing: select * from `customer` WHERE `cust_name`=? 
                  ==> Parameters: 李白(String)

 public void test1(){
        SqlSession sqlSession = MyBatisUtils.openSession();
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        List<Customer> customer = mapper.getCustomer(null, null);
    }

输出SQL：==>  Preparing: select * from `customer` WHERE 1=1 

5.set标签

set标签   会添加update中的set   并把最后一个条件后的 ， 去掉          主要用在更新操作

 <update id="updateCustomer">
        update `customer`
        <set>
        <if test="cust_name != null and cust_name != ''">
            cust_name=#{cust_name},
        </if>
        <if test="cust_profession != null and cust_profession != ''">
            cust_profession=#{cust_profession},
        </if>
        </set>
        where cust_id=#{cust_id}
    </update>

测试类：

public void test2(){
        SqlSession sqlSession = MyBatisUtils.openSession();
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        Customer customer = new Customer();
        customer.setCust_id(2);
        customer.setCust_name("李白白");
        customer.setCust_profession("肉");
        mapper.updateCustomer(customer);
        sqlSession.commit();
        sqlSession.close();
    }

SQL语句：==>  Preparing: update `customer` SET cust_name=?, cust_profession=? where cust_id=? 
                  ==> Parameters: 李白白(String), 肉(String), 2(Integer)

6.foreach标签

 <select id="getCustomers"  parameterType="Integer[]" resultType="com.itlike.domain.Customer">
        select * from `customer` where cust_id in
        <foreach collection="array"   open="("   close=")"   separator="," item="ids">
            #{ids}
        </foreach>
    </select>

测试类：

public void test3(){
        SqlSession sqlSession = MyBatisUtils.openSession();
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        List<Customer> customers = mapper.getCustomers(new Integer[]{2,3,4,5});
    }

输出的SQL：

==>  Preparing: select * from `customer` where cust_id in ( ? , ? , ? , ? ) 
==> Parameters: 2(Integer), 3(Integer), 4(Integer), 5(Integer）

list同array用法相同

7.bind标签

bind标签：可以取出传入的值   重新处理 赋值给另外一个值

接口中定义的方法：

public Customer getCustomerWithId(@Param("id") Integer id);

这里虽然只传递一个参数，但是还是得添加param注解，为了bind中能够接收到 

bind标签

 <select id="getCustomerWithId"  resultType="Customer">
      <bind name="newId" value="id+1"/>
        select * from `customer` where cust_id=#{newId}
    </select>

测试类：

 public void test4(){
        SqlSession sqlSession = MyBatisUtils.openSession();
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        Customer customerWithId = mapper.getCustomerWithId(2);
        sqlSession.close();
    }

输出SQL：

==>  Preparing: select * from `customer` where cust_id=? 
==> Parameters: 3(Integer)