Mybatis—动态SQL

动态SQL：是指根据不同的条件生成不同的SQL语句

Mybatis 动态 sql 是做什么的

Mybatis 动态 sql 可以让我们在 Xml 映射文件内，以标签的形式编写动态 sql，完成逻辑判断和动态拼接 sql 的功能。

Mybatis 提 供 了 9 种 动 态 sql 标 签

trim , foreach when ,where ,if , otherwise, set ,choose , bind

动态 sql 的执行原理为，使用 OGNL(表达式语言) 从 sql 参数对象中计算表达式的值，根据表达式

的值动态拼接 sql，以此来完成动态 sql 的功能

#{}与${}的区别

#{}是预编译处理，${}是字符串替换。

Mybatis 在处理#{}时，会将 sql 中的#{}替换为?号，调用 PreparedStatement 的 set 方法来赋值；

Mybatis 在处理${}时，就是把${}替换成变量的值。

使用#{}可以有效的防止 SQL 注入，提高系统安全性。

下面是实现Mybatis动态SQL的demo

实体类

package org.zhx.entity;

import java.util.List;

public class ClassInfo {
    private int classId;
    private String className;
    private List<StudentInfo> 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 List<StudentInfo> getStudentInfoList() {
        return studentInfoList;
    }

    public void setStudentInfoList(List<StudentInfo> studentInfoList) {
        this.studentInfoList = studentInfoList;
    }

    public ClassInfo() {
    }

    public ClassInfo(int classId, String className, List<StudentInfo> studentInfoList) {
        this.classId = classId;
        this.className = className;
        this.studentInfoList = studentInfoList;
    }

    public ClassInfo(int classId, String className) {
        this.classId = classId;
        this.className = className;
    }
}

package org.zhx.entity;

public class StudentInfo {
    private int stuId;
    private String stuName;
    private String password;
    private String stuAge;
    private String stuSex;
    private int stuClassId;
    private ClassInfo classInfo;

    public int getStuId() {
        return stuId;
    }

    public void setStuId(int stuId) {
        this.stuId = stuId;
    }

    public String getStuName() {
        return stuName;
    }

    public void setStuName(String stuName) {
        this.stuName = stuName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getStuAge() {
        return stuAge;
    }

    public void setStuAge(String stuAge) {
        this.stuAge = stuAge;
    }

    public String getStuSex() {
        return stuSex;
    }

    public void setStuSex(String stuSex) {
        this.stuSex = stuSex;
    }

    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() {
    }



    public StudentInfo(int stuId, String stuName, String password, String stuAge, String stuSex, int stuClassId, ClassInfo classInfo) {
        this.stuId = stuId;
        this.stuName = stuName;
        this.password = password;
        this.stuAge = stuAge;
        this.stuSex = stuSex;
        this.stuClassId = stuClassId;
        this.classInfo = classInfo;
    }

    public StudentInfo(int stuId, String stuName, String password, String stuAge, String stuSex, int stuClassId) {
        this.stuId = stuId;
        this.stuName = stuName;
        this.password = password;
        this.stuAge = stuAge;
        this.stuSex = stuSex;
        this.stuClassId = stuClassId;
    }
}

接口

package org.zhx.dao;

import org.zhx.entity.ClassInfo;

import java.util.List;

public interface ClassInfoDao {
    public ClassInfo findClassById(int id);
}

package org.zhx.dao;

import org.apache.ibatis.annotations.Param;
import org.zhx.entity.ClassInfo;
import org.zhx.entity.StudentInfo;

import java.util.List;

public interface StudentInfoDao {
    List<StudentInfo> findAll();

    public int addStu(@Param("stuname") String stuName, @Param("password")String password, @Param("stuage")String stuAge, @Param("stusex")String stuSex, @Param("stuclassid")int stuClassId);

    public int updateStu(@Param("stuage")int stuAge,@Param("stuid")int stuId);

    public int deleteStu(@Param("stuid")int stuId);

    public List<StudentInfo> findByList(int [] array);

    public List<StudentInfo> findByLists(List<Integer> list);

    public List<StudentInfo> findByChoose(@Param("stuname")String stuName,@Param("classid") int classId);

    public List<StudentInfo> findByTest(@Param("stuname")String stuName,@Param("classid") int classId,@Param("stuage") int stuAge);

    public int update(StudentInfo s);

    public StudentInfo findById(String id);
}

工具类

​
package org.zhx.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对象
    private static SqlSessionFactory sessionFactory;
    static {
        String resource = "MyBatis.xml";
        try {
            InputStream is = Resources.getResourceAsStream(resource);
            sessionFactory = new SqlSessionFactoryBuilder().build(is);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //    创建sqlsession连接对象
    public static SqlSession getSession(){
        return sessionFactory.openSession();
    }
    //      创建关闭sqlsession连接对象
    public static void closeSession(SqlSession session){
        if (session!=null){
            session.close();
        }
    }
}

​

Mapper

<?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="org.zhx.dao.ClassInfoDao">
    <select id="findClassById" resultType="ClassInfo" >
        select s.*,c.classname from studentinfo s,classinfo c where s.stuclassid=c.classid and classid=#{classid};
    </select>
</mapper>

<?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="org.zhx.dao.StudentInfoDao">

    <select id="findAll" resultType="StudentInfo">
        select s.*,c.classname from studentinfo s,classinfo c where s.stuclassid=c.classid
    </select>
    <insert id="addStu"  parameterType="org.zhx.entity.StudentInfo" useGeneratedKeys="true">
        insert into studentinfo (stuname,password,stuage,stusex,stuclassid) values (#{stuname},#{password},#{stuage},#{stusex},#{stuclassid})
    </insert>

    <update id="updateStu" parameterType="org.zhx.entity.StudentInfo">
        update studentinfo set stuage=#{stuage} where stuid=#{stuid}
    </update>

    <delete id="deleteStu" parameterType="org.zhx.entity.StudentInfo">
        delete from studentinfo where stuid=#{stuid}
    </delete>

    <select id="findByList" resultType="org.zhx.entity.StudentInfo">
        select * from studentinfo where stuid in
        <foreach item="stuid" open="(" separator="," close=")" collection="array">
            #{stuid}
        </foreach>
    </select>

    <select id="findByLists" resultType="org.zhx.entity.StudentInfo">
        select * from studentinfo where stuid in
        <foreach collection="list" item="stuid" open="(" separator="," close=")">
            #{stuid}
        </foreach>
    </select>

    <select id="findByChoose" resultType="org.zhx.entity.StudentInfo">
        select * from studentinfo where 1=1
        <choose>
            <when test="stuname != null and stuname != ''">stuname like concat('%',#{stuname},'%')</when>
            <when test="classid != null and classid != ''">and stuclassid=#{classid}</when>
            <otherwise></otherwise>
        </choose>
    </select>

    <select id="findByTest" resultType="org.zhx.entity.StudentInfo">
        select * from studentinfo
        <where>
            <trim suffix="" suffixOverrides="and">
                <if test="stuname != null and stuname != ''">stuname like concat(#{stuname},'%')</if>
                <if test="classid != null and classid !=0">and stuclassid=#{classid}</if>
                <if test="stuage != null and stuage != 0">and stuage>#{stuage}</if>
            </trim>
        </where>
    </select>

    <update id="update" parameterType="StudentInfo">
        update studentinfo
        <set>
            <trim suffix="where stuid=#{stuId}" suffixOverrides="," >
                <if test="stuName != null and stuName != ''">stuname=#{stuName},</if>
            </trim>
        </set>
    </update>

    <select id="findById" resultType="org.zhx.entity.StudentInfo" >
        select * from  studentinfo where stuid=#{stuid}
    </select>

</mapper>

测试类

package org.zhx.test;

import org.apache.ibatis.session.SqlSession;
import org.zhx.dao.ClassInfoDao;
import org.zhx.dao.StudentInfoDao;
import org.zhx.entity.ClassInfo;
import org.zhx.entity.StudentInfo;
import org.zhx.util.MybatisUtil;

import java.util.ArrayList;
import java.util.List;

public class Test {
    public static void main(String[] args) {
//        test1();
//        test2();
//        test3();
//        test4();
//        test5();
//        test6();
//        test7();
//        test8();
//        test9();
        test10();
    }

    private static void test10() {
        SqlSession session = MybatisUtil.getSession();
        StudentInfo s = session.getMapper(StudentInfoDao.class).findById("3");
        if (s!=null){
            s.setStuName("李四");
            int result = session.getMapper(StudentInfoDao.class).update(s);
            if (result>0){
                session.commit();
                System.out.println(s.getStuClassId()+"\t"+s.getStuName());
            }
        }
    }

    private static void test9() {
        SqlSession session = MybatisUtil.getSession();
        List<StudentInfo> list = session.getMapper(StudentInfoDao.class).findByTest(null,1,0);
        for (StudentInfo s:list) {
            System.out.println(s.getStuClassId()+"\t"+s.getStuName());
        }
    }

    private static void test8() {
        SqlSession session = MybatisUtil.getSession();
        List<StudentInfo> list = session.getMapper(StudentInfoDao.class).findByChoose("张",5);
        for (StudentInfo s:list) {
            System.out.println(s.getStuClassId()+"\t"+s.getStuName());
        }
    }

    private static void test7() {
        SqlSession session = MybatisUtil.getSession();
        List<Integer> list = new ArrayList<Integer>();
        list.add(2);
        list.add(4);
        List<StudentInfo> list1 = session.getMapper(StudentInfoDao.class).findByLists(list);
        for (StudentInfo s:list1) {
            System.out.println(s.getStuId());
            System.out.println(s.getStuName());
        }
    }

    private static void test6() {
        SqlSession session = MybatisUtil.getSession();
        int [] array = {1,2};
        List<StudentInfo> list = session.getMapper(StudentInfoDao.class).findByList(array);
        for (StudentInfo s:list) {
            System.out.println(s.getStuName());
        }
    }

    private static void test5() {
        SqlSession session = MybatisUtil.getSession();
        int stu =  session.getMapper(StudentInfoDao.class).deleteStu(2);
        if (stu>0){
            session.commit();
            System.out.println("删除成功");
        }
    }

    private static void test4() {
        SqlSession session = MybatisUtil.getSession();
        int stu =  session.getMapper(StudentInfoDao.class).updateStu(25,2);
        if (stu>0){
            session.commit();
            System.out.println("修改成功");
        }
    }

    private static void test3() {
        SqlSession session = MybatisUtil.getSession();
        int stu =  session.getMapper(StudentInfoDao.class).addStu("小猪","123","27","男",1);
        if (stu>0){
            session.commit();
            System.out.println("添加成功");
        }
    }

    private static void test2() {
        SqlSession session = MybatisUtil.getSession();
        ClassInfo ci =  session.getMapper(ClassInfoDao.class).findClassById(1);
        System.out.println(ci.getClassName());
        for (StudentInfo s:ci.getStudentInfoList()) {
            System.out.println(s.getStuName());
        }
    }

    private static void test1() {
        SqlSession session = MybatisUtil.getSession();
        List<StudentInfo> list =  session.getMapper(StudentInfoDao.class).findAll();
        for (StudentInfo s: list) {
            System.out.println(s.getStuName());
            System.out.println(s.getClassInfo().getClassName());
        }
    }
}