11.2 基于注解的动态SQL1 select

发表于 2019-06-17 更新于 2020-06-05 分类于 4 Spring+Mybatis企业应用实战(第2版) ，第11章 MyBatis的注解配置， 11.2 基于注解的动态SQL Waline：本文字数： 6.7k 阅读时长 ≈ 6 分钟

动态生成注解形式的select语句

根据Map动态生成select

在EmployeeDynamicSQLProvider.java中加入如下方法:

public String selectEmployeeWithParamMap(Map<String, Object> param)
{
    return new SQL() {
        {
            SELECT("*");
            FROM("tb_employee");
            if (param.get("id") != null)
            {
                WHERE("id=#{id}");
            }
            if (param.get("loginname") != null)
            {
                WHERE("loginname=#{loginname}");
            }
            if (param.get("password") != null)
            {
                WHERE("password=#{password}");
            }
            if (param.get("name") != null)
            {
                WHERE("name=#{name}");
            }
            if (param.get("sex") != null)
            {
                WHERE("sex=#{sex}");
            }
            if (param.get("age") != null)
            {
                WHERE("age=#{age}");
            }
            if (param.get("phone") != null)
            {
                WHERE("phone=#{phone}");
            }
            if (param.get("sal") != null)
            {
                WHERE("sal=#{sal}");
            }
            if (param.get("state") != null)
            {
                WHERE("state=#{state}");
            }
        }
    }.toString();
}

该方法根据map的key来动态生成WHERE子句。
并在mapper接口中添加如下方法:

@SelectProvider(
    type = EmployeeDynamicSQLProvider.class,
    method = "selectEmployeeWithParamMap"
)
List<Employee> selectEmployeeWithParamMap(Map<String, Object> param);

SelectByMapParam.java

/MyADynamicSQLTest/src/test/SelectByMapParam.java

package test;

import java.util.HashMap;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.Employee;
import fractory.SqlSessionFactoryTools;
import mapper.EmployeeMapper;

public class SelectByMapParam {
    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            // 获取mapper接口代理对象
            EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
            // 创建Map,作为查询的参数
            HashMap<String, Object> param = new HashMap<String, Object>();
            param.put("id", 1);
            param.put("sex", "男");
            param.put("age", 19);
            // 执行按map中的参数查询
            List<Employee> employees = employeeMapper.selectEmployeeWithParamMap(param);
            employees.forEach(employee -> System.out.println("    " + employee));
            // 提交事务
            sqlSession.commit();
        } catch (Exception e) {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally {
            // 关闭会话
            if (sqlSession != null)
                sqlSession.close();
        }
    }
}

运行测试类,控制台输出如下:

DEBUG [main] ==>  Preparing: SELECT * FROM tb_employee WHERE (sex=? AND age=?) 
DEBUG [main] ==> Parameters: 男(String), 19(Integer)
DEBUG [main] <==      Total: 2
    Employee [id=1, loginname=xiaoming, password=xiaoming, name=小明, sex=男, age=19, phone=123456789123, sal=9800.0, state=active]
    Employee [id=2, loginname=xiaowang, password=xiaowang, name=小王, sex=男, age=19, phone=123456789123, sal=6800.0, state=active]

可以看到执行的SQL:

1	SELECT * FROM tb_employee WHERE (sex=? AND age=?)

是根据输入的map参数动态生成的.

再添加一个查询条件

现在再往map中放入一个id(取消注释)：

......
// 创建Map,作为查询的参数
HashMap<String, Object> param = new HashMap<String, Object>();
param.put("id", 1);
param.put("sex", "男");
param.put("age", 19);
// 执行按map中的参数查询
List<Employee> employees = employeeMapper.selectEmployeeWithParamMap(param);
employees.forEach(employee -> System.out.println("    " + employee));
......

再次运行,控制台输出如下:

DEBUG [main] ==>  Preparing: SELECT * FROM tb_employee WHERE (id=? AND sex=? AND age=?) 
DEBUG [main] ==> Parameters: 1(Integer), 男(String), 19(Integer)
DEBUG [main] <==      Total: 1
    Employee [id=1, loginname=xiaoming, password=xiaoming, name=小明, sex=男, age=19, phone=123456789123, sal=9800.0, state=active]

可以看到mybatis根据提供的id,sex,age三个参数动态的生成了如下SQL语句:

1	SELECT * FROM tb_employee WHERE (id=? AND sex=? AND age=?)

根据持久化对象动态生成select

在动态SQL生成器EmployeeDynamicSQLProvider.java中添加如下方法:

public String selectEmployeeWithParamPO(Employee parm)
{
    return new SQL() {
        {
            SELECT("*");
            FROM("tb_employee");
            if (parm.getId() != null)
            {
                WHERE("id=#{id}");
            }
            if (parm.getLoginname() != null)
            {
                WHERE("loginname=#{loginname}");
            }
            if (parm.getPassword() != null)
            {
                WHERE("password=#{password}");
            }
            if (parm.getName() != null)
            {
                WHERE("name=#{name}");
            }
            if (parm.getSex() != null)
            {
                WHERE("sex=#{sex}");
            }
            if (parm.getAge() != null)
            {
                WHERE("age=#{age}");
            }
            if (parm.getPhone() != null)
            {
                WHERE("phone=#{phone}");
            }
            if (parm.getSal() != null)
            {
                WHERE("sal=#{sal}");
            }
            if (parm.getState() != null)
            {
                WHERE("state=#{state}");
            }
        }
    }.toString();
}

然后在mapper接口中,添加如下方法:

@SelectProvider(
    type = EmployeeDynamicSQLProvider.class,
    method = "selectEmployeeWithParamPO"
)
List<Employee> selectEmployeeWithParamPo(Employee param);

SelectByPoParam.java

/MyADynamicSQLTest/src/test/SelectByPoParam.java

package test;

import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.Employee;
import fractory.SqlSessionFactoryTools;
import mapper.EmployeeMapper;

public class SelectByPoParam {
    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            // 获取mapper接口代理对象
            EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
            // 创建存放查询条件的PO类
            Employee param = new Employee();
            // param.setId(1);
            param.setAge(19);
            param.setSex("男");
            // 使用PO类里面的套件进行查询
            List<Employee> employees = employeeMapper.selectEmployeeWithParamPo(param);
            // 输出查询结果
            employees.forEach(employee -> System.out.println("  " + employee));
            // 提交事务
            sqlSession.commit();
        } catch (Exception e) {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally {
            // 关闭会话
            if (sqlSession != null)
                sqlSession.close();
        }
    }
}

我们在测试类中提供了sex和age两个参数。mybatis将会根据这两个参数生成SQL语句,运行该测试类,控制台输出如下:

DEBUG [main] ==>  Preparing: SELECT * FROM tb_employee WHERE (sex=? AND age=?) 
DEBUG [main] ==> Parameters: 男(String), 19(Integer)
DEBUG [main] <==      Total: 2
  Employee [id=1, loginname=xiaoming, password=xiaoming, name=小明, sex=男, age=19, phone=123456789123, sal=9800.0, state=active]
  Employee [id=2, loginname=xiaowang, password=xiaowang, name=小王, sex=男, age=19, phone=123456789123, sal=6800.0, state=active]

再添加一个查询条件

现在取消id参数前面的注释,再提供一个id参数,运行测试类,控制台输出如下:

DEBUG [main] ==>  Preparing: SELECT * FROM tb_employee WHERE (id=? AND sex=? AND age=?) 
DEBUG [main] ==> Parameters: 1(Integer), 男(String), 19(Integer)
DEBUG [main] <==      Total: 1
  Employee [id=1, loginname=xiaoming, password=xiaoming, name=小明, sex=男, age=19, phone=123456789123, sal=9800.0, state=active]