MyBatis

发表于 阅读次数：
本文字数： 20k 阅读时长 ≈ 19 分钟

你能够被理解吗

MyBatis

ORMapping: Object Relationship Mapping 对象关系映射
Java 到 MySQL 的映射、以面向对象的思想来管理理数据库

简单使用

源生接口执行

  • pom.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.dream.xiaobo</groupId>
    <artifactId>mybatis1</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.6</version>
        </dependency>

    </dependencies>

<!--    保证能扫描到mapper.xml文件-->
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>
    </build>

</project>
  • entity
1
2
3
4
5
6
7
8
9
10
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {

    private Long id;
    private String username;
    private String password;

}
  • mybatis-config.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<?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是执行哪一个-->
    <environments default="mybatisTest">
        <!--    配置Mybatis运行环境-->
        <environment id="mybatisTest">
<!--            配置JDBC事务管理-->
            <transactionManager type="JDBC"></transactionManager>
<!--            配置POOLED数据源连接池-->
            <dataSource type="POOLED">
<!--                配置使用驱动-->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<!--                配置数据库路径-->
                <property name="url" value="jdbc:mysql://localhost:3306/db_mybatis?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC"/>
<!--                配置数据库用户名-->
                <property name="username" value="root"/>
<!--                配置数据库密码-->
                <property name="password" value="xiaobo"/>
            </dataSource>
        </environment>
    </environments>

<!--    映射到mapper文件-->
    <mappers>
        <mapper resource="com/dream/xiaobo/mapper/userMapper.xml"></mapper>
    </mappers>
</configuration>
  • userMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
<?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">

<!--namespace指定自己的位置-->
<mapper namespace="com.dream.xiaobo.mapper.userMapper">

<!--    添加语句-->
    <insert id="save" parameterType="com.dream.xiaobo.entity.User">
        INSERT INTO user(username,password) VALUES(#{username },#{password })
    </insert>

</mapper>
  • test
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public static void main(String[] args) {
        //读取配置文件
        InputStream stream = MybastisTest.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
        //创建SqlSessionFactoryBuilder工厂
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        //在SqlSessionFactoryBuilder工厂中同过build方法拿到SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(stream);
        //SqlSessionFactory通过openSession方法拿到SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();

        String url = "com.dream.xiaobo.mapper.userMapper.save";

        User user = new User(1L,"xiaobo","xiaboo");
        //告诉位置以及传入的对象准备执行
        sqlSession.insert(url,user);
        //提交事务、开始执行
        sqlSession.commit();

    }

mapper代理实现接口执行

  • userRespository
1
2
3
4
5
6
7
8
9
10
11
12
13
public interface userRespository {

    public int save(User user);

    public int update(User user);

    public int deleteById(Long id);

    public List<User> findAll();

    public User findById(Long id);

}
  • userRespository.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?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.dream.xiaobo.respository.userRespository">

    <insert id="save" parameterType="com.dream.xiaobo.entity.User">
        INSERT INTO user(username,password) VALUES(#{username },#{password })
    </insert>
    <update id="update" parameterType="com.dream.xiaobo.entity.User">
        UPDATE user SET username = #{username },password = #{password } WHERE id = #{id }
    </update>
    <delete id="deleteById" parameterType="long">
        DELETE FROM user WHERE id = #{id }
    </delete>
    <select id="findAll" resultType="com.dream.xiaobo.entity.User">
        SELECT * FROM user
    </select>
    <select id="findById" parameterType="long" resultType="com.dream.xiaobo.entity.User">
        SELECT * FROM user WHERE id = #{id }
    </select>
</mapper>

namespace:当前mapper的所在包名+文件名
insert：执行添加操作
delete：执行删除操作
update：执行更新操作
select：执行查询操作
id：与接口方法名相同
parameterType：与接口中对应方法的参数类型一致
resultType：与接口中对应方法的返回值类型一致

  • mybatis-config.xml
1
2
3
4
<!--    映射到mapper文件-->
    <mappers>
        <mapper resource="com/dream/xiaobo/respository/userRespository.xml"></mapper>
    </mappers>
  • test
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
public static void main(String[] args) {

        InputStream stream = TestMybatis.class.getClassLoader().getResourceAsStream("mybatis-config.xml");

        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();

        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(stream);

        SqlSession sqlSession = sqlSessionFactory.openSession();

//        拿到userRespository的运行时类
        userRespository mapper = sqlSession.getMapper(userRespository.class);

        //查询全部数据
        List<User> list = mapper.findAll();
        for(User user: list){
            System.out.println(user);
        }

//        根据Id进行查询
        User byId = mapper.findById(1L);
        System.out.println(byId);

//        添加数据
        mapper.save(new User(4L,"wyb","xiaobo"));
        sqlSession.commit();

//        更新数据
        User user = mapper.findById(1L);
        user.setUsername("wxh");
        user.setPassword("xiaobo");
        int update = mapper.update(user);
        sqlSession.commit();

//        删除数据
        mapper.deleteById(1L);
        sqlSession.commit();


//        关闭资源
        sqlSession.close();
    }

mapper.xml

parameterType 参数数据类型

  • 基本数据类型
  • String类型
  • 包装类类型
  • 多个参数
1
2
3
<select id="findByNameAndAge">
  select * from user where username = #{arg0} and password = #{arg1}
</select>
  • javaBean

resultType 结果数据类型

  • 基本数据类型
  • String数据类型
  • 包装类数据类型
  • javaBean

级联查询

一对多的情况

  • entity
1
2
3
4
5
6
7
8
9
10
11
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {

    private Long id;
    private String name;
    private String sex;
    private Integer age;
    private User user;
}
  • mapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<resultMap id="studentMap" type="com.dream.xiaobo.entity.Student">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="sex" property="sex"></result>
        <result column="age" property="age"></result>
        <association property="user" javaType="com.dream.xiaobo.entity.User">
            <id column="id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
        </association>
    </resultMap>

    <select id="findById2" resultMap="studentMap" parameterType="java.lang.Integer">
        SELECT s.id,s.age,s.`name`,s.sex,u.username,u.`password` FROM student s,user u
        WHERE s.id = 1 AND s.userId = u.id
    </select>
````

> resultMap：是数据库与实体类形成映射关系  
> id：主键字段映射  
> result：普通字段映射
> column：数据库表字段名  
> property：实体类字段名  
> association：对象之间映射

**多对对的情况**

- entity

```java
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {

    private Long id;
    private String name;
    private String sex;
    private Integer age;
    private List<User> users;
}
  • mapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<resultMap id="studentMap" type="com.dream.xiaobo.entity.Student">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="sex" property="sex"/>
        <result column="age" property="age"/>
        <collection property="users" ofType="com.dream.xiaobo.entity.User">
            <id column="id" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
        </collection>
    </resultMap>


    <select id="findById2" resultMap="studentMap" parameterType="java.lang.Integer">
        SELECT s.id,s.age,s.`name`,s.sex,u.username,u.`password` FROM student s,user u
        WHERE s.id = 1 AND s.userId = u.id
    </select>

collention：集合对象映射
ofType：泛型内对象

MyBatis延迟加载

别名懒加载、惰性加载、可提高程序的运行效率、在某些特定的情况下去访问特定的数据库、在某些情况下不去访问某些表、在一定成都上减少了交互次数、根据具体的业务需求来动态减少数据表的查询工作

1
2
3
4
5
6
<settings>
        <!-- 打印SQL-->
        <setting name="logImpl" value="STDOUT_LOGGING" />
        <!-- 开启延迟加载 -->
        <setting name="lazyLoadingEnabled" value="true"/>
    </settings>

MyBatis缓存

一级缓存：SqlSession级别、默认开启、不能关闭

创建SqlSession对象、在对象中有一个HashMap用于存储数据、不同的SqlSession之间缓存数据区域是互不影响的

一级缓存的作用域是SqlSession、当同一个SqlSession中执行两次相同的SQL语句、第一次执行完毕会将结果保存到缓存中、第二次查询时直接从缓存中获取

SqlSession执行了DML操作（INSERT、UPDATE、DELETE）、MyBatis必须将缓存清空保证数据的准确性、否则会出现读脏数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
public static void main(String[] args) {

        InputStream stream = TestMybatis.class.getClassLoader().getResourceAsStream("mybatis-config.xml");

        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();

        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(stream);

        SqlSession sqlSession = sqlSessionFactory.openSession();

//        拿到userRespository的运行时类
        userRespository mapper = sqlSession.getMapper(userRespository.class);


        //根据Id进行查询
        User byId = mapper.findById(2L);
        System.out.println(byId);
        sqlSession.close();
        sqlSession = sqlSessionFactory.openSession();
        mapper = sqlSession.getMapper(userRespository.class);
        System.out.println(mapper.findById(3L));
        sqlSession.close();
}

二级缓存、Mapper级别、默认关闭、可以开启

多个SqlSession使用同一个Mapper的SQL语句操作数据库、得到的数据会存在二级缓存区、使用HashMap进行数据存储、SqlSession可以使用二级缓存、二级缓存是跨SqlSession的

二级缓存的多个SqlSession共享的、其作用域是Mapper的同一个namespace、不同的SQLSession执行的是相同的namspace下的SQL语句、参数也相等、第一次执行后会将数据保存到二级缓存中、第二次查询时也是直接从二级缓存中取出数据

mybatis自带二级缓存配置

  • mybatis-config.xml
1
2
3
4
5
6
7
8
<settings>
        <!-- 打印SQL-->
        <setting name="logImpl" value="STDOUT_LOGGING" />
        <!-- 开启延迟加载 -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 开启⼆二级缓存 -->
        <setting name="cacheEnabled" value="true"/>
    </settings>
  • mapper.xml
1
<cache></cache>
  • entity(实现Serializable接口)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User implements Serializable {

    private Long id;
    private String username;
    private String password;
    private List<Integer> ints;

    public User(Long id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }
}

第三方ehcache二级缓存配置

  • pom.xml
1
2
3
4
5
6
7
8
9
10
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-ehcache</artifactId>
    <version>1.0.0</version>
</dependency>
<dependency>
    <groupId>net.sf.ehcache</groupId>
    <artifactId>ehcache-core</artifactId>
    <version>2.4.3</version>
</dependency>
  • ehcache.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="../config/ehcache.xsd">
    <diskStore/>

    <defaultCache
        maxElementsOnDisk="10000000"
        eternal="false"
        overflowToDisk="false"
        timeToIdleSeconds="120"
        timeToLiveSeconds="120"
        diskExpiryThreadIntervalSeconds="120"
        memoryStoreEvictionPolicy="LRU">
</defaultCache>
</ehcache>
  • mybatis-config.xml
1
2
3
4
5
6
7
8
<settings>
    <!-- 打印SQL-->
    <setting name="logImpl" value="STDOUT_LOGGING" />
    <!-- 开启延迟加载 -->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!-- 开启⼆二级缓存 -->
    <setting name="cacheEnabled" value="true"/>
</settings>
  • mapper.xml
1
2
3
4
5
6
7
<cache type="org.mybatis.caches.ehcache.EhcacheCache">
    <!-- 缓存创建之后，最后⼀一次访问缓存的时间⾄至缓存失效的时间间隔 -->   <property name="timeToIdleSeconds" value="3600"/>
    <!-- 缓存⾃自创建时间起⾄至失效的时间间隔 -->
    <property name="timeToLiveSeconds" value="3600"/>
    <!-- 缓存回收策略略，LRU表示移除近期使⽤用最少的对象 -->
    <property name="memoryStoreEvictionPolicy" value="LRU"/>
    </cache>

MyBatis动态SQL

减少开发者的工作量、程序⾃自动根据业务参数来决定 SQL 的组成

  • while、if标签
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="findByUser" parameterType="com.dream.xiaobo.entity.User" resultType="com.dream.xiaobo.entity.User">
        SELECT * FROM user
        <where>
            <if test="id != null">
                id = #{id }
            </if>
            <if test="username != null">
                AND username = #{username }
            </if>
            <if test="password != null">
                AND password = #{password }
            </if>
        </where>
    </select>

where标签：自动判断是否要删除语句块中的AND关键字、如果检测到where直接和AND拼接、则自动删除
if标签：自动根据表达式结果来决定是否将对应的语句添加到SQL中、条件成立添加、反之不添加

  • choose、when标签
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<select id="findByUser" parameterType="com.dream.xiaobo.entity.User" resultType="com.dream.xiaobo.entity.User">
        SELECT * FROM user
        <where>
            <choose>
                <when test="id != null">
                    id = #{id }
                </when>
                <when test="username != null">
                    AND username = #{username }
                </when>
                <when test="password != null">
                    AND password = #{password }
                </when>
            </choose>
        </where>
    </select>
  • trim标签
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="findByUser" parameterType="com.dream.xiaobo.entity.User" resultType="com.dream.xiaobo.entity.User">
        SELECT * FROM user
        <trim prefix="where" prefixOverrides="and">
            <if test="id != null">
                id = #{id }
            </if>
            <if test="username != null">
                AND username = #{username }
            </if>
             <if test="password != null">
                 AND password = #{password }
             </if>
        </trim>
    </select>

trim标签：prefix和suffix属性都会被用于生成实际的SQL语句、和标签内部语句进行拼接、但是如果语句前后出现prefixOverrides或者suffixOverrides属性汇总所指定的值、那么自动将其移除

  • set标签
1
2
3
4
5
6
7
8
9
10
11
12
<update id="updateUser" parameterType="com.dream.xiaobo.entity.User">
        UPDATE user
        <set>
            <if test="username != null">
                username = #{username },
            </if>
            <if test="password != null">
                password = #{password }
            </if>
            WHERE id = #{id }
        </set>
    </update>

set标签：用于更新

  • foreach标签
1
2
3
4
5
6
7
8
<select id="findByUser2" parameterType="com.dream.xiaobo.entity.User" resultType="com.dream.xiaobo.entity.User">
        SELECT * FROM user
        <where>
            <foreach collection="ints" open="id IN (" close=")" item="id" separator=",">
                #{id }
            </foreach>
        </where>
    </select>

foreach标签：迭代生成的一系列值、主要用于SQL的IN语句

MyBatis逆向工程

自动创建、实体类、自定义Mapper接口、Mapper.xml、支持基本的CRUD

  • pom.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.2</version>
        </dependency>

    </dependencies>
  • generatorConfig.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <context id="DB2Tables" targetRuntime="MyBatis3">
        <commentGenerator>
            <!-- 是否去除自动生成的注释 -->
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>
        <!-- Mysql数据库连接的信息：驱动类、连接地址、用户名、密码 -->
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/db_mybatis?useUnicode=true&amp;charcaterEncoding=UTF-8&amp;serverTimezone=UTC"
                        userId="root"
                        password="xiaobo">
        </jdbcConnection>
        <!-- Oracle数据库
            <jdbcConnection driverClass="oracle.jdbc.OracleDriver"
                connectionURL="jdbc:oracle:thin:@127.0.0.1:1521:yycg"
                userId="yycg"
                password="yycg">
            </jdbcConnection>
        -->

        <!-- 默认为false，把JDBC DECIMAL 和NUMERIC类型解析为Integer，为true时
        把JDBC DECIMAL 和NUMERIC类型解析为java.math.BigDecimal -->
        <javaTypeResolver >
            <property name="forceBigDecimals" value="false" />
        </javaTypeResolver>

        <!-- targetProject：生成POJO类的位置 -->
        <javaModelGenerator targetPackage="com.dream.xiaobo.pojo" targetProject="./src/main/java">
            <!-- enableSubPackages:是否让schema作为包的后缀 -->
            <property name="enableSubPackages" value="false" />
            <!-- 从数据库返回的值被清理前后的空格 -->
            <property name="trimStrings" value="true" />
        </javaModelGenerator>

        <!-- targetProject：mapper映射文件生成的位置 -->
        <sqlMapGenerator targetPackage="mapper"  targetProject="./src/main/resources">
            <!-- enableSubPackages:是否让schema作为包的后缀 -->
            <property name="enableSubPackages" value="false" />
        </sqlMapGenerator>

        <!-- targetProject：mapper接口生成的的位置 -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.dream.xiaobo.dao"  targetProject="./src/main/java">
            <!-- enableSubPackages:是否让schema作为包的后缀 -->
            <property name="enableSubPackages" value="false" />
        </javaClientGenerator>

        <!-- 指定数据表 -->
        <table schema="user" tableName="User"></table>

        <!-- 有些表的字段需要指定java类型
        <table schema="DB2ADMIN" tableName="ALLTYPES" domainObjectName="Customer" >
          <property name="useActualColumnNames" value="true"/>
          <generatedKey column="ID" sqlStatement="DB2" identity="true" />
          <columnOverride column="DATE_FIELD" property="startDate" />
          <ignoreColumn column="FRED" />
          <columnOverride column="LONG_VARCHAR_FIELD" jdbcType="VARCHAR" />
        </table> -->

    </context>
</generatorConfiguration>
  • main入口
1
2
3
4
5
6
7
8
9
10
11
12
13
public static void main(String[] args) throws IOException, XMLParserException,
            InvalidConfigurationException, SQLException, InterruptedException {
        List<String> warnings = new ArrayList<String>();
        boolean overwrite = true;
        //自己的配置文件位置
        File configFile = new File("./src/main/resources/generatorConfig.xml");
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(configFile);
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        //mybatis-generator-core依赖 1.3.6 后才有MyBatisGenerator类,需要jre1.8才能运行
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        myBatisGenerator.generate(null);
    }

正确的开始、微小的长进、然后持续、嘿、我是小博、带你一起看我目之所及的世界……

-------------本文结束 感谢您的阅读-------------

本文标题:MyBatis

文章作者:小博

发布时间:2021年07月16日 - 23:16

最后更新:2021年07月16日 - 23:45

原始链接:https://codexiaobo.github.io/posts/469711973/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。