Java笔记之Mybatis(六):动态SQL
if标签
1.场景
根据学生名称查询学生信息,如果学生姓名为null或者空,就查询所有的学生信息,否则就模糊查询学生信息
2.Student.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="com.mybatis.demo.pojo"> <!-- 根据学生名字模糊查询学生信息 --> <select id="getStudentByName" parameterType="string" resultType="Student"> select * from t_student <if test="value!=null and value!=''"> where student_name like concat('%',#{value},'%') </if> </select> </mapper>
3.新建MybatisTest5类
package com.mybatis.demo.test; import java.io.IOException; import java.io.InputStream; import java.util.List; 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 com.mybatis.demo.pojo.Student; public class MybatisTest5 { public static void main(String[] args) throws IOException { //加载mybatis的配置文件 InputStream inputStream = Resources.getResourceAsStream("mybatisConfig.xml"); //获取SqlSessionFactory对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //使用SqlSessionFactory对象创建SqlSession对象 SqlSession sqlSession = sqlSessionFactory.openSession(); //名称为null String name1=null; List<Student> students1 = sqlSession.selectList("getStudentByName",name1); for (Student student : students1) { System.out.println(student.getStudentId()+"\t"+student.getStudentName()); } System.out.println("======华丽的分割线======"); //名称为空 String name2=""; List<Student> students2 = sqlSession.selectList("getStudentByName",name2); for (Student student : students2) { System.out.println(student.getStudentId()+"\t"+student.getStudentName()); } System.out.println("======华丽的分割线======"); //名称既不为null也不为空 String name3="张"; List<Student> students3 = sqlSession.selectList("getStudentByName",name3); for (Student student : students3) { System.out.println(student.getStudentId()+"\t"+student.getStudentName()); } } }
4.运行MybatisTest5类,结果如下:
2 李四 3 王五 4 赵六 5 韩七 6 小明 7 张无忌 8 张三丰 9 赵云 10 赵三 ======华丽的分割线====== 2 李四 3 王五 4 赵六 5 韩七 6 小明 7 张无忌 8 张三丰 9 赵云 10 赵三 ======华丽的分割线====== 7 张无忌 8 张三丰
where标签
1.场景
根据姓名和年龄查询学生信息,如果两个条件都为null或空,就查询所有的学生信息,如果其中一个为空或null,就只以另一个条件查询,否则两个条件同时查询
2.修改Student类的studentName字段的类型为Integer
package com.mybatis.demo.pojo; public class Student { private int studentId; private String studentName; private Integer studentAge; public int getStudentId() { return studentId; } public void setStudentId(int studentId) { this.studentId = studentId; } public String getStudentName() { return studentName; } public void setStudentName(String studentName) { this.studentName = studentName; } public Integer getStudentAge() { return studentAge; } public void setStudentAge(Integer studentAge) { this.studentAge = studentAge; } }
3.修改Student.xml添加根据学生名称和年龄查询学生信息的sql映射配置
<?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="com.mybatis.demo.pojo"> <!-- 根据学生名字模糊查询学生信息 --> <select id="getStudentByName" parameterType="string" resultType="Student"> select * from t_student <if test="value!=null and value!=''"> where student_name like concat('%',#{value},'%') </if> </select> <!-- 根据姓名和年龄查询学生信息 --> <select id="getStudentByNameAndAge" parameterType="Student" resultType="Student"> select * from t_student <where> <if test="studentName!=null and studentName!=''"> and student_name like concat('%',#{studentName},'%') </if> <if test="studentAge!=null and studentAge!=''"> and student_age=#{studentAge} </if> </where> </select> </mapper>
4.新建MybatisTest6类
package com.mybatis.demo.test; import java.io.IOException; import java.io.InputStream; import java.util.List; 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 com.mybatis.demo.pojo.Student; public class MybatisTest6 { public static void main(String[] args) throws IOException { //加载mybatis的配置文件 InputStream inputStream = Resources.getResourceAsStream("mybatisConfig.xml"); //获取SqlSessionFactory对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //使用SqlSessionFactory对象创建SqlSession对象 SqlSession sqlSession = sqlSessionFactory.openSession(); //年龄和姓名都设置值 Student student1=new Student(); student1.setStudentAge(25); student1.setStudentName("张"); List<Student> students1 = sqlSession.selectList("getStudentByNameAndAge", student1); for (Student student : students1) { System.out.println(student.getStudentName()+"\t"+student.getStudentAge()); } System.out.println("======华丽的分割线======"); //只设置年龄 Student student2=new Student(); student2.setStudentAge(25); List<Student> students2 = sqlSession.selectList("getStudentByNameAndAge", student2); for (Student student : students2) { System.out.println(student.getStudentName()+"\t"+student.getStudentAge()); } System.out.println("======华丽的分割线======"); //只设置名称 Student student3=new Student(); student3.setStudentName("张");; List<Student> students3 = sqlSession.selectList("getStudentByNameAndAge", student3); for (Student student : students3) { System.out.println(student.getStudentName()+"\t"+student.getStudentAge()); } System.out.println("======华丽的分割线======"); //都不设置 Student student4=new Student(); List<Student> students4 = sqlSession.selectList("getStudentByNameAndAge", student4); for (Student student : students4) { System.out.println(student.getStudentName()+"\t"+student.getStudentAge()); } } }
5.运行MybatisTest6类,结果如下
张无忌 25 ======华丽的分割线====== 王五 25 张无忌 25 ======华丽的分割线====== 张无忌 25 张三丰 100 ======华丽的分割线====== 李四 24 王五 25 赵六 26 韩七 27 小明 10 张无忌 25 张三丰 100 赵云 27 赵三 24
6.说明
where标签用于多条件查询;
标签里对条件进行判断,如果有条件成立,会在sql语句后拼接上where 查询条件;如果没有条件成立,就不会做任何拼接;如果有多个条件成立,除了进行拼接,还回去掉多余的and 或者or;
例如本例中,如果名称和年龄都满足if标签的条件,那么就会在sql语句后拼接上where and student_name like concat('%',#{studentName},'%') and student_age=#{studentAge} 并去除其中标红的and.
bind标签
1.场景
同where标签
2.修改Student.xml中的根据学生名称和年龄查询学生信息的sql映射配置
<?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="com.mybatis.demo.pojo"> <!-- 根据学生名字模糊查询学生信息 --> <select id="getStudentByName" parameterType="string" resultType="Student"> select * from t_student <if test="value!=null and value!=''"> where student_name like concat('%',#{value},'%') </if> </select> <!-- 根据姓名和年龄查询学生信息 --> <select id="getStudentByNameAndAge" parameterType="Student" resultType="Student"> select * from t_student <where> <if test="studentName!=null and studentName!=''"> <bind name="newName" value="'%'+studentName+'%'"/> and student_name like #{newName} </if> <if test="studentAge!=null and studentAge!=''"> and student_age=#{studentAge} </if> </where> </select> </mapper>
3.运行MybatisTest6,结果如下
张无忌 25 ======华丽的分割线====== 王五 25 张无忌 25 ======华丽的分割线====== 张无忌 25 张三丰 100 ======华丽的分割线====== 李四 24 王五 25 赵六 26 韩七 27 小明 10 张无忌 25 张三丰 100 赵云 27 赵三 24
4.说明
bind标签用来绑定值到字段上,可以直接在sql语句中使用这个字段,也可以对字符串进行拼接重新赋值;
在本例中,将名称字段值前后与%拼接,赋值给newName字段,直接在sql语句中进行模糊查询;
choose标签
1.场景
同where场景,只不过将名称和年龄都为空或null时的查询条件改为年龄大于等于25
2.修改Student.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="com.mybatis.demo.pojo"> <!-- 根据学生名字模糊查询学生信息 --> <select id="getStudentByName" parameterType="string" resultType="Student"> select * from t_student <if test="value!=null and value!=''"> where student_name like concat('%',#{value},'%') </if> </select> <!-- 根据姓名和年龄查询学生信息 --> <select id="getStudentByNameAndAge" parameterType="Student" resultType="Student"> select * from t_student <where> <choose> <when test="studentName!=null and studentName!=''"> <bind name="newName" value="'%'+studentName+'%'"/> and student_name like #{newName} <if test="studentAge!=null and studentAge!=''"> and student_age=#{studentAge} </if> </when> <when test="studentAge!=null and studentAge!=''"> and student_age=#{studentAge} </when> <otherwise> and student_age>25 </otherwise> </choose> </where> </select> </mapper>
3.运行MybatisTest6类,结果如下
张无忌 25 ======华丽的分割线====== 王五 25 张无忌 25 ======华丽的分割线====== 张无忌 25 张三丰 100 ======华丽的分割线====== 赵六 26 韩七 27 张三丰 100 赵云 27
4.说明
1.choose标签的功能类似于Java中的switch,其中的when标签类似于switch中加了break的case,otherwise类似于switch中的default;
2.在本例中的Student.xml中的choose标签下的第一个when标签里另外加了一个if判断,如果不加if判断的话,名称满足条件就不会去判断年龄是否满足条件,直接进行名称的模糊查询并返回结果,这样的话就跟只设置名称的查询结果是一样的;
3.Student.xml中如果两个when标签条件都不满足才会执行otherwise标签里的内容;
foreach标签
1.场景
根据传入的学生ID的List集合查询学生信息,如果集合为null,就查询所有的学生信息
2.修改Student.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="com.mybatis.demo.pojo"> <!-- 根据学生名字模糊查询学生信息 --> <select id="getStudentByName" parameterType="string" resultType="Student"> select * from t_student <if test="value!=null and value!=''"> where student_name like concat('%',#{value},'%') </if> </select> <!-- 根据姓名和年龄查询学生信息 --> <select id="getStudentByNameAndAge" parameterType="Student" resultType="Student"> select * from t_student <where> <choose> <when test="studentName!=null and studentName!=''"> <bind name="newName" value="'%'+studentName+'%'"/> and student_name like #{newName} <if test="studentAge!=null and studentAge!=''"> and student_age=#{studentAge} </if> </when> <when test="studentAge!=null and studentAge!=''"> and student_age=#{studentAge} </when> <otherwise> and student_age>25 </otherwise> </choose> </where> </select> <!-- 根据传入的学生ID的List集合查询学生信息 --> <select id="getStudentByIds" parameterType="list" resultType="Student"> select * from t_student <where> <if test="list!=null"> student_id in <foreach collection="list" item="id" open="(" close=")" separator=","> #{id} </foreach> </if> </where> </select> </mapper>
3.新建MybatisTest7类
package com.mybatis.demo.test; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; 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 com.mybatis.demo.pojo.Student; public class MybatisTest7 { public static void main(String[] args) throws IOException { //加载mybatis的配置文件 InputStream inputStream = Resources.getResourceAsStream("mybatisConfig.xml"); //获取SqlSessionFactory对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //使用SqlSessionFactory对象创建SqlSession对象 SqlSession sqlSession = sqlSessionFactory.openSession(); List<Integer> ids=new ArrayList<Integer>(); ids.add(2); ids.add(6); ids.add(8); ids.add(10); List<Student> students = sqlSession.selectList("getStudentByIds",ids); for (Student student : students) { System.out.println(student.getStudentId()+"\t"+student.getStudentName()+"\t"+student.getStudentAge()); } System.out.println("======华丽的分割线======"); List<Student> students2 = sqlSession.selectList("getStudentByIds",null); for (Student student : students2) { System.out.println(student.getStudentId()+"\t"+student.getStudentName()+"\t"+student.getStudentAge()); } } }
4.运行MybatisTest7类,结果如下
2 李四 24 6 小明 10 8 张三丰 100 10 赵三 24 ======华丽的分割线====== 2 李四 24 3 王五 25 4 赵六 26 5 韩七 27 6 小明 10 7 张无忌 25 8 张三丰 100 9 赵云 27 10 赵三 24
5.说明
foreach标签一般是用在sql语句的in语法部分;
set标签
1.场景
根据ID修改学生信息,如果字段的值为null就不进行设置修改
2.修改Student.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="com.mybatis.demo.pojo"> <!-- 根据学生名字模糊查询学生信息 --> <select id="getStudentByName" parameterType="string" resultType="Student"> select * from t_student <if test="value!=null and value!=''"> where student_name like concat('%',#{value},'%') </if> </select> <!-- 根据姓名和年龄查询学生信息 --> <select id="getStudentByNameAndAge" parameterType="Student" resultType="Student"> select * from t_student <where> <choose> <when test="studentName!=null and studentName!=''"> <bind name="newName" value="'%'+studentName+'%'"/> and student_name like #{newName} <if test="studentAge!=null and studentAge!=''"> and student_age=#{studentAge} </if> </when> <when test="studentAge!=null and studentAge!=''"> and student_age=#{studentAge} </when> <otherwise> and student_age>25 </otherwise> </choose> </where> </select> <!-- 根据传入的学生ID的List集合查询学生信息 --> <select id="getStudentByIds" parameterType="list" resultType="Student"> select * from t_student <where> <if test="list!=null"> student_id in <foreach collection="list" item="id" open="(" close=")" separator=","> #{id} </foreach> </if> </where> </select> <!-- 根据Id修改学生信息 --> <update id="updateStudentById" parameterType="Student"> update t_student <set> <if test="studentName!=null">student_name=#{studentName},</if> <if test="studentAge!=null">student_age=#{studentAge}</if> </set> where student_id=#{studentId} </update> </mapper>
3.新建MybatisTest8类
package com.mybatis.demo.test; import java.io.IOException; import java.io.InputStream; import java.util.List; 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 com.mybatis.demo.pojo.Student; public class MybatisTest8 { public static void main(String[] args) throws IOException { //加载mybatis的配置文件 InputStream inputStream = Resources.getResourceAsStream("mybatisConfig.xml"); //获取SqlSessionFactory对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //使用SqlSessionFactory对象创建SqlSession对象 SqlSession sqlSession = sqlSessionFactory.openSession(); System.out.println("======修改之前======"); List<Student> students1 = sqlSession.selectList("getStudentByIds",null); for (Student student2 : students1) { System.out.println(student2.getStudentId()+"\t"+student2.getStudentName()+"\t"+student2.getStudentAge()); } System.out.println("======开始修改======"); Student student=new Student(); student.setStudentId(2); student.setStudentName("李逵"); sqlSession.update("updateStudentById", student); sqlSession.commit(); System.out.println("======修改之后======"); List<Student> students2 = sqlSession.selectList("getStudentByIds",null); for (Student student2 : students2) { System.out.println(student2.getStudentId()+"\t"+student2.getStudentName()+"\t"+student2.getStudentAge()); } sqlSession.close(); } }
4.运行MybatisTest8类,结果如下
======修改之前====== 2 李四 24 3 王五 25 4 赵六 26 5 韩七 27 6 小明 10 7 张无忌 25 8 张三丰 100 9 赵云 27 10 赵三 24 ======开始修改====== ======修改之后====== 2 李逵 24 3 王五 25 4 赵六 26 5 韩七 27 6 小明 10 7 张无忌 25 8 张三丰 100 9 赵云 27 10 赵三 24
trim标签
1.概念
trim标签可以用来定制功能,例如:
where标签可以改为:
<trim prefix="where" prefixOverrides="and |or "></trim>
意思就是指对trim标签之间的sql语句来说,如果有条件成立,在这些sql语句之前先拼接where,再拼接这些sql语句,最后把这些sql语句之前多余的and或者or去掉.
set标签可以改为:
<trim prefix="set" suffixOverrides=","></trim>
意思就是指对trim标签之间的sql语句来说,如果有条件成立,在这些sql语句之前先拼接set,再拼接这些sql语句,最后把这些sql语句之后多余的逗号,去掉.
2.修改Student.xml,将id为getStudentByIds的where标签和id为updateStudentById的set标签做了trim替换
<?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="com.mybatis.demo.pojo"> <!-- 根据学生名字模糊查询学生信息 --> <select id="getStudentByName" parameterType="string" resultType="Student"> select * from t_student <if test="value!=null and value!=''"> where student_name like concat('%',#{value},'%') </if> </select> <!-- 根据姓名和年龄查询学生信息 --> <select id="getStudentByNameAndAge" parameterType="Student" resultType="Student"> select * from t_student <where> <choose> <when test="studentName!=null and studentName!=''"> <bind name="newName" value="'%'+studentName+'%'"/> and student_name like #{newName} <if test="studentAge!=null and studentAge!=''"> and student_age=#{studentAge} </if> </when> <when test="studentAge!=null and studentAge!=''"> and student_age=#{studentAge} </when> <otherwise> and student_age>25 </otherwise> </choose> </where> </select> <!-- 根据传入的学生ID的List集合查询学生信息 --> <select id="getStudentByIds" parameterType="list" resultType="Student"> select * from t_student <trim prefix="where" prefixOverrides="and |or "> <if test="list!=null"> student_id in <foreach collection="list" item="id" open="(" close=")" separator=","> #{id} </foreach> </if> </trim> </select> <!-- 根据Id修改学生信息 --> <update id="updateStudentById" parameterType="Student"> update t_student <trim prefix="set" suffixOverrides=","> <if test="studentName!=null">student_name=#{studentName},</if> <if test="studentAge!=null">student_age=#{studentAge}</if> </trim> where student_id=#{studentId} </update> </mapper>
3.将数据库中id=2的学生名称改回"李四",运行MybatisTest7类,结果如下,与where标签的一样
2 李四 24 6 小明 10 8 张三丰 100 10 赵三 24 ======华丽的分割线====== 2 李四 24 3 王五 25 4 赵六 26 5 韩七 27 6 小明 10 7 张无忌 25 8 张三丰 100 9 赵云 27 10 赵三 24
4.运行MybatisTest8类,结果如下,与set标签的一样
======修改之前====== 2 李四 24 3 王五 25 4 赵六 26 5 韩七 27 6 小明 10 7 张无忌 25 8 张三丰 100 9 赵云 27 10 赵三 24 ======开始修改====== ======修改之后====== 2 李逵 24 3 王五 25 4 赵六 26 5 韩七 27 6 小明 10 7 张无忌 25 8 张三丰 100 9 赵云 27 10 赵三 24
