MyBatis学习系列（二）--增删改查

MyBatis学习系列（二）--增删改查

数据库及表的建立

CREATE DATABASE `mybatis`; /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
CREATE TABLE `user` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT 'user id',
  `name` varchar(30) DEFAULT NULL COMMENT 'user name',
  `pwd` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

实体类

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 + '\'' +
                '}';
    }
}

Mapper接口

import com.kuang.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserMapper {

    List<User> getUserList();

	//根据用户id查询用户
    User getUserById(@Param("id") int id);

    //插入一个用户

    int addUser(User user);

    //修改用户
    int updateUser(User user);

    //根据id删除用户
    int deleteUserById(int id);
}

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.kuang.dao.UserMapper">
    <select id="getUserList" resultType="com.kuang.pojo.User">
        SELECT * FROM `user`
    </select>

    <select id="getUserById" parameterType="int" resultType="com.kuang.pojo.User">
        SELECT *
        FROM `user`
        WHERE `id`=#{id}
    </select>

    <insert id="addUser" parameterType="com.kuang.pojo.User">
        INSERT INTO `user` (`id`, `name`, `pwd`) VALUES (#{id}, #{name}, #{pwd})
    </insert>


    <update id="updateUser" parameterType="com.kuang.pojo.User">
        UPDATE `user` SET `name`=#{name}, `pwd`=#{pwd}
        WHERE `id`=#{id}
    </update>


    <delete id="deleteUserById" parameterType="int">
        DELETE FROM `user`
        WHERE `id`=#{id}
    </delete>
</mapper>

总结几点：

• mapper标签中的namespace属性绑定Mapper接口
• 增删改查对应的标签名为insert、delete、update、select
• id属性将sql语句与绑定Mapper接口中的某个方法绑定
• parameterType指定方法的参数类型。如果不是原始类型，必须用全限定名。insert、delete、update、select四种标签都有这种属性
• resultType为insert标签的特有属性，指定sql查询语句返回的List容器的类型参数

测试

import com.kuang.pojo.User;
import com.kuang.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class UserMapperTest {

    @Test
    public void test1(){
        try (SqlSession sqlSession = MyBatisUtil.getSqlSession()){

            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<User> users = userMapper.getUserList();
            for (User user : users) {
                System.out.println(user);
            }
        }

    }


    @Test
    public void getUserById(){
        try (SqlSession sqlSession = MyBatisUtil.getSqlSession()){

            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User user = userMapper.getUserById(1);
            System.out.println(user);
        }

    }


    @Test
    public void addUser(){
        try (SqlSession sqlSession = MyBatisUtil.getSqlSession()){

            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            int res = userMapper.addUser(new User(4, "张三", "123456"));
            if (res > 0){
                System.out.println("插入成功");
            }
            sqlSession.commit();
        }

    }

    @Test
    public void updateUser(){
        try (SqlSession sqlSession = MyBatisUtil.getSqlSession()){

            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            int res = userMapper.updateUser(new User(4, "里斯", "654321"));
            if (res > 0){
                System.out.println("修改成功");
            }
            sqlSession.commit();
        }

    }

    @Test
    public void deleteUserById(){
        try(SqlSession sqlSession = MyBatisUtil.getSqlSession()){
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            int ret = userMapper.deleteUserById(4);
            if (ret > 0){
                System.out.println("删除成功");
            }
            sqlSession.commit();
        }
    }
}

注意一点：

• 除查询操作外，其余的操作要想生效，通过sqlSession的commit方法提交事务。（MyBatis默认关闭事务的自动提交）