2021-05-18

MyBatis

1.1 SSM

SSH 
Spring   Struts2    Hibernate
整合类    视图层      持久层    
 
SSM
Spring   Struts2    MyBatis
整合类    视图层      持久层    

Spring   SpringMVC  MyBatis 
整合类    视图层      持久层 

SM
SpringBoot                 MyBatis 
Spring全家桶                持久层
    
SpringCloud 微服务框架   

1.2 MyBatis 简介

官方网址：https://mybatis.org/mybatis-3/zh/index.html

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。

1.3 入门

1.创建一个maven工程（ com.aaa.mybatis）

2 导包

<dependencies>

   <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
    </dependency>

    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.6</version>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.16</version>
    </dependency>

  </dependencies>

3 创建一个 resources 文件夹 并且 mark as Resources Root(main下创建resources包)
4 创建一个 mybatis-config.xml文件(resources下的mybatis-config.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>

    <!--环境们 其实就是配置数据源信息等环境    default="development"  标明当前使用哪个环境  -->
    <environments default="development">

        <!--其中的一个环境 配置-->
        <environment id="development">
            <!-- transactionManager mybatis自带的事务操作  type="JDBC"  JDBC 是一个别名 代表mybatis写好的事务类但是名字太长了 所以简写了   -->
            <transactionManager type="JDBC"/>
            <!-- dataSource 数据源   type="POOLED"   POOLED 是mybatis自带的数据库连接池   -->
            <dataSource type="POOLED">
                <property name="driver"    value="com.mysql.jdbc.Driver"/>
                <property name="url"       value="jdbc:mysql:///mytest?characterEncoding=UTF-8&useSSL=false"/>
                <property name="username"  value="root"/>
                <property name="password"  value="123456"/>
            </dataSource>
        </environment>

    </environments>
    
    <mappers>
        <mapper resource="mapper/StudentMapper1.xml"/>
    </mappers>


</configuration>

5 在 resources 下 创建一个 mapper文件夹 再在文件夹中创建一个 StudentMapper.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 namespace="com.aaa.mapper.StudentMapper">

</mapper>

1.4 使用mybatis 完成增删改查

A 在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.aaa.mapper.StudentMapper">


    <insert id="saveStudent">
        insert  into student values (null,'张三',18,'新装')
    </insert>

</mapper>

BJava代码

@Test
    public void test() throws Exception{

        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        /*  SqlSession 相当于我们拿到一个连接对象  进行数据库操作  */
        SqlSession sqlSession = sqlSessionFactory.openSession();

        int i = sqlSession.insert("com.aaa.mapper.StudentMapper.saveStudent");

        System.out.println(i);

        /* mybatis自带事务操作 我们增删改的时候 要提交事务 */
        sqlSession.commit();

        sqlSession.close();
    }

1.5传参

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student implements Serializable {

      private   Integer   id;
      private   String   name;
      private   Integer   age;
      private   String    address;

    
}

<mapper namespace="com.aaa.mapper.StudentMapper">


    <insert id="saveStudent"  parameterType="com.aaa.entity.Student"  >

          insert  into  student   values   (null , #{name} ,#{age},#{address}   )

    </insert>

</mapper>

 @Test
    public void test() throws Exception{


        Student student = new Student(0, "haha", 18, "sadf");

        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        /*  SqlSession 相当于我们拿到一个连接对象  进行数据库操作  */
        SqlSession sqlSession = sqlSessionFactory.openSession();


        sqlSession.insert("com.aaa.mapper.StudentMapper.saveStudent"  , student  );


        /* mybatis自带事务操作 我们增删改的时候 要提交事务 */
        sqlSession.commit();

        sqlSession.close();
    }

1.6删除

@Test
public void test1() throws Exception{
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    /*  SqlSession 相当于我们拿到一个连接对象  进行数据库操作  */
    SqlSession sqlSession = sqlSessionFactory.openSession();


    int delete = sqlSession.delete("com.aaa.mapper.StudentMapper.deleteStudent", 5);



    /* mybatis自带事务操作 我们增删改的时候 要提交事务 */
    sqlSession.commit();

    sqlSession.close();
}

<delete id="deleteStudent"  parameterType="java.lang.Integer"  >
          delete  from student  where  id =  #{id}
    </delete>

修改

<update id="updateStudent"  parameterType="com.aaa.entity.Student">
      update  student  set  name=#{name} , age=#{age} , address = #{address} where id = #{id}
</update>
 @Test
    public void test2() throws Exception{
        Student student = new Student(6, "李立", 19, "北京");
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        /*  SqlSession 相当于我们拿到一个连接对象  进行数据库操作  */
        SqlSession sqlSession = sqlSessionFactory.openSession();

        int i = sqlSession.update("com.aaa.mapper.StudentMapper.updateStudent", student);

        /* mybatis自带事务操作 我们增删改的时候 要提交事务 */
        sqlSession.commit();

        sqlSession.close();
    }

查询操作

 <select id="getOne" parameterType="java.lang.Integer"   resultType="com.aaa.entity.Student"  >
          select  * from student  where id = #{id}
    </select>


@Test
public void test2() throws Exception{
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        /*  SqlSession 相当于我们拿到一个连接对象  进行数据库操作  */
        SqlSession sqlSession = sqlSessionFactory.openSession();

        Student s = sqlSession.selectOne("com.aaa.mapper.StudentMapper.getOne", 1);

        System.out.println(s);

        sqlSession.close();
 }




<select id="listAll"     resultType="com.aaa.entity.Student"    >
     select  * from  student
</select>
 @Test
    public void test3() throws Exception{
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        /*  SqlSession 相当于我们拿到一个连接对象  进行数据库操作  */
        SqlSession sqlSession = sqlSessionFactory.openSession();

        List<Student> data = sqlSession.selectList("com.aaa.mapper.StudentMapper.listAll");

        System.out.println(data);

        sqlSession.close();
    }

1.7命名空间和id

namespace    A  目前我们使用命名空间+id的形式调用mybatis中的 sql语句  命名空间起到 区分不同的mapper.xml作用
             B  将来使用接口绑定的时候 需要与接口的全限定名匹配
 也就是说 多个mapper的命名空间不能重复        

id  是mapper中不同标签的唯一标识符

 在之前版本的 MyBatis 中，命名空间（Namespaces）的作用并不大，是可选的。 但现在，随着命名空间越发重要，你必须指定命名空间。
命名空间的作用有两个，一个是利用更长的全限定名来将不同的语句隔离开来，同时也实现了你上面见到的接口绑定。就算你觉得暂时用不到接口绑定，你也应该遵循这里的规定，以防哪天你改变了主意。 长远来看，只要将命名空间置于合适的 Java 包命名空间之中，你的代码会变得更加整洁，也有利于你更方便地使用 MyBatis。

命名解析：为了减少输入量，MyBatis 对所有具有名称的配置元素（包括语句，结果映射，缓存等）使用了如下的命名解析规则。

    全限定名（比如 “com.mypackage.MyMapper.selectAllThings）将被直接用于查找及使用。
    短名称（比如 “selectAllThings”）如果全局唯一也可以作为一个单独的引用。 如果不唯一，有两个或两个以上的相同名称（比如 “com.foo.selectAllThings” 和 “com.bar.selectAllThings”），那么使用时就会产生“短名称不唯一”的错误，这种情况下就必须使用全限定名。

<mapper namespace="com.aaa.mapper.StudentMapper">


    <insert id="saveStudent"   parameterType="com.aaa.entity.Student"  >
          insert  into  student   values   (null , #{name} ,#{age},#{address}   )
    </insert>
    
<mapper>    

1.8 SqlSessionFactoryBuilder优化

package com.aaa.test;

import com.aaa.entity.Student;
import com.aaa.mapper.StudentMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

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

/**
 * @author： Jiang
 * @date： 2021/4/4 10:42
 * @description： TODO
 * @modifiedBy：
 * @version: 1.0
 */
public class JavaTest {

    private  SqlSession sqlSession;

    @Before
    public void aaa() throws Exception{
        String resource = "mybatisconfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }


    @After
    public void bbb(){
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void test(){

        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        Student one = mapper.getOne(26);
        System.out.println(one);

        int i1 = mapper.deleteStu(24);
        List<Student> students = mapper.listAll();

        Student student = new Student(26, "awew", 45, "hanghzou");

        int i = mapper.insertStu(student);
        int i2 = mapper.updateStu(student);


    }

}

运行结果：
D:\workerApp\jdk1.8\bin\java.exe -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:D:\workerApp\IntelliJ IDEA 2018.3.3\lib\idea_rt.jar=3930:D:\workerApp\IntelliJ IDEA 2018.3.3\bin" -Dfile.encoding=UTF-8 -classpath "D:\workerApp\IntelliJ IDEA 2018.3.3\lib\idea_rt.jar;D:\workerApp\IntelliJ IDEA 2018.3.3\plugins\junit\lib\junit-rt.jar;D:\workerApp\IntelliJ IDEA 2018.3.3\plugins\junit\lib\junit5-rt.jar;D:\workerApp\jdk1.8\jre\lib\charsets.jar;D:\workerApp\jdk1.8\jre\lib\deploy.jar;D:\workerApp\jdk1.8\jre\lib\ext\access-bridge-64.jar;D:\workerApp\jdk1.8\jre\lib\ext\cldrdata.jar;D:\workerApp\jdk1.8\jre\lib\ext\dnsns.jar;D:\workerApp\jdk1.8\jre\lib\ext\jaccess.jar;D:\workerApp\jdk1.8\jre\lib\ext\jfxrt.jar;D:\workerApp\jdk1.8\jre\lib\ext\localedata.jar;D:\workerApp\jdk1.8\jre\lib\ext\nashorn.jar;D:\workerApp\jdk1.8\jre\lib\ext\sunec.jar;D:\workerApp\jdk1.8\jre\lib\ext\sunjce_provider.jar;D:\workerApp\jdk1.8\jre\lib\ext\sunmscapi.jar;D:\workerApp\jdk1.8\jre\lib\ext\sunpkcs11.jar;D:\workerApp\jdk1.8\jre\lib\ext\zipfs.jar;D:\workerApp\jdk1.8\jre\lib\javaws.jar;D:\workerApp\jdk1.8\jre\lib\jce.jar;D:\workerApp\jdk1.8\jre\lib\jfr.jar;D:\workerApp\jdk1.8\jre\lib\jfxswt.jar;D:\workerApp\jdk1.8\jre\lib\jsse.jar;D:\workerApp\jdk1.8\jre\lib\management-agent.jar;D:\workerApp\jdk1.8\jre\lib\plugin.jar;D:\workerApp\jdk1.8\jre\lib\resources.jar;D:\workerApp\jdk1.8\jre\lib\rt.jar;D:\IdeaProjects\mybatis04\target\classes;D:\MavenRepository\org\projectlombok\lombok\1.18.16\lombok-1.18.16.jar;D:\MavenRepository\mysql\mysql-connector-java\5.1.47\mysql-connector-java-5.1.47.jar;D:\MavenRepository\org\mybatis\mybatis\3.5.6\mybatis-3.5.6.jar;D:\MavenRepository\junit\junit\4.13.2\junit-4.13.2.jar;D:\MavenRepository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar;D:\MavenRepository\log4j\log4j\1.2.12\log4j-1.2.12.jar" com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 -junit4 com.aaa.test.JavaTest
Tue May 18 22:46:18 CST 2021 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
DEBUG [main] - ==>  Preparing: select * from student where id=?
DEBUG [main] - ==> Parameters: 26(Integer)
DEBUG [main] - <==      Total: 0
null
DEBUG [main] - ==>  Preparing: delete from student where id=?
DEBUG [main] - ==> Parameters: 24(Integer)
DEBUG [main] - <==    Updates: 0
DEBUG [main] - ==>  Preparing: select * from student
DEBUG [main] - ==> Parameters: 
TRACE [main] - <==    Columns: id, name, age, address
TRACE [main] - <==        Row: 18, wangxiaowe, 16, beijign
TRACE [main] - <==        Row: 19, wuwu, 15, bbbeijgn
TRACE [main] - <==        Row: 20, 里里, 15, nanjign
TRACE [main] - <==        Row: 21, sfafasfdasdf, 18, sadf
TRACE [main] - <==        Row: 28, wangxiaowe, 41, hangzhiou
TRACE [main] - <==        Row: 29, wangxiaowe, 41, hangzhiou
TRACE [main] - <==        Row: 31, wangxiaowe, 41, hangzhiou
TRACE [main] - <==        Row: 37, wangxiaowe, 41, hangzhiou
TRACE [main] - <==        Row: 39, asds, 16, asdsa
TRACE [main] - <==        Row: 40, 李立, 16, 上海
TRACE [main] - <==        Row: 41, 王二小, 16, 上海
TRACE [main] - <==        Row: 42, 王二小, 14, 上海
TRACE [main] - <==        Row: 43, wang, 16, ss
TRACE [main] - <==        Row: 44, adfs, 16, nanjin
TRACE [main] - <==        Row: 45, adrf, 16, 上海
TRACE [main] - <==        Row: 46, adrf, 16, 上海
DEBUG [main] - <==      Total: 16
DEBUG [main] - ==>  Preparing: insert into student values (null,?,?,?)
DEBUG [main] - ==> Parameters: awew(String), 45(Integer), hanghzou(String)
DEBUG [main] - <==    Updates: 1
DEBUG [main] - ==>  Preparing: update student set name=? where id=?
DEBUG [main] - ==> Parameters: awew(String), 26(Integer)
DEBUG [main] - <==    Updates: 0

Process finished with exit code 0

1.9 总配置文件配置别名

A  系统别名

我们java中有很多类 ，例如 java.lang.Integer  java.util.List  java.util.Map 等java原生类  mybatis已经帮我们起好别名
    
  <delete id="deleteStudent"  parameterType="java.lang.Integer"  >
          delete  from student  where  id =  #{id}
    </delete>
        
     <delete id="deleteStudent"  parameterType="int"  >
          delete  from student  where  id =  #{id}
    </delete>   

别名

别名	映射的类型
_byte	byte
_long	long
_short	short
_int	int
_integer	int
_double	double
_float	float
_boolean	boolean
string	String
byte	Byte
long	Long
short	Short
int	Integer
integer	Integer
double	Double
float	Float
boolean	Boolean
date	Date
decimal	BigDecimal
bigdecimal	BigDecimal
object	Object
map	Map
hashmap	HashMap
list	List
arraylist	ArrayList
collection	Collection
iterator	Iterator

B  自定义别名

在总的配置文件中

    <typeAliases>
        <typeAlias alias="stu" type="com.aaa.entity.Student"></typeAlias>
    </typeAliases>

在mapper中可以直接使用
    <insert id="saveStudent"   parameterType="stu"  >
          insert  into  student   values   (null , #{name} ,#{age},#{address}   )
    </insert> 

但是这样的还是很麻烦 因为如果实体类太多了 此时我们需要配置大量的别名，所以我们可以使用包扫描
 <typeAliases>
        <package name="com.aaa.entity"></package>
    </typeAliases>

每一个在包 domain.blog 中的 Java Bean，在没有注解的情况下，会使用 Bean 的首字母小写的非限定类名来作为它的别名。 比如 com.aaa.entity.Student 的别名为 student；若有注解，则别名为其注解值。见下面的例子：

@Alias("stu")
public class Student {
    ...
}
<insert id="saveStudent"   parameterType="student"  >
          insert  into  student   values   (null , #{name} ,#{age},#{address}   )
    </insert>

    <delete id="deleteStudent"  parameterType="int"  >
          delete  from student  where  id =  #{id}
    </delete>

1.10 ORM映射

对象关系映射（英语：Object Relational Mapping，简称ORM，或O/RM，或O/R mapping），是一种程序设计技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。从效果上说，它其实是创建了一个可在编程语言里使用的“虚拟对象数据库”。如今已有很多免费和付费的ORM产品，而有些程序员更倾向于创建自己的ORM工具。
    
mybatis可以称之为一个ORM框架。

    当我们调用这个SQL的时候
     <select id="listAll"     resultType="student"    >
          select  * from  student
    </select>

     mybatis会将 每一行数据 映射到 一个 student对象中。
    规则:  列名 对应  对象的 成员变量名 
student 表 
 id  name age  address
 1	张三	19	南京
 2	李四	19	南京
 3	李四	15	南京
 4	王五	21	上海
        
student 对象
 id  name age  address
        
如果数据库列的名字叫做 s_id  s_name s_age s_address 此时就无法注入到 student中。
我们此时可以：
 A 修改学生的成员变量名  但是不可取  因为 java命名规范为 驼峰命名
student 对象
 s_id  s_name  s_age  s_address
        
 B 修改数据库 但是数据库的命名规范为下划线        
 
 C 别名 或者 映射
  
    <select id="listAll"     resultType="student"    >
          select  s_id  id ,s_name  name,s_age age  ,s_address  address   from  student
    </select>
      
     
    <resultMap id="haha" type="student" >
         <id      column="s_id"  property="id"     ></id>
         <result  column="s_name" property="name"  ></result>
         <result  column="s_age" property="age"  ></result>
         <result  column="s_address" property="address"  ></result>
    </resultMap>

    <select id="listAll"    resultMap="haha"   >
          select  *   from  student
    </select>

深入思考：
 @Test
 public void test4() throws Exception{
        //  sqlSession.selectList     "com.aaa.mapper.StudentMapper.listAll"
        //  xml中   select  *   from  student   student
        Class.forName("com.mysql.jdbc.Driver"); //总配置文件 数据源
        Connection conn = DriverManager.getConnection("jdbc:mysql:///mytest", "root", "123456");

        PreparedStatement ps = conn.prepareStatement(" select  s_id  id ,s_name  name,s_age age  ,s_address  address   from  student" );
        ResultSet set = ps.executeQuery();
        int columnCount = set.getMetaData().getColumnCount();
        List<Object> data = new ArrayList<>();
        while( set.next()  ){

            Class<?> aClass = Class.forName("com.aaa.entity.Student");
            Object o = aClass.newInstance();

            for(int i=1;i<=columnCount;i++){
                String columnLabel = set.getMetaData().getColumnLabel(i);
                Object value = set.getObject(i);

                Field declaredField = aClass.getDeclaredField(columnLabel);
                declaredField.setAccessible(true);
                declaredField.set(  o  ,   value  );
            }
            data.add(   o );
        }

        System.out.println(data);

    }

1.11 selectOne的毛病

 @Test
    public void test2() throws Exception{

        Student student = new Student(6, "暗律撒旦法", 19, "撒旦法");
        Student s = sqlSession.selectOne("com.aaa.mapper.StudentMapper.getOne", 1);
    }


<select id="getOne" parameterType="int"   resultType="student"  >
      select   s_id  id ,s_name  name,s_age age  ,s_address  address  from student  where s_id > #{id}
</select>
        
        
org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 11

selectOne的底层其实就是selectList
    
public <T> T selectOne(String statement, Object parameter) {
        List<T> list = this.selectList(statement, parameter);
        if (list.size() == 1) {
            return list.get(0);
        } else if (list.size() > 1) {
            throw new TooManyResultsException("Expected one result (or null) to be returned by selectOne(), but found: " + list.size());
        } else {
            return null;
        }
  }

此时我们也就明白了   DML操作其实也是一样的  insert  delete  update
 public int delete(String statement, Object parameter) {
        return this.update(statement, parameter);
    }
public int insert(String statement, Object parameter) {
        return this.update(statement, parameter);
    }

 public int update(String statement, Object parameter) {
        int var4;
        try {
            this.dirty = true;
            MappedStatement ms = this.configuration.getMappedStatement(statement);
            var4 = this.executor.update(ms, this.wrapCollection(parameter));
        } catch (Exception var8) {
            throw ExceptionFactory.wrapException("Error updating database.  Cause: " + var8, var8);
        } finally {
            ErrorContext.instance().reset();
        }

        return var4;
}


并且 xml也是一样的
  <update id="saveStudent"   parameterType="student"  >
          insert  into  student   values   (null , #{name} ,#{age},#{address}   )
    </update>

    <update id="deleteStudent"  parameterType="int"  >
          delete  from student  where  s_id =  #{id}
    </update>

    <update id="updateStudent"  parameterType="student">
          update  student  set  name=#{name} , age=#{age} , address = #{address} where s_id = #{id}
    </update>

1.12 sql标签

<select id="getOne" parameterType="int"   resultType="student"  >
          select   s_id  id ,s_name  name,s_age age  ,s_address  address  from student  where s_id > #{id}
 </select>


    <select id="listAll"     resultType="student"    >
          select  s_id  id ,s_name  name,s_age age  ,s_address  address   from  student
    </select>
此时我们可以将重复的内容 使用一个sql标签 其他地方引入

 <sql id="sss">
        s_id  id ,s_name  name,s_age age  ,s_address  address
   </sql>

    <select id="getOne" parameterType="int"   resultType="student"  >
          select
           <include refid="sss" />
           from student  where s_id > #{id}
    </select>



    <select id="listAll"     resultType="student"    >
          select  
          <include refid="sss" />   
          from  student
    </select>

1.13mappers

 既然 MyBatis 的行为已经由上述元素配置完了，我们现在就要来定义 SQL 映射语句了。 但首先，我们需要告诉 MyBatis 到哪里去找到这些语句。 在自动查找资源方面，Java 并没有提供一个很好的解决方案，所以最好的办法是直接告诉 MyBatis 到哪里去找映射文件。 你可以使用相对于类路径的资源引用，或完全限定资源定位符（包括 file:/// 形式的 URL），或类名和包名等。例如：

<!-- 使用相对于类路径的资源引用 -->
<mappers>
  <mapper resource="org/mybatis/builder/AuthorMapper.xml"/>
  <mapper resource="org/mybatis/builder/BlogMapper.xml"/>
  <mapper resource="org/mybatis/builder/PostMapper.xml"/>
</mappers>

<!-- 使用完全限定资源定位符（URL） -->
<mappers>
  <mapper url="file:///var/mappers/AuthorMapper.xml"/>
  <mapper url="file:///var/mappers/BlogMapper.xml"/>
  <mapper url="file:///var/mappers/PostMapper.xml"/>
</mappers>

<!-- 使用映射器接口实现类的完全限定类名 -->
<mappers>
  <mapper class="org.mybatis.builder.AuthorMapper"/>
  <mapper class="org.mybatis.builder.BlogMapper"/>
  <mapper class="org.mybatis.builder.PostMapper"/>
</mappers>

<!-- 将包内的映射器接口实现全部注册为映射器 -->
<mappers>
  <package name="org.mybatis.builder"/>
</mappers>

二、mybatis进阶

2.1mapper.xml中获取主键

在insert 语句中 添加一行新数据。 我们的主键id都是自动递增的。此时有可能我们的业务需要在 insert之后 拿到主键 然后再继续其他的业务。

<insert id="saveStudent"   parameterType="student"    useGeneratedKeys="true"  keyColumn="s_id"  keyProperty="id" >
          insert  into  student   values   (null , #{name} ,#{age},#{address}   )
    </insert>
useGeneratedKeys   是否获取主键  设置成 true 代表获取
keyColumn          数据库主键列
keyProperty        接收主键的成员变量   
我们添加学生的时候 传递的参数是  parameterType="student"  此时获取的主键会赋值给 student 的id属性

@Test
    public void test() throws Exception{

        Student student = new Student(0, "sfafasfdasdf", 18, "sadf");

        int insert = sqlSession.insert("com.aaa.mapper.StudentMapper.saveStudent", student);

        System.out.println(insert);

        System.out.println(  student.getId()  );

    }

代码如下（示例）：

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import warnings
warnings.filterwarnings('ignore')
import  ssl
ssl._create_default_https_context = ssl._create_unverified_context

2.2statementType

在mapper文件中可以使用statementType标记使用什么的对象操作SQL语句。
statementType：标记操作SQL的对象
取值说明：
1、STATEMENT:直接操作sql，不进行预编译，获取数据：$—Statement
2、PREPARED:预处理，参数，进行预编译，获取数据：#—–PreparedStatement:默认
3、CALLABLE:执行存储过程————CallableStatement
其中如果在文件中，取值不同，那么获取参数的方式也不相同

<update id="update4" statementType="STATEMENT">
    update tb_car set price=${price} where id=${id}
    </update>
    <update id="update5" statementType="PREPARED">
    update tb_car set xh=#{xh} where id=#{id}
    </update>

代码如下（示例）：

2.3 日志输出

A 在pom文件中添加log4j的依赖

<dependency>
  <groupId>log4j</groupId>
  <artifactId>log4j</artifactId>
  <version>1.2.12</version>
</dependency>

B 在resources文件夹中创建一个log4j.properties的日志文件，并添加日志配置内容

log4j.rootLogger=ERROR, stdout

log4j.logger.com.aaa=TRACE

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

2.4 #和$的区别

简单说#{}是经过预编译的,是安全的。

而${}是未经过预编译的,仅仅是取变量的值,是非安全的,存在SQL注入。

#{} 这种取值是编译好SQL语句再取值 ${} 这种是取值以后再去编译SQL语句

<update id="deleteStudent"  parameterType="int"  >
          delete  from student  where  s_id =  ${id}
 </update>

DEBUG [main] - ==>  Preparing: delete from student where s_id = 5
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==    Updates: 0


 <update id="deleteStudent"  parameterType="int"  >
          delete  from student  where  s_id =  #{id}
 </update>                         

DEBUG [main] - ==>  Preparing: delete from student where s_id = ?
DEBUG [main] - ==> Parameters: 5(I

2.5

A 为什么需要接口绑定
这是我们现在使用mybatis写的代码
Student student = new Student(6, “暗律撒旦法”, 19, “撒旦法”);
Student s = sqlSession.selectOne(“com.aaa.mapper.StudentMapper.getOne”, 1);
这个代码的问题是：
1 “com.aaa.mapper.StudentMapper.getOne” 不能写错 一旦写错就有问题的 并且写起来很麻烦
2 参数 1 代表要查询的id 对应的是 SQL语句

select

from student where s_id = #{id}

但是 我们这个函数的参数可以随便写
Student student = new Student(6, “暗律撒旦法”, 19, “撒旦法”);
Student s = sqlSession.selectOne(“com.aaa.mapper.StudentMapper.getOne”, 1.5);
此时就会出错
所以mybatis 就退出了一套接口绑定的玩法，让我们能解决这些问题

B 接口绑定的四对应

第一步：创建一个接口 com.aaa.mapper.StudentMapper
public interface StudentMapper {

}

第二步： 添加接口函数
public interface StudentMapper {

 int   saveStudent(Student s);

}
第三步： 创建mapper.xml 添加对相应标签（四对应）
① namespace 对应 接口的全限定名
② 标签id 对应 函数名
③ parameterType 对应 函数的参数类型
④ resultType 对应 函数的返回值类型

package com.aaa.mapper;
import com.aaa.entity.Student;
public interface StudentMapper {
Student getOne(Integer id);
}

 <select id="getOne"  parameterType="int"  resultType="student">

     select  s_id id,s_name name,s_age age,s_address address  from  student  where s_id = #{id}

 </select>

第四步： 调用
@Test
public void test1(){
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student one = mapper.getOne(7);
System.out.println(one);
}

原理：
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student one = mapper.getOne(7);

System.out.println(one);
此时有个大问题， StudentMapper是一个接口，此时我们并没有写实现类。但是 Student one = mapper.getOne(7); 还能调用。
说明 StudentMapper 有实现类。但是我们没学 那肯定是mybatis帮我们写的

sqlSession.getMapper(StudentMapper.class); 返回的就是 StudentMapper 实现类的对象
该处使用的url网络请求的数据。

底层原理

public <T> T getMapper(Class<T> type) {
    return this.configuration.getMapper(type, this);
}
public <T> T getMapper(Class<T> type, SqlSession sqlSession) {
        return this.mapperRegistry.getMapper(type, sqlSession);
    }

 public <T> T getMapper(Class<T> type, SqlSession sqlSession) {
        MapperProxyFactory<T> mapperProxyFactory = (MapperProxyFactory)this.knownMappers.get(type);
        if (mapperProxyFactory == null) {
            throw new BindingException("Type " + type + " is not known to the MapperRegistry.");
        } else {
            try {
                return mapperProxyFactory.newInstance(sqlSession);
            } catch (Exception var5) {
                throw new BindingException("Error getting mapper instance. Cause: " + var5, var5);
            }
        }
    }

public T newInstance(SqlSession sqlSession) {
        MapperProxy<T> mapperProxy = new MapperProxy(sqlSession, this.mapperInterface, this.methodCache);
        return this.newInstance(mapperProxy);
    }
 protected T newInstance(MapperProxy<T> mapperProxy) {
        return Proxy.newProxyInstance(this.mapperInterface.getClassLoader(), new Class[]{this.mapperInterface}, mapperProxy);
}

接口绑定代码：

public interface StudentMapper {

     Student  getOne(Integer  id);

     int   insertStu(Student   s);

     int   deleteStu(Integer  id);

     int   updateStu(Student  s);

     List<Student> listAll();
}

<mapper namespace="com.aaa.mapper.StudentMapper">

    <!-- 如果接口绑定 参数类型parameterType可以不写  但是resultType一定要写 -->
     <select id="getOne"   resultType="student">
         select  s_id id,s_name name,s_age age,s_address address  from  student  where s_id = #{id}
     </select>


     <insert id="insertStu"  >
          insert  into  student  values (null,#{name},#{age},#{address})
     </insert>

    <delete id="deleteStu" >
        delete  from student  where s_id = #{id}
    </delete>

    <update id="updateStu" >
        update student set s_name = #{name},s_age=#{age},s_address=#{address}  where s_id=#{id}
    </update>

    <select id="listAll"  resultType="student">
          select  s_id id,s_name name,s_age age,s_address address  from  student
    </select>


</mapper>

2.6多参数传递

例如 我们需要去 login表中 查询 username  和 password 。
    
方案A  将 username  和 password 封装到一个对象中 ，再将对象当成参数
方案B  将 username  和 password 封装到一个Map中 ，再将Map对象当成参数
方案C  传递多个参数 用注解配置
List<Student> queryStudent(@Param("haha") int id, @Param("hehe") int age);

<select id="queryStudent"  resultType="student">
          select  s_id id,s_name name,s_age age,s_address address
          from
          student
          where  s_id = #{haha}  and  s_age = #{hehe}
 </select>
其实很垃圾，底层其实就是一个map   相当于
  Map  map = new HashMap();
  map.put("haha" , id );
  map.put("hehe" , age );
List<Student> queryStudent(Map  m);

2.7动态sql

接口代码
  List<Student> queryStudent(StudentQuery query);

@Data
@AllArgsConstructor
@NoArgsConstructor
public class StudentQuery implements Serializable {

    private  String   name;
    private  Integer  minAge;
    private  Integer  maxAge;

    private  Integer  currentPage;
    private  Integer  pageCount;

    public Integer  getIndex(){
        return (currentPage-1)*pageCount;
    }
}
测试类
    @Test
    public void test1(){
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        StudentQuery studentQuery = new StudentQuery("", 12, 25, 1, 3);

        List<Student> students = mapper.queryStudent(studentQuery);
        System.out.println(students);
    }
}

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 namespace="com.aaa.mapper.StudentMapper">


    <select id="queryStudent"  resultType="com.aaa.query.StudentQuery">
        select  id id,name name,age age,address address  from  student

        <where>
            <if  test="  name != null and  name != ''   ">
                and   name   like   concat('%' , #{name}  , '%' )
            </if>
            <if test="   minAge != null  and  minAge != ''  ">
                and   age  &gt;  #{minAge}
            </if>
            <if test="   maxAge != null  and  maxAge != ''  ">
                and   age   &lt;  #{maxAge}
            </if>
        </where>

        limit   #{ index } , #{ pageCount }

    </select>
</mapper>
#### 注解配置sql
mybatis支持省略mapper.xml 只写接口 并且在接口方法上面通过注解配置SQL

A 配置以下接口扫描 mybatis-config.xml

    <mappers>
        <package name="com.aaa.mapper"></package>
    </mappers>
B 此时直接在方法上面通过注解配置SQL

public interface StudentMapper {

     @Select("select  s_id id,s_name name ,s_age age ,s_address address from student  where s_id = #{id}")
     Student  getOne(Integer  id);

     @Insert(" insert  into student  values (null,#{name},#{age},#{address})  ")
     @Options(useGeneratedKeys = true,keyColumn = "s_id" , keyProperty = "id")
     int   insertStu(Student   s);

     @Delete("delete from student where s_id = #{id}")
     int   deleteStu(Integer  id);

     @Update("update student set s_name=#{name},s_age=#{age},s_address=#{address} where s_id = #{id}")
     int   updateStu(Student  s);

     @Select("select  s_id id,s_name name ,s_age age ,s_address address from student")
     List<Student> listAll();

}