MyBatis——动态 sql

动态 sql

文章目录

动态 sql
- 1、环境搭建
- 2、if 判断 & OGNL
- - 2.1、where 查询条件
  - 2.2、sql_trim 自定义字符串截取
- 3、choose 分支选择
- - 3.1、set 与 if 结合的动态更新
- 4、foreach 遍历集合
- - 4.1、foreach 批量插入的两种方式
- 5、内置参数：_parameter & _databaseld
- 6、bind 绑定
- 7、抽取可重用的 sql 片段

1、环境搭建

先创建一个 EmployeeMapperDynamicSQL 接口：

package mybatis.dao;

public interface EmployeeMapperDynamicSQL {

}

创建对应的 EmployeeMapperDynamicSQL.xml 的配置文件：

2、if 判断 & OGNL

在接口中定义查询方法：

public interface EmployeeMapperDynamicSQL {
    //携带了哪个字段，查询条件就带上哪个字段的值
    public List getEmpByConditionIf(Employee employee);
}

在配置文件中实现 sql 查询：



    select * from tbl_employee
    where
    
    
        id = #{id}
    
    
        and last_name like #{lastName}
    
    
        and email = #{email}
    
    
    
        and gender = #{gender}

测试：

@Test
public void test1() throws IOException {
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    try {
        EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
        Employee employee = new Employee(1, "%e%", "jerry3@qq.com", null);

        List empByConditionIf = mapper.getEmpByConditionIf(employee);
        for (Employee emp:
             empByConditionIf) {
            System.out.println(emp);
        }
    }
    finally {
        sqlSession.close();
    }
}

DEBUG 12-28 18:55:30,112 ==>  Preparing: select * from tbl_employee where id = ? and last_name like ? and email = ?  (baseJdbcLogger.java:137) 
DEBUG 12-28 18:55:30,146 ==> Parameters: 1(Integer), %e%(String), jerry3@qq.com(String)  (baseJdbcLogger.java:137) 
DEBUG 12-28 18:55:30,162 <==      Total: 1  (baseJdbcLogger.java:137) 
Employee{id=1, lastName='jerry2', email='jerry3@qq.com', gender='0', dept=null}

2.1、where 查询条件

上边配置文件中的 sql 存在问题：如果给定的参数中没有带 id，直接给了 last_name，那么 sql 语句中上来就是 and last_name like，那么 sql 语句就会语法报错。

@Test
public void test1() throws IOException {
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    try {
        EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
        //Employee employee = new Employee(1, "%e%", "jerry3@qq.com", null);
        Employee employee = new Employee(null, "%e%", null, null);

        List empByConditionIf = mapper.getEmpByConditionIf(employee);
        for (Employee emp:
             empByConditionIf) {
            System.out.println(emp);
        }

        //查询的时候如果某些条件没带可能sql拼装会有问题
        //1、给where后边加上 1=1，以后的条件都有 and xxx
        //2、mybatis可以使用where标签来将所有的查询条件包括在内
        	//where只会去掉第一个多出来的and或者or
    }
    finally {
        sqlSession.close();
    }
}

DEBUG 12-28 19:14:54,907 ==>  Preparing: select * from tbl_employee where and last_name like ?  (baseJdbcLogger.java:137) 
DEBUG 12-28 19:14:54,955 ==> Parameters: %e%(String)  (baseJdbcLogger.java:137) 

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and last_name like '%e%'' at line 6
...

第一种解决方法：


    select * from tbl_employee
    where 1=1
    
    
        and id = #{id}
    
    
        and last_name like #{lastName}
    
    
        and email = #{email}
    
    
    
        and gender = #{gender}

在 where 之后加上 1=1，然后每个 if 标签里边加上 and。

DEBUG 12-28 19:17:01,641 ==>  Preparing: select * from tbl_employee where 1=1 and last_name like ?  (baseJdbcLogger.java:137) 
DEBUG 12-28 19:17:01,671 ==> Parameters: %e%(String)  (baseJdbcLogger.java:137) 
DEBUG 12-28 19:17:01,688 <==      Total: 3  (baseJdbcLogger.java:137) 
Employee{id=1, lastName='jerry2', email='jerry3@qq.com', gender='0', dept=null}
Employee{id=2, lastName='jerry1', email='jerry1@qq.com', gender='1', dept=null}
Employee{id=3, lastName='jerry', email='jerry@qq.com', gender='1', dept=null}

第二种解决方法：


    select * from tbl_employee
    
    
        
        
            id = #{id}
        
        
            and last_name like #{lastName}
        
        
            and email = #{email}
        
        
        
            and gender = #{gender}

mybatis 可以使用 where 标签来将所有的查询条件包括在内。

DEBUG 12-28 19:17:01,641 ==>  Preparing: select * from tbl_employee where 1=1 and last_name like ?  (baseJdbcLogger.java:137) 
DEBUG 12-28 19:17:01,671 ==> Parameters: %e%(String)  (baseJdbcLogger.java:137) 
DEBUG 12-28 19:17:01,688 <==      Total: 3  (baseJdbcLogger.java:137) 
Employee{id=1, lastName='jerry2', email='jerry3@qq.com', gender='0', dept=null}
Employee{id=2, lastName='jerry1', email='jerry1@qq.com', gender='1', dept=null}
Employee{id=3, lastName='jerry', email='jerry@qq.com', gender='1', dept=null}

但是 where 标签无法解决的问题：


    select * from tbl_employee
    
    
        
        
            id = #{id} and
        
        
            last_name like #{lastName} and
        
        
            email = #{email} and
        
        
        
            gender = #{gender}

DEBUG 12-28 19:24:40,253 ==>  Preparing: select * from tbl_employee WHERe last_name like ? and  (baseJdbcLogger.java:137) 
DEBUG 12-28 19:24:40,284 ==> Parameters: %e%(String)  (baseJdbcLogger.java:137)

2.2、sql_trim 自定义字符串截取

public interface EmployeeMapperDynamicSQL {
    public List getEmpByConditionTrim(Employee employee);


    select * from tbl_employee
    
    
        
        
            id = #{id} and
        
        
            last_name like #{lastName} and
        
        
            email = #{email} and
        
        
        
            gender = #{gender} and

测试：

@Test
public void test2() throws IOException {
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    try {
        EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
        //Employee employee = new Employee(1, "%e%", "jerry3@qq.com", null);
        Employee employee = new Employee(null, "%e%", null, null);

        List empByConditionTrim = mapper.getEmpByConditionTrim(employee);
        for (Employee emp:
                empByConditionTrim) {
            System.out.println(emp);
        }

        //查询的时候如果某些条件没带可能sql拼装会有问题
        //1、给where后边加上 1=1，以后的条件都有 and xxx
        //2、mybatis可以使用where标签来将所有的查询条件包括在内
            //where只会去掉第一个多出来的and或者or
    }
    finally {
        sqlSession.close();
    }
}

DEBUG 12-28 19:40:30,915 ==>  Preparing: select * from tbl_employee where last_name like ?  (baseJdbcLogger.java:137) 
DEBUG 12-28 19:40:30,966 ==> Parameters: %e%(String)  (baseJdbcLogger.java:137) 
DEBUG 12-28 19:40:30,990 <==      Total: 3  (baseJdbcLogger.java:137) 
Employee{id=1, lastName='jerry2', email='jerry3@qq.com', gender='0', dept=null}
Employee{id=2, lastName='jerry1', email='jerry1@qq.com', gender='1', dept=null}
Employee{id=3, lastName='jerry', email='jerry@qq.com', gender='1', dept=null}

3、choose 分支选择

public interface EmployeeMapperDynamicSQL {
    public List getEmpByConditionChoose(Employee employee);


    select * from tbl_employee
    
        
        
            
                id = #{id}
            
            
                last_name like #{lastName}
            
            
                email = #{email}
            
            
                gender = 0

测试：

@Test
public void test3() throws IOException {
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    try {
        EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);

        //测试choose
        //Employee employee = new Employee(null, "%e%", null, null);
        Employee employee = new Employee(null, null, null, null);

        List empByConditionChoose = mapper.getEmpByConditionChoose(employee);
        for (Employee emp:
                empByConditionChoose) {
            System.out.println(emp);
        }

    }
    finally {
        sqlSession.close();
    }
}

DEBUG 12-28 20:42:02,675 ==>  Preparing: select * from tbl_employee WHERe gender = 0  (baseJdbcLogger.java:137) 
DEBUG 12-28 20:42:02,716 ==> Parameters:   (baseJdbcLogger.java:137) 
DEBUG 12-28 20:42:02,739 <==      Total: 1  (baseJdbcLogger.java:137) 
Employee{id=1, lastName='jerry2', email='jerry3@qq.com', gender='0', dept=null}

什么也没提供，只查出了 gender 为 0 的。

3.1、set 与 if 结合的动态更新

public interface EmployeeMapperDynamicSQL {
    public void updateEmp(Employee employee);


    update tbl_employee
    
        
            last_name = #{lastName},
        
        
            email = #{email},
        
        
            gender = #{gender}
        
    
    where id = #{id}

测试：

@Test
public void test4() throws IOException {
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    try {
        EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);

        //测试set标签
        Employee employee = new Employee(1, "Admin", null, null);

        mapper.updateEmp(employee);

    }
    finally {
        sqlSession.close();
    }
}

DEBUG 12-28 21:23:03,559 ==>  Preparing: update tbl_employee SET last_name = ? where id = ?  (baseJdbcLogger.java:137) 
DEBUG 12-28 21:23:03,592 ==> Parameters: Admin(String), 1(Integer)  (baseJdbcLogger.java:137) 
DEBUG 12-28 21:23:03,594 <==    Updates: 1  (baseJdbcLogger.java:137)

mysql> select * from tbl_employee;
+----+-----------+--------+---------------+------+
| id | last_name | gender | email         | d_id |
+----+-----------+--------+---------------+------+
|  1 | Admin     | 0      | jerry3@qq.com |    1 |
|  2 | jerry1    | 1      | jerry1@qq.com |    2 |
|  3 | jerry     | 1      | jerry@qq.com  |    1 |
+----+-----------+--------+---------------+------+
3 rows in set (0.00 sec)

4、foreach 遍历集合

public interface EmployeeMapperDynamicSQL {
    public List getEmpsByConditionForeach(List ids);


    select * from tbl_employee where id in
    
    
        #{item_id}

测试：

@Test
public void test5() throws IOException {
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    try {
        EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);

        List empsByConditionForeach = mapper.getEmpsByConditionForeach(Arrays.asList(1, 2, 3));

        for (Employee emp:
             empsByConditionForeach) {
            System.out.println(emp);
        }

    }
    finally {
        sqlSession.close();
    }
}

DEBUG 12-28 21:46:37,323 ==>  Preparing: select * from tbl_employee where id in ( ? , ? , ? )  (baseJdbcLogger.java:137) 
DEBUG 12-28 21:46:37,359 ==> Parameters: 1(Integer), 2(Integer), 3(Integer)  (baseJdbcLogger.java:137) 
DEBUG 12-28 21:46:37,378 <==      Total: 3  (baseJdbcLogger.java:137) 
Employee{id=1, lastName='Admin', email='jerry3@qq.com', gender='0', dept=null}
Employee{id=2, lastName='jerry1', email='jerry1@qq.com', gender='1', dept=null}
Employee{id=3, lastName='jerry', email='jerry@qq.com', gender='1', dept=null}

更简洁的写法：


    select * from tbl_employee
    
    
        #{item_id}

4.1、foreach 批量插入的两种方式

第一种方法：

public interface EmployeeMapperDynamicSQL {
    public void addEmps(@Param("emps")List emps);



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

测试：

@Test
public void test6() throws IOException {
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    try {
        EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);

        List emps = new ArrayList<>();
        emps.add(new Employee(null, "smith", "smith@qq.com", "1", new Department(1)));
        emps.add(new Employee(null, "allen", "allen@qq.com", "0", new Department(1)));

        mapper.addEmps(emps);
    }
    finally {
        sqlSession.close();
    }
}

DEBUG 12-29 10:02:12,768 ==>  Preparing: insert into tbl_employee(last_name, email, gender, d_id) values (?, ?, ?, ?) , (?, ?, ?, ?)  (baseJdbcLogger.java:137) 
DEBUG 12-29 10:02:12,813 ==> Parameters: smith(String), smith@qq.com(String), 1(String), 1(Integer), allen(String), allen@qq.com(String), 0(String), 1(Integer)  (baseJdbcLogger.java:137) 
DEBUG 12-29 10:02:12,818 <==    Updates: 2  (baseJdbcLogger.java:137)

mysql> select * from tbl_employee;
+----+-----------+--------+---------------+------+
| id | last_name | gender | email         | d_id |
+----+-----------+--------+---------------+------+
|  1 | Admin     | 0      | jerry3@qq.com |    1 |
|  2 | jerry1    | 1      | jerry1@qq.com |    2 |
|  3 | jerry     | 1      | jerry@qq.com  |    1 |
|  4 | smith     | 1      | smith@qq.com  |    1 |
|  5 | allen     | 0      | allen@qq.com  |    1 |
+----+-----------+--------+---------------+------+
5 rows in set (0.00 sec)

第二种方法：


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

但这种方式还需要更改配置，让 mysql 支持这种语法：

jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://192.168.31.140:3306/mybatis?allowMultiQueries=true
jdbc.username = root
jdbc.password = Opfordream@0518

5、内置参数：_parameter & _databaseld

参数：_databaseld

public interface EmployeeMapperDynamicSQL {
    public List getEmpsTestInnerParameter(Employee employee);



    
        select * from tbl_employee

测试：

@Test
public void test7() throws IOException {
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    try {
        EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);

        List empsTestInnerParameter = mapper.getEmpsTestInnerParameter(new Employee());
        for (Employee emp :
                empsTestInnerParameter) {
            System.out.println(emp);
        }
    }
    finally {
        sqlSession.close();
    }
}

DEBUG 12-29 10:50:03,989 ==>  Preparing: select * from tbl_employee  (baseJdbcLogger.java:137) 
DEBUG 12-29 10:50:04,022 ==> Parameters:   (baseJdbcLogger.java:137) 
DEBUG 12-29 10:50:04,047 <==      Total: 7  (baseJdbcLogger.java:137) 
Employee{id=1, lastName='Admin', email='jerry3@qq.com', gender='0', dept=null}
Employee{id=2, lastName='jerry1', email='jerry1@qq.com', gender='1', dept=null}
Employee{id=3, lastName='jerry', email='jerry@qq.com', gender='1', dept=null}
Employee{id=4, lastName='smith', email='smith@qq.com', gender='1', dept=null}
Employee{id=5, lastName='allen', email='allen@qq.com', gender='0', dept=null}
Employee{id=6, lastName='smith', email='smith@qq.com', gender='1', dept=null}
Employee{id=7, lastName='allen', email='allen@qq.com', gender='0', dept=null}

参数：_parameter


    
        select * from tbl_employee
        
            where last_name = #{_parameter.lastName}

DEBUG 12-29 10:54:39,038 ==>  Preparing: select * from tbl_employee where last_name = ?  (baseJdbcLogger.java:137) 
DEBUG 12-29 10:54:39,067 ==> Parameters: null  (baseJdbcLogger.java:137) 
DEBUG 12-29 10:54:39,085 <==      Total: 0  (baseJdbcLogger.java:137)

6、bind 绑定


    
    
    
        select * from tbl_employee
        
            where last_name like #{_lastName}

@Test
public void test8() throws IOException {
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    try {
        EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);

        Employee employee = new Employee();
        employee.setLastName("e");
        List empsTestInnerParameter = mapper.getEmpsTestInnerParameter(employee);
        for (Employee emp :
                empsTestInnerParameter) {
            System.out.println(emp);
        }
    }
    finally {
        sqlSession.close();
    }
}

DEBUG 12-29 11:26:31,361 ==>  Preparing: select * from tbl_employee where last_name like ?  (baseJdbcLogger.java:137) 
DEBUG 12-29 11:26:31,390 ==> Parameters: %e%(String)  (baseJdbcLogger.java:137) 
DEBUG 12-29 11:26:31,409 <==      Total: 4  (baseJdbcLogger.java:137) 
Employee{id=2, lastName='jerry1', email='jerry1@qq.com', gender='1', dept=null}
Employee{id=3, lastName='jerry', email='jerry@qq.com', gender='1', dept=null}
Employee{id=5, lastName='allen', email='allen@qq.com', gender='0', dept=null}
Employee{id=7, lastName='allen', email='allen@qq.com', gender='0', dept=null}

但是就模糊查询还是推荐以下形式：

try {
    EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);

    Employee employee = new Employee();
    employee.setLastName("%e%");
    List empsTestInnerParameter = mapper.getEmpsTestInnerParameter(employee);
    for (Employee emp :
            empsTestInnerParameter) {
        System.out.println(emp);
    }
}

7、抽取可重用的 sql 片段


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




    
    last_name, email, gender, d_id

测试：

@Test
public void test6() throws IOException {
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    try {
        EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);

        List emps = new ArrayList<>();
        emps.add(new Employee(null, "smith", "smith@qq.com", "1", new Department(1)));
        emps.add(new Employee(null, "allen", "allen@qq.com", "0", new Department(1)));

        mapper.addEmps(emps);
    }
    finally {
        sqlSession.close();
    }
}

MyBatis——动态 sql

Java相关栏目本月热门文章