Mybatis中动态sql语句拼接
在选择语句where后面添加1=1与其他选择条件做连接操作
先在sql选择语句where后面添加1=1(1=1都知道是绝对能够匹配的条件)这个查询条件,再在后面做连接每个字符串前面添加and/or,这样无论进行何种查询,结果都不会报错,可以说是比较简便的方法了。具体操作示例如下:
sql语句定义(使用xml文件)
<mapper namespace="com.zxm.mybatis.dao.EmployeeMapperDynamicSQL">
<select id="getEmpDyByEmp" resultType="com.zxm.mybatis.bean.Employee">
select * from tbl_employee
where 1=1
<!-- 使用where 1=1来消除前面出现多余and的情况 -->
<if test="id != 0">
and id=#{id}
</if>
<if test="gender == 0 or gender == 2">
and gender=#{gender}
</if>
<if test="lastName!='' and lastName!=null">
and last_name like #{lastName}
</if>
</select>
</mapper>
测试代码
public SqlSessionFactory getSqlSessionFactory() {
String resource = "mybatis-config.xml";
InputStream inputStream = null;
SqlSessionFactory sqlSessionFactory = null;
try {
inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
return sqlSessionFactory;
}
@Test
public void test() {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
EmployeeMapperDynamicSQL empDy = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee emp = new Employee();
emp.setId(0);
emp.setLastName("tom");
emp.setGender("0");
List<Employee> list = empDy.getEmpDyByEmp(emp);
for(Employee e:list) {
System.out.println(e);
}
sqlSession.close();
}
使用mybatis的where标签
使用mybatis自带的where,mybatis会自动消除where标签里拼接字符串前面多余的字符串,但不会消除后面的多余的字符串,测试代码同上。
SQL语句定义
<mapper namespace="com.zxm.mybatis.dao.EmployeeMapperDynamicSQL">
<select id="getEmpDyByEmp" resultType="com.zxm.mybatis.bean.Employee">
select * from tbl_employee
<where>
<if test="id!=0">
id=#{id}
</if>
<if test="lastName!=null && lastName!=""">
and last_name like #{lastName}
</if>
<if test="email!=null and email.trim()!=""">
and email=#{email}
</if>
<if test="gender==0 or gender==2">
and gender=#{gender}
</if>
</where>
</select>
</mapper>
使用trim标签
where不能够解决拼接字符串后面有多余字符串的情况,trim可以自定义拼接形式,只要按照合理的规则,这些规则可以通过trim标签的属性来实现。
trim标签具有如下属性:
- prefix 可以设置trim标签类拼接字符串的前缀
- prefixOverrides 前缀覆盖,去掉整个字符串前面多余的字符
- suffix 后缀,给拼串后的整个字符串添加一个后缀
- suffixOverrides 后缀覆盖,去掉整个字符串后面多余的字符
SQL语句定义
<mapper namespace="com.zxm.mybatis.dao.EmployeeMapperDynamicSQL">
<select id="getEmpDyByEmp" resultType="com.zxm.mybatis.bean.Employee">
select * from tbl_employee
<trim prefix="where" suffixOverrides="and">
<if test="id!=0">
id=#{id} and
</if>
<if test="lastName!=null && lastName!=""">
last_name like #{lastName} and
</if>
<if test="email!=null and email.trim()!=""">
email=#{email} and
</if>
<if test="gender==0 or gender==2">
gender=#{gender}
</if>
</trim>
</select>
</mapper>