1、动态查询客户

• CustomerMapper.java代码：

public List<Customer> findCustomerDynamic(Customer c)throws  SQLException;

• CustomerMapper.xml代码：

<select id="findCustomerDynamic" resultType="Customer">
        select * from t_customer
        <where><!-- 自动删除第一个and -->
            <if test="id!=null">
                id like "%"#{id}"%"
            </if>
            <if test="username!=null and username!=''">
                and username like "%"#{username}"%"
            </if>
            <if test="jobs!=null and jobs!=''">
                and jobs like "%"#{jobs}"%"
            </if>
            <if test="phone!=null and phone!=''">
                and phone like "%"#{phone}"%"
            </if>
        </where>
        order by username desc
    </select>

<where>自动删除第一个and,order by 写在<where> 后面；
<if>里面字符串比较不需用equals

• test代码：

    @Test
    public void testDynamicSearch1() throws Exception {
        String path = "SqlMapConfig.xml";
        Reader reader = Resources.getResourceAsReader(path);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
        
        Customer c = new Customer();
        c.setId(6);
        c.setUsername("露西");
        c.setJobs("软件工程师");
        c.setPhone("15940471234");
        
        try
        {
            List<Customer> list = customerMapper.getCustomerDynamic(c);
            for(Customer item: list)
            {
                System.out.println(item.getId()+"\t"+item.getUsername()+"\t"+item.getPhone());          
            }
            
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            
        }   
    }

2、动态更新信息

• CustomerMapper.java代码：

public int updateCustomerDynamic(Customer c)throws  SQLException;

• CustomerMapper.xml代码：

  <update id="updateCustomerDynamic" >
        update t_customer
        <set><!-- 自动删除最后一个逗号 -->
            <if test="username!=null and username!=''">
                username=#{username},
            </if>
            <if test="jobs!=null and jobs!=''">
                jobs=#{jobs},
            </if>
            <if test="phone!=null and phone!=''">
                phone=#{phone},
            </if>
        </set>
        where id=#{id}
  </update>

<set>自动删除最后一个逗号，where写在<set>后；

• test代码：

@Test
    public void updateCustomerDynamic() throws Exception {
        String path="mybatis-config.xml";
        Reader reader = Resources.getResourceAsReader(path);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
        Customer c=new Customer();
        c.setId(5);
        c.setUsername("张三777");
        c.setJobs("农民666");
        c.setPhone("12546841259");
            try {
                int i=customerMapper.updateCustomerDynamic(c);
                if(i>0) {
                    sqlSession.commit();
                }else {
                    sqlSession.rollback();
                }
            } catch (SQLException e) {
                sqlSession.rollback();
                e.printStackTrace();
            }finally {
                sqlSession.close();
            }
        
        
    }

注意：增删改之后要关闭sqlSession工厂

3、动态拼接foreach——SQL中的in

collection：入参类型（数组类型是array）
open：以什么开始
close：以什么结尾
separator：分隔符
item：为拼接好的结果起名（随意写）
（1）list入参

• CustomerMapper.java代码：

    public List<Customer> findCustomerByIds(List<Integer> list)throws SQLException;

• CustomerMapper.xml代码：

    <select id="findCustomerByIds"  resultType="Customer">
        select * from t_customer where id in
        <foreach collection="list" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
    </select>

• test代码：

    @Test
    public void findCustomerByIds() throws Exception{
        String path="mybatis-config.xml";
        Reader reader = Resources.getResourceAsReader(path);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
        List<Integer> list=new ArrayList();
        list.add(1);
        list.add(2);
        list.add(3);
        List<Customer> l = customerMapper.findCustomerByIds(list);
        for (Customer c : l) {
            System.out.println(c);
        }
    }

（2）数组入参

• CustomerMapper.java代码：

public List<Customer> findCustomerByIds2(int[] a)throws SQLException;

• CustomerMapper.xml代码：

    <select id="findCustomerByIds2"  resultType="Customer">
        select * from t_customer where id in
        <foreach collection="array" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
    </select>

• test代码：

        @Test
        public void findCustomerByIds2() throws Exception{
        String path="mybatis-config.xml";
        Reader reader = Resources.getResourceAsReader(path);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
            int[] a= {1,2,3};
            List<Customer> l = customerMapper.findCustomerByIds2(a);
            for (Customer c : l) {
                System.out.println(c);
            }
        }

（3）map入参

• CustomerMapper.java代码：

public List<Customer> findCustomerByIds3(HashMap<String,Object> hm)throws SQLException;

• CustomerMapper.xml代码（collection写的是map里面的key值）：

    <select id="findCustomerByIds3"  resultType="Customer">
        select * from t_customer where id in
        <foreach collection="ids" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
        and username=#{username}
    </select>

• test代码：

        @Test
        public void findCustomerByIds3() throws Exception{
        String path="mybatis-config.xml";
        Reader reader = Resources.getResourceAsReader(path);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
            HashMap<String, Object> hm=new HashMap<>();
            int[] a= {1,2,3};
            hm.put("ids", a);
            hm.put("username", "tom");
            List<Customer> l = customerMapper.findCustomerByIds3(hm);
            for (Customer c : l) {
                System.out.println(c);
            }
        }

4、SQL片段

定义重复的SQL片段
引用SQL片段代码：

<include refid="query_user_where"></include>

注意：如果refid指定的id不在本mapper文件中，需要前面加namespace；
定义SQL片段代码：

<sql id="query_user_where">
   内容...
</sql>

经验：基于单表来定义片段，可用性更高；片段中不要包括where