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

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

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

1、目录结构

2、MySql数据表

3、笔记及案例 (1) mybatis-config.xml （MyBatis主配置文件）

(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 selectWhere(Student student);

    // foreach
    List selectForEach(List idList);

    // list为对象
    List selectForEachTwo(List students);

}

(5) StudentDao.xml（Dao接口对应的mapper文件，笔记在这！）

    
    
        select * from student
    
    
        id, name, email
    











    
    
        select * from student where 1 = 1
        
            and name = #{name}
        
        
            and age = #{age}
        
    

    
--         select * from student
        
--      where会自动忽略首个 or 或 and，并自动增删 “where” 关键字 
        
            
                or name = #{name}
            
            
                or age <= #{age}
            
        
    


    




    
--         select * from student
        select  from student

        
            where id in
            
                #{id}
            
        
    

    
    
        select * from student
        
            where id in
            
                #{student.id}
            
        
    

(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 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 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 idList = new ArrayList<>();
        idList.add(1006);
        idList.add(1011);
        idList.add(1009);
        idList.add(1013);

        List 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 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 students = dao.selectForEachTwo(idList);

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


}

Over！