Java自动化测试(数据库断言 18)

发表于 | 分类于 | |

Java自动化测试(数据库断言 18)

数据库

数据库，简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、截取、更新、删除等操作。
所谓“数据库”是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。

JDBC 驱动程序

JDBC(java database connectivity)驱动程序是对 JDBC 规范完整的实现，它的存在在 Java 程序与数据库系统之间建立了一条通信的渠道。

1
2
3
4
5
6
7
DBUtils封装了JDBC的操作，核心功能如下：
	1、QueryRunner中提供对sql语句操作的API.
	2、ResultSetHandler接口，用于定义select操作后，怎样封装结果集.
	3、DbUtils类是一个工具类，定义了关闭资源与事务处理的方法
QueryRunner核心类：
	update(Connection conn, String sql, Object... params) ，执行insert update delete操作
	query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) ，执行 select操作

Maven中配置DBUtils

1
2
3
4
5
6
7
8
9
10
11
12
<!-- mysql数据库驱动 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.38</version>
</dependency>
<!-- 数据库连接工具包 -->
<dependency>
    <groupId>commons-dbutils</groupId>
    <artifactId>commons-dbutils</artifactId>
    <version>1.6</version>
</dependency>

数据库连接工具类

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
package com.zhongxin.utils;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCUtils {

    public static Connection getConnection() {
        //定义数据库连接
        String url = "jdbc:mysql://api.lemonban.com:3306/futureloan?useUnicode=true&characterEncoding=utf-8";
        String user = "future";
        String password = "123456";
        //定义数据库连接对象
        Connection conn = null;
        try {
            //你导入的数据库驱动包， mysql。
            conn = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

更新数据

1
2
3
4
5
6
7
8
public static void update() throws SQLException {
    QueryRunner runner = new QueryRunner();
    String sql = "UPDATE member SET leave_amount=100 WHERE id=2073699;";
    Connection conn = JDBCUtils.getConnection();
    int count = runner.update(conn, sql);
    System.out.println(count);
    JDBCUtils.close(conn);
}

插入数据

1
2
3
4
5
6
7
8
public static void insert() throws SQLException {
    QueryRunner runner = new QueryRunner();
    String sql = "INSERT INTO member VALUES(NULL,'zhong','123456','15168230600,1,100,NOW());";
    Connection conn = JDBCUtils.getConnection();
    int count = runner.update(conn, sql);
    System.out.println(count);
    JDBCUtils.close(conn);
}

从上可以看出，更新和插入的区别就在SQL语句不同

查询数据

通过MapHandler查询

1
2
3
4
5
6
7
8
9
public static void main(String[] args) throws Exception {
    QueryRunner runner = new QueryRunner();
    String sql = "select * from member where id=2073699";
    Connection conn = JDBCUtils.getConnection();
    MapHandler handler = new MapHandler();
    Map<String, Object> map = runner.query(conn, sql, handler);
    System.out.println(map);
    JDBCUtils.close(conn);
}

数据查询

定义一个数据对象

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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
package com.zhongxin.pojo;

import java.math.BigDecimal;
import java.sql.Timestamp;

public class Member {
    private int id;
    private String reg_name;
    private String pwd;
    private String mobile_phone;
    private int type;
    private BigDecimal leave_amount;
    private Timestamp reg_time;

    @Override
    public String toString() {
        return "Member{" +
                "id=" + id +
                ", reg_name='" + reg_name + '\'' +
                ", pwd='" + pwd + '\'' +
                ", mobile_phone='" + mobile_phone + '\'' +
                ", type=" + type +
                ", leave_amount=" + leave_amount +
                ", reg_time=" + reg_time +
                '}';
    }

    public int getId() {
        return id;
    }

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

    public String getReg_name() {
        return reg_name;
    }

    public void setReg_name(String reg_name) {
        this.reg_name = reg_name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public String getMobile_phone() {
        return mobile_phone;
    }

    public void setMobile_phone(String mobile_phone) {
        this.mobile_phone = mobile_phone;
    }

    public int getType() {
        return type;
    }

    public void setType(int type) {
        this.type = type;
    }

    public BigDecimal getLeave_amount() {
        return leave_amount;
    }

    public void setLeave_amount(BigDecimal leave_amount) {
        this.leave_amount = leave_amount;
    }

    public Timestamp getReg_time() {
        return reg_time;
    }

    public void setReg_time(Timestamp reg_time) {
        this.reg_time = reg_time;
    }
}

通过BeanHandler获取

1
2
3
4
5
6
7
8
9
public static void beanHandler() throws SQLException {
        QueryRunner runner = new QueryRunner();
        String sql = "select * from member where id=2073699";
        Connection conn = JDBCUtils.getConnection();
        BeanHandler<Member> handler = new BeanHandler<>(Member.class);
        Member m = runner.query(conn, sql, handler);
        System.out.println(m);
        JDBCUtils.close(conn);
    }

查询结果

通过BeanListHandler查询

1
2
3
4
5
6
7
8
9
10
11
public static void main(String[] args) throws Exception {
    QueryRunner runner = new QueryRunner();
    String sql = "select * from member";
    Connection conn = JDBCUtils.getConnection();
    BeanListHandler<Member> handler = new BeanListHandler<>(Member.class);
    List<Member> list = runner.query(conn, sql, handler);
    for (Member member : list) {
        System.out.println(member);
    }
    JDBCUtils.close(conn);
}

通过ScalarHandler查询

1
2
3
4
5
6
7
8
9
public static void scalarHandler() throws SQLException {
    QueryRunner runner = new QueryRunner();
    String sql = "select count(*) from member where id=2073699";
    Connection conn = JDBCUtils.getConnection();
    ScalarHandler<Long> handler = new ScalarHandler<>();
    Long count = runner.query(conn, sql, handler);
    System.out.println(count);
    JDBCUtils.close(conn);
}

数据库断言

从excel中读取sql语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public static Object getSingleResult(String sql) {
    if (StringUtils.isBlank(sql)) {
        return null;
    }
    // 1. 定义返回值
    Object result = null;
    try {
        // 2. 创建DBUtils sql语句操作类
        Connection conn = JDBCUtils.getConnection();
        // 3. 获取数据库连接
        QueryRunner runner = new QueryRunner();
        // 4. 创建ScalarHandler，针对单行单列的数据
        ScalarHandler handler = new ScalarHandler();
        // 5. 执行sql语句
        result = runner.query(conn, sql, handler);
        // 6. 关闭数据库连接
        JDBCUtils.close(conn);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return result;
}

对注册进行数据库断言

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Test(dataProvider = "datas")
public void test(CaseInfo caseInfo) {
    Long beforeSQLresult = (Long) SQLUtils.getSingleResult(caseInfo.getSql());
    String responseBody = HttpUtils.call(caseInfo, UserData.DEFAULT_HEADERS);
    responseAssert(caseInfo.getExpectedResult(), responseBody);
    addWriteBackData(sheetIndex, caseInfo.getId(), 8, responseBody);
    Long afterSQLresult = (Long) SQLUtils.getSingleResult(caseInfo.getSql());
    if (StringUtils.isNoneBlank(caseInfo.getSql())) {
        if (beforeSQLresult == 0 && afterSQLresult == 1) {
            System.out.println("数据库断言成功");
        } else {
            System.out.println("数据库断言失败");
        }
    }
}

对充值进行数据库断言

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Test(dataProvider = "datas")
public void test(CaseInfo caseInfo) {
    BigDecimal beforeSQLresult = (BigDecimal) SQLUtils.getSingleResult(caseInfo.getSql());
    HashMap<String, String> headers = getAuthorizationHeader();
    String responseBody = HttpUtils.call(caseInfo, headers);
    responseAssert(caseInfo.getExpectedResult(), responseBody);
    addWriteBackData(sheetIndex, caseInfo.getId(), 8, responseBody);
    BigDecimal afterSQLresult = (BigDecimal) SQLUtils.getSingleResult(caseInfo.getSql());
    if (StringUtils.isNotBlank(caseInfo.getSql())) {
        String amountStr = JSONPath.read(caseInfo.getParams(), "$.amount").toString();
        BigDecimal amout = new BigDecimal(amountStr);
        BigDecimal subtractResult = afterSQLresult.subtract(beforeSQLresult);
        // compareTo == 0 => 相等
        if (subtractResult.compareTo(amout) == 0) {
            System.out.println("数据库断言成功");
        } else {
            System.out.println("数据库断言失败");
        }
    }
}
wechat
欢迎您扫一扫上面的微信公众号，订阅我的博客！
您的支持将鼓励我继续创作！