【MyBatis】MyBatis动态SQL（笔记三）

【MyBatis】MyBatis学习笔记三（动态SQL）

• 注：在本文中，关于动态SQL的所有笔记均写于案例源码中。

1、目录结构

2、MySql数据表

3、笔记及案例

(1) mybatis-config.xml （MyBatis主配置文件）

<?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>

    <settings>
        <!-- 设置日志系统 -->
        <setting name="logImpl" value="STDOUT_LOGGING" />
    </settings>

    <environments default="development">
        <environment id="development">

            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/springdb?userUnicode=true&amp;characterEncoding=utf-8&amp;serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="abc123"/>
            </dataSource>

        </environment>
    </environments>

    <mappers>
        <mapper resource="com/Etui/dao/StudentDao.xml"/>
    </mappers>

</configuration>

(2) MyBatisUtil.java（MyBatis工具类）

package com.Etui.uitls;

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 {

//    private static SqlSessionFactory sessionFactory = null;
//    static {
//        try {
//            InputStream resource = Resources.getResourceAsStream("mybatis-config.xml");
//            sessionFactory = new SqlSessionFactoryBuilder().build(resource);
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//    }
//
//    // 获取SqlSession对象的方法
//    public static SqlSession getSqlSession() {
//        SqlSession session = null;
//        if(sessionFactory != null) {
//            session = sessionFactory.openSession();
//        }
//        return session;
//    }

    private static InputStream resource = null;
    private static SqlSessionFactory factory = null;
    static {
        try {
            resource = Resources.getResourceAsStream("mybatis-config.xml");
            factory = new SqlSessionFactoryBuilder().build(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        SqlSession session = null;
        if (factory != null) {
            session = factory.openSession();
        }
        return session;
    }
}

(3) Student.java（数据表对应的实体类）

package com.Etui.entity;


public class Student {
    private Integer id;
    private String name;
    private String email;
    private Integer age;

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", age=" + age +
                '}';
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getEmail() {
        return email;
    }

    public Integer getAge() {
        return age;
    }

    public Student() {
    }

    public Student(Integer id, String name, String email, Integer age) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.age = age;
    }
}

(4) StudentDao.java （Dao接口文件）

package com.Etui.dao;

import com.Etui.entity.Student;

import java.util.List;

public interface StudentDao {
    // 查询一条记录——if标签
    Student selectStudentById(Student student);

    // where
    List<Student> selectWhere(Student student);

    // foreach
    List<Student> selectForEach(List<Integer> idList);

    // list为对象
    List<Student> selectForEachTwo(List<Student> students);

}

(5) StudentDao.xml（Dao接口对应的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="com.Etui.dao.StudentDao">
    <!-- SQL语句块 -->
    <sql id="selectStudent">
        select * from student
    </sql>
    <sql id="selectFields">
        id, name, email
    </sql>


<!--    <select id="selectStudentById" resultType="com.Etui.entity.Student">-->
<!--        select * from student where-->
<!--        <if test="name != null and name != ''">-->
<!--            name = #{name}-->
<!--        </if>-->
<!--        <if test="age > 0">-->
<!--            and age = #{age}-->
<!--        </if>-->
<!--    </select>-->
    <!-- 较为严谨的写法 -->
    <select id="selectStudentById" resultType="com.Etui.entity.Student">
        select * from student where 1 = 1
        <if test="name != null and name != ''">
            and name = #{name}
        </if>
        <if test="age > 0">
            and age = #{age}
        </if>
    </select>

    <select id="selectWhere" resultType="com.Etui.entity.Student">
--         select * from student
        <include refid="selectStudent" />
--      where会自动忽略首个 or 或 and，并自动增删 “where” 关键字 
        <where>
            <if test="name != null and name !=''">
                or name = #{name}
            </if>
            <if test="age > 0">
                or age &lt;= #{age}
            </if>
        </where>
    </select>


    <!--
        foreach 循环
            collection：表示循环的对象是 数组/List集合。如果dao接口方法的形参是 数组， collection = "array", 如果dao接口形参是List， collection = "list"
            open：循环开始时的字符。sql.append("(");
            close: 循环结束时的字符。sql.append(")");
            item: 成员，自定义的变量。 Integer item = idList.get(i); // item是集合成员
            separator: 集合成员之间的分隔符。 sql.append(","); // 集合成员之间的分隔符
            #{item的值}：获取集合成员的值

    -->
<!--    <foreach collection="" open="" close="" item="" separator="">-->
<!--        #{}-->
<!--    </foreach>-->

    <select id="selectForEach" resultType="com.Etui.entity.Student">
--         select * from student
        select <include refid="selectFields"/> from student

        <if test="list != null and list.size() > 0">
            where id in
            <foreach collection="list" open="(" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </if>
    </select>

    <!-- 当List集合的元素为对象时 -->
    <select id="selectForEachTwo" resultType="com.Etui.entity.Student">
        select * from student
        <if test="list != null and list.size() > 0">
            where id in
            <foreach collection="list" open="(" close=")" separator="," item="student" >
                #{student.id}
            </foreach>
        </if>
    </select>
</mapper>

(6) MyBatisTest.java（测试类）

package com.Etui;

import com.Etui.dao.StudentDao;
import com.Etui.entity.Student;
import com.Etui.uitls.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

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

public class MybatisTest {

    @Test
    public void testFor() {
        List<Integer> idList = new ArrayList<>();
        idList.add(1001);
        idList.add(1003);
        idList.add(1009);
        idList.add(1012);

        // 查询id在idList中的student
        // select * from student where id in (1001,1003,1009,1012)

        // 手动生成SQL语句如下
        StringBuffer sb = new StringBuffer();
        sb.append("select * from student where id in ");
        sb.append("(");
        for (Integer id : idList) {
            sb.append(id + ",");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(")");
        System.out.println(sb);
    }

    @Test
    public void testSelectStudentById() {
        SqlSession session = MyBatisUtil.getSqlSession();
        StudentDao dao = session.getMapper(StudentDao.class);
        Student student = new Student(null, "刻晴", null, 21);
        Student stu = dao.selectStudentById(student);
        System.out.println(stu);
        // 关闭SqlSession对象
        session.close();
    }

    @Test
    public void testSelectWhere() {
        SqlSession session = MyBatisUtil.getSqlSession();
        StudentDao dao = session.getMapper(StudentDao.class);
        Student student = new Student();
        student.setName("刻晴");
        student.setAge(20);
        List<Student> students = dao.selectWhere(student);
        students.forEach(System.out::println);
        session.close();
    }

    @Test
    public void testSelectForEach() {
        SqlSession session = MyBatisUtil.getSqlSession();
        StudentDao dao = session.getMapper(StudentDao.class);

        List<Integer> idList = new ArrayList<>();
        idList.add(1006);
        idList.add(1011);
        idList.add(1009);
        idList.add(1013);

        List<Student> students = dao.selectForEach(idList);

        students.forEach(System.out::println);
        session.close();
    }


    @Test
    public void testSelectForEachTwo() {
        SqlSession session = MyBatisUtil.getSqlSession();
        StudentDao dao = session.getMapper(StudentDao.class);

        List<Student> idList = new ArrayList<>();
        idList.add(new Student(1006, null, null, null));
        idList.add(new Student(1011, null, null, null));
        idList.add(new Student(1009, null, null, null));
        idList.add(new Student(1013, null, null, null));

        List<Student> students = dao.selectForEachTwo(idList);

        students.forEach(System.out::println);
        session.close();
    }


}

Over！