加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 (https://www.lidatong.com.cn/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 大数据 > 正文

mybatis动态sql之分支选择(学习choose、when、otherwise标签)

发布时间:2020-12-14 18:02:19 所属栏目:大数据 来源:网络整理
导读:EmployeeMapperDynamicSql.java package com.gong.mybatis.mapper; import java.util.List; java.util.Map; org.apache.ibatis.annotations.MapKey; com.gong.mybatis.bean.Employee; public interface EmployeeMapperDynamicSql { public ListEmployee getE

EmployeeMapperDynamicSql.java

package com.gong.mybatis.mapper;

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

 org.apache.ibatis.annotations.MapKey;

 com.gong.mybatis.bean.Employee;

public interface EmployeeMapperDynamicSql {
    public List<Employee> getEmpByConditionIf(Employee employee);
}

EmployeeMapperDynamicSql.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.gong.mybatis.mapper.EmployeeMapperDynamicSql">
    <!-- 查询,要查那个就带上那个条件 -->
    select id="getEmpByConditionIf" resultType="com.gong.mybatis.bean.Employee">
        select * from tbl_employee 
        where>
            choose>
                when test="id!=null"
                    id=#{id}
                </when="lastName!=null"
                    last_name like #{lastName}
                ="email!=null"
                    email=#{email}
                otherwise>
        select>
mapper>

说明:当传入了id,就会根据id来查询,传入了lastName,就根据last_name来查询,传入了email,就根据email查询。否则就执行otherwise标签里面的。

测试:

 com.gong.mybatis.test;

 java.io.IOException;
 java.io.InputStream;
 org.apache.ibatis.io.Resources;
 org.apache.ibatis.session.SqlSession;
 org.apache.ibatis.session.SqlSessionFactory;
 org.apache.ibatis.session.SqlSessionFactoryBuilder;
 org.junit.Test;

 com.gong.mybatis.bean.Employee;
 com.gong.mybatis.mapper.EmployeeMapperDynamicSql;

class TestMybatis3 {
    
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream is = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(is);
    }

    @Test
    void test()  IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperDynamicSql mapper = openSession.getMapper(EmployeeMapperDynamicSql.);
            Employee employee =  Employee();
//            employee.setId(3);
            employee.setLastName("%小%");
            employee.setEmail("xiaoming@qq.com");
            List<Employee> es = mapper.getEmpByConditionIf(employee);
            for(Employee e:es) {
                System.err.println(e);
            }
            openSession.commit();
        } finally {
            openSession.close();
        }
        
    }
    
}

首先根据id=3进行查询:

DEBUG 01-21 13:45:17,288 ==>  Preparing: select * from tbl_employee WHERE id=?   (BaseJdbcLogger.java:145) 
DEBUG 01-21 13:45:17,339 ==> Parameters: 3(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 01-21 13:45:17,410 ==      Total: 1  (BaseJdbcLogger.java:145) 
Employee [id=3,lastName=小红,1)">gender=0,1)">email=xiaohong@qq.com,1)">dept=null]

根据lastName="%小%"进行模糊查询:

DEBUG 01-21 13:46:04,385 ==>  Preparing: select * from tbl_employee WHERE last_name like ?   (BaseJdbcLogger.java:145) 
DEBUG 01-21 13:46:04,417 ==> Parameters: %小%(String)  (BaseJdbcLogger.java:145) 
DEBUG 01-21 13:46:04,502 2  (BaseJdbcLogger.java:145) 
Employee [id=null]
Employee [id=4,1)">=小明,1)">=xiaoming@qq.com,1)">=null]

根据email=xiximayou@qq.com进行查询:

DEBUG 01-21 13:46:43,284 ==>  Preparing: select * from tbl_employee WHERE email=?   (BaseJdbcLogger.java:145) 
DEBUG 01-21 13:46:43,347 ==> Parameters: xiximayou@qq.com(String)  (BaseJdbcLogger.java:145) 
Employee [id=1,lastName=xiximayou,gender=1,email=xiximayou@qq.com,dept=null]
DEBUG 01-21 13:46:43,394 1  (BaseJdbcLogger.java:145) 

根据id=3,lastName=%小%进行查询:

DEBUG 01-21 13:47:30,447 ==>  Preparing: select * from tbl_employee WHERE id=?   (BaseJdbcLogger.java:145) 
DEBUG 01-21 13:47:30,522 ==> Parameters: 3(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 01-21 13:47:30,553 =null]

不输入任何参数,默认查询:

DEBUG 01-21 14:18:25,159 ==>  Preparing: select * from tbl_employee   (BaseJdbcLogger.java:145) 
DEBUG 01-21 14:18:25,243 ==> Parameters:   (BaseJdbcLogger.java:145) 
DEBUG 01-21 14:18:25,274 4  (BaseJdbcLogger.java:145) 
Employee [id=1,1)">=xiximayou,1)">=xiximayou@qq.com,1)">=2,1)">=jack,1)">=675544321@qq.com,1)">=null]

此时就会查询出所有的数据。

(编辑:李大同)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章
      热点阅读