开始MyBatis之旅

一、什么是MyBatis

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

• 未使用MyBatis

• 使用MyBatis

二、如何获取MyBatis

MyBatis可使用Maven导入：

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

三、上手MyBatis

搭建环境

create database mybatis;

use mybatis;

create table user (
  id int(20) not null primary key,
  name varchar(30) default null,
  pwd varchar(30) default null
)engine=innodb charset=utf-8;

insert into user (id, name, pwd) values
(1, '张三', '123456'),
(2, '李四', '234567'),
(3, '王五', '345678');

导入依赖

在Maven配置文件中，进行操作（导入依赖）

<!-- 导入依赖 -->
<dependencies>
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.11</version>
  </dependency>
  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.2</version>
  </dependency>
  <dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
  </dependency>
</dependencies>

创建模块

编写MyBatis核心配置文件

推荐MyBatis核心配置文件的命名：mybatis-config.xml，推荐配置文件放置在哪：resources目录下

<?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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf-8&amp;characterSetResults=utf-8&amp;useSSL=false&amp;verifyServerCertificate=false&amp;autoReconnect=true&amp;autoReconnectForPools=true&amp;allowMultiQueries=true"/>
                <property name="username" value="root"/>
                <property name="password" value="txq199807031578"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

按照MVC架构编写一系列类文件和接口文件

UserDao接口、User实体类

public interface UserDao {
    //获取全部用户
    List<User> getUserList();
}

//实体类
public class User {
    private int id;
    private String name;
    private String pwd;

    public User() {}

    public User(int id, String name, String pwd) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}

编写MyBatis_Util工具类

这里可以简单看下MyBatis官方文档，思考下 编写MyBatis核心配置文件和编写MyBatis_Util工具类这两者有什么关系

// sqlSessionFactory --> sqlSession
public class MyBatis_Util {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

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

编写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">
<!-- namespace=绑定一个对应的Dao/Mapper接口 -->
<!-- 等同于JDBC中的UserDaoImpl(接口实现类)，转变为Mapper配置文件 -->
<mapper namespace="com.comprehensive.dao.UserMapper">
    <!-- 获取全部用户 -->
    <select id="getUserList" resultType="com.comprehensive.pojo.User">
        select * from mybatis.user
    </select>
</mapper>

测试

public class UserDao_test {
    @Test
    public void test() {
        //获取SqlSession对象
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        //执行SQL语句
        //方式一：mapper
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        for(User user : mapper.getUserList()) System.out.println(user);
        sqlSession.close();
    }
}

遇到的问题及修复

错误信息为：org.apache.ibatis.binding.BindingException: Type interface com.comprehensive.dao.UserDao is not known to the MapperRegistry.
具体可参照官方文档中的 XML配置 章节 中的 映射器 小节

实际上，我们之前没有在MyBatis核心配置文件中配置 映射器mappers，我们需要加上

<!-- 每一个mapper.xml都需要在MyBatis核心配置文件中注册 -->
<mappers>
  <mapper resource="com/comprehensive/dao/UserMapper.xml"/>
</mappers>

再次测试，发现还是报错，且是同一个错误：org.apache.ibatis.binding.BindingException: Type interface com.comprehensive.dao.UserDao is not known to the MapperRegistry.
这时应该看看Mapper映射器文件中的namespace是否写错

结果是写错了，本来UserDao接口，namespace这里写成了UserMapper，服了，Mapper映射器文件这里改过来应该就没事了吧（改成如下形式后，继续测试）

玛德，还是报错了。这里的报错信息是：org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: java.io.IOException: Could not find resource com/comprehensive/dao/UserMapper.xml
找不到就有点奇怪了，我的MyBatis_01模块里不是有UserMapper.xml文件么？想了想，这里不是用maven创建的项目么，如果Maven项目里有xml配置文件需要被读取，是不是要设置什么玩意（详情请见本人的Maven文档，增加xml配置信息）

<!-- Maven由于“约定大于配置”，我们之后可能会遇到写的配置文件无法被导出或者生效的问题，解决方案如下： -->
<build>
  <resources>
    <resource>
      <directory>src/main/resources</directory>
      <includes>
        <include>**/*.properties</include>
        <include>**/*.xml</include>
      </includes>
      <filtering>true</filtering>
    </resource>
    <resource>
      <directory>src/main/java</directory>
      <includes>
        <include>**/*.properties</include>
        <include>**/*.xml</include>
      </includes>
      <filtering>true</filtering>
    </resource>
  </resources>
</build>

增加xml配置信息后，再次测试（再不过我人快没了）

四、增删改查

编写UserDao接口

public interface UserDao {
    //获取全部用户
    List<User> getUserList();
    //根据特定条件查询用户
    User getUserById(int id);
    //增加一名用户
    void addUser(User user);
    //删除特定的用户
    void deleteUser(int id);
    //更新特定的用户的信息
    void updateUser(int id);
}

编写UserMapper映射器文件

<?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=绑定一个对应的Dao/Mapper接口 -->
<!-- 等同于JDBC中的UserDaoImpl(接口实现类)，转变为Mapper配置文件 -->
<mapper namespace="com.comprehensive.dao.UserDao">
    <!-- 获取全部用户 -->
    <select id="getUserList" resultType="com.comprehensive.pojo.User">
        select * from mybatis.user
    </select>
    <!-- 根据特定条件查询用户 -->
    <select id="getUserById" parameterType="int" resultType="com.comprehensive.pojo.User">
        select * from mybatis.user where id=#{id}
    </select>
    <!-- 增加一名用户 -->
    <insert id="addUser" parameterType="com.comprehensive.pojo.User">
        insert into mybatis.user (id, name, pwd) values (#{id}, #{name}, #{pwd})
    </insert>
    <!-- 删除特定的用户 -->
    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id=#{id}
    </delete>
    <!-- 更新特定的用户的信息 -->
    <update id="updateUser" parameterType="int">
        update mybatis.user set name='刘炀', pwd='456789' where id=#{id}
    </update>
</mapper>

测试

注意点：增删改（insert，delete，update）操作需要提交事务 sqlSession.commit(); 这样才能确保MySQL数据库中的数据得以修改

public class UserDao_test {
    @Test
    public void test() {
        //获取SqlSession对象
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        //执行SQL语句
        //方式一：mapper
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        for(User user : mapper.getUserList()) System.out.println(user);
        sqlSession.close();
    }
    @Test
    public void test_query() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        System.out.println(userDao.getUserById(1));
        sqlSession.close();
    }
    @Test
    public void test_add() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.addUser(new User(4, "李杰亮", "123456"));
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void test_delete() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.deleteUser(4);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void test_update() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.updateUser(1);
        sqlSession.commit();
        sqlSession.close();
    }
}

五、万能Map和模糊查询

万能Map

在字段比较多的时候用map非常方便，在项目中应用非常多。

• UserMapper.xml文件中

#{userId} 括号里边的名称可以随意取，不过在测试类中需与map的key键名称保持一致

• 测试类中

map.put("userId", 4); map的key键名称需与UserMapper.xml中的括号内名称一致；value值则是你自己设置的CRUD条件

<?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=绑定一个对应的Dao/Mapper接口 -->
<!-- 等同于JDBC中的UserDaoImpl(接口实现类)，转变为Mapper配置文件 -->
<mapper namespace="com.comprehensive.dao.UserDao">
    <!-- 获取全部用户 -->
    <select id="getUserList" resultType="com.comprehensive.pojo.User">
        select * from mybatis.user
    </select>
    <!-- 根据特定条件查询用户 -->
    <select id="getUserById" parameterType="int" resultType="com.comprehensive.pojo.User">
        select * from mybatis.user where id=#{id}
    </select>
    <!-- 增加一名用户 -->
    <insert id="addUser" parameterType="com.comprehensive.pojo.User">
        insert into mybatis.user (id, name, pwd) values (#{id}, #{name}, #{pwd})
    </insert>
    <!-- 删除特定的用户 -->
    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id=#{id}
    </delete>
    <!-- 更新特定的用户的信息 -->
    <update id="updateUser" parameterType="int">
        update mybatis.user set name='刘炀', pwd='456789' where id=#{id}
    </update>
    <!-- 万能Map 根据特定条件查询用户 -->
    <select id="getUserByIdAndName" parameterType="map" resultType="com.comprehensive.pojo.User">
        select * from mybatis.user where id=#{userId} and name=#{userName}
    </select>
    <!-- 万能Map 增加一名用户 -->
    <insert id="addUserByIdAndName" parameterType="map">
        insert into mybatis.user (id, name) values (#{userId}, #{userName})
    </insert>
</mapper>

public class UserDao_test {
    @Test
    public void test() {
        //获取SqlSession对象
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        //执行SQL语句
        //方式一：mapper
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        for(User user : mapper.getUserList()) System.out.println(user);
        sqlSession.close();
    }
    @Test
    public void test_query() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        System.out.println(userDao.getUserById(1));
        sqlSession.close();
    }
    @Test
    public void test_add() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.addUser(new User(4, "李杰亮", "123456"));
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void test_delete() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.deleteUser(4);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void test_update() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.updateUser(1);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    //万能Map 根据特定条件查询用户
    public void test_queryByIdAndName() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("userId", 4); map.put("userName", "李杰亮");
        System.out.println(userDao.getUserByIdAndName(map));
    }
    @Test
    //万能Map 增加一名用户
    public void test_addByIdAndName() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("userId", 5); map.put("userName", "周希程");
        userDao.addUserByIdAndName(map);
        sqlSession.commit();
        sqlSession.close();
    }
}

模糊查询

<?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=绑定一个对应的Dao/Mapper接口 -->
<!-- 等同于JDBC中的UserDaoImpl(接口实现类)，转变为Mapper配置文件 -->
<mapper namespace="com.comprehensive.dao.UserDao">
    <!-- 获取全部用户 -->
    <select id="getUserList" resultType="com.comprehensive.pojo.User">
        select * from mybatis.user
    </select>
    <!-- 根据特定条件查询用户 -->
    <select id="getUserById" parameterType="int" resultType="com.comprehensive.pojo.User">
        select * from mybatis.user where id=#{id}
    </select>
    <!-- 增加一名用户 -->
    <insert id="addUser" parameterType="com.comprehensive.pojo.User">
        insert into mybatis.user (id, name, pwd) values (#{id}, #{name}, #{pwd})
    </insert>
    <!-- 删除特定的用户 -->
    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id=#{id}
    </delete>
    <!-- 更新特定的用户的信息 -->
    <update id="updateUser" parameterType="int">
        update mybatis.user set name='刘炀', pwd='456789' where id=#{id}
    </update>
    <!-- 万能Map 根据特定条件查询用户 -->
    <select id="getUserByIdAndName" parameterType="map" resultType="com.comprehensive.pojo.User">
        select * from mybatis.user where id=#{userId} and name=#{userName}
    </select>
    <!-- 万能Map 增加一名用户 -->
    <insert id="addUserByIdAndName" parameterType="map">
        insert into mybatis.user (id, name) values (#{userId}, #{userName})
    </insert>
    <!-- 模糊查询 -->
    <select id="getUserByLike" resultType="com.comprehensive.pojo.User">
        select * from mybatis.user where name like #{name}
    </select>
</mapper>

public class UserDao_test {
    @Test
    public void test() {
        //获取SqlSession对象
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        //执行SQL语句
        //方式一：mapper
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        for(User user : mapper.getUserList()) System.out.println(user);
        sqlSession.close();
    }
    @Test
    public void test_query() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        System.out.println(userDao.getUserById(1));
        sqlSession.close();
    }
    @Test
    public void test_add() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.addUser(new User(4, "李杰亮", "123456"));
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void test_delete() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.deleteUser(4);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void test_update() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.updateUser(1);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    //万能Map 根据特定条件查询用户
    public void test_queryByIdAndName() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("userId", 4); map.put("userName", "李杰亮");
        System.out.println(userDao.getUserByIdAndName(map));
    }
    @Test
    //万能Map 增加一名用户
    public void test_addByIdAndName() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("userId", 5); map.put("userName", "周希程");
        userDao.addUserByIdAndName(map);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    //模糊查询
    public void test_queryByLike() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        System.out.println(userDao.getUserByLike("%亮%"));
        sqlSession.close();
    }
}