动态 SQL
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事,但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言,MyBatis显著地提升了这一特性的易用性。
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器,你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中,需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。
if
choose (when, otherwise)
trim (where, set)
foreach
一、MyBatis动态语句分为4种元素:
MyBatis的动态SQL语句是基于OGNL表达式的。可以方便的在SQL语句中实现某些逻辑,总体说来MyBatis动态SQL语句主要有以下几类:
⭐ if语句(简单的条件判断)。
⭐ choose(when,otherwize),相当于Java语言中的switch,与JSTL中的choose很类似。
⭐ trim(对包含的内容加上prefix,或者suffix等,前缀,后缀)。
⭐ where(主要是用来简化SQL语句中where条件判断的,能智能的处理and or,不必担心多余导致语法错误)。
⭐ set(主要用于更新时)。
⭐ foreach(在实现MyBatis in语句查询时特别有用)。
下面用介绍几种方式:
依据数据库创建class类,student类
ClassInfo
package cn.yyj1.entity;
import java.util.List;
public class ClassInfo {
private int classid;
private String className;
private List<StudentInfo> studentInfoList;
public List<StudentInfo> getStudentInfoList() {
return studentInfoList;
}
public void setStudentInfoList(List<StudentInfo> studentInfoList) {
this.studentInfoList = studentInfoList;
}
public int getClassid() {
return classid;
}
public void setClassid(int classid) {
this.classid = classid;
}
public String getClassName() {
return className;
}
public void setClassName(String className) {
this.className = className;
}
public ClassInfo(int classid, String className) {
this.classid = classid;
this.className = className;
}
public ClassInfo() {
}
}
StudentInfo类
package cn.yyj1.entity;
public class StudentInfo {
private String studentid;
private String studentName;
private String studentSex;
private String studentPhone;
private String studentAddress;
private int studentAge;
private int stuclassid;
//私有的 类型 名字
//只有空的构造方法 才能查到className 的信息
private ClassInfo classInfo;
public String getStudentid() {
return studentid;
}
public void setStudentid(String studentid) {
this.studentid = studentid;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public String getStudentSex() {
return studentSex;
}
public void setStudentSex(String studentSex) {
this.studentSex = studentSex;
}
public String getStudentPhone() {
return studentPhone;
}
public void setStudentPhone(String studentPhone) {
this.studentPhone = studentPhone;
}
public String getStudentAddress() {
return studentAddress;
}
public void setStudentAddress(String studentAddress) {
this.studentAddress = studentAddress;
}
public int getStudentAge() {
return studentAge;
}
public void setStudentAge(int studentAge) {
this.studentAge = studentAge;
}
public int getStuclassid() {
return stuclassid;
}
public void setStuclassid(int stuclassid) {
this.stuclassid = stuclassid;
}
public ClassInfo getClassInfo() {
return classInfo;
}
public void setClassInfo(ClassInfo classInfo) {
this.classInfo = classInfo;
}
public StudentInfo(String studentid, String studentName, String studentSex, String studentPhone, String studentAddress, int studentAge, int stuclassid, ClassInfo classInfo) {
this.studentid = studentid;
this.studentName = studentName;
this.studentSex = studentSex;
this.studentPhone = studentPhone;
this.studentAddress = studentAddress;
this.studentAge = studentAge;
this.stuclassid = stuclassid;
this.classInfo = classInfo;
}
public StudentInfo(String studentName, String studentSex, String studentPhone, String studentAddress, int studentAge, int stuclassid, ClassInfo classInfo) {
this.studentName = studentName;
this.studentSex = studentSex;
this.studentPhone = studentPhone;
this.studentAddress = studentAddress;
this.studentAge = studentAge;
this.stuclassid = stuclassid;
this.classInfo = classInfo;
}
public StudentInfo() {
}
}
创建mapper类 和mapper.xml
package cn.yyj1.mapper;
import cn.yyj1.entity.ClassInfo;
import cn.yyj1.entity.StudentInfo;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface StudentInfoMapper {
//根据班级编号查询所属班级的所有学生信息 id 班级编号 所属这个班级的学生
public List<StudentInfo> findStuByClassId(int id);
//根据班级编号查询班级信息 id 班级信息 班级信息
public ClassInfo findClassByClassId(int id);
//根据班级编号查询所属姓名的学生
public List<StudentInfo> findStudNameByClassId(@Param("stuclassid") int stuclassid, @Param("studentName") String studentName);
//根据ID修改学生信息
public int update (StudentInfo si);
public StudentInfo findStudentById(String id);
//根据数组查询所有学生信息 定义数组 int [] array
public List<StudentInfo> findStudentByArray(int [] array);
//根据集合查学生信息
public List<StudentInfo> findStudentByList(List<StudentInfo> list);
//根据Map查询
public List<StudentInfo> findStudentByMap(Map<String,Object> map);
//Choose
public List<StudentInfo> findStudentByChoose(@Param("studentName")String studentName,@Param("studentAge")int studentAge,@Param("stuclassid")int stuclassid);
//分页
public List<StudentInfo> findStudentByPage(@Param("pageSize")int pageSize,@Param("pageCode")int pageCode);
}
mapper.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.xml 中的namespace 设置为 mapper.java 的全限定名。 -->
<!-- 该映射文件取代了dao的实现类
namespace是指要实现的接口的全限定名 -->
<mapper namespace="cn.yyj1.mapper.StudentInfoMapper">
<!--test1-->
<resultMap id="s" type="ClassInfo">
<result property="classid" column="classid"></result>
<result property="className" column="className"></result>
</resultMap>
<!--配置结果的类型 类型是StudentInfo id起个名字 -->
<resultMap id="stulist" type="StudentInfo">
<!--配置结果 property 实体类的属性是什么————对应——column 这个类在数据库表中所对应的字段 (害怕两个类属性不一样) -->
<result property="studentid" column="studentid"></result>
<result property="studentName" column="studentName"></result>
<result property="studentSex" column="studentSex"></result>
<result property="studentPhone" column="studentPhone"></result>
<result property="studentAddress" column="studentAddress"></result>
<result property="studentAge" column="studentAge"></result>
<result property="stuclassid" column="stuclassid"></result>
<!--association (关联 联合) 多对一 resultMap 与班级表 id 对应一致-->
<association property="classInfo" javaType="ClassInfo" resultMap="s"></association>
</resultMap>
<!--根据学生表的classid和stuclassid 查询 学生表的姓名-->
<select id="findStuByClassId" parameterType="int" resultMap="stulist">
select s.*,c.className from student s,classinfo c where s.stuclassid=c.classid and s.stuclassid=#{stuclassid}
</select>
<!--test2-->
<!--班级和学生的关系 一对多的关系 用collection-->
<!--一对多时,对象成员使用collection映射 , javaType指定该属性类型 , ofType指属性的元素类型-->
<resultMap id="classes" type="ClassInfo">
<result property="classid" column="classid"></result>
<result property="className" column="className"></result>
<!--在班级里面他的属性叫studentInfoList -->
<collection property="studentInfoList" ofType="StudentInfo">
<result property="studentid" column="studentid"></result>
<result property="studentName" column="studentName"></result>
<result property="studentSex" column="studentSex"></result>
<result property="studentPhone" column="studentPhone"></result>
<result property="studentAddress" column="studentAddress"></result>
<result property="studentAge" column="studentAge"></result>
<result property="stuclassid" column="stuclassid"></result>
</collection>
<!--sql语句 select * from classinfo where classid=#{classid} 班级表中没有学生-->
</resultMap>
<select id="findClassByClassId" parameterType="int" resultMap="classes">
select s.*,c.className from student s,classinfo c where s.stuclassid=c.classid and c.classid=#{classid}
</select>
<!--test3-->
<!--返回值类型resultType StudentInfo -->
<select id="findStudNameByClassId" parameterType="int" resultType="StudentInfo">
SELECT * FROM student
<where>
<trim suffix="" suffixOverrides="and">
<if test="stuclassid!=null">
stuclassid=#{stuclassid}
</if>
<if test="studentName!=null">
and studentName LIKE concat(concat('%',#{studentName}),'%')
</if>
</trim>
</where>
</select>
<!--test4-->
<select id="findStudentById" resultType="StudentInfo" parameterType="String">
SELECT * FROM student WHERE studentid=#{studentid}
</select>
<!--修改 -->
<update id="update" parameterType="StudentInfo">
UPDATE student
/**/
<trim suffix="where studentid=#{studentid}" suffixOverrides=",">
<set>
<if test="studentName!=null">studentName=#{studentName},</if>
<if test="studentSex!=null">studentSex=#{studentSex},</if>
<if test="studentPhone!=null">studentPhone=#{studentPhone},</if>
<if test="studentAddress!=null">studentAddress=#{studentAddress},</if>
<if test="studentAge!=null">studentAge=#{studentAge},</if>
<if test="stuclassid!=null">stuclassid=#{stuclassid},</if>
</set>
</trim>
</update>
<!--test5 根据数组查询所有学生信息-->
<select id="findStudentByArray" resultType="StudentInfo">
SELECT * from student WHERE stuclassid IN
<foreach collection="array" item="stuclassid" open="(" separator=", " close=")">
#{stuclassid}
</foreach>
</select>
<!--test6 根据list集合查询学生信息-->
<select id="findStudentByList" resultType="StudentInfo">
SELECT * from student WHERE stuclassid IN
<foreach collection="list" item="stuclassid" open="(" separator="," close=")">
#{stuclassid}
</foreach>
</select>
<!--test7-->
<select id="findStudentByMap" resultType="StudentInfo">
SELECT * FROM student where studentName LIKE concat(concat('%',#{studentName}),'%') and stuclassid in
<foreach collection="list" item="map" open="(" separator="," close=")">
#{map}
</foreach>
</select>
<!--test8 String 类型不为空 int类型不等于零 -->
<select id="findStudentByChoose" resultType="StudentInfo">
SELECT * FROM student where 1=1
<choose>
<when test="studentName!=null ">
AND studentName LIKE concat(concat('%',#{studentName}),'%')
</when>
<when test="studentAge!=0">
AND studentAge>#{studentAge}
</when>
<otherwise>
and stuclassid=#{stuclassid}
</otherwise>
</choose>
</select>
<!--tset9 分页-->
<select id="findStudentByPage" resultType="StudentInfo">
SELECT * FROM student
<if test="pageCode!=0">
limit #{pageSize},#{pageCode}
</if>
</select>
</mapper>
创建工具类:
package cn.yyj1.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 {
//加载mybatis配置文件
//创建sqlsessionfactory对象
//创建sqlsession连接对象
//创建关闭sqlsession连接对象
//mybatis 数据库查询
private static SqlSessionFactory sessionFactory;
static{
String resource="mybatis1.xml";
try {
InputStream is= Resources.getResourceAsStream(resource);
sessionFactory=new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static SqlSession getSession(){
return sessionFactory.openSession();
}
public static void closeSession(SqlSession session){
if(session!=null){
session.close();
}
}
}
连接数据库的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>
<typeAliases>
<!--所有类型都在包entity里面 所以在mapper.xml只用写包里面的的内容就行了-->
<package name="cn.yyj1.entity"></package>
</typeAliases>
<!-- 设置一个默认的连接环境信息 -->
<environments default="aa">
<!-- 连接环境信息,取一个任意唯一的名字 -->
<environment id="aa">
<!-- mybatis使用jdbc事务管理方式 -->
<transactionManager type="JDBC"></transactionManager>
<!-- mybatis使用连接池方式来获取连接 -->
<dataSource type="POOLED">
<!-- 配置与数据库交互的4个必要属性 -->
<property name="driver" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/school2"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</dataSource>
</environment>
</environments>
<!--配置接口所对应的xml文件-->
<!-- 加载映射文件-->
<mappers>
<mapper resource="cn/yyj1/mapper/StudentInfoMapper.xml"></mapper>
</mappers>
</configuration>
测试类:
package cn.yyj1.test;
import cn.yyj1.entity.ClassInfo;
import cn.yyj1.entity.StudentInfo;
import cn.yyj1.mapper.StudentInfoMapper;
import cn.yyj1.util.MybatisUtil;
import com.sun.scenario.effect.impl.sw.sse.SSEBlend_SRC_OUTPeer;
import org.apache.ibatis.session.SqlSession;
import java.io.ObjectStreamClass;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class Test {
public static void main(String[] args) {
// test1();
// test2();
// test3();
// test4();
// test5();
// test6();
// test7();
// test8();
test9();
}
private static void test9() {
SqlSession session = MybatisUtil.getSession();
int pageSize=2;int pageCode=2;
int before=(pageCode-1)*pageSize;
int after=pageSize;
List<StudentInfo> LIST=session.getMapper(StudentInfoMapper.class).findStudentByPage(before,after);
System.out.println(LIST);
for (StudentInfo s:LIST){
System.out.println(s.getStudentName()+"\t"+s.getStuclassid());
}
}
private static void test8() {
//Choose 等于java中的 Switch 语句 第一次直接跳入名字中含有 “赵” 的
// SqlSession session = MybatisUtil.getSession();
// List<StudentInfo> list=session.getMapper(StudentInfoMapper.class).findStudentByChoose("赵",20,1);
// for (StudentInfo l:list){
// System.out.println(l.getStudentName()+"\t"+l.getStuclassid()+"\t"+l.getStudentAge());
// }
//第二次跳入年龄大于20岁的
SqlSession session = MybatisUtil.getSession();
List<StudentInfo> list=session.getMapper(StudentInfoMapper.class).findStudentByChoose(null,20,1);
System.out.println(list);
for (StudentInfo l:list){
System.out.println(l.getStudentName()+"\t"+l.getStuclassid()+"\t"+l.getStudentAge());
}
//第三次进入 班级 id 为 1 的
// SqlSession session = MybatisUtil.getSession();
// List<StudentInfo> list=session.getMapper(StudentInfoMapper.class).findStudentByChoose(null,0,1);
// System.out.println(list);
// for (StudentInfo l:list){
// System.out.println(l.getStudentName()+"\t"+l.getStuclassid()+"\t"+l.getStudentAge());
// }
}
private static void test7() {
SqlSession session = MybatisUtil.getSession();
Map<String,Object> map = new HashMap<String,Object>();
List list1 = new ArrayList();
list1.add(1);
list1.add(2);
map.put("studentName","赵");
map.put("list",list1);
List<StudentInfo> li=session.getMapper(StudentInfoMapper.class).findStudentByMap(map);
for (StudentInfo a:li){
System.out.println(a.getStudentName()+"\t"+a.getStuclassid());
}
}
private static void test6() {
SqlSession session = MybatisUtil.getSession();
//新建一个list集合 命名为list1
ArrayList list1 = new ArrayList();
//向list1 集合中添加数据
list1.add(2);
list1.add(3);
//遍历获取 打印输出
List<StudentInfo> list=session.getMapper(StudentInfoMapper.class).findStudentByList(list1);
for (StudentInfo i:list){
System.out.println(i.getStudentName()+"\t"+i.getStuclassid());
}
}
private static void test5() {
SqlSession session = MybatisUtil.getSession();
int[] array={1,2};
List<StudentInfo> list=session.getMapper(StudentInfoMapper.class).findStudentByArray(array);
for (StudentInfo i:list){
System.out.println(i.getStudentName()+"\t"+i.getStuclassid());
}
}
private static void test4() {
SqlSession session = MybatisUtil.getSession();
//先查找ID 再给Id更改
StudentInfo a=session.getMapper(StudentInfoMapper.class).findStudentById("s123452");
a.setStudentName("张大东");
int b=session.getMapper(StudentInfoMapper.class).update(a);
if (b>0){
System.out.println("ok");
}
session.commit();
}
private static void test3() {
SqlSession session = MybatisUtil.getSession();
List<StudentInfo> list=session.getMapper(StudentInfoMapper.class).findStudNameByClassId(1,"赵");
for (StudentInfo i:list){
System.out.println(i.getStudentName());
}
}
private static void test2() {
SqlSession session = MybatisUtil.getSession();
ClassInfo list=session.getMapper(StudentInfoMapper.class).findClassByClassId(2);
System.out.println(list.getClassName());
List<StudentInfo> lists=list.getStudentInfoList();
for (StudentInfo u:lists){
System.out.println(u.getStudentName());
}
}
private static void test1() {
SqlSession session = MybatisUtil.getSession();
//遍历集合 输出
List<StudentInfo> list=session.getMapper(StudentInfoMapper.class).findStuByClassId(1);
System.out.println(list.size());
for (StudentInfo u:list){
System.out.println(u.getStudentName()+"\t"+u.getClassInfo().getClassName());
}
}
}
其中:
when
when元素表示当when中的条件满足的时候就输出其中的内容,跟JAVA中的switch效果差不多的是按照条件的顺序,当when中有条件满足的时候,就会跳出choose,即所有的when和otherwise条件中,只有一个会输出,当所有的条件都不满足的时候就输出otherwise中的内容
set
set元素主要是用在更新操作的时候,它的主要功能和where元素其实是差不多的,主要是在包含的语句前输出一个set,然后如果包含的语句是以逗号结束的话将会把该逗号忽略,如果set包含的内容为空的话则会出错。有了set元素就可以动态的更新那些修改了的字段。
foreach
foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合。foreach元素的属性主要有item,index,collection,open,separator,close。item表示集合中每一个元素进行迭代时的别名,index指定一个名字,用于表示在迭代过程中,每次迭代到的位置,open表示该语句以什么开始,separator表示在每次进行迭代之间以什么符号作为分隔符,close表示以什么结束,在使用foreach的时候最关键的也是最容易出错的就是collection属性,该属性是必须指定的,但是在不同情况下,该属性的值是不一样的,主要有一下3种情况:
1、如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
2、如果传入的是单参数且参数类型是一个Array数组的时候,collection的属性值为array
3、如果传入的参数是多个的时候,就需要把它们封装成一个Map,当然单参数也可以封装成Map,实际上如果在传入参数的时候,在MyBatis里面也是会把它封装成一个Map的,Map的key就是参数名,所以这个时候collection属性值就是传入的List或Array对象在自己封装的Map里面的key
trim
trim元素的主要功能是可以在自己包含的内容前加上某些前缀,也可以在其后加上某些后缀,与之对应的属性是prefix和suffix;可以把包含内容的首部某些内容覆盖,即忽略,也可以把尾部的某些内容覆盖,对应的属性是prefixOverrides和suffixOverrides;正因为trim有这样的功能,所以也可以非常简单的利用trim来代替where元素的功能