Mybatis简单测试

发表于 | 分类于 ，
字数统计: 2.1k 字 | 阅读时长 ≈ 12 分钟

0.准备工具

因为配置文件都是读取相同的，所以可以进行工厂类封装。

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
package org.fkit.factory;

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

public class FKSqlSessionFactory {
    private static SqlSessionFactory sqlSessionFactory = null;
    static {
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }

    public static SqlSessionFactory getSqlSessionFactory(){
        return sqlSessionFactory;
    }
}

1.简单的select，insert，update，delete测试

在UserMapper.xml中配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<insert id="saveUser" parameterType="user" useGeneratedKeys="true">
          INSERT INTO tb_user(name, sex, age) VALUES (#{name},#{sex},#{age})
</insert>

<select id="selectUser" parameterType="int" resultType="user">
    SELECT * from tb_user where id = #{id}
</select>
<update id="modifyUser" parameterType="user" >
    UPDATE tb_user set name = #{name}, sex = #{sex}, age = #{age}
    WHERE id = #{id}
</update>
<delete id="removeUser" parameterType="int">
    DELETE from tb_user where id=#{id}
</delete>

注：均包含在中。

可以看到，在saveUser和modifyUser中，都是传入的User对象，而在selectUser和removeUser传入的是int值，所以在测试时，需要进行对应。resultType是返回类型，返回User类型，需要使用User user = …获取到该对象，默认返回的是int类型（好像是。。忘记了）。

分别对其进行测试及结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//InsertTest.java
public class InsertTest {
    public static void main(String args[]){
        SqlSession sqlSession = FKSqlSessionFactory.getSqlSession();
        User user = new User("jack", "男", 22);
        sqlSession.insert("org.fkit.mapper.UserMapper.saveUser", user);
        sqlSession.commit();
        sqlSession.close();
    }
}

//output:
DEBUG [main] - ==>  Preparing: INSERT INTO tb_user(name, sex, age) VALUES (?,?,?) 
DEBUG [main] - ==> Parameters: jack(String), 男(String), 22(Integer)
DEBUG [main] - <==    Updates: 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//SelectTest.java
public class SelectTest {
    public static void main(String args[]){
        SqlSession sqlSession = FKSqlSessionFactory.getSqlSession();
        User user = sqlSession.selectOne("org.fkit.mapper.UserMapper.selectUser", 1);
        System.out.println(user.getName());
        sqlSession.commit();
        sqlSession.close();
    }
}

//output:
DEBUG [main] - ==>  Preparing: SELECT * from tb_user where id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
User{id=1, name='hello?', sex='g', age=11}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
//UpdateTest.java
public class UpdateTest {
    public static void main(String args[]){
        SqlSession sqlSession = FKSqlSessionFactory.getSqlSession();
        User user = sqlSession.selectOne("org.fkit.mapper.UserMapper.selectUser", 1);
        user.setName("hello?");
        sqlSession.update("org.fkit.mapper.UserMapper.modifyUser", user);
        sqlSession.commit();
        sqlSession.close();
    }
}

//output:
DEBUG [main] - ==>  Preparing: SELECT * from tb_user where id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: UPDATE tb_user set name = ?, sex = ?, age = ? WHERE id = ? 
DEBUG [main] - ==> Parameters: hello?(String), g(String), 11(Integer), 1(Integer)
DEBUG [main] - <==    Updates: 1
1
2
3
4
5
6
7
8
9
10
11
12
13
//DeleteTest.java
public class DeleteTest {
    public static void main(String args[]){
        SqlSession sqlSession = FKSqlSessionFactory.getSqlSession();
        sqlSession.selectOne("org.fkit.mapper.UserMapper.removeUser", 3);
        sqlSession.commit();
        sqlSession.close();
    }
}

//output:
DEBUG [main] - ==>  Preparing: DELETE from tb_user where id=? 
DEBUG [main] - ==> Parameters: 3(Integer)

2.测试ResultMaps

resultMap是mybatis中最重要最强大的元素，它的作用是告诉mybatis将从结果集中取出的数据转化成开发者所需要的对象。

1
2
3
4
5
6
<select id="selectUsersToMap" resultType="map">
    SELECT * from tb_user
</select>
<select id="selectUsers" resultType="org.fkit.domain.User">
    SELECT * from tb_user
</select>

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
51
52
53
54
55
56
57
58
59
60
//SelectMapTest.java
package org.fkit.test;

import org.apache.ibatis.session.SqlSession;
import org.fkit.domain.User;
import org.fkit.factory.FKSqlSessionFactory;

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

public class SelectMapTest {
    public static void main(String args[]) {
        SqlSession sqlSession = FKSqlSessionFactory.getSqlSession();
//        获取map对象集，每个user映射为一个map
        List<Map<String, Object>> list =
                sqlSession.selectList("org.fkit.mapper.UserMapper.selectUsersToMap");
        for (Map<String, Object> row : list) {
            System.out.println(row);
        }
//        获取user对象集
        List<User> users =
                sqlSession.selectList("org.fkit.mapper.UserMapper.selectUsers");
        for (User user : users) {
            System.out.println(user);
        }
        sqlSession.commit();
        sqlSession.close();
    }
}
//output:
DEBUG [main] - ==>  Preparing: SELECT * from tb_user 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 12
{sex=g, name=hello?, id=1, age=11}
{sex=男, name=gg, id=4, age=26}
{sex=男, name=gg, id=5, age=26}
{sex=男, name=gg, id=6, age=26}
{sex=男, name=gg, id=7, age=26}
{sex=男, name=gg, id=8, age=26}
{sex=男, name=gg, id=9, age=26}
{sex=男, name=gg, id=10, age=26}
{sex=男, name=gg, id=11, age=26}
{sex=男, name=gg, id=12, age=26}
{sex=男, name=jack, id=13, age=22}
{sex=男, name=jack, id=14, age=22}
DEBUG [main] - ==>  Preparing: SELECT * from tb_user 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 12
User{id=1, name='hello?', sex='g', age=11}
User{id=4, name='gg', sex='男', age=26}
User{id=5, name='gg', sex='男', age=26}
User{id=6, name='gg', sex='男', age=26}
User{id=7, name='gg', sex='男', age=26}
User{id=8, name='gg', sex='男', age=26}
User{id=9, name='gg', sex='男', age=26}
User{id=10, name='gg', sex='男', age=26}
User{id=11, name='gg', sex='男', age=26}
User{id=12, name='gg', sex='男', age=26}
User{id=13, name='jack', sex='男', age=22}
User{id=14, name='jack', sex='男', age=22}

查询语句返回的每一条数据都被封装成了一个Map集合，列名作为Map集合的key，而列的值作为Map的value。

虽然数据被封装成Map集合返回，但是Map集合并不能很好描述一个领域的模型，如果像后一个测试一样，使用POJO类来作为领域模型描述数据，会更加准确。

比较特殊的情况：查询到的数据的列和需要返回的对象（User）的属性不一致，则mybatis不会自动赋值，可以使用resultMap进行处理。

在数据库中创建一个tb_user2表，并且插入一定数据：

1
2
3
4
5
6
7
8
CREATE TABLE tb_user2
(
  user_id   INT AUTO_INCREMENT
    PRIMARY KEY,
  user_name VARCHAR(18) NULL,
  user_sex  CHAR(2)     NULL,
  user_age  INT         NULL
)

User映射表使用原来的User.java。
在UserMapper.xml中添加：

1
2
3
4
5
6
7
8
9
10
<resultMap id="userResultMap" type="org.fkit.domain.User">
    <id property="id" column="user_id"/>
    <!--column对应数据库中的属性-->
    <result property="name" column="user_name"/>
    <result property="sex" column="user_sex"/>
    <result property="age" column="user_age"/>
</resultMap>
<select id="selectUsers2" resultMap="userResultMap">
    SELECT * from tb_user2
</select>

测试：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
//ResultMapTest.java
public class ResultMapTest {
    public static void main(String args[]) {
        SqlSession sqlSession = FKSqlSessionFactory.getSqlSession();
        List<User> users =
                sqlSession.selectList("org.fkit.mapper.UserMapper.selectUsers2");
        for (User user : users) {
            System.out.println(user);
        }
    }
}
//output:
DEBUG [main] - ==>  Preparing: SELECT * from tb_user2 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 2
User{id=1, name='qq1', sex='q', age=12}
User{id=2, name='qq2', sex='b', age=13}

（待补充

3.多表查询

两个表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE tb_clazz
(
  id   INT AUTO_INCREMENT
    PRIMARY KEY,
  CODE VARCHAR(18) NULL,
  CONSTRAINT TB_CLAZZ_id_uindex
  UNIQUE (id)
)
CREATE TABLE tb_student
(
  id       INT AUTO_INCREMENT
    PRIMARY KEY,
  NAME     VARCHAR(18) NULL,
  sex      CHAR(3)     NULL,
  age      INT         NULL,
  clazz_id INT         NULL,
  CONSTRAINT TB_STUDENT_id_uindex
  UNIQUE (id),
  CONSTRAINT TB_STUDENT_tb_clazz_id_fk
  FOREIGN KEY (clazz_id) REFERENCES tb_clazz (id)
)

插入2个班级，4个学生，其中每2个学生对应一个班级。
建立pojo类：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
public class Clazz {
    private Integer id;
    private String code;
    //setter,getter
}


public class Student {
    private Integer id;
    private String name;
    private String sex;
    private Integer age;
    private Clazz clazz;
    //getter,setter

UserMapper.xml映射：

1
2
3
4
5
6
7
8
9
10
11
12
<resultMap id="studentResultMap" type="org.fkit.domain.Student">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="sex" column="sex"/>
    <result property="age" column="age"/>
    <association property="clazz" column="clazz_id"
                 javaType="org.fkit.domain.Clazz" select="selectClazzWithId"/>

</resultMap>
<select id="selectClazzWithId" resultType="org.fkit.domain.Clazz">
      SELECT * FROM tb_clazz where id = #{id}
</select>

测试：

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
//SelectStudentTest.java
public class SelectStudentTest {
    public static void main(String args[]) {
        SqlSession sqlSession = FKSqlSessionFactory.getSqlSession();
        List<Student> students =
                sqlSession.selectList("org.fkit.mapper.UserMapper.selectStudent");
        for (Student student : students) {
            System.out.println(student);
        }
        sqlSession.commit();
        sqlSession.close();
    }
}
//output:
DEBUG [main] - ==>  Preparing: SELECT * FROM tb_student 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - ====>  Preparing: SELECT * FROM tb_clazz where id = ? 
DEBUG [main] - ====> Parameters: 1(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - ====>  Preparing: SELECT * FROM tb_clazz where id = ? 
DEBUG [main] - ====> Parameters: 2(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - <==      Total: 4
Student{id=1, name='jack', sex='男', age=22, clazz=Clazz{id=1, code='j1601'}}
Student{id=2, name='rose', sex='女', age=18, clazz=Clazz{id=1, code='j1601'}}
Student{id=3, name='tom', sex='男', age=25, clazz=Clazz{id=2, code='j1602'}}
Student{id=4, name='mary', sex='女', age=20, clazz=Clazz{id=2, code='j1602'}}

查询每个班级的学生：

修改clazz.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
public class Clazz {
    private Integer id;
    private String code;
    private List<Student> students;

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }
...
}

UserMapper.xml添加：

1
2
3
4
5
6
7
8
9
10
11
12
13
<resultMap id="clazzResultMap" type="org.fkit.domain.Clazz">
    <id property="id" column="id"/>
    <result property="code" column="code"/>
    <collection property="students" javaType="ArrayList"
                column="id" ofType="org.fkit.domain.Student"
                select="selectStudentWithId"/>
</resultMap>
<select id="selectStudentWithId" resultType="org.fkit.domain.Student">
    SELECT * FROM tb_student where clazz_id = #{id}
</select>
<select id="selectClazz" resultMap="clazzResultMap">
    SELECT * from tb_clazz
</select>

测试：

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
//SelectClazzTest.java
public class SelectClazzTest {
    public static void main(String args[]) {
        SqlSession sqlSession = FKSqlSessionFactory.getSqlSession();
        List<Clazz> clazzes =
                sqlSession.selectList("org.fkit.mapper.UserMapper.selectClazz");
        for (Clazz clazz : clazzes) {
            System.out.println(clazz);
            List<Student> students = clazz.getStudents();
            for (Student student : students) {
                System.out.println(student);
            }
        }
        sqlSession.commit();
        sqlSession.close();
    }
}
//output:
DEBUG [main] - ==>  Preparing: SELECT * from tb_clazz 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - ====>  Preparing: SELECT * FROM tb_student where clazz_id = ? 
DEBUG [main] - ====> Parameters: 1(Integer)
DEBUG [main] - <====      Total: 2
DEBUG [main] - ====>  Preparing: SELECT * FROM tb_student where clazz_id = ? 
DEBUG [main] - ====> Parameters: 2(Integer)
DEBUG [main] - <====      Total: 2
DEBUG [main] - <==      Total: 2
Clazz{id=1, code='j1601'}
Student{id=1, name='jack', sex='男', age=22, clazz=null}
Student{id=2, name='rose', sex='女', age=18, clazz=null}
Clazz{id=2, code='j1602'}
Student{id=3, name='tom', sex='男', age=25, clazz=null}
Student{id=4, name='mary', sex='女', age=20, clazz=null}

看到student没有clazz属性，应该是不能递归查询。

-------------本文结束 感谢您的阅读-------------