MyBatis使用

一、简介

demo 地址：https://github.com/DKAngel/MyBatisDemo
如何上传项目到 github

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。可以通过XML或注解的方式来配置和映射原始类型、接口和 POJO为数据库中的记录。详见 MyBatis官方文档

各ORM框架对比 MyBatis、Hibernate、JPA

二、使用（XML配置）

准备工作

• 安装 MySQL
• 启动 MySQL: net start mysql
• 登录 MySQL：mysql -u root -p[pwd]
• 创建 test 数据库：create database test

1. 创建user表

CREATE TABLE `t_user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sex` enum('MALE','FEMALE') DEFAULT 'MALE',
  `age` smallint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;

2. 创建 SpringBoot 项目

通过 IDEA 创建SpringBoot项目，勾选依赖：Mybatis Framework、MySQL Driver

依赖下载缓慢，修改maven setting.xml

3. 配置mybatis-config.xml

mysql.properties

Driver=com.mysql.cj.jdbc.Driver
Url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true
Username=root
Password=123

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>
    <properties resource="mysql.properties"/>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${Driver}"/>
                <property name="url" value="${Url}"/>
                <property name="username" value="${Username}"/>
                <property name="password" value="${Password}"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

4. 创建类 User.java，SexEnum.java

User.java

package com.example.demo.entity;

public class User {
    private Integer id;
    private String name;
    private Enum sex;
    private Integer age;

    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 Enum getSex() {
        return sex;
    }

    public void setSex(Enum sex) {
        this.sex = sex;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

SexEnum.java

package com.example.demo.common;

public enum SexEnum {
    MALE,
    FEMALE,
}

5. 创建Mapper类 UserMapper.java

UserMapper.java

package com.example.demo.mapper;

import com.example.demo.entity.User;
import java.util.List;

public interface UserMapper {
    int insertUser(User user);

    List<User> selectAllUser();

    User selectUserById(Integer id);
}

6. 创建Mapper配置文件 UserMapper.xml

UserMapper.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.example.demo.mapper.UserMapper">
    <resultMap id="userMap" type="com.example.demo.entity.User">
        <id property="id" column="id" javaType="java.lang.Integer"/>
        <result property="name" column="name" javaType="java.lang.String"/>
        <result property="sex" column="sex" javaType="com.example.demo.common.SexEnum"/>
        <result property="age" column="age" javaType="java.lang.Integer"/>
    </resultMap>
    <insert id="insertUser">
        insert into t_user (name, sex, age)
        values (#{name}, #{sex}, #{age});
    </insert>

    <select id="selectAllUser" resultMap="userMap">
        select *
        from t_user;
    </select>

    <select id="selectUserById" resultMap="userMap">
        select *
        from t_user
        where id = #{id};
    </select>
</mapper>

7. 在mybatis-config.xml 中添加要扫描的mapper和别名

mybatis-config.xml

...
<!-- 别名扫描，否则 mapper.xml 中的 resultMap type需要写全包名 -->
<typeAliases>
    <package name="com.example.demo.entity"/>
</typeAliases>
...
<mappers>
<!-- 如果包含多级目录，不能直接用点创建(com.demo.mapper)，可以使用斜杆创建(com/demo/mapper) -->
<!-- https://blog.csdn.net/lmy86263/article/details/53428417 -->
<mapper resource="com/example/demo/mapper/UserMapper.xml"/>
</mappers>

8. IDEA 配置 Database

强迫症，没有配置Database的话，UserMapper.xml里的sql语句会报红

• User: root
• Password: 123
• Url: jdbc:mysql://localhost:3306/test

错误参考

• Server returns invalid timezone. Go to 'Advanced' tab and set 'serverTimezone' property manually
  • 打开MySQL客户端，执行 set global time_zone='+8:00';
• Connection to test@localhost failed. [08001] Could not create connection to database server. Attempted reconnect 3 times. Giving up.
  • URL后面添加?serverTimezone=GMT，参考这里

9. 创建 tool 类获取 SqlSessionFactory

package com.example.demo.tool;

import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisTool {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

10. LLT测试配置是否正确

在 UserMapper.java 中使用快捷键 Alt + Insert 创建测试类

package com.example.demo.mapper;

import com.example.demo.entity.SexEnum;
import com.example.demo.entity.User;
import com.example.demo.tool.MyBatisTool;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Assert;
import org.junit.jupiter.api.Test;

class UserMapperTest {

    @Test
    void insertUser() {
        User user = new User();
        user.setName("dkangel");
        user.setSex(SexEnum.MALE);
        user.setAge(24);

        SqlSession sqlSession = MyBatisTool.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int len;
        try {
            len = userMapper.insertUser(user);
            sqlSession.commit(true);
        } finally {
            sqlSession.close();
        }
        Assert.assertEquals(1, len);
    }

    @Test
    void selectAllUser() {
        SqlSession sqlSession = MyBatisTool.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> users;
        try {
            users = userMapper.selectAllUser();
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
        Assert.assertEquals(1, users.size());
    }

    @Test
    void selectUserById() {
        SqlSession sqlSession = MyBatisTool.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user;
        try {
            user = userMapper.selectUserById(101);
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
        Assert.assertEquals("dkangel", user.getName());
    }
}

错误参考

• Public Key Retrieval is not allowed