MyBatisCRUD操作及配置解析

系列文章目录

CRUD操作

---

提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

程序员日常工作中90%都是在处理CRUD相关的操作，今天我们也从这里开始了解mybatis配置相关知识点。

---

环境配置介绍

1.xml文件配置实例（UserMapper.xml）

配置文件中namespace中的名称为对应Mapper接口或者Dao接口的完整包名,必须一致！

代码如下（示例）：

<?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">
<!--绑定一个对应的dao/Mapper接口-->
<mapper namespace="com.kuang.dao.UserMapper">
    <select id="getUserLike" resultType="com.kuang.pojo.User">
        select * from mybatis.user where name like #{value};
    </select>

    <select id="getUserLike2" resultType="com.kuang.pojo.User">
        select * from mybatis.user where name like "%"#{value}"%";
    </select>

    <select id="getUserList" resultType="com.kuang.pojo.User">
        select * from mybatis.user;
    </select>

    <select id="getUserById" parameterType="int" resultType="com.kuang.pojo.User">
        select * from mybatis.user where id=#{id};
    </select>

    <insert id="insertUser" parameterType="com.kuang.pojo.User">
        insert into mybatis.user(id,name,pwd) values(#{id},#{name},#{pwd});
    </insert>

    <insert id="addUser" parameterType="com.kuang.pojo.User">
        insert into mybatis.user(id,name,pwd) values(#{id},#{name},#{pwd});
    </insert>

    <update id="updateUser" parameterType="com.kuang.pojo.User">
        update mybatis.user set name=#{name},pwd=#{pwd} where id=#{id};
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id=#{id};
    </delete>

    <insert id="addUser2" parameterType="map">
        insert into mybatis.user(id,name,pwd) values(#{id},#{name},#{pwd});
    </insert>
</mapper>

2.数据请求接口配置文件（UserMapper.java）

代码如下（示例）：

import com.kuang.pojo.User;

import java.util.List;
import java.util.Map;

public interface UserMapper {
    //    查询所有的用户
    List<User> getUserList();
    //    查询所有的用户使用like语句
    List<User> getUserLike(String value);

    //    查询所有的用户使用like语句
    List<User> getUserLike2(String value);

    //    根据ID查询用户信息
    User getUserById(int id);

    //    插入一个用户
    int insertUser(User user);

    //    插入一个用户
    int addUser(User user);

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

    //    删除一个用户
    int deleteUser(int id);

    //    万能的map
    int addUser2(Map<String,Object> map);
}

3.POJO生成器（User.java）

数据库中创建表在此省略

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {

    private int id;

    private String name;

    private String password;

}

4.工具类文件（MybatisUtils.java）

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

import java.io.IOException;
import java.io.InputStream;

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
//            第一步：使用mybatis获取SqlSessionFactory
            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();
    }
}

5. resoures文件配置

5.1 数据库文件db.properties

# 数据库连接
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=false
username=root
password=wang123456

5.2 基础文件配置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="db.properties">
        <property name="username" value="root"/>
        <property name="pwd" value="123456"/>
    </properties>
    <settings>
<!--        标准日志工程-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    
    <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="${pwd}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper class="com.kuang.dao.UserMapper"></mapper>
    </mappers>
</configuration>

6.主要CRUD测试用例（UserDaoTest.java）

import com.kuang.pojo.User;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.Test;

import java.util.HashMap;
import java.util.List;

public class UserDaoTest {
    @Test
    public void test_getUserByRowBounds(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();

        RowBounds rowBounds = new RowBounds(1,4);
        List<User> userList =sqlSession.selectList("com.kuang.dao.UserMapper.getUserByRowBounds",
                null,
                rowBounds);
        for (User user : userList) {
            System.out.println(user);
        }
//        增删改都需要提交事务
        sqlSession.commit();
        sqlSession.close();
    }


    @Test
    public void test_getUserLimit(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();

        UserMapper userDao = sqlSession.getMapper(UserMapper.class);

        HashMap<String,Object> map = new HashMap<String,Object>();
        map.put("startIndex",0);
        map.put("pageSize",20);
        List<User> userList =userDao.getUserLimit(map);
        for (User user : userList) {
            System.out.println(user);
        }
//        增删改都需要提交事务
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void test_getUserLike(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();

        UserMapper userDao = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userDao.getUserLike("%王宸玉%");
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }

    @Test
    public void test_getUserLike2(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();

        UserMapper userDao = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userDao.getUserLike2("王宸玉");
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }
    @Test
    public void test_getUserList(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();

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

    @Test
    public void test_getUserById(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();

        UserMapper userDao = sqlSession.getMapper(UserMapper.class);
        User user = userDao.getUserById(2);
        System.out.println(user);
        sqlSession.close();
    }

    @Test
    public void test_insertUser(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();

        UserMapper userDao = sqlSession.getMapper(UserMapper.class);

        int num = userDao.insertUser(new User(9,"王宸熙","wangchenxi"));
        if(num>0){
            System.out.println("插入新的数据成功");
        }
//        增删改都需要提交事务
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void test_addUser2(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();

        UserMapper userDao = sqlSession.getMapper(UserMapper.class);

        HashMap<String,Object> map = new HashMap<String,Object>();
        map.put("id",7);
        map.put("name","王宸玉");
        map.put("pwd","wangchenyu");
        int num = userDao.addUser2(map);
        if(num>0){
            System.out.println("插入新的数据成功");
        }
//        增删改都需要提交事务
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void test_addtUser(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();

        UserMapper userDao = sqlSession.getMapper(UserMapper.class);

        int num = userDao.addUser(new User(5,"王宸玉","wangchenxi"));
        if(num>0){
            System.out.println("插入新的数据成功");
        }
//        增删改都需要提交事务
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void test_updateUser(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();

        UserMapper userDao = sqlSession.getMapper(UserMapper.class);

        userDao.updateUser(new User(5,"王宸玉","wangchenyu12"));
//        增删改都需要提交事务
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void test_deleteUser(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();

        UserMapper userDao = sqlSession.getMapper(UserMapper.class);

        userDao.deleteUser(1);
//        增删改都需要提交事务
        sqlSession.commit();
        sqlSession.close();
    }
}

7.maven工程中包引用（pom.xml）

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.22</version>
    </dependency>

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

    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    <dependency>
        <groupId>org.testng</groupId>
        <artifactId>testng</artifactId>
        <version>7.3.0</version>
    </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.16</version>
        </dependency>

---

总结

• 增、删、改操作需要提交事务！
• 接口所有的普通参数，尽量都写上@Param参数，尤其是多个参数时，必须写上！
• 有时候根据业务的需求，可以考虑使用map传递参数！
• 为了规范操作，在SQL的配置文件中，我们尽量将Parameter参数和resultType都写上！