有的时候需要根据要查询的参数动态的拼接SQL语句
常用标签:
- if:字符判断
- choose【when...otherwise】:分支选择
- trim【where,set】:字符串截取,其中where标签封装查询条件,set标签封装修改条件
- foreach:
if案例
1)在EmployeeMapper接口文件添加一个方法
public Student getStudent(Student student);
2)如果要写下列的SQL语句,只要是不为空,就作为查询条件,如下所示,这样写实际上是有问题的,所以我们要写成动态SQL语句:
<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,如下所示:
<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)测试代码
@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语句的拼装就会有问题!【name前有一个and】
- where 标签
<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标签:可以自定义字符串的截取规则
<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>
相当于确保了第一个case 符合之后,就跳出
案例演示:
1.在EmployeeMapper接口中添加一个方法
public List<Student> getStus(Student student);
2.sql映射文件
<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中添加一个更新的方法
public void updateStu(Student student);
2)在sql映射文件中,填写相应的sql语句,如下所示【set标签可以将字段后面的逗号去掉】
<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)测试类代码为
@Test public void TestUpdateStu(){ StudentMapper bean = ioc.getBean(StudentMapper.class); bean.updateStu(new Student(4, "jackk", null, null)); }
将set标签用trim标签代替
<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:遍历元素
public List<Student> getStuByIdForEach(@Param("ids")List<Integer> ids);
2.在MyBatis的sql映射文件中写相应的代码
<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.测试类代码
@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接口类中添加批量插入的方法
public void insertStus(@Param("stus")List<Student> student);
2.在EmployeeMapper.xml的sql映射文件中添加响应的语句
foreach 中用 collection,collection中是从Mapper接口传来的参数,separator是去掉中间符号
<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.测试代码
@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-缓存机制
一级缓存:
案例:测试一级缓存【默认是开启的】
@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的缓存
@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相同,但是查询条件不一样[当前缓存中还没有这个数据]
就是相当于根据不同条件再次查找
@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相同,但是两次查询之间执行了增删改操作【这次增删改可能对当前数据有影响】
因为默认自动刷新了缓存
@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相同,手动清除了一级缓存[缓存清空]
手动清除了缓存,所以得重新查找
@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)开启全局二级缓存配置:
<setting name="cacheEnabled" value="true"/>
2)去mapper.xml中配置使用二级缓存
<cache eviction="FIFO" size="100" readOnly="false"/>
3)我们的POJO需要实现序列化接口[implements Serializable]
4)必须先关闭之前的sqlsession对象
测试:
可以看到只发送了一次SQL语句,第二次查询时从二级缓存中拿到的数据,并没有发送新的sql语句。
@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中就开启了二级缓存。