Mybatis进阶三(动态SQL)

一、动态Sql

在与数据库交互时，一些复杂的sql，mybatis可以帮助实现sql拼接

• where + if：判断字段为空时，则不处理；
• set + if ：判断字段为空时，则不处理；
• jdbcType： 在update，insert，delete 时，字段传入空，则进行转换
• 传入数组的foreach；
• 传入list的foreach；
• 传入map的foreach；
• sql：高频sql语句的抽取；

Mapper.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.zte.dao.IEmployeeDao">

    <!--1. where 加 if 判断：条件查询 -->
    <select id="findList" parameterType="com.zte.bo.EmployeeBO" resultType="com.zte.bo.EmployeeBO">
        select * from employee
         <!-- where作用：(1): 如果该字段为空，则不进行sql拼接；
                           (2): 去掉第一个if判断前面的AND-->
        <where>
            <if test="name!=null and name!=''">
                AND name=#{name}
            </if>
            <if test="address!=null and address!=''">
                AND address like '%${address}%'
            </if>
            <if test="is_enable!=null and is_enable!=''">
                AND is_enable=#{is_enable}
            </if>
            <if test="phone!=null and phone!=''">
                AND phone=#{phone}
            </if>
            <if test="email!=null and email!=''">
                AND email=#{email}
            </if>
        </where>
    </select>

    <!--2 set + if 判断-->
    <update id="updateEmployee" parameterType="com.zte.bo.EmployeeBO">
        update employee
        <set>
            <!-- set作用： (1): 如果该字段为空，则不进行sql拼接；
                           (2): 去掉最后一个if中的 , 号-->
            <if test="name!=null and name!=''">
                name=#{name},
            </if>
            <if test="address!=null and address!=''">
                address=#{address},
            </if>
            <if test="is_enable!=null and is_enable!=''">
                is_enable=#{is_enable},
            </if>
            <if test="phone!=null and phone!=''">
                phone=#{phone},
            </if>
            <if test="email!=null and email!=''">
                AND email=#{email},
            </if>
        </set>
        where id=#{id}
    </update>

    <!-- 3 插入值时候的idbctype属性-->
       <!--1. 在insert，update，delete时候尽量指明，不然可能出现传入值为为，mybatis无法识别的问题-->
       <!--2. 一般不会报错，可以不写，mybatis可以智能识别-->
    <insert id="insertEmployee" parameterType="com.zte.bo.EmployeeBO">
        insert into employee
        (name,
        address,
        is_enable,
        phone,
        email,
        begin_date,
        score
        )
        values
        (
        #{name,jdbcType=VARCHAR},
        #{address,jdbcType=VARCHAR},
        #{is_enable,jdbcType=VARCHAR},
        #{phone,jdbcType=VARCHAR},
        #{email,jdbcType=VARCHAR},
        #{begin_date},
        #{score}
        )
    </insert>

    <!--一般循环是用在  in 的条件中-->
    <!--4，传入数组：缺点是必须新建一个实体类，将arr放进去-->
       <!-- collection:  要遍历的变量名称，必须一致
             index：     在list和数组中,index是元素的序号，在map中，index是元素的key，该参数可随意写。
             open close：循环得到的以什么开头及结尾
             separator:   各个变量以什么分割
             item：       遍历得到的变量名称，与#{item}相同，一般与数据库的字段一样-->
    <select id="getEmployeeWithArr" parameterType="com.zte.bo.EmployeeQueryBO" resultType="com.zte.bo.EmployeeBO">
        select * from employee where id in
        <foreach collection="arrsh" index="index" open="(" close=")" separator="," item="var">
            #{var}
        </foreach>
    </select>

    <!--5. 传入list时候，入参类型：list        collection：必须用list作为名字-->
    <select id="getEmployeeWithList" parameterType="list" resultType="com.zte.bo.EmployeeBO">
        select * from employee where id in
        <foreach collection="list" index="index" open="(" close=")" separator="," item="var">
            #{var}
        </foreach>
    </select>

    <!--6. 传入map，入参类型：map
     collection：必须用idList作为名字,是map中的具体属性的名字，类似封装数组的POJO-->
    <select id="getEmployeeWithMap" parameterType="map" resultType="com.zte.bo.EmployeeBO">
        select * from employee where id in
        <foreach collection="idList" index="index" open="(" close=")" separator="," item="var">
            #{var}
        </foreach>
    </select>

    <!--7. sql的抽取: 部分频繁使用的字段，可以进行抽取-->
    <sql id="allColumn">
        t.id,t.name,t.address,t.is_enable,t.phone,t.email,t.begin_date,t.score
    </sql>

    <select id="selectWithPart" resultType="com.zte.bo.EmployeeBO">
        select
        <include refid="allColumn"></include>
        from employee as t
    </select>

</mapper>

IEmployee.java

 package com.zte.dao;

import com.zte.bo.EmployeeBO;
import com.zte.bo.EmployeeQueryBO;

import java.util.List;
import java.util.Map;

public interface IEmployeeDao {

    public List<EmployeeBO> findList(EmployeeBO employee);

    public int updateEmployee(EmployeeBO employee);

    public int insertEmployee(EmployeeBO employee);

    public List<EmployeeBO> getEmployeeWithArr(EmployeeQueryBO queryBO);

    public List<EmployeeBO> getEmployeeWithList(List<Integer> idList);

    public List<EmployeeBO> getEmployeeWithMap(Map<String,Object> map);

    public List<EmployeeBO> selectWithPart();

}

EmployeeBo.java

package com.zte.bo;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;

@Data
public class EmployeeBO implements Serializable {
    private static final long serialVersionUID = 6762459744226015041L;
    private int id;
    private String name;
    private String address;
    private String is_enable;
    private String phone;
    private String email;
    private Date begin_date;
    private int score;

}

EmployeeQueryBO

package com.zte.bo;

import lombok.Data;
/** 继承EmployeeBO后，该POJO中会有其他的字段*/
@Data
public class EmployeeQueryBO extends EmployeeBO{

    /** arr: 在mapper.xml中 ，collection = arrsh */
    private int[] arrsh;
}

测试类

 package com.zte.test;

import com.zte.bo.EmployeeBO;
import com.zte.bo.EmployeeQueryBO;
import com.zte.dao.IEmployeeDao;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MybatisTest {
    private InputStream in = null;
    private SqlSessionFactoryBuilder builder = null;
    private SqlSessionFactory factory = null;
    private SqlSession sqlSession = null;
    private IEmployeeDao dao = null;

    @Before
    public void init() throws IOException {
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        builder = new SqlSessionFactoryBuilder();
        factory = builder.build(in);
        sqlSession = factory.openSession();
        // 传入的是接口的字节码文件
        // 得到的是接口的具体实现类，该实现类是mybatis动态代理生成的
        dao = sqlSession.getMapper(IEmployeeDao.class);
    }

    @After
    public void close() throws IOException {
        sqlSession.close();
        in.close();
    }

    @Test
    public void findList() {
        EmployeeBO bo = new EmployeeBO();
        bo.setAddress("Peking");
        bo.setName("puck");
        List<EmployeeBO> list = dao.findList(bo);
        if (null != list && list.size() > 0) {
            System.out.println(list.size());
        }
    }

    @Test
    public void updateEmployee() {
        EmployeeBO bo = new EmployeeBO();
        bo.setId(1);
        bo.setAddress("peter");
        bo.setName("Peking");
        int i = dao.updateEmployee(bo);
        sqlSession.commit();
        System.out.println(i);
    }

    @Test
    public void insertEmployee() {
        EmployeeBO bo = new EmployeeBO();
        bo.setAddress("Tom");
        bo.setName("American");
        int i = dao.insertEmployee(bo);
        sqlSession.commit();
        System.out.println(i);
    }

    /**
     * 新建bo，里面放arr[]
     */
    @Test
    public void getEmployeeWithArr() {
        EmployeeQueryBO bo = new EmployeeQueryBO();
        int[] baseArray = {1, 2, 3, 4};
        /** collection中传的： 实体类中的arr，而不是baseArray*/
        bo.setArrsh(baseArray);
        List<EmployeeBO> employeeWithArr = dao.getEmployeeWithArr(bo);
        if (null != employeeWithArr && employeeWithArr.size() > 0) {
            System.out.println(employeeWithArr);
        }
    }

    /**
     * 将id放在list中，就不用新建bo了
     */
    @Test
    public void getEmployeeWithList() {
        /** collection中传的： listInput*/
        List<Integer> listInput = new ArrayList<Integer>();
        listInput.add(1);
        listInput.add(2);
        listInput.add(3);
        listInput.add(4);
        List<EmployeeBO> employeeWithList = dao.getEmployeeWithList(listInput);
        if (null != employeeWithList && employeeWithList.size() > 0) {
            System.out.println(employeeWithList);
        }
    }

    /**
     * 将id放在list中，再把list放在map中，就不用新建bo了
     */
    @Test
    public void getEmployeeWithMap() {

        List<Integer> listInput = new ArrayList<Integer>();
        Map<String, Object> map = new HashMap<String, Object>(15);
        listInput.add(1);
        listInput.add(2);
        listInput.add(3);
        listInput.add(4);
        /** collection中传的： idList*/
        map.put("idList", listInput);
        List<EmployeeBO> employeeWithList = dao.getEmployeeWithList(listInput);
        if (null != employeeWithList && employeeWithList.size() > 0) {
            System.out.println(employeeWithList);
        }
    }

    @Test
    public void selectWithPart() {
        List<EmployeeBO> list = dao.selectWithPart();
        System.out.println(list);
    }

}