mybatis——动态SQL

<?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">

    
    
    
    
        select * from tbl_employee
--         where
        
            <if test="id!=null">
                id=#{id}
            if>
            <if test="lastName!=null and lastName!=''">
                and last_name=#{lastName}
            if>
            <if test="email!=null and email.trim()!=''">
                and email=#{email}
            if>
            <if test="gender==0 or gender==1">
                and gender=#{gender}
            if>
        
    

    
    
        select * from tbl_employee
--         后面多出的and或or where标签不能解决
--         prefix="" ：前缀:trim标签体中是整个字符串拼串后的结果
--                     prefix给拼串后的整个字符串加一个前缀
--         prefixOverrides="" ：前缀覆盖，去掉整个字符串前面多余的字符
--         suffix="" ：后缀
--                     suffix给拼串后的整个字符串加一个前缀
--         suffixOverrides=""：后缀覆盖，去掉整个字符串后面多余的字符
--         自定义字符串的截取规则

        -
            <if test="id!=null">
                id=#{id} and
            if>
            <if test="lastName!=null and lastName!=''">
                last_name=#{lastName} and
            if>
            <if test="email!=null and email.trim()!=''">
                email=#{email} and
            if>
            <if test="gender==0 or gender==1">
                gender=#{gender}
            if>
        
    

    
    
        select * from tbl_employee
        
--             如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个
            
                
                    id=#{id}
                
                
                    last_name=#{lastName}
                
                
                    email=#{email}
                
                
                   gender = 0
                
            
        
    

    
    
         update tbl_employee
         
             <if test="lastName!=null">
                 last_name=#{lastName},
             if>
             <if test="email!=null">
                 email=#{email},
             if>
             <if test="gender!=null">
                 gender=#{gender}
             if>
         
        where id=#{id}
    


    
    
        select * from tbl_employee where id in
        -- collection：指定要遍历的集合；
        -- list类型的参数会特殊处理封装在map中，map的key就叫list
        -- separator:每个元素之间的分隔符
        -- item：将当前遍历出的元素赋值给指定的变量
--         open：遍历出所有结果拼接一个开始字符串
--         close：遍历出所有结果拼接一个结束的字符串
--         index：遍历list的时候是索引，index就是索引，item就是值
--                遍历map的时候index表示的就是map的key，item就是map的值
        -- #{变量名}：就能取出变量的值也就是当前遍历的元素
        
            #{item_id}
        
    

    
    
    
    
        insert into tbl_employee(last_name,email,gender,d_id)
        values
        
            (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
        
    


    

    
    
        <if test="_databaseId=='mysql">
            select * from tbl_employee
        if>
        <if test="_databaseId=='oracle">
            select * from employees
        if>

    
    

package com.lzr.dao;

import com.lzr.bean.Employee;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author GGBond
 * @create 2021-05-26-8:49
 */
public interface EmployeeMapperDynamicSQL {

    //携带了哪个字段查询条件就带上这个字段的值

    public List getEmpsByConditionIf(Employee employee);

    public List getEmpsByConditionTrim(Employee employee);

    public List getEmpsByConditionChoose(Employee employee);

    public void updateEmp(Employee employee);

    public List getEmpsByConditionForeach(@Param("ids")List ids);

    public void addEmps(@Param("emps")List emps);

    public List getEmpsTestInnerParameter(Employee employee);

}

 @Test
    public void test03() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(1,"Admin",null,null);
//            List emps = mapper.getEmpsByConditionIf(employee);
//            for (Employee emp: emps) {
//                System.out.println(emp);
//            }
            //查询的时候如果某些条件没带可能sql拼装会有问题
            //1.给where后面加上1=1，以后的条件都and XXX.
            //2.mybatis使用where标签来将所有的查询条件包括在内,mybatis就会将where标签中拼装的sql，多出来的
            //and或者or去掉；where只会去掉第一个多出来的and或者or。

            //测试trim
//            List emps2 = mapper.getEmpsByConditionTrim(employee);
//            for (Employee emp: emps2){
//                System.out.println(emp);

//            }

            //测试choose
//            List list = mapper.getEmpsByConditionChoose(employee);
//            for (Employee emp : list) {
//                System.out.println(emp);
//            }
            //测试set标签
//            mapper.updateEmp(employee);
//            openSession.commit();
//            List list = mapper.getEmpsByConditionForeach(Arrays.asList(1, 2, 3, 4));
//            for (Employee emp:list) {
//                System.out.println(emp);
//            }

        }finally {
            openSession.close();
        }
    }

    @Test
    public void test04() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            List emps = new ArrayList<>();
            emps.add(new Employee(null,"smith","smith@atgugu.com","1",new Department(1)));
            emps.add(new Employee(null,"allen","allen@atgugu.com","0",new Department(1)));
            mapper.addEmps(emps);
            openSession.commit();
        }finally {
            openSession.close();
        }
    }

    @Test
    public void test05() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            List list = mapper.getEmpsTestInnerParameter(new Employee());
            for (Employee employee:list) {
                System.out.println(employee);
            }

        }finally {
            openSession.close();
        }
    }