直接使用if 标签时,由于只要满足条件就直接拼接,所以很容易引起sql语法错误。
以where 标签为例,where标签里面包含一个或者多个if标签,至少一个test条件成立时,就会在sql语句后面加 where 关键字并拼接语句,test均不成立,则不添加 where 关键字,并且会自动删除紧跟在 where 关键字后面的 and 以及 or 使得 sql 语句正确。
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<!--配置数据源:创建Connection对象-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/bjpowernode?useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="admin"/>
</dataSource>
</environment>
</environments>
<!--指定其他mapper文件的位置
找到其他mapper文件的目的是找到sql语句
使用注意:
resource="mapper.文件的路径,用/分割"-->
<mappers>
<mapper resource="Dao/StudentDao.xml"/>
</mappers>
</configuration>
StudentDao
package Dao;
import domain.Student;
public interface StudentDao {
//where标签
List<Student> selectWhere(Student st);
}
StudentDao.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="Dao.StudentDao">
<!--动态sql
使用if标签时,由于只要满足条件就直接拼接
所以很容易引起sql语法错误。
where标签里面包含一个或多个if标签
至少一个test条件成立时,就在sql语句后加where关键字并拼接语句
test均不成立,则不添加where关键字
并且会自动删除紧跟在where关键字后面的and 和 or使得sql语句正确
-->
<select id="selectWhere" resultType="domain.Student">
select name,age from student
<where>
<if test="age > 0">or age = #{age}</if>
<if test="name !=null">or name =#{name}</if>
</where>
</select>
</mapper>
实体类Student
package domain;
public class Student {
private Integer id;
private String name;
private String email;
private Integer age;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "学生实体{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", age=" + age +
'}';
}
}
工具类MyBatisUtil
package util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class MyBatisUtil {
//获得SqlSessionFactory
public static SqlSessionFactory sqlSessionFactory;
static{
String config="mybatis-config.xml ";
try {
InputStream inputStream = Resources.getResourceAsStream(config);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
//获得sqlSession
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
Test类
import Dao.StudentDao;
import domain.Student;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
public class MyTest {
@Test
//where标签
public void testSelectWhere(){
//获取sqlSession
SqlSession sqlSession = MyBatisUtil.getSqlSession();
//获取dao实现类
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
//调用接口中的方法
Student st=new Student();
st.setAge(19);
st.setName("张磊");
List<Student> list= mapper.selectWhere(st);
//lambda表达式,遍历集合
list.forEach(stu-> System.out.println(stu));
sqlSession.close();
}
}
测试结果
学生实体{id=null, name='张磊', email='null', age=23}
学生实体{id=null, name='陆启赛', email='null', age=19}