数据库
数据库，简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、截取、更新、删除等操作。 所谓“数据库”是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。
JDBC 驱动程序
JDBC(java database connectivity)驱动程序是对 JDBC 规范完整的实现，它的存在在 Java 程序与数据库系统之间建立了一条通信的渠道。
代码语言：javascript复制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
代码语言：javascript复制<!-- 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>
数据库连接工具类
代码语言：javascript复制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();
}
}
}
}
更新数据
代码语言：javascript复制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);
}
插入数据
代码语言：javascript复制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查询
代码语言：javascript复制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);
}
数据查询
定义一个数据对象
代码语言：javascript复制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获取
代码语言：javascript复制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查询
代码语言：javascript复制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查询
代码语言：javascript复制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语句
代码语言：javascript复制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;
}
对注册进行数据库断言
代码语言：javascript复制@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("数据库断言失败");
}
}
}
对充值进行数据库断言
代码语言：javascript复制@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("数据库断言失败");
}
}
}
