本篇文章主要介绍了使用MyBatis框架完成数据库的增、删、改、查操作。

准备工作

运行schema.sql和data.sql脚本文件中的 SQL 语句创建t_user表并添加部分测试数据。

schema.sql：

DROP TABLE IF EXISTS `t_user`;
create table t_user
(
    id          int auto_increment primary key COMMENT '主键',
    username    varchar(64) null COMMENT '用户名',
    nick_name   varchar(64) null COMMENT '昵称',
    password    varchar(64) null COMMENT '密码',
    phone       varchar(32) null COMMENT '手机号',
    create_time varchar(64) null COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

data.sql：

insert into t_user(`username`, `nick_name`, `password`, `phone`, `create_time`)
values ('user01', 'user01', '123456', '15900001111', '2022-07-14 16:00:00');
insert into t_user(`username`, `nick_name`, `password`, `phone`, `create_time`)
values ('user02', 'user02', '123456', '15900001111', '2022-07-14 16:00:00');
insert into t_user(`username`, `nick_name`, `password`, `phone`, `create_time`)
values ('user03', 'user03', '123456', '15900001111', '2022-07-14 16:00:00');
insert into t_user(`username`, `nick_name`, `password`, `phone`, `create_time`)
values ('user04', 'user04', '123456', '15900001111', '2022-07-14 16:00:00');

使用MyBatis框架操作数据库步骤：

（1）定义Java实体

User.java

@Data
public class User {
    private Integer id;
    private String username;
    private String nickName;
    private String password;
    private String phone;
    private String createTime;
}

（2）定义Mapper接口

UserDao.java

public interface UserDao {
    void insert(User user);
    void update(User user);
    User findByUsername(String username);
    void deleteById(Integer id);
}

（3）编写XML映射文件

UserDao.xml

<?xml version="1.0" encoding="utf-8"?>


    
        insert into t_user(`username`, `nick_name`, `password`, `phone`, `create_time`)
        values(#{username}, #{nickName}, #{password}, #{phone}, #{createTime})
    
    
        update t_user
        set username = #{username}, nick_name = #{nickName}, password = #{password}, phone = #{phone}, create_time = #{createTime}
        where id = #{id}
    
    
        select id, username, nick_name as nickName, password, phone, create_time as createTime
        from t_user where username = #{username}
    
    
        delete from t_user where id = #{id}
    

（4）编写MyBatis主配置文件

MyBatis使用 XML 文件格式描述配置信息，内容如下：

<?xml version="1.0" encoding="utf-8"?>


    
        
    
    
        
            
            
                
                
                
                
            
        
    
    
        
    

（5）编写测试类

Demo.java

public class Demo {
    public static void main(String[] args) throws IOException {
        // 获取配置文件输入流
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        // 创建 SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 获取 SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 获取 Mapper 接口
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        // 新增一个用户
        User newUser = new User();
        newUser.setUsername("new-user");
        newUser.setNickName("新建的用户（修改前）");
        newUser.setPassword("123123");
        newUser.setPhone("13900001111");
        newUser.setCreateTime("2022-07-14 16:00:00");
        userDao.insert(newUser);
        // 根据用户名获取用户信息
        User user = userDao.findByUsername("new-user");
        System.out.println("########## 修改前用户信息 ##########");
        System.out.println(new Gson().toJson(user));
        // 修改用户信息
        user.setUsername("update-user");
        user.setNickName("新建的用户（修改后）");
        userDao.update(user);
        user = userDao.findByUsername("update-user");
        System.out.println("########## 修改后用户信息 ##########");
        System.out.println(new Gson().toJson(user));
        // 删除用户
        userDao.deleteById(user.getId());
    }
}

输出结果：

########## 修改前用户信息 ##########
{"id":5,"username":"new-user","nickName":"新建的用户（修改前）","password":"123123","phone":"13900001111","createTime":"2022-07-14 16:00:00"}
########## 修改后用户信息 ##########
{"id":5,"username":"update-user","nickName":"新建的用户（修改后）","password":"123123","phone":"13900001111","createTime":"2022-07-14 16:00:00"}