使用Mybatis实现数据库的CURD操作

Mybatis 评论

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

在传统的JDBC 实现中，我们需要把查询过程写在java 类中，这样非常不便于后期维护，而使用Mybatis 则可以将查询语句配置在配置文件中，只需要维护好映射关系即可， 下面我们就来一起看雪如何去使用Mybatis吧。

创建数据库并插入数据

首先，我们创建一个mybatis_db 的数据库，然后创建一个表 t_user，在这个表中我们插入几条数据，如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> create database mybatis_db;
Query OK, 1 row affected (0.01 sec)

mysql> use mybatis_db;
Database changed
mysql> create table t_user(
    -> id int(32) primary key auto_increment,
    -> username varchar(50),
    -> jobs varchar(50),
    -> phone varchar(16));
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> insert into t_user values(1,"zhangsan","teacher","13142767333");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user values(2,"lisi","engineer","13142767334");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user values(3,"wangwu","pilot","12342767334");
Query OK, 1 row affected (0.00 sec)

mysql>

配置pom.xml 下载jar包

我们创建一个maven工程，并配置pom.xml 下载mybatis 和mysql-connect-java 的jar包，目前mybatis 的最新版本是3.5.4

1
2
3
4
5
6
7
8
9
10
<dependency>
     <groupId>org.mybatis</groupId>
     <artifactId>mybatis</artifactId>
     <version>3.5.4</version>
   </dependency>
   <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>8.0.19</version>
   </dependency>

整个工程的目录如下

20200408074929

配置mybatis-config

接下来，我们在 idea 的resource 中创建一个mybatis-config.xml 的配置文件，内容如下，具体不需要过多解释就是配置JDBC相关的参数以及通过mappers 定义一个mybatis 的映射文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?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="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://192.168.10.128:3306/mybatis_db" />
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="UserMapper.xml" />
    </mappers>
</configuration>

创建user类

接下里我们创建对于的User类，并生成get和set以及toString 方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
package com.mybatis;

public class User {
    private Integer id;
    private String username;
    private String jobs;
    private String phone;

    public Integer getId() {
        return id;
    }

    public String getUsername() {
        return username;
    }

    public String getJobs() {
        return jobs;
    }

    public String getPhone() {
        return phone;
    }

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

    public void setUsername(String username) {
        this.username = username;
    }

    public void setJobs(String jobs) {
        this.jobs = jobs;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", jobs='" + jobs + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }
}

创建测试类

配置UserMapper.xml

接下里，我们创建映射文件UserMapper.xml 然后需要制定一个namespace就是 UserMapper，接着写一个select 语句，定义id和参数类型以及resultType，resultMap是指描述如何从数据库结果集中加载对象，是最复杂也是最强大的元素。

1
2
3
4
5
6
7
8
9
<?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="UserMapper">
    <!--根据用户编号获取用户信息 -->
    <select id="findUserById" parameterType="Integer" resultType="com.mybatis.User">
		select * from t_user where id=#{id}
	</select>
</mapper>

然后我们创建一个测试类，根据id来查询用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package com.mybatis;

import java.io.InputStream;
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 org.junit.jupiter.api.Test;

/**
 * @Auth: xxxx
 * @E-mail: xxx
 * @title: MybatisTest
 * @projectName: mybatis
 * @description: TODO 描述信息
 * @Date 2020/4/7 9:15 下午
 **/
public class MybatisTest {

    @Test
    public void findUserByIdTest() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = sqlSession.selectOne("findUserById", 1);
        System.out.println(user.toString());
        sqlSession.close();
    }

}

如果我们希望进行模糊查询，则在UserMapper.xml 中定义

1
2
3
4
<select id="findUserByName" parameterType="String" resultType="com.mybatisdemo.User">
        select * from t_user where username like concat('%','${value}','%')

</select>

然后在测试类中新建一个方法进行测试

1
2
3
4
5
6
7
8
9
10
11
12
13

@Test
  public void findUserByNameTest() throws  Exception {
      String resource = "mybatis-config.xml";
      InputStream inputStream = Resources.getResourceAsStream(resource);
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
      SqlSession sqlSession = sqlSessionFactory.openSession();
      List<User> users = sqlSession.selectList("findUserByName","g");
      for (User user:users){
          System.out.println(user.toString());
      }
      sqlSession.close();
      }

如图所示

接下来我们测试下添加新用户，首先我们在UserMapper.xml 中定义一个insert元素

1
2
3
<insert id="addUser" parameterType="com.mybatisdemo.User" >
       insert into t_user(username,jobs,phone) value (#{username},#{jobs},#{phone})
</insert>

然后来写测试接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Test
public void addUser() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User();
        user.setUsername("beiluo");
        user.setJobs("DevOps");
        user.setPhone("1314566666");
        int rows = sqlSession.insert("addUser",user);
        if (rows >0){
            System.out.println("Success add "+ rows +"data！");
        }else{
            System.out.println("add data fail!");
        }
        sqlSession.commit();
        sqlSession.close();

    }

如下所示

接下来测试更新

1
2
3
 <update id="updateUserInfo" parameterType="com.mybatisdemo.User">
        update t_user set username=#{username},jobs=#{jobs},phone=#{phone} where id =#{id}
</update>

然后写个测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
@Test

   public void updateUserinfo() throws  Exception {

       String resource = "mybatis-config.xml";
       InputStream inputStream = Resources.getResourceAsStream(resource);
       SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
       SqlSession sqlSession = sqlSessionFactory.openSession();
       User user = new User();
       user.setId(1);
       user.setUsername("jike");
       user.setJobs("qa");
       user.setPhone("13142764432");
       int rows = sqlSession.update("updateUserInfo",user);
       if (rows >0){
           System.out.println("Success update "+ rows +" data！");
       }else{
           System.out.println("update data fail!");
       }
       sqlSession.commit();
       sqlSession.close();

   }

执行后如下所示

最后，我们测试下删除功能

1
2
3
 <delete id="deleteUser" parameterType="com.mybatisdemo.User" >
        delete from t_user where id=#{id}
</delete>

测试类如下

@Test
    public void deleteUser() throws Exception {

        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        int rows = sqlSession.delete("deleteUser",1);
        if (rows >0){
            System.out.println("Success delete "+ rows +" data！");
        }else{
            System.out.println("delete data fail!");
        }
        sqlSession.commit();
        sqlSession.close();


    }

执行结果如下

20200408201759

阿文运维 & CSDN 专家作者

喜欢新事物，关注云计算行业，对新的技术有追求，喜欢写作和coding