mybatis基础学习1

客官°小女子只卖身不卖艺 2022-09-03 04:19 220阅读 0赞

## 写在前面 ##

接[这篇文章][Link 1]。

## 14：LanguageDriver ##

该接口用于获取和封装相关的sql信息以及sql所需要的参数信息：

public interface LanguageDriver { 
      // 创建用于传递给JDBC statement的实际参数的ParameterHandler对象
      ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql);
    
      // 创建封装xml文件中statement信息的SqlSource对象，在启动时加载xml的过程中执行
      SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType);
    
      // 同上一个方法，不过数据来源是注解
      SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType);
    }

接下来我们来自定义一个LanguageDriver，看下如何使用。

### 14.1：建表 ###

CREATE TABLE `test_language_driver` (
      `id` INT(32) NOT NULL AUTO_INCREMENT,
      `myname` VARCHAR(63) DEFAULT NULL,
      `myage` INT(32) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8

### 14.2：对应实体 ###

public class TestLanguageDriver { 
        private Integer id;
        private String myname;
        private Integer myage;
    
        ...getter setter tostring...
    }

### 14.3：对应dto ###

public class TestLanguageDriverDto { 
        private Integer id;
        private String myname;
        private Integer myage;
    
        ...getter setter tostring...
    }

### 14.4：对应接口 ###

public interface TestLanguageDriverMapper { 
    
        TestLanguageDriver fetchById(int id);
        void insertOne(TestLanguageDriverDto testLanguageDriverDto);
    
    }

### 14.5：自定义LanguangeDriver ###

> 只是做了sout，证明被调用了！

public class MyLanguageDriver extends XMLLanguageDriver { 
    
        @Override
        public SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType) { 
            System.out.println("MyLanguageDriver.createSqlSource");
            return super.createSqlSource(configuration, script, parameterType);
        }
    }

### 14.6：定义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="yudaosourcecode.mybatis.mapper.TestLanguageDriverMapper">
        
        <resultMap id="myResultMap" type="yudaosourcecode.mybatis.model.TestLanguageDriver">
            <id property="id" column="id" javaType="integer" jdbcType="INTEGER"/>
            <result property="myname" column="myname" javaType="string" jdbcType="VARCHAR"/>
            <result property="myage" column="myage" javaType="integer" jdbcType="INTEGER"/>
        </resultMap>
    
        
        <parameterMap id="myParameterMap" type="yudaosourcecode.mybatis.dto.TestLanguageDriverDto">
            <parameter property="id" javaType="integer" jdbcType="INTEGER"/>
            <parameter property="myname" javaType="string" jdbcType="VARCHAR"/>
            <parameter property="myage" javaType="integer" jdbcType="INTEGER"/>
        </parameterMap>
    
        <insert id="insertOne" parameterMap="myParameterMap">
            INSERT INTO `test_language_driver`
            (`myname`,
             `myage`)
            VALUES (#{myname},
                    #{myage})
        </insert>
    
        <select id="fetchById" resultMap="myResultMap" lang="MyLanguageDriver">
            SELECT * FROM test_language_driver t WHERE t.`id`=#{id}
        </select>
    </mapper>

### 14.7：定义测试代码 ###

public class TestLanguageDriverMapperTest { 
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession sqlSession;
        private TestLanguageDriverMapper testLanguageDriverMapper;
        ///467d8d45399f44fd951793509add17f7
        @Before
        public void setUp() throws Exception { 
            String resource = "mybatis/mybatis-config-1.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            this.sqlSessionFactory = sqlSessionFactory;
            this.sqlSession = this.sqlSessionFactory.openSession();
            this.testLanguageDriverMapper =
                    this.sqlSession.getMapper(TestLanguageDriverMapper.class);
        }
    
        @Test
        public void fetchById() { 
            System.out.println(testLanguageDriverMapper.fetchById(1));
        }
    
        @Test
        public void insertOne() { 
            TestLanguageDriverDto testLanguageDriverDto = new TestLanguageDriverDto();
            testLanguageDriverDto.setMyage(new Random().nextInt(100));
            testLanguageDriverDto.setMyname(UUID.randomUUID().toString());
            testLanguageDriverMapper.insertOne(testLanguageDriverDto);
    
            sqlSession.commit();
            sqlSession.close();
        }
    }

执行任意测试代码，可以看到自定义LanguageDriver被调用了，如下：

MyLanguageDriver.createSqlSource
    {"id":1,"myage":82,"myname":"930aef2e-f6a8-49dd-a38b-2ab7f8ef77cf"}
    Process finished with exit code 0

## 15：statement`insert`中selectKey ##

用在`<insert/>`标签内部，用来生成主键，下面看个例子。

### 15.1：表 ###

create table test_selectkey(
    	id varchar(128) primary key,
    	myname varchar(64),
    	myage int(32)
    );

### 15.2：实体 ###

public class TestSelectKey { 
        private String id;
        private String myname;
        private int myage;
    
        ...getter setter tostring...
    }

### 15.3：dto ###

public class TestSelectKeyDto { 
        private String id;
        private String myname;
        private int myage;
    
        ...getter setter tostring...
    }

### 15.4：接口 ###

public interface TestSelectKeyMapper { 
        void insertByQueryParamter(TestSelectKeyDto testSelectKeyDto);
        TestSelectKey getById(String id);
    }

### 15.5：mapper 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="yudaosourcecode.mybatis.mapper.TestSelectKeyMapper">
        <resultMap id="myResultMap" type="yudaosourcecode.mybatis.model.TestSelectKey">
            <id property="id" column="id" javaType="string" jdbcType="VARCHAR"/>
            <result property="myname" column="myname" javaType="string" jdbcType="VARCHAR"/>
            <result property="myage" column="myage" javaType="integer" jdbcType="INTEGER"/>
        </resultMap>
    
        <parameterMap id="myParameterMap" type="yudaosourcecode.mybatis.dto.TestSelectKeyDto">
            <parameter property="id" javaType="string" jdbcType="VARCHAR"/>
            <parameter property="myname" javaType="string" jdbcType="VARCHAR"/>
            <parameter property="myage" javaType="integer" jdbcType="INTEGER"/>
        </parameterMap>
    
        <insert id="insertByQueryParamter" parameterMap="myParameterMap">
            
            <selectKey keyProperty="id" keyColumn="id" resultType="java.lang.String" order="BEFORE">
                select uuid()
            </selectKey>
            INSERT INTO `test_selectkey`
                    (`id`,
                    `myname`,
                    `myage`)
            VALUES (#{id},
                    #{myname},
                    #{myage})
        </insert>
    </mapper>

### 15.6：测试代码 ###

public class TestSelectKeyMapperTest { 
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession sqlSession;
        private TestSelectKeyMapper testSelectKeyMapper;
    
        @Before
        public void setUp() throws Exception { 
            String resource = "mybatis/mybatis-config-1.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            this.sqlSessionFactory = sqlSessionFactory;
            SqlSession sqlSession = sqlSessionFactory.openSession();
            this.sqlSession = sqlSession;
            this.testSelectKeyMapper = sqlSession.getMapper(TestSelectKeyMapper.class);
        }
    
        @Test
        public void insertByQueryParamter() { 
            TestSelectKeyDto testSelectKeyDto = new TestSelectKeyDto();
            testSelectKeyDto.setMyage(new Random().nextInt(100));
            testSelectKeyDto.setMyname(UUID.randomUUID().toString());
            testSelectKeyMapper.insertByQueryParamter(testSelectKeyDto);
            sqlSession.commit();
            sqlSession.close();
        }
    
        @Test
        public void getById() { 
        }
    }

运行`insertByQueryParamter`即可插入数据。

## 16：注解方式配置mybatis ##

### 16.1：表 ###

CREATE TABLE test_mybatis_annotation(
    	id INT(32) AUTO_INCREMENT PRIMARY KEY,
    	myname VARCHAR(64),
    	myage INT(32)
    );

### 16.2：实体 ###

public class TestMybatisAnnotation implements Serializable { 
    
        private Integer id;
        private String myname;
        private Integer myage;
    
        ...snip getter setter tostring...
    }

### 16.3：dto ###

public class TestMybatisAnnotationDto { 
        private Integer id;
        private String myname;
        private Integer myage;
    
        ...snip getter setter tostring...
    }

### 16.4：mapper接口 ###

// 在xml中增删改查的四个标签分别是<insert/>,<delete/>,<update/>,<select/>
    // 对应的注解版本是@Insert,@Delete,@Update,@Select
    @CacheNamespace(implementation = PerpetualCache.class, eviction = LruCache.class)
    public interface TestMybatisAnnotationMapper { 
        // 增加的方法
        @Insert(value = "INSERT INTO `test_mybatis_annotation` (`myname`, `myage`) VALUES (#{myname}, #{myage})")
        void insertOne(TestMybatisAnnotationDto testMybatisAnnotationDto);
        // 删除的方法
        // 修改的方法
        // 查询的方法
        @Select(value = "SELECT * FROM test_mybatis_annotation t WHERE t.`id`=#{id} ")
        TestMybatisAnnotation findOne(int id);
    }

### 16.5：mapper xml ###

不需要，信息配置在mapper接口中配置。

### 16.6：全局config配置文件 ###

需要配置mapper接口所在包mappers的扫描，如下是我本地的：

### 16.7：测试代码 ###

public class TestMybatisAnnotationMapperTest { 
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession sqlSession;
        private TestMybatisAnnotationMapper testMybatisAnnotationMapper;
    
        @Before
        public void setUp() throws Exception { 
            String resource = "mybatis/mybatis-config-annotation.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            this.sqlSessionFactory = sqlSessionFactory;
            this.sqlSession = this.sqlSessionFactory.openSession();
            this.testMybatisAnnotationMapper =
                    this.sqlSession.getMapper(TestMybatisAnnotationMapper.class);
        }
        @Test
        public void insertOne() { 
            TestMybatisAnnotationDto testMybatisAnnotationDto = new TestMybatisAnnotationDto();
            testMybatisAnnotationDto.setMyage(new Random().nextInt(100));
            testMybatisAnnotationDto.setMyname(UUID.randomUUID().toString());
            testMybatisAnnotationMapper.insertOne(testMybatisAnnotationDto);
            sqlSession.commit();
            sqlSession.close();
        }
    
        @Test
        public void findOne() { 
            /*System.err.println("同session第一次查询："); System.out.println(testMybatisAnnotationMapper.findOne(1)); System.err.println("同session第二次查询："); System.out.println(testMybatisAnnotationMapper.findOne(1)); */
            System.err.println("不同session第一次查询：");
            SqlSession sqlSession1 = sqlSessionFactory.openSession();
            System.out.println(sqlSession1.getMapper(TestMybatisAnnotationMapper.class).findOne(1));
            sqlSession1.close();
            System.err.println("不同session第二次查询：");
            SqlSession sqlSession2 = sqlSessionFactory.openSession();
            System.out.println(sqlSession2.getMapper(TestMybatisAnnotationMapper.class).findOne(1));
    
        }
    }

运行，略。

## 17：动态sql ##

> 使用mapper 接口注解和mapper xml混合方式，分别演示使用注解和在xml中使用动态sql语句。

### 17.1：建表 ###

CREATE TABLE test_dynamic_sql(
    	id INT(32) AUTO_INCREMENT PRIMARY KEY,
    	myname VARCHAR(64),
    	myage INT(32)
    );

### 17.2：实体 ###

public class TestDynamicSql { 
        private Integer id;
        private String myname;
        private Integer myage;
    
        ...snip getter setter tostrin...
    }

### 17.3：dto ###

public class TestDynamicSqlDto { 
        private Integer id;
        private String myname;
        private Integer myage;
    
        ...snip getter setter tostring...
    }

### 17.4：mapper 接口 ###

public interface TestDynamicSqlMapper { 
        @Insert(value = {  "INSERT INTO `test_dynamic_sql`",
                "(`myname`, `myage`)",
                "VALUES",
                "(#{myname}, #{myage})" })
        void insertOneWithSqlAnnotation(TestDynamicSqlDto testDynamicSqlDto);
    
        @Select("<script>" +
                "select * from test_dynamic_sql " +
                "<if test=\"myname!=null\"> where myname like #{myname}</if>" +
                "</script>")
        List<TestDynamicSql> queryListWithAnnotationDynamicSql(TestDynamicSqlDto testDynamicSqlDto);
    
        List<TestDynamicSql> queryListWithXmlDynamicSql(TestDynamicSqlDto testDynamicSqlDto);
    }

### 17.5：mapper xml ###

### 17.6：测试代码 ###

public class TestDynamicSqlMapperTest { 
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession sqlSession;
        private TestDynamicSqlMapper testDynamicSqlMapper;
    
        @Before
        public void setUp() throws Exception { 
            String resource = "mybatis/mybatis-config-annotation.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            this.sqlSessionFactory = sqlSessionFactory;
            this.sqlSession = this.sqlSessionFactory.openSession();
            this.testDynamicSqlMapper =
                    this.sqlSession.getMapper(TestDynamicSqlMapper.class);
        }
    
        @Test
        public void insertOneWithSqlAnnotation() { 
            TestDynamicSqlDto testDynamicSqlDto = new TestDynamicSqlDto();
            testDynamicSqlDto.setMyname(UUID.randomUUID().toString());
            testDynamicSqlDto.setMyage(new Random().nextInt(100));
            this.testDynamicSqlMapper.insertOneWithSqlAnnotation(testDynamicSqlDto);
            this.sqlSession.commit();
            this.sqlSession.close();
        }
    
        @Test
        public void queryListWithAnnotationDynamicSql() { 
            TestDynamicSqlDto testDynamicSqlDto = new TestDynamicSqlDto();
            testDynamicSqlDto.setMyname("%8%");
            System.out.println(this.testDynamicSqlMapper
                    .queryListWithAnnotationDynamicSql(testDynamicSqlDto));
        }
    
        @Test
        public void queryListWithXmlDynamicSql() { 
            TestDynamicSqlDto testDynamicSqlDto = new TestDynamicSqlDto();
            testDynamicSqlDto.setMyage(8);
            System.out.println(this.testDynamicSqlMapper
                    .queryListWithXmlDynamicSql(testDynamicSqlDto));
        }
    }

可以运行各个方法进行相关测试。

## 18：bind标签 ##

> 本部分在`17：动态sql`基础上分析。

`<bind/>`标签用于处理传入参数值，处理后绑定到另一个参数上，执行一个转换的操作，比如模糊查询场景，`%`就可以通过这种方式添加，避免程序每个地方都做重复的工作。  
在mapper 接口中增加如下方法：

List<TestDynamicSql> queryListWithXmlDynamicSqlBind(TestDynamicSqlDto testDynamicSqlDto);

在mapper xml中提供实现，并使用`<bind/>`标签转换myname参数添加`%`，如下：

<select id="queryListWithXmlDynamicSqlBind" resultMap="myResultMap" statementType="PREPARED">
        
        <bind name="myname_add_percent" value="'%' + myname + '%'"/>
        SELECT * FROM test_dynamic_sql t WHERE t.`myname` LIKE #{myname_add_percent}
    </select>

测试代码：

@Test
    public void queryListWithXmlDynamicSqlBind() { 
        TestDynamicSqlDto testDynamicSqlDto = new TestDynamicSqlDto();
        testDynamicSqlDto.setMyname("4ad4");
        System.out.println(this.testDynamicSqlMapper
                .queryListWithXmlDynamicSqlBind(testDynamicSqlDto));
    }

运行，如下是我本地的输出日志:

![在这里插入图片描述][f98a2b0a41454911a1e09eb7336a0caf.png]  
从日志中可以看出来，已经在字符串的前后成功添加了`%`了。

## 19：trim,set,where标签 ##

> 本部分在`17：动态sql`基础上分析。

这三个标签是在动态SQL语句中，用于处理因为某些if条件不满足而导致的sql语法错误问题。

### 19.1：where ###

where标签用于过滤结果，有时候我们在mybatis中会这样写：

SELECT * FROM test_dynamic_sql t where
    <if test="myname!=null">
        t.`myname` LIKE #{myname}
    </if>
    <if test="myage!=null">
        and myage > #{myage}
    </if>

但是此时如果是第一个条件不满足sql语句就变为`SELECT * FROM test_dynamic_sql t where and myage > #{myage}`,很明显不满足sql语法，或者是2个条件都不满足则sql语句变为`SELECT * FROM test_dynamic_sql t where`，同样是不满足sql语法，`<where/>`标签就是用来解决这个问题的，会动态决定是否添加where关键字，以及是否需要删除where关键字后的and关键字，如下接口配置和mapper xml配置：

List<TestDynamicSql> queryListWithXmlDynamicSqlWhere(TestDynamicSqlDto testDynamicSqlDto);

<select id="queryListWithXmlDynamicSqlWhere" resultMap="myResultMap" statementType="PREPARED">
       SELECT * FROM test_dynamic_sql t 
        <where>
            <if test="myname!=null">
                t.`myname` LIKE #{myname}
            </if>
            <if test="myage!=null">
                and myage > #{myage}
            </if>
        </where>
    </select>

就能解决上述描述的问题了，主要做了如下的几件事：

1：动态添加where前缀，如果是需要的话
    2：动态删除where关键字后的and，如果是需要的话

对于上述的过程，mybatis还提供了`<trim/>`标签达到同样的效果，该标签信息如下：

<!ATTLIST trim
    prefix CDATA #IMPLIED
    prefixOverrides CDATA #IMPLIED
    suffix CDATA #IMPLIED
    suffixOverrides CDATA #IMPLIED
    >

可以看到有2对属性分别是`prefix ,prefixOverrides`,`suffix,suffixOverrides`,作用是`添加前缀/后缀，以及替换前后缀指定字符`，比如设置`prefix="where" prefixOverrides="and"`意思就是添加where前缀，并动态删除where后的and关键字，上例修改mapper xml 如下：

<select id="queryListWithXmlDynamicSqlWhere" resultMap="myResultMap" statementType="PREPARED">
        SELECT * FROM test_dynamic_sql t 
         
         
         <trim prefix="where" prefixOverrides="or|and">
             <if test="myname!=null">
                 t.`myname` LIKE #{myname}
             </if>
             <if test="myage!=null">
                 and myage > #{myage}
             </if>
         </trim>
    </select>

### 19.2：set ###

当我们进行更新数据的时候可能会写如下的sql语句：

<update id="queryListWithXmlDynamicSqlSet" parameterType="yudaosourcecode.mybatis.annotation.dto.TestDynamicSqlDto">
        UPDATE test_dynamic_sql SET 
        <if test="myname!=null">myname=#{myname},</if>
        <if test="myage!=null">myage=#{myage}</if>
        WHERE id=2
    </update>

此时，当第二个条件为空时生成的sql语句为`UPDATE test_dynamic_sql SET myname=#{myname},`，当二者都为空时生成的sql语句是`UPDATE test_dynamic_sql SET`，这两种情况都会有sql的语法错误问题，为了解决这类问题，mybatis引入了`<set/>`标签，可以修改如下：

<update id="queryListWithXmlDynamicSqlSet" parameterType="yudaosourcecode.mybatis.annotation.dto.TestDynamicSqlDto">
    
    
    
    
            UPDATE test_dynamic_sql
            <set>
                <if test="myname!=null">myname=#{myname},</if>
                <if test="myage!=null">myage=#{myage}</if>
            </set>
            WHERE id=2
    </update>

此时mybatis会替我们去除`可能多余的,或者是set关键字`，类似于where中使用trim解决这个问题，此处也是可以的，因为trim可以动态的设置要添加的前后缀以及需要删除的前后缀，即，trim对于此类问题提供了通用的解决方案，修改如下：

<update id="queryListWithXmlDynamicSqlSet" parameterType="yudaosourcecode.mybatis.annotation.dto.TestDynamicSqlDto">
       
       
       
       
       
       UPDATE test_dynamic_sql
       <trim prefix="set" suffixOverrides=",">
           <if test="myname!=null">myname=#{myname},</if>
           <if test="myage!=null">myage=#{myage}</if>
       </trim>
       WHERE id=2
    </update>

## 20：foreach ##

> foreach的作用就是通过给定的数据源按照设置的格式生成字符串，最终用于拼接生成sql语句，我们只需要根据自己想要的目标字符串来设置相关的信息即可。

本部分在`17：动态sql`基础上分析。  
我们来看个动态生成in语句中的`('ele1', 'ele2')`的例子，创建mapper 接口和mapper xml，如下：

List<TestDynamicSql> queryListWithXmlDynamicSqlForeach(List<Integer> idsList);

<select id="queryListWithXmlDynamicSqlForeach" resultMap="myResultMap" parameterType="java.util.List">
        SELECT * FROM test_dynamic_sql t
        WHERE t.`id` IN
        <foreach collection="list" index="" open="(" close=")" item="itemId" separator=",">
            #{ itemId}
        </foreach>
    </select>

测试代码：

@Test
    public void queryListWithXmlDynamicSqlForeach() { 
        List<Integer> idsList = new ArrayList() { 
            { 
                add(1);
                add(2);
            }
        };
        System.out.println(this.testDynamicSqlMapper.queryListWithXmlDynamicSqlForeach(idsList));
    }

运行如下是我本地日志：

2021-08-18 16--14---16,630 [dongshidaddy--^^--yudaosourcecode.mybatis.annotation.mapper.TestDynamicSqlMapper.queryListWithXmlDynamicSqlForeach]-[DEBUG] ==>  Preparing: SELECT * FROM test_dynamic_sql t WHERE t.`id` IN ( ? , ? ) 
    2021-08-18 16--14---16,650 [dongshidaddy--^^--yudaosourcecode.mybatis.annotation.mapper.TestDynamicSqlMapper.queryListWithXmlDynamicSqlForeach]-[DEBUG] ==> Parameters: 1(Integer), 2(Integer)
    2021-08-18 16--14---16,681 [dongshidaddy--^^--yudaosourcecode.mybatis.annotation.mapper.TestDynamicSqlMapper.queryListWithXmlDynamicSqlForeach]-[DEBUG] <==      Total: 2
    [{ "id":1,"myage":65,"myname":"8fb20cb1-2752-4181-87a5-3adb30ee72df"}, { "id":2,"myage":8,"myname":"%4ad4%"}]

[Link 1]: https://blog.csdn.net/wang0907/article/details/118482256
[f98a2b0a41454911a1e09eb7336a0caf.png]: /images/20220829/29dc2d87a23444728318919e4ade71aa.png