MyBatis入门实验(三)之增删查改

实验内容

使用Mybatis执行对数据库表的增删查改操作

操作步骤

一、安装

添加Maven依赖（本文使用版本为3.4.6）

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>x.x.x</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.42</version>
</dependency>

二、创建数据库及表结构

CREATE TABLE `user` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(32) NOT NULL COMMENT '用户名',
  `password` varchar(64) NOT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

三、创建 Mybatis 配置文件

在 src/main/resources 目录下创建 mybatis-config.xml 文件，内容如下

<configuration>
    <properties resource="jdbc.properties"></properties>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 加载XML，同时加载接口类 -->
    <mappers>
        <mapper class="tutorial.mybatis.mapper.UserMapper"></mapper>
        <mapper resource="mybatis/User.xml"></mapper>
    </mappers>
</configuration>

在 src/main/resources 目录下创建 jdbc.properties 文件，内容如下

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/tutorial_mybatis?characterEncoding=utf-8&useSSL=true
jdbc.username=root
jdbc.password=

四、创建实体类和映射文件

创建包 tutorial.mybatis.model，并在该包下创建 User 类，内容如下

public class User {

    private Long id;

    private String username;

    private String password;
    
    // 省略 get / set 方法
}

在 src/main/resources 目录下创建目录 mybatis，并在该目录下创建 User.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">
<!-- 命名空间修改为接口类的路径，mybatis 会通过接口类自动寻址至相应的XML文件，获取SQL语句 -->
<mapper namespace="tutorial.mybatis.mapper.UserMapper">

    <select id="getUserById" parameterType="long" resultType="tutorial.mybatis.model.User">
        SELECT * FROM `user` WHERE id = #{id}
    </select>
    
    <select id="listAll" resultType="tutorial.mybatis.model.User">
        SELECT * FROM `user`
    </select>

    <insert id="save" parameterType="tutorial.mybatis.model.User">
        INSERT INTO `user` VALUES (#{id}, #{username}, #{username})
    </insert>

    <delete id="deleteById" parameterType="long">
        DELETE FROM `user` WHERE id = #{id}
    </delete>

    <delete id="deleteAll">
        DELETE FROM `user`
    </delete>

    <update id="updateById" parameterType="tutorial.mybatis.model.User">
        UPDATE `user` SET `username` = #{username}, `password` = #{password} where id = #{id}
    </update>

</mapper>

五、创建接口类

创建包 tutorial.mybatis.mapper，并在该包下创建接口 UserMapper，内容如下

public interface UserMapper {

    User getUserById(Long id);

    List<User> listAll();

    int save(User user);

    int updateById(User user);

    int deleteById(Long id);

    int deleteAll();

}

六、构建

准备工作就绪，开始最终章，创建启动类 MybatisConfig，内容如下：

public class MybatisConfig {

    private static SqlSessionFactory sqlSessionFactory;
    private static Reader reader;

    static {
        try {
            reader = Resources.getResourceAsReader("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static SqlSessionFactory getSession() {
        return sqlSessionFactory;
    }

    public static void main(String[] args) throws IOException {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            init(session);
            testInsert(session);
            User user1 = testGet(session, 1L);
            testUpdate(session, user1);
            User user2 = testGet(session, 2L);
            testDelete(session, user2);
        } finally {
            session.close();
        }
    }

    private static void init(SqlSession session) {
        UserMapper userMapper = session.getMapper(UserMapper.class);
        userMapper.deleteAll();
    }

    public static void testInsert(SqlSession session) {
        UserMapper userMapper = session.getMapper(UserMapper.class);
        User user1 = new User() {{
            setId(1L);
            setUsername("admin");
            setPassword("admin");
        }};
        User user2 = new User() {{
            setId(2L);
            setUsername("user");
            setPassword("user");
        }};
        userMapper.save(user1);
        userMapper.save(user2);
        System.out.println("------- 数据添加完成，现有数据 --------");
        testList(session);
    }

    public static void testUpdate(SqlSession session, User user) {
        UserMapper userMapper = session.getMapper(UserMapper.class);
        user.setPassword("12345678");
        userMapper.updateById(user);
        System.out.println("------- 修改ID为" + user.getId() + "的用户密码为12345678 --------");
        testList(session);
    }

    public static void testDelete(SqlSession session, User user) {
        UserMapper userMapper = session.getMapper(UserMapper.class);
        userMapper.deleteById(user.getId());
        System.out.println("------- 删除ID为" + user.getId() + "的用户 --------");
        testList(session);
    }

    public static User testGet(SqlSession session, Long id) {
        UserMapper userMapper = session.getMapper(UserMapper.class);
        return userMapper.getUserById(id);
    }

    private static void testList(SqlSession session) {
        UserMapper userMapper = session.getMapper(UserMapper.class);
        List<User> list = userMapper.listAll();
        if (list != null && !list.isEmpty()) {
            for (User user : list) {
                printUser(user);
            }
        }
    }

    private static void printUser(User user) {
        String userInfo = "ID：" + user.getId() + "名字：" + user.getUsername()+", 密码：" + user.getPassword();
        System.out.println(userInfo);
    }
}

打印结果为：

------- 数据添加完成，现有数据 --------
ID：1名字：admin, 密码：admin
ID：2名字：user, 密码：user
------- 修改ID为1的用户密码为12345678 --------
ID：1名字：admin, 密码：12345678
ID：2名字：user, 密码：user
------- 删除ID为2的用户 --------
ID：1名字：admin, 密码：12345678

内容整理

mybatis 为每一种数据库操作都定义了相应的注解

XML	注解
insert	@insert
update	@update
delete	@delete
select	@select