有的时候需要根据要查询的参数动态的拼接SQL语句
常用标签:
- if:字符判断
- choose【when...otherwise】:分支选择
- trim【where,set】:字符串截取,其中where标签封装查询条件,set标签封装修改条件
- foreach:
if案例
1)在EmployeeMapper接口文件添加一个方法
1
|
public
Student getStudent(Student student);
|
2)如果要写下列的SQL语句,只要是不为空,就作为查询条件,如下所示,这样写实际上是有问题的,所以我们要写成动态SQL语句:
1
2
3
|
<select id=
"getEmployeeByConditionIf"
resultType=
"com.neuedu.entity.Employee"
>
select *from tbl_employee where id = #{id} and user_name = #{userName} and email = #{email} and gender = #{gender}
</select>
|
3)用if标签改写为动态SQL,如下所示(官网:www.fhadmin.org):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
<select id=
"getStudent"
resultType=
"com.neuedu.mybatis.entity.Student"
>
SELECT *
FROM student
where
<
if
test=
"id != null"
>
id=#{id}
</
if
>
<
if
test=
"name !=null and name!=''"
>
and name=#{name}
</
if
>
<
if
test=
"password !=null and password !=''"
>
and password=#{password}
</
if
>
<
if
test=
"email !=null and email !=''"
>
and email=#{email}
</
if
>
</select>
|
4)测试代码(官网:www.fhadmin.org)
1
2
3
4
5
6
7
8
|
@Test
public
void
TestgetStudent(){
StudentMapper bean = ioc.getBean(StudentMapper.
class
);
Student student =
new
Student(
4
,
"jack"
,
"111"
,
"jack@qq.com"
);
System.out.println(student);
Student student2 = bean.getStudent(student);
System.out.println(student2);
}
|
#测试结果没问题,
但是仔细来说,上面的sql语句是有问题的,当我们不给动态sql语句传递id值的时候,sql语句的拼装就会有问题!(官网:www.fhadmin.org)【name前有一个and】
- where 标签
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
<select id=
"getStudent"
resultType=
"com.neuedu.mybatis.entity.Student"
>
SELECT *
FROM student
<where>
<
if
test=
"id != null"
>
id=#{id}
</
if
>
<
if
test=
"name !=null and name!=''"
>
and name=#{name}
</
if
>
<
if
test=
"password !=null and password !=''"
>
and password=#{password}
</
if
>
<
if
test=
"email !=null and email !=''"
>
and email=#{email}
</
if
>
</where>
</select>
|
3.需要注意:where标签只会去掉第一个多出来的and或者or
也就是说使用where标签有时候还是不能解决问题的,那怎么办呢?我们这里可以使用trim标签!
- trim标签:可以自定义字符串的截取规则
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
<select id=
"getStudent"
resultType=
"com.neuedu.mybatis.entity.Student"
>
SELECT *
FROM student
<trim prefix=
"where"
prefixOverrides=
"and"
>
<
if
test=
"id != null"
>
id=#{id}
</
if
>
<
if
test=
"name !=null and name!=''"
>
and name=#{name}
</
if
>
<
if
test=
"password !=null and password !=''"
>
and password=#{password}
</
if
>
<
if
test=
"email !=null and email !=''"
>
and email=#{email}
</
if
>
</trim>
</select>
|
- choose标签:分支选择,类似于Java中的带了break的switch...case
相当于确保了第一个case 符合之后,就跳出
案例演示:
1.在EmployeeMapper接口中添加一个方法
1
|
public
List<Student> getStus(Student student);
|
2.sql映射文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
<select id=
"getStus"
resultType=
"com.neuedu.mybatis.entity.Student"
>
select * from student
<where>
<choose>
<when test=
"id !=null"
>
id = #{id}
</when>
<when test=
"name !=null and name!=''"
>
name = #{name}
</when>
<when test=
"password !=null and password!=''"
>
password = #{password}
</when>
<when test=
"email !=null and email!=''"
>
email = #{email}
</when>
<otherwise>
1
=
1
</otherwise>
</choose>
</where>
</select>
|
- set标签:字符串截取,可以写在trim里面
set元素会动态前置set关键字,同时也会消除无关的逗号
1)在EmployeeMapper中添加一个更新的方法
1
|
public
void
updateStu(Student student);
|
2)在sql映射文件中,填写相应的sql语句,如下所示【set标签可以将字段后面的逗号去掉】
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
<update id=
"updateStu"
>
update student
<set>
<
if
test=
"name !=null and name!=''"
>
name=#{name},
</
if
>
<
if
test=
"password !=null and password !=''"
>
password=#{password},
</
if
>
<
if
test=
"email !=null and email !=''"
>
email=#{email}
</
if
>
</set>
where id = #{id}
</update>
|
3)测试类代码为
1
2
3
4
5
|
@Test
public
void
TestUpdateStu(){
StudentMapper bean = ioc.getBean(StudentMapper.
class
);
bean.updateStu(
new
Student(
4
,
"jackk"
,
null
,
null
));
}
|
将set标签用trim标签代替
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
<update id=
"updateStu"
>
update student
<trim prefix=
"set"
suffixOverrides=
","
>
<
if
test=
"name !=null and name!=''"
>
name=#{name},
</
if
>
<
if
test=
"password !=null and password !=''"
>
password=#{password},
</
if
>
<
if
test=
"email !=null and email !=''"
>
email=#{email}
</
if
>
</trim>
where id = #{id}
</update>
|
- foreach:遍历元素
1
|
public
List<Student> getStuByIdForEach(
@Param
(
"ids"
)List<Integer> ids);
|
2.在MyBatis的sql映射文件中写相应的代码
1
2
3
4
5
6
7
8
|
<select id=
"getStuByIdForEach"
resultType=
"com.neuedu.mybatis.entity.Student"
>
select * from student
where id
in
<foreach collection=
"ids"
item=
"id"
open=
"("
close=
")"
separator=
","
>
#{id}
</foreach>
</select>
|
3.测试类代码
1
2
3
4
5
6
7
8
9
|
@Test
public
void
getStuByIdForEach(){
StudentMapper bean = ioc.getBean(StudentMapper.
class
);
List<Integer> list = Arrays.asList(
16
,
17
,
18
,
19
);
List<Student> stuByIdForEachlist = bean.getStuByIdForEach(list);
for
(Student student : stuByIdForEachlist) {
System.out.println(student);
}
}
|
foreach标签还可以用于批量保存数据,
1.在EmployeeMapper接口类中添加批量插入的方法
1
|
public
void
insertStus(
@Param
(
"stus"
)List<Student> student);
|
2.在EmployeeMapper.xml的sql映射文件中添加响应的语句
foreach 中用 collection,collection中是从Mapper接口传来的参数,separator是去掉中间符号
1
2
3
4
5
6
|
<insert id=
"insertStus"
>
insert into student (name,password,email) values
<foreach collection=
"stus"
item=
"stu"
separator=
","
>
(#{stu.name},#{stu.password},#{stu.email})
</foreach>
</insert>
|
3.测试代码
1
2
3
4
5
6
7
8
9
|
@Test
public
void
TestInsertStus(){
StudentMapper bean = ioc.getBean(StudentMapper.
class
);
List<Student> list =
new
ArrayList<Student>();
list.add(
new
Student(
"123"
,
"123"
,
"123"
));
list.add(
new
Student(
"123"
,
"123"
,
"123"
));
list.add(
new
Student(
"123"
,
"123"
,
"123"
));
bean.insertStus(list);
}
|
MyBatis-缓存机制
一级缓存:
案例:测试一级缓存(官网:www.fhadmin.org)【默认是开启的】
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
@Test
public
void
TestFirstCache(){
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
session = sqlSessionFactory.openSession();
mapper = session.getMapper(EmployeeMapper.
class
);
Employee emp = mapper.getEmpInfoById(
4
);
System.out.println(emp);
Employee emp2 = mapper.getEmpInfoById(
4
);
System.out.println(emp2);
System.out.println(emp == emp2);
session.commit();
session.close();
}
|
一级缓存失效的情况【4种】(没有使用到当前一级缓存的情况,效果就是,还需要再向数据库发出查询)
1.sqlSession不同,重新定义SqlSession
将返回两条select语句
将返回false,说明emp2不是emp的缓存
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
@Test
public
void
TestFirstCache(){
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
session = sqlSessionFactory.openSession();
mapper = session.getMapper(EmployeeMapper.
class
);
Employee emp = mapper.getEmpInfoById(
4
);
System.out.println(emp);
SqlSession session2 = sqlSessionFactory.openSession();
EmployeeMapper mapper2 = session2.getMapper(EmployeeMapper.
class
);
Employee emp2 = mapper2.getEmpInfoById(
4
);
System.out.println(emp2);
System.out.println(emp == emp2);
session.commit();
session.close();
}
|
2.SqlSession相同,但是查询条件不一样[当前缓存中还没有这个数据]
就是相当于根据不同条件再次查找(官网:www.fhadmin.org)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
@Test
public
void
TestFirstCache(){
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
session = sqlSessionFactory.openSession();
mapper = session.getMapper(EmployeeMapper.
class
);
Employee emp = mapper.getEmpInfoById(
4
);
System.out.println(emp);
Employee emp2 = mapper.getEmpInfoById(
16
);
System.out.println(emp2);
System.out.println(emp == emp2);
session.commit();
session.close();
}
|
3.SqlSession相同,但是两次查询之间执行了增删改操作【这次增删改可能对当前数据有影响】
因为默认自动刷新了缓存
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
@Test
public
void
TestFirstCache(){
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
session = sqlSessionFactory.openSession();
mapper = session.getMapper(EmployeeMapper.
class
);
Employee emp = mapper.getEmpInfoById(
4
);
System.out.println(emp);
mapper.deleteEmp(
16
);
Employee emp2 = mapper.getEmpInfoById(
4
);
System.out.println(emp2);
System.out.println(emp == emp2);
session.commit();
session.close();
}
|
4.SqlSession相同,手动清除了一级缓存[缓存清空]
手动清除了缓存,所以得重新查找
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
@Test
public
void
TestFirstCache(){
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
session = sqlSessionFactory.openSession();
mapper = session.getMapper(EmployeeMapper.
class
);
Employee emp = mapper.getEmpInfoById(
4
);
System.out.println(emp);
session.clearCache();
Employee emp2 = mapper.getEmpInfoById(
4
);
System.out.println(emp2);
System.out.println(emp == emp2);
session.commit();
session.close();
}
|
二级缓存:
案例:
1)开启全局二级缓存配置:
1
|
<setting name=
"cacheEnabled"
value=
"true"
/>
|
2)去mapper.xml中配置使用二级缓存
1
|
<cache eviction=
"FIFO"
size=
"100"
readOnly=
"false"
/>
|
3)我们的POJO需要实现序列化接口[implements Serializable]
4)必须先关闭之前的sqlsession对象
测试:
可以看到只发送了一次SQL语句,第二次查询时从二级缓存中拿到的数据,并没有发送新的sql语句。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
@Test
public
void
TestFirstCache(){
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
session = sqlSessionFactory.openSession();
mapper = session.getMapper(EmployeeMapper.
class
);
Employee emp = mapper.getEmpInfoById(
4
);
System.out.println(emp);
session.close();
SqlSession session2 = sqlSessionFactory.openSession();
EmployeeMapper mapper2 = session2.getMapper(EmployeeMapper.
class
);
Employee emp2 = mapper2.getEmpInfoById(
4
);
System.out.println(emp2);
session2.close();
}
|
需要注意的是:只有一级缓存中关闭的情况下,二级缓存才会被使用。
需要注意的是:在哪个Mapper.xml文件中开启了<cache>缓存标签,哪个Mapper中就开启了二级缓存。