MyBatis基础篇

注意：本篇文章主讲语法，jar包导入大家可以在网上搜索（有问题一定要学会利用搜索引擎解决！！！），全程代码会写到文章中。

     1.  MyBatis是一个半自动ORM框架（作为解决数据库发展和面向对象发展不一致的问题）

​ O:objict—面向对象

​ R:relation—关系型数据库

​ M:map—映射

​ 半自动：mybatis框架需要我们手写sql语句

​ mybatis框架不依赖于服务器

2. 优点：

   (1)：解决了SQL语句和Java代码之间的耦合

   (2)：连接数据库速率比较快

   (3):取值问题会变得非常简单

1.Flower类
mysql> select * from flower;
+----+-----------+-----------+------------+
| id | name      | price     | production |
+----+-----------+-----------+------------+
|  1 | 玫瑰花    |        15 | 中国       |
|  2 | 夕颜      |        18 | 中国       |
|  5 | 满天星    |      1314 | 挪威       |
|  6 | 牡丹      |         5 | 中国       |
|  7 | 丁香      |       188 | 锦州       |
|  8 | 杜鹃      |      1688 | 金陵       |
|  9 | 琉璃      |     18888 | 临安       |
| 10 | 彼岸花    | 188888888 | 黄泉       |
| 11 | 玫瑰花    |        15 | 咸阳       |
+----+-----------+-----------+------------+
9 rows in set (0.00 sec)

2.Student类
mysql> select * from student;
+-----+--------+---------+
| sid | sname  | clazzno |
+-----+--------+---------+
|   1 | 张三   |       1 |
|   2 | 李四   |       1 |
|   3 | 王五   |       2 |
|   4 | 赵六   |       3 |
|   5 | 田七   |       4 |
+-----+--------+---------+
5 rows in set (0.00 sec)

3.Clazz类
mysql> select * from clazz;
+---------+-----------------+
| clazzno | cname           |
+---------+-----------------+
|       1 | 505java班       |
|       2 | 509java班       |
|       3 | 402AI班级       |
|       4 | 203前端班级     |
+---------+-----------------+
4 rows in set (0.00 sec)

1.创建实体类（Flower）
    
package pers.szs.entity;

public class Flower {

    private Integer id;
    private String name;
    private Integer price;
    private String production;

    public Flower() {
    }

    public Flower(Integer id, String name, Integer price, String production) {
        this.id = id;
        this.name = name;
        this.price = price;
        this.production = production;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Integer getPrice() {
        return price;
    }

    public void setPrice(Integer price) {
        this.price = price;
    }

    public String getProduction() {
        return production;
    }

    public void setProduction(String production) {
        this.production = production;
    }

    @Override
    public String toString() {
        return "Flower{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", price=" + price +
                ", production='" + production + ''' +
                '}';
    }
}

        
  

        
2.配置mybatis.xml文件
    






      

    
      
          
    

    
    

        
        
    


    
      

           

              

              

                
                
                
                

            

        

    


    
    
        
        

    





属性文件(jdbc.properties):
    
m_driver=com.mysql.cj.jdbc.Driver
m_url=jdbc:mysql://localhost:3306/mybatis2?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
m_user=root
m_password=******
    
 日志文件(log4j.properties):
 log4j.rootCategory=error,console,logfile

log4j.logger.pers.szs.mapper=debug

log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=- %m%n

log4j.appender.logfile=org.apache.log4j.FileAppender
log4j.appender.logfile.File=d:/bjsxt.log
log4j.appender.logfile.Append=true
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%-4r [%t] %-5p %c %x -%m%n

 
    
注：
    日志记录等级：
    FATAL>ERROR>WAR>INFO>DEBUG 
   
 

package pers.szs.mapper;

import pers.szs.entity.Flower;

import java.util.List;
import java.util.Map;

public interface FlowerMapper {

    //1.查询所有信息
    List selectAll();


    //2.按照参数查询

    List selectMore1(int id);
    List selectMore2(String name,String production);
    List selectMore3(Flower flower);
    List selectMore4(Map map);
    
    //3.添加操作

    int insert1(int id,String name,int price,String producton);

    int insert2(Flower flower);
    
     //4.修改操作

    int update(int id,String name,int price,String producton);

    //5.删除操作
    int delete(int id);

    
}

    

        id,name,price,production

    

    
    

        select  from flower

    

    

        select * from flower where id=#{param1}

    


    

        select * from flower where name=#{param1} and production=#{param2}

    


    

        
        select * from flower where name=#{name} and production=#{production}

    

    

        
        select * from flower where name=#{key1} and production=#{key2}

    

    


    


        
        insert into flower values(default,#{param2},#{param3},#{param4})

    

    

        insert into flower values(#{id},#{name},#{price},#{production})

    

    

        update flower set name=#{param2},price=#{param3},production=#{param4} where id=#{param1}

    


    

        delete from flower where id=#{param1}

    





 
测试类（TestA） 
package pers.szs.test;

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 pers.szs.entity.Flower;
import pers.szs.mapper.FlowerMapper;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TestA {
    public static void main(String[] args) throws IOException {

        //解析mybatis.xml文件
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis.xml");


        //创建sqlSession工厂

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        //创建sqlSession对象

          //openSession(bool b) b默认为false:事务不会自动提交(即update,delete,insert操作不会自动提交至数据库,会保存到内存中,需要手动提交)
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        //调用方法

        FlowerMapper flowerMapper = sqlSession.getMapper(FlowerMapper.class);


        //关闭资源

        sqlSession.close();

    }
}

 
​ 通过第一部分的学习，我们已经掌握了利用mybatis实现基本的增删改查操作，但是我们可能会疑惑，利用mybatis进行操作似乎与直接在命令行或者图形界面操作没有区别，额，而且好像更加复杂了，因为需要我们配置许多文件，而且测试类需要使用许多我们不了解的类和接口。接下来我们就一起来看一看mybatis的强大之处。
 
第二部分（动态SQL） 
1.九个标签：where if when set trim foreach band sql include（加粗的重点掌握，其他的用的不多，了解一下）
 
FlowerMapper2 
package pers.szs.mapper;

import pers.szs.entity.Flower;

import java.util.List;
import java.util.Map;

public interface FlowerMapper2 {

    //1.查询操作

    //if
    List selectMore1(String name,String production);


    //choose when otherwise
    List selectMore2(String name,String production);


    //foreach
    List selectMore3(List list);

    //bind(模糊查询)
    List selectMore4(String name,String production);

    List selectMore5(String name,String production);


    //2.修改操作

    int update1(Flower flower);


    int update2(Flower flower);




 
FlowerMapper2.xml 





    

    

        select * from flower


        
        

            

                name=#{param1}

            

            
                and production=#{param2}

            


        


    

    
    
        
        select * from flower
        
        
            
               
                
                

                    name=#{param1}
                    
                
                
                

                    and production=#{param2}

                

                
                    1=1
                
                
            
            
            
        

    
    
    
    
        
        
        select * from flower where id in

        

            #{item}

        

        
    


    

        select * from flower

        

            

                name like '%${param1}%'

            

            

                and production like '%${param2}%'

            

        

    
    
    
        
        select * from flower 
        
        

            
                
                

                name like #{pa1}
            


            

                

                production like #{pa2}

            
            
        
        
    
    
    
    
    
    
    

        update flower

        
        

           

               name=#{name},

           

            

                price=#{price},
            

            

                production=#{production}

            

        

        where id=#{id}

    


    

        update flower

        

            

                name=#{name},

            

            

                price=#{price},
            

            

                production=#{production}

            


        

        where id=#{id}


    

疑难点：第一个update操作 name like ‘%${name}%’ 为什么不能直接写 name like ‘%#{name}%’,反而却要利用bind标签

因为"#“底层为占位符’?’，” " 底 层 为 直 接 拼 写 ， 而 此 处 不 支 持 占 位 符 ， 所 以 只 能 用 ′ "底层为直接拼写，而此处不支持占位符，所以只能用' "底层为直接拼写，而此处不支持占位符，所以只能用′'或者利用bind标签解决

其实学到这里大家对于mybatis应该都有了自己的理解，我个人认为mybatis关键在于一个字"拼"；

好了，闲话少说，接下来继续学习多表查询

三种方式：业务代码的方式

​ N+1

​ 多表查询SQL

StudentMapper(Intreface)

package pers.szs.mapper;

import pers.szs.entity.Clazz;
import pers.szs.entity.Student;

import java.util.List;

public interface StudentMapper {

    //查询所有学生的信息
    List selectAll();


    List selectAll2();

    List selectAll3();

    //按照班级编号查询班级信息
    Clazz selectOne(int clazzno);


    //根据班级编号查询学生信息
    List selectMore(int clazzno);
}

StudentMapper.xml

    

        select * from student where clazzno=#{param1}
    

    
    

        select * from student

    

    

        select * from clazz where clazzno=#{param1}

    


    
    

        select * from student

    
    


        
          
          
          

        
        

    

    
    

        select * from student s join clazz c on s.clazzno=c.clazzno

    

    

        

        
        



        

            
            

        



    


 
ClazzMapper(Intreface)
 
package pers.szs.mapper;

import pers.szs.entity.Clazz;
import pers.szs.entity.Student;

import java.util.List;

public interface ClazzMapper {

    //查询所有班级信息
    List selectAll();

    List selectAll2();

    List selectAll3();

    //按照班级编号查询学生信息
    List  selectMore(int clazzno);

    //按照指定编号查询班级信息
    Clazz selectOne(int clazzo);


}

 
ClazzMapper.xml
 







    select * from clazz where clazzno=#{param1}



    
    

        select * from clazz

    

    

        select * from student where clazzno=#{param1}

    

    
    

        select * from clazz

    


    

        
        

        

    

    
    

        select * from student s join clazz c on s.clazzno=c.clazzno

    

    

        
        

        

            

            
            

        


    

1.业务代码的方式（完全通过Java代码来实现多表查询）

(通过查询学生信息获取clazzno,再通过clazzno来查询班级信息)

package pers.szs.test;

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 pers.szs.entity.Clazz;
import pers.szs.entity.Flower;
import pers.szs.entity.Student;
import pers.szs.mapper.ClazzMapper;
import pers.szs.mapper.FlowerMapper2;
import pers.szs.mapper.StudentMapper;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestB {
    public static void main(String[] args) throws IOException {

        //解析mybatis.xml文件
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis.xml");


        //创建sqlSession工厂

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        //创建sqlSession对象

          //openSession(bool b) b默认为false:事务不会自动提交(即update,delete,insert操作不会自动提交至数据库,会保存到内存中,需要手动提交)
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        //调用方法

        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        ClazzMapper clazzMapper = sqlSession.getMapper(ClazzMapper.class);


        List studentList = studentMapper.selectAll();

        for(Student student:studentList){

            Integer clazzno = student.getClazzno();

            Clazz clazz = studentMapper.selectOne(clazzno);

            System.out.println(student+"--->"+clazz);


        }

        System.out.println("===================================================================================");

        for(Student student:studentList){

            Integer clazzno = student.getClazzno();

            Clazz clazz = studentMapper.selectOne(clazzno);

            student.setClazz(clazz);

            System.out.println(student);

        }


        //关闭资源

        sqlSession.close();

    }
}

(通过查询班级信息获取clazzno,再通过clazzno来查询学生信息)

package pers.szs.test;

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 pers.szs.entity.Clazz;
import pers.szs.entity.Student;
import pers.szs.mapper.ClazzMapper;
import pers.szs.mapper.StudentMapper;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestC {
    public static void main(String[] args) throws IOException {

        //解析mybatis.xml文件
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis.xml");


        //创建sqlSession工厂

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        //创建sqlSession对象

          //openSession(bool b) b默认为false:事务不会自动提交(即update,delete,insert操作不会自动提交至数据库,会保存到内存中,需要手动提交)
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        //调用方法

        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        ClazzMapper clazzMapper = sqlSession.getMapper(ClazzMapper.class);

        List clazzList = clazzMapper.selectAll();
        for(Clazz clazz:clazzList){

            Integer clazzno = clazz.getClazzno();
            List studentList = clazzMapper.selectMore(clazzno);

            System.out.println(clazz+"--->"+studentList);
        }


        System.out.println("=========================================================================================");


        for(Clazz clazz:clazzList){
            Integer clazzno = clazz.getClazzno();

            List studentList = clazzMapper.selectMore(clazzno);

            clazz.setList(studentList);

            System.out.println(clazz);

        }


        //关闭资源

        sqlSession.close();

    }
}

2.N+1方式

(查询学生所有信息，包括clazz属性)

package pers.szs.test;

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 pers.szs.entity.Clazz;
import pers.szs.entity.Student;
import pers.szs.mapper.ClazzMapper;
import pers.szs.mapper.StudentMapper;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestD {
    public static void main(String[] args) throws IOException {

        //解析mybatis.xml文件
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis.xml");


        //创建sqlSession工厂

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        //创建sqlSession对象

          //openSession(bool b) b默认为false:事务不会自动提交(即update,delete,insert操作不会自动提交至数据库,会保存到内存中,需要手动提交)
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        //调用方法

        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        ClazzMapper clazzMapper = sqlSession.getMapper(ClazzMapper.class);


        List studentList = studentMapper.selectAll2();
        System.out.println(studentList);
        //关闭资源

        sqlSession.close();

    }
}

（查询班级所有信息，包括list属性）

package pers.szs.test;

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 pers.szs.entity.Clazz;
import pers.szs.entity.Student;
import pers.szs.mapper.ClazzMapper;
import pers.szs.mapper.StudentMapper;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestE {
    public static void main(String[] args) throws IOException {

        //解析mybatis.xml文件
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis.xml");


        //创建sqlSession工厂

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        //创建sqlSession对象

          //openSession(bool b) b默认为false:事务不会自动提交(即update,delete,insert操作不会自动提交至数据库,会保存到内存中,需要手动提交)
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        //调用方法

        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        ClazzMapper clazzMapper = sqlSession.getMapper(ClazzMapper.class);


        List clazzList = clazzMapper.selectAll2();
        System.out.println(clazzList);
        //关闭资源

        sqlSession.close();

    }
}

3. 多表查询SQL

package pers.szs.test;

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 pers.szs.entity.Clazz;
import pers.szs.entity.Flower;
import pers.szs.entity.Student;
import pers.szs.mapper.ClazzMapper;
import pers.szs.mapper.FlowerMapper2;
import pers.szs.mapper.StudentMapper;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestF {
    public static void main(String[] args) throws IOException {

        //解析mybatis.xml文件
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis.xml");


        //创建sqlSession工厂

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        //创建sqlSession对象

          //openSession(bool b) b默认为false:事务不会自动提交(即update,delete,insert操作不会自动提交至数据库,会保存到内存中,需要手动提交)
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        //调用方法

        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        ClazzMapper clazzMapper = sqlSession.getMapper(ClazzMapper.class);

        List studentList = studentMapper.selectAll3();
        System.out.println(studentList);

        System.out.println("====================================================================================");
        List clazzList = clazzMapper.selectAll3();
        System.out.println(clazzList);


        //关闭资源

        sqlSession.close();

    }
}

学到这里大家可能会发现一个问题，每次书写Test类时都要反复书写重样的内容，包括解析mybatis.xml文件等，那么我们如何对现有的代码进行优化？

优化方案一：提取工具类

工具类

package pers.szs.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 pers.szs.entity.Clazz;
import pers.szs.entity.Student;
import pers.szs.mapper.ClazzMapper;
import pers.szs.mapper.StudentMapper;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class DBUtil {


    //解析mybatis.xml文件
    private static SqlSessionFactory sqlSessionFactory=null;
   static {

        InputStream resourceAsStream;

        {
            try {
                resourceAsStream = Resources.getResourceAsStream("mybatis.xml");

                 sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    public static SqlSession getSqlSession(){


        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        return sqlSession;

    }



}

测试类

package pers.szs.test;

import pers.szs.entity.Student;
import pers.szs.mapper.StudentMapper;
import pers.szs.util.DBUtil;

import java.util.List;

public class TestG {

    public static void main(String[] args) {


        StudentMapper studentMapper = DBUtil.getSqlSession().getMapper(StudentMapper.class);


        List studentList = studentMapper.selectAll3();
        System.out.println(studentList);
    }
}

经过优化我们可以发现，测试类的代码已经大大减少，但是这仍然不是最终方案，因为我们会发现，如果用户提出一个请求需要我们查询需要后台查询所有学生和班级的信息，测试类如下：

package pers.szs.test;

import pers.szs.entity.Clazz;
import pers.szs.entity.Student;
import pers.szs.mapper.ClazzMapper;
import pers.szs.mapper.StudentMapper;
import pers.szs.util.DBUtil;

import java.util.List;

public class TestG {

    public static void main(String[] args) {

           selectA();
        
    }
    
    public static void selectA(){

        StudentMapper studentMapper = DBUtil.getSqlSession().getMapper(StudentMapper.class);

        List studentList = studentMapper.selectAll3();
        System.out.println(studentList);
        
        selectB();

    }
    
    public static void selectB(){
        ClazzMapper clazzMapper=DBUtil.getSqlSession().getMapper(ClazzMapper.class);
        List clazzList = clazzMapper.selectAll3();
        System.out.println(clazzList);

    }
}

通过测试类我们可以发现，每查询一个数据库表的信息，我们就需要使用Dbutil获取一个SqlSession对象，这样无形中造成了sqlSession对象的浪费，为此我们对DBUtil类进行优化

优化方案二：ThreadLocal 作用：在同一个线程中实现数据（sqlSession）的共享

​ 底层使用的map集合：map.put(key,value)

​ map.put(线程ID,conn)

package pers.szs.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 pers.szs.entity.Clazz;
import pers.szs.entity.Student;
import pers.szs.mapper.ClazzMapper;
import pers.szs.mapper.StudentMapper;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class DBUtil {


    //解析mybatis.xml文件
    private static SqlSessionFactory sqlSessionFactory;

    //创建ThreadLocal对象
    private static ThreadLocal tl=new ThreadLocal<>();

   static {

        InputStream resourceAsStream;

        {
            try {
                resourceAsStream = Resources.getResourceAsStream("mybatis.xml");

                 sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static SqlSession getSqlSession(){


       //获得ThreadLocal中的SqlSession对象
       SqlSession sqlSession=tl.get();

       if(sqlSession==null){

           sqlSession = sqlSessionFactory.openSession(true);

           //把创建好的对象放到ThreadLocal中
           tl.set(sqlSession);
       }



        return tl.get();

    }


   //关闭sqlSession
    public static void closeAll(){

        SqlSession sqlSession = tl.get();

        if(sqlSession!=null){
            sqlSession.close();
        }

        tl.set(null);
    }
}

测试类：TestG

package pers.szs.test;

import pers.szs.entity.Clazz;
import pers.szs.entity.Student;
import pers.szs.mapper.ClazzMapper;
import pers.szs.mapper.StudentMapper;
import pers.szs.util.DBUtil;

import java.util.List;

public class TestG {

    public static void main(String[] args) {

           selectA();
    }


    public static void selectA(){

        StudentMapper studentMapper = DBUtil.getSqlSession().getMapper(StudentMapper.class);

        List studentList = studentMapper.selectAll3();
        System.out.println(studentList);
        DBUtil.closeAll();

        selectB();

    }

    public static void selectB(){
        ClazzMapper clazzMapper=DBUtil.getSqlSession().getMapper(ClazzMapper.class);
        List clazzList = clazzMapper.selectAll3();
        System.out.println(clazzList);

        DBUtil.closeAll();

    }
}