记录一次TestNg+MyBatis中的SqlSession出现的问题，问题虽然解决了但尚未明白问题

-->

记录一次TestNg+MyBatis中的SqlSession出现的问题，问题虽然解决了但尚未明白问题

2020-01-19 15:21发布

站内文章 / 移动开发

0 0

女痞

女 | 书童

私信

问题：

TestNg测试端项目结构：

几个主要文件的内容：

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper
 3         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 <mapper namespace="com.course">
 6     <!-- 获取登录接口case -->
 7     <select id="loginCase" parameterType="Integer" resultType="com.course.model.LoginCase">
 8         SELECT * FROM loginCase
 9         WHERE id = #{id};
10     </select>
11     <!--添加用户接口case-->
12     <select id="addUserCase" parameterType="Integer" resultType="com.course.model.AddUserCase">
13         select * from addUserCase where id=#{id};
14     </select>
15     <!-- 获取用户信息case -->
16     <select id="getUserInfoCase" parameterType="Integer" resultType="com.course.model.GetUserInfoCase">
17         SELECT * FROM getUserInfoCase
18         WHERE id = #{id};
19     </select>
20     <!-- 获取用户列表case -->
21     <select id="getUserListCase" parameterType="Integer" resultType="com.course.model.GetUserListCase">
22         <!-- SQL语句 -->
23         select * from getUserListCase where id=#{id};
24     </select>
25     <!-- 更新/删除用户信息case -->
26     <select id="updateUserInfoCase" parameterType="Integer" resultType="com.course.model.UpdateUserInfoCase">
27         select * from updateUserInfoCase where id = #{id};
28     </select>
29 
30     <!--添加用户接口-->
31     <select id="addUser" parameterType="com.course.model.AddUserCase" resultType="com.course.model.User">
32         select * from user
33         where userName=#{userName}
34         and password=#{password}
35         and sex=#{sex}
36         and age=#{age}
37         and permission=#{permission}
38         and isDelete=#{isDelete};
39     </select>
40     <!-- 获取用户信息 -->
41     <select id="getUserInfo" parameterType="com.course.model.GetUserInfoCase" resultType="com.course.model.User">
42         select * from user WHERE
43         id=#{userId};
44     </select>
45 
46     <!--获取用户列表-->
47     <select id="getUserList" parameterType="com.course.model.GetUserListCase" resultType="com.course.model.User">
48         <!-- SQL语句 -->
49         select * from user
50         <trim prefix="WHERE" prefixOverrides="and">
51             <if test="null != userName and '' !=userName">
52                 AND userName=#{userName}
53             </if>
54             <if test="null != sex and '' !=sex">
55                 AND sex=#{sex}
56             </if>
57             <if test="null != age and '' !=age">
58                 AND age=#{age}
59             </if>
60         </trim>
61         ;
62     </select>
63     <!--获取更新后的数据-->
64     <select id="getUpdateUserInfo" parameterType="com.course.model.UpdateUserInfoCase" resultType="com.course.model.User">
65         select * from user
66         <trim prefix="WHERE" prefixOverrides="and">
67             <if test="null != userName and '' !=userName">
68                 AND userName=#{userName}
69             </if>
70             <if test="null != sex and '' !=sex">
71                 AND sex=#{sex}
72             </if>
73             <if test="null != age and '' !=age">
74                 AND age=#{age}
75             </if>
76             <if test="null != permission and '' !=permission">
77                 AND permission=#{permission}
78             </if>
79             <if test="null != isDelete and '' !=isDelete">
80                 AND isDelete=#{isDelete}
81             </if>
82         </trim>
83         And id = #{userId};
84     </select>
85 
86     <insert id="insertUser" parameterType="com.course.model.User">
87         INSERT INTO USER(userName, password, sex, age, permission, isDelete)
88         VALUES(#{userName}, #{password}, #{sex}, #{age}, #{permission}, #{isDelete})
89     </insert>
90 </mapper>

SQLMapper.xml

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 3         "http://mybatis.org/dtd/mybatis-3-config.dtd">
 4 <!-- 注意configuration中的标签有一定的顺序
 5 properties, settings, typeAliases, typeHandlers, objectFactory, objectWrapperFactory,
 6 reflectorFactory, plugins, environments, databaseIdProvider, mappers
 7 -->
 8 <configuration>
 9     <!-- 设置log记录sql语句 -->
10     <settings>
11         <setting name="logImpl" value="STDOUT_LOGGING"/>
12     </settings>
13     <!-- 注册对象的空间命名 -->
14     <environments default="development">
15         <environment id="development">
16             <transactionManager type="JDBC" />
17             <dataSource type="POOLED">
18                 <!-- 1.加载数据库驱动 -->
19                 <property name="driver" value="com.mysql.jdbc.Driver" />
20                 <!-- 2.数据库连接地址 -->
21                 <property name="url" value="jdbc:mysql://localhost:3306/course12?characterEncoding=utf8&amp;useSSL=false" />
22                 <!-- 3.数据库用户 -->
23                 <property name="username" value="root" />
24                 <!-- 4.数据库密码 -->
25                 <property name="password" value="yxh1990" />
26             </dataSource>
27         </environment>
28     </environments>
29     <!-- 注册映射文件：java对象与数据库之间的xml文件路径 -->
30     <mappers>
31         <mapper resource="mapper/SQLMapper.xml" />
32     </mappers>
33 
34 </configuration>

databaseConfig.xml

 1 package com.course.utils;
 2 
 3 import org.apache.ibatis.io.Resources;
 4 import org.apache.ibatis.session.SqlSession;
 5 import org.apache.ibatis.session.SqlSessionFactory;
 6 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 7 
 8 import java.io.IOException;
 9 import java.io.Reader;
10 
11 public class DatabaseUtil {
12 
13     public static SqlSession getSqlSession() throws IOException {
14         // 获取配置的资源文件
15         Reader reader = Resources.getResourceAsReader("databaseConfig.xml");
16 
17         SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
18         // sqlSession就是能够执行配置文件中的sql语句
19         SqlSession sqlSession = factory.openSession();
20         return sqlSession;
21     }
22 }

DatabaseUtil.java

GetUserInfoTest中运行过程描述：

1.通过DataBaseUtil获取一个SqlSession session=DatabaseUtil.getSqlSession()，

2.session通过映射文件获取getuserinfocase表第一行内容并封装到GetUserInfoCase实体中

3.使用HttpClient发送请求访问getUserInfo接口（参数为GetUserInfoCase），返回查询的结果

4.session通过映射文件和参数查询获取user表中的user信息

GetUserInfoTest.java

 1 package com.course.cases;
 2 
 3 import com.course.config.TestConfig;
 4 import com.course.model.GetUserInfoCase;
 5 import com.course.model.User;
 6 import com.course.utils.DatabaseUtil;
 7 import org.apache.http.HttpResponse;
 8 import org.apache.http.client.methods.HttpPost;
 9 import org.apache.http.entity.StringEntity;
10 import org.apache.http.util.EntityUtils;
11 import org.apache.ibatis.session.SqlSession;
12 import org.json.JSONArray;
13 import org.json.JSONObject;
14 import org.testng.Assert;
15 import org.testng.annotations.Test;
16 
17 import java.io.IOException;
18 import java.util.ArrayList;
19 import java.util.Arrays;
20 import java.util.List;
21 
22 public class GetUserInfoTest {
23 
24     @Test(dependsOnGroups = "loginTrue", description = "获取userId为1的用户信息")
25     public void getUserInfo() throws IOException, InterruptedException {
26         SqlSession session = DatabaseUtil.getSqlSession();
27         GetUserInfoCase getUserInfoCase = session.selectOne("getUserInfoCase", 1);
28         System.out.println(getUserInfoCase.toString());
29         System.out.println(TestConfig.getUserInfoUrl);
30 
31         //下边为写完接口的代码
32         JSONArray resultJson = getJsonResult(getUserInfoCase);
33 
34         User user = session.selectOne(getUserInfoCase.getExpected(), getUserInfoCase);
35         System.out.println("自己查询数据库获取用户信息："+user.toString());
36 
37         List userList = new ArrayList();
38         userList.add(user);
39         JSONArray jsonArray = new JSONArray(userList);
40         System.out.println("获取用户信息："+jsonArray.toString());
41         System.out.println("调用接口获取用户信息："+resultJson.toString());
42         Assert.assertEquals(jsonArray.toString(), resultJson.toString());
43     }
44 
45     private JSONArray getJsonResult(GetUserInfoCase getUserInfoCase) throws IOException {
46         HttpPost post = new HttpPost(TestConfig.getUserInfoUrl);
47         JSONObject param = new JSONObject();
48         param.put("id", getUserInfoCase.getUserId());
49         // 设置请求头信息 设置header
50         post.setHeader("content-type", "application/json");
51         //将参数信息添加到方法中
52         StringEntity entity = new StringEntity(param.toString(), "utf-8");
53         post.setEntity(entity);
54         //设置cookies
55         TestConfig.defaultHttpClient.setCookieStore(TestConfig.store);
56         //声明一个对象来进行响应结果的存储
57         String result;
58         //执行post方法
59         HttpResponse response = TestConfig.defaultHttpClient.execute(post);
60         //获取响应结果
61         result = EntityUtils.toString(response.getEntity(), "utf-8");
62         System.out.println("调用接口result："+result);
63         JSONArray jsonArray = new JSONArray(result);
64         System.out.println(jsonArray.toString());
65         return jsonArray;
66     }
67 }

AddUserTest中运行过程描述：

1.通过DataBaseUtil获取一个SqlSession session=DatabaseUtil.getSqlSession()，

2.session通过映射文件获取adduserinfocase表第一行内容并封装到AddUserInfoCase实体中

3.使用HttpClient发送请求访问getUserInfo接口（参数为AddUserInfoCase），接口将信息插入user表

4.session通过映射文件和参数查询获取user表中的user信息（？此处无法查询到user，user为null）

AddUserTest.java

 1 package com.course.cases;
 2 
 3 import com.course.config.TestConfig;
 4 import com.course.model.AddUserCase;
 5 import com.course.model.User;
 6 import com.course.utils.DatabaseUtil;
 7 import org.apache.http.HttpResponse;
 8 import org.apache.http.client.methods.HttpPost;
 9 import org.apache.http.entity.StringEntity;
10 import org.apache.http.util.EntityUtils;
11 import org.apache.ibatis.session.SqlSession;
12 import org.json.JSONObject;
13 import org.testng.Assert;
14 import org.testng.annotations.Test;
15 
16 import java.io.IOException;
17 import java.util.Random;
18 
19 public class AddUserTest {
20 
21     @Test(dependsOnGroups = "loginTrue", description = "添加用户接口")
22     public void addUser() throws IOException, InterruptedException {
23         SqlSession session = DatabaseUtil.getSqlSession();
24         AddUserCase addUserCase = session.selectOne("addUserCase", 1);
25         // 将查询到的addUserCase中的userName更新，防止每次都要删除user表中的记录
26         addUserCase.setUserName(addUserCase.getUserName()+ new Random().nextInt());
27         System.out.println(addUserCase.toString());
28         System.out.println(TestConfig.addUserUrl);
29 
30         // 发送请求，获取结果
31         String result = getResult(addUserCase);
32         System.out.println("异步结果"+result);
33         //验证返回结果
34         /* 此处需要新建一个SqlSession才能查询新增的user，不然无法查询结果 */
35         //SqlSession session1 = DatabaseUtil.getSqlSession();
36         User user = session.selectOne("addUser", addUserCase);
37         System.out.println(user.toString());
38 
39         Assert.assertEquals(addUserCase.getExpected(), result);
40     }
41 
42     private String getResult(AddUserCase addUserCase) throws IOException {
43         HttpPost post = new HttpPost(TestConfig.addUserUrl);
44         JSONObject param = new JSONObject();
45         param.put("userName", addUserCase.getUserName());
46         param.put("password", addUserCase.getPassword());
47         param.put("sex", addUserCase.getSex());
48         param.put("age", addUserCase.getAge());
49         param.put("permission", addUserCase.getPermission());
50         param.put("isDelete", addUserCase.getIsDelete());
51 
52         // 设置头信息
53         post.setHeader("content-type", "application/json");
54         StringEntity entity = new StringEntity(param.toString(), "utf-8");
55         post.setEntity(entity);
56 
57         // 设置cookies
58         TestConfig.defaultHttpClient.setCookieStore(TestConfig.store);
59         String result;  //存放返回结果
60         HttpResponse response = TestConfig.defaultHttpClient.execute(post);
61 
62         result = EntityUtils.toString(response.getEntity(), "utf-8");
63 
64         System.out.println(result);
65 
66         return result;
67     }
68 }

如何解决上面user为null的情况？目前发现两种解决方法：

1.重新建一个SqlSession查询数据库就好了，SqlSession session1 = DatabaseUtil.getSqlSession();

2.修改SqlSession sqlSession = factory.openSession();为SqlSession sqlSession = factory.openSession(true);

 1 package com.course.utils;
 2 
 3 import org.apache.ibatis.io.Resources;
 4 import org.apache.ibatis.session.SqlSession;
 5 import org.apache.ibatis.session.SqlSessionFactory;
 6 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 7 
 8 import java.io.IOException;
 9 import java.io.Reader;
10 
11 public class DatabaseUtil {
12 
13     public static SqlSession getSqlSession() throws IOException {
14         // 获取配置的资源文件
15         Reader reader = Resources.getResourceAsReader("databaseConfig.xml");
16 
17         SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
18         // sqlSession就是能够执行配置文件中的sql语句
19         SqlSession sqlSession = factory.openSession();
20         return sqlSession;
21     }
22 }