动态生成注解形式的select语句
- 根据Map参数构建动态select语句
  - 测试类
- 根据持久化对象参数构建动态select语句
  - 测试类

动态生成注解形式的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);

测试类

package test;
import java.util.HashMap;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.Employee;
import fractory.SqlSessionFratoryTools;
import mapper.EmployeeMapper;
public class TestParmMap {
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFratoryTools.getSqlSession();
            // 获取mapper接口代理对象
            EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
            HashMap<String, Object> param = new HashMap<String, Object>();
//            param.put("id", 1);
            param.put("sex", "男");
            param.put("age", 19);
            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(取消注释),再次运行,控制台输出如下:

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生成器中添加如下方法:

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);

测试类

package test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.Employee;
import fractory.SqlSessionFratoryTools;
import mapper.EmployeeMapper;
public class TestParamPO {
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFratoryTools.getSqlSession();
            // 获取mapper接口代理对象
            EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
            Employee param = new Employee();
            // param.setId(1);
            param.setAge(19);
            param.setSex("男");
            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]

原文链接: 11.2 基于注解的动态SQL1 select