前言
在编译阶段,就可以确定的SQL语句称为静态SQL;在程序运行阶段,根据条件不同才能确定下来的SQL叫动态SQL。如:学生表数据查询时,查询条件有年龄、姓名、专业、班级等,SQL语句条件根据当前的使用场景可能是这些条件的任意组合。Mybaits提供了一种基于OGNL(对象导航图语言)的表达式来完成动态SQL,极大方便了动态sql编程。
实验目的
掌握基于OGNL表达式实现动态SQL的使用
实验内容
以查询学生信息为例,学习if、choose(when、otherwise)、where、trim、set、forEach、bind等元素的使用。
实验步骤
1. 创建学生表及学生信息维护,学生表结构如下:
字段名称 | 字段代码 | 数据类型 | 备注 |
学号 | sno | integer | 主键自增 |
学生姓名 | student_name | varchar(50) | |
年龄 | student_age | integer |
2. 使用maven创建控制台工程和环境搭建(参照实验2.1)
3. 实验准备
- 创建实体类,代码如下:
@Data
public class Student {
private Integer sno;
private String studentName;
private Integer studentAge;
}
- 创建接口文件
public interface StudentDao {
List<StudentEntity> getStudentByCondition(Map<String,Object> param);
List<StudentEntity> getStudentByCondition2(Map<String,Object> param);
int UpdateStudentById(StudentEntity student);
int insertStudent(StudentEntity student);
int insertBatch(@Param("students") List<StudentEntity> students);
List<StudentEntity> getStudentByBind(StudentEntity student);
}
3. 动态SQL主要元素的使用
- IF 元素,用于单条分支判断,如:根据学生的姓名、年龄的进行组合查询,示例代码如下
<select id="getStudentByCondition" resultType="com.bjwl.pojo7.StudentEntity">
select * from tb_student where 1=1
<if test="name !=null and name !=''">
and student_name like concat('%',#{name},'%')
</if>
<if test="age !=null and age !=''">
and student_age =#{age}
</if>
</select>
测试代码如下所示
public void testIf() throws IOException {
SqlSession sqlSession = BatisUtils.getSqlSessionFactory().openSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
Map<String,Object> map = new HashMap<>();
map.put("name","张");
map.put("age",null);
List<StudentEntity> students = dao.getStudentByCondition(map);
for (StudentEntity student:students){
System.out.println(student.toString());
}
}
运行结果如下
- Choose(when otherwise)元素:用于多条分支判断,只执行某一条分支,如:根据学生的姓名、年龄的进行组合查询,示例代码如下
<select id="getStudentByCondition2" resultMap="com.bjwl.pojo7.StudentEntity">
select * from tb_student where 1=1
<choose>
<when test="name !=null and name !=''">
and student_name like concat('%',#{name},'%')
</when>
<when test="age !=null and age !=''">
and student_age =#{age}
</when>
<otherwise>
and student_name = '王小丫'
</otherwise>
</choose>
</select>
测试代码如下:
public void testChoose() throws IOException {
SqlSession sqlSession = BatisUtils.getSqlSessionFactory().openSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
Map<String,Object> map = new HashMap<>();
/*map.put("name","王");
map.put("age",18);*/
/*屏蔽map中的name项试试 比较和两条if语句的区别*/
List<StudentEntity> students = dao.getStudentByCondition2(map);
for (StudentEntity student:students){
System.out.println(student.toString());
}
}
运行结果如下:
- 辅助元素Where,myBaits提供辅助元素Where,当查询带条件时自动加where,不带条件时自动去掉where,代码如下所示
<select id="getStudentByCondition3" resultType="com.bjwl.pojo7.StudentEntity">
select * from tb_student
<where>
<if test="sname !=null and sname !=''">
and student_name like concat('%',#{sname},'%')
</if>
<if test="age !=null and age !=''">
and student_age =#{age}
</if>
</where>
</select>
- 辅助元素trim的使用:使用trim自动的增加或者取消多余的字符,用于去除sql语句中多余的and关键字,逗号
<insert id="insertStudent">
insert into tb_student
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="studentName != null">
student_name,
</if>
<if test="studentAge != null">
student_age
</if>
</trim>
<trim prefix="values(" suffix=")" suffixOverrides=",">
<if test="studentName != null">
#{studentName},
</if>
<if test="studentAge != null">
#{studentAge}
</if>
</trim>
</insert>
Trim元素的有prefix、prefixOverrides、suffix、suffixOverrides四个属性,prefix开头添加一个,prefixOverrides开头去掉一个,suffix结尾添加一个,suffixOverrides结尾去掉一个,如代码的第3、12行,增加前缀和后缀,去掉多余的逗号。
测试代码如下
public void testTrim2() throws IOException {
SqlSession sqlSession = BatisUtils.getSqlSessionFactory().openSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
StudentEntity student = new StudentEntity();
student.setStudentName(null);
student.setStudentAge(30);
dao.insertStudent(student);
sqlSession.commit();
/*去掉age的赋值,试试 查看sql语句,是不是没有了“,”*/
/* List<StudentEntity> students = dao.getStudentByCondition3("任我行",null);*/
List<StudentEntity> students = dao.getStudentByCondition3(null,30);
for (StudentEntity stu:students){
System.out.println(stu.toString());
}
}
运行结果
- 辅助元素set的使用: 在update语句中需要使用set语句完成对数据表中的字段进行更新,并能够自动去除多余的逗号,示例代码如下。
<update id="UpdateStudentById">
update tb_student
<set>
<if test="studentName!=null">
student_name=#{studentName},
</if>
<if test="studentAge!=null">
student_age=#{studentAge},
</if>
</set>
where sno=#{sno}
</update>
省略测试类和执行结果
- Foreach元素的使用:循环语句的使用,通常使用在in语句环境中、批量插入、批量更新等语句中。其中:item是当前循环中元素;index当前元素所在位置下标;collection是传递过来的参数类型;open和close是使用什么样的符号,将这些元素包裹起来;separator是元素之间的间隔符号。如:批量增加学生信息,映射文件代码如下
<insert id="insertBatch">
insert into tb_student(student_name,student_age)
values
<foreach collection="students" item="student" index="index" separator=",">
(#{student.studentName},#{student.studentAge})
</foreach>
</insert>
测试代码如下:
public void testExecutorTypeBatch() throws IOException {
SqlSession sqlSession = BatisUtils.getSqlSessionFactory().openSession(ExecutorType.BATCH);
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<StudentEntity> students = new ArrayList<>();
for (int i = 0; i <5 ; i++) {
StudentEntity student=new StudentEntity();
student.setStudentName("王大锤");
student.setStudentAge(18);
students.add(student);
}
dao.insertBatch(students);
sqlSession.commit();
sqlSession.close();
}
运行界面如下图所示
- bind元素的使用:用来在映射文件中定义变量,该变量的值为参数的值和拼接一些其他字符串后组成的字符串。如:使用学生姓名进行查询时,使用bind元素,查询名为“小凡”的同学,其映射文件代码如下所示
<select id="getStudentByBind" resultType="com.bjwl.pojo7.StudentEntity" >
<bind name="name" value="'%'+studentName"/>
select * from tb_student where student_name like #{name}
</select>
测试代码如下
public void testBind() throws IOException {
SqlSession sqlSession = BatisUtils.getSqlSessionFactory().openSession(ExecutorType.BATCH);
StudentDao dao = sqlSession.getMapper(StudentDao.class);
StudentEntity student = new StudentEntity();
student.setStudentName("小凡");
List<StudentEntity> stus = dao.getStudentByBind(student);
for (StudentEntity stu : stus) {
System.out.println(stu.toString());
}
sqlSession.close();
}
运行结果如下: