mybatis动态sql使用bind绑定
1、bind绑定可以将ognl表达式的值绑定到一个变量中，方便后来引用这个变量的值。
<bind name="_empName" value="'%' + empName +'%'"/>
2、假如我们想要模糊查询员工姓名：
<select id="getEmpsByConditionChoose" resultMap="MyEmpByStep" databaseId="mysql">
select *
from tbl_emp
<where>
<if test="empId!=null">
emp_id = #{empId}
</if>
<if test="empName!=null">
and emp_name like #{_empName}
</if>
</where>
</select>
3、编写单元测试代码：
@Test
public void test1() throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
employee.setEmpId(1);
employee.setEmpName("%曹%");
List<Employee> employees = employeeMapper.getEmpsByConditionChoose(employee);
System.out.println(employees);
}finally {
sqlSession.close();
}
}
4、假如我们想要这个百分号不要写在代码，而是想写在sql语句中，可以使用bind标签进行绑定
<select id="getEmpsByConditionChoose" resultMap="MyEmpByStep" databaseId="mysql">
select *
from tbl_emp
<bind name="_empName" value="'%' + empName +'%'"/>
<where>
<if test="empId!=null">
emp_id = #{empId}
</if>
<if test="empName!=null">
and emp_name like #{_empName}
</if>
</where>
</select>
5、运行单元测试方法，查看后台打印的sql语句。
6、模糊查询我们推荐使用在代码中使用%传值。
public void test1() throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
employee.setEmpId(1);
employee.setEmpName("%曹%");
List<Employee> employees = employeeMapper.getEmpsByConditionChoose(employee);
System.out.println(employees);
}finally {
sqlSession.close();
}
}
