【Mybatis学习笔记】2.动态SQL

SNAPSHOT

AddressesMapper.java（interface）

package com.cyk.mapper;

import com.cyk.pojo.Addresses;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface AddressesMapper {

    //where和if标签
    List<Addresses> queryByCountryCity(@Param("country") String country, @Param("city") String city);

    //set标签
    int update(Addresses addresses);

    //choose标签
    List<Addresses> query(Addresses addresses);

    //trim标签
    List<Addresses> queryTrim(Addresses addresses);

    //foreach标签
    List<Addresses> queryByIds(List<Integer> list);

    //模糊查询
    List<Addresses> queryLike(@Param("city") String city);

    //sql标签
    List<Addresses> ListAll();
}

AddressesMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.cyk.mapper.AddressesMapper">

    <!--//where和if标签的使用-->
    <!--where可以消除多余的and，所以sql语句统一加上and-->
    <select id="queryByCountryCity" resultType="com.cyk.pojo.Addresses">
        select * from mybatis3.addresses
        <where>
            <if test="country != null and country != ''">
                and country = #{country}
            </if>
            <if test="city != null">
                and city = #{city}
            </if>
        </where>
    </select>

    <!--//set标签-->
    <update id="update">
      update addresses
      <set>
        <if test="country != null and country != ''">
            country = #{country},
        </if>
        <if test="city != null and city != ''">
            city = #{city},
        </if>
        <if test="street != null and street != ''">
            street = #{street},
        </if>
        <if test="state != null and state != ''">
            state = #{state},
        </if>
        <if test="zip != null and zip != ''">
            zip = #{zip}
        </if>
      </set>

      <where>
          ADDR_ID = #{addrId}
      </where>
    </update>

    <!--//choose标签-->
    <select id="query" resultType="com.cyk.pojo.Addresses">
        select * from mybatis3.addresses
        <where>
            <choose>
                <when test="country != null">
                    and country = #{country}
                </when>
                <when test="state != null">
                    and state = #{state}
                </when>
                <otherwise>
                    and city = #{city}
                </otherwise>
            </choose>
        </where>
    </select>

    <select id="queryTrim" resultType="com.cyk.pojo.Addresses">
        select * from mybatis3.addresses
        <trim prefix="where" suffixOverrides="and">
            <if test="country != null and country != ''">
                country = #{country} and
            </if>
            <if test="city != null and city != ''">
                city = #{city} and
            </if>
            <if test="street != null and street != ''">
                street = #{street} and
            </if>
            <if test="state != null and state != ''">
                state = #{state} and
            </if>
            <if test="zip != null and zip != ''">
                zip = #{zip} and
            </if>
        </trim>
    </select>

    <!--foreach标签-->
    <select id="queryByIds" resultType="com.cyk.pojo.Addresses">
      select * from mybatis3.addresses
      <where>
          addr_id in
          <foreach collection="list" open="(" close=")" item="i" separator=",">
              #{i}
          </foreach>
      </where>
    </select>

    <select id="queryLike" resultType="com.cyk.pojo.Addresses">
       <!--方案一-->
        <!--select * from mybatis3.addresses
        where city like #{city};-->
        <!--方案二-->
        <!--select * from mybatis3.addresses
        where city like concat('%',#{city},'%');-->
       <bind name="_city" value="'%'+city+'%'"/>
         select * from mybatis3.addresses
         where city like #{_city};
     </select>

    <sql id="baseColumn">
        country,state,city
    </sql>

    <select id="ListAll" resultType="com.cyk.pojo.Addresses">
        select <include refid="baseColumn"/> from mybatis3.addresses
    </select>

</mapper>

测试类

package com.cyk;

import com.cyk.mapper.AddressesMapper;
import com.cyk.pojo.Addresses;
import com.cyk.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

public class TestDynamicSQL {

    @Test
    public void m1(){
        SqlSession sqlSession = MybatisUtil.getSession();
        AddressesMapper mapper = sqlSession.getMapper(AddressesMapper.class);

        List<Addresses> addresses = mapper.queryByCountryCity(null, "Lawton");
        System.out.println(addresses);

        sqlSession.close();
    }

    @Test
    public void m2(){
        SqlSession sqlSession = MybatisUtil.getSession();
        AddressesMapper mapper = sqlSession.getMapper(AddressesMapper.class);

        Addresses addresses = new Addresses();
        addresses.setAddrId(5);
        addresses.setCity("aa");
        addresses.setCountry("China");
        addresses.setState("guangdong");
        addresses.setStreet("xxstreet");

        sqlSession.commit();
        System.out.println(addresses.getCity());
        sqlSession.close();
    }

    @Test
    public void m3(){
        SqlSession sqlSession = MybatisUtil.getSession();
        AddressesMapper mapper = sqlSession.getMapper(AddressesMapper.class);

        Addresses addresses = new Addresses();
        addresses.setCountry("San Diego");
        List<Addresses> query = mapper.query(addresses);

        System.out.println(query);

        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void m4(){
        SqlSession sqlSession = MybatisUtil.getSession();
        AddressesMapper mapper = sqlSession.getMapper(AddressesMapper.class);

        Addresses addresses = new Addresses();
        addresses.setState("CA");
        List<Addresses> queryTrim = mapper.queryTrim(addresses);

        System.out.println(queryTrim);

        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void m5(){
        SqlSession sqlSession = MybatisUtil.getSession();
        AddressesMapper mapper = sqlSession.getMapper(AddressesMapper.class);

        List<Integer> l = new ArrayList<>();
        l.add(1);
        l.add(2);
        l.add(3);
        List<Addresses> addresses = mapper.queryByIds(l);
        System.out.println(addresses);

        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void m6(){
        SqlSession sqlSession = MybatisUtil.getSession();
        AddressesMapper mapper = sqlSession.getMapper(AddressesMapper.class);

        //模糊查询解决方案一：在应用程序层加入%%拼接
//        List<Addresses> addresses = mapper.queryLike("%Law%");
//        System.out.println(addresses);

        //模糊查询解决方案二：通过mysql的concat函数完成
//        List<Addresses> addresses = mapper.queryLike("Law");
//        System.out.println(addresses);

        //模糊查询解决方案三：通过bind标签对变量重新绑定，再引用新变量即可
        List<Addresses> addresses = mapper.queryLike("Law");
        System.out.println(addresses);

        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void m7(){
        SqlSession sqlSession = MybatisUtil.getSession();
        AddressesMapper mapper = sqlSession.getMapper(AddressesMapper.class);

        List<Addresses> addresses = mapper.ListAll();
        System.out.println(addresses);

        sqlSession.commit();
        sqlSession.close();
    }
}