PnxSql

PnxSql是一个low-level的jdbc客户端,用于PnxTest项目中对数据库进行 增删读写操作。支持参数绑定、简单易用的流式API。

基本用法演示

//从数据库获取首条数据,保存结果到Map
String sql = "SELECT account_id, mobile FROM tb_account 
  WHERE account_id=:accountId and mobile=:mobile ORDER BY create_date DESC LIMIT 10";

Optional<Map<String, Object>> stock = PnxSql.select(sql)
  .bind("accountId", 32000027188L)
  .bind("mobile", 16411111111L)
  .asOne();
if(stock.isPresent()){
  Map<String, Object> rowData = stock.get();
  PnxAssert.assertThat(rowData.get("account_id")).isEqualTo(32000027188L);
}

//从数据库获取多个数据,保存结果到列表
String sql = "SELECT account_id, mobile FROM tb_account 
  WHERE mobile=:mobile ORDER BY create_date DESC LIMIT 10";

  List<Map<String, Object>> stock = PnxSql.select(sql)
    .bind("mobile", 16411111111L)
    .asList();

//从数据库获取多个数据,保存结果到Bean
String sql = "SELECT account_id, mobile FROM tb_account 
  WHERE mobile=:mobile ORDER BY create_date DESC LIMIT 10";

Optional<Account> stock = PnxSql.select(sql)
                .bind("mobile", 16411111111L)
                .asOne(Account.class);
if(stock.isPresent()){
  Account account = stock.get();
  PnxAssert.assertThat(account.getAccountId()).isEqualTo(32000027188L);
}


//更新数据
 String sql = "update tb_account set mobile=:mobile where account_id=:accountId";
 PnxSql.update(sql)
  .bind("accountId", 32000027218L)
  .bind("mobile", 19411111111L)
  .execute();

数据库连接配置

数据库的连接配置需放置在对应的环境properties文件中,以mysql为例:

#数据库连接地址
pnx.db.url=jdbc:mysql://yourDatabaseAddress:3306/yourDB
#驱动
pnx.db.driver=com.mysql.cj.jdbc.Driver
#登录用户
pnx.db.user=root
#登录密码,建议使用Pnxtest加密,不要直接暴露
pnx.db.password=secret.sXI4yXOv1TC5nfH4
#连接超时时间,单位秒
pnx.db.timeout=5
#时区
pnx.db.timezone=GMT+8

多数据源

在某些测试项目中,要用到多少个数据源、数据库,可以使用下面的方式进行区分:

DbConfig anotherDb = new DbConfig();
anotherDb.setUrl(PnxContest.getString("your-another-db-url"));
anotherDb.setDriver(PnxContest.getString("your-another-db-driver"));
anotherDb.setUser(PnxContest.getString("your-another-db-user"));
anotherDb.setPassword(PnxContest.getString("your-another-db-password"));

PnxSql.select(sql, anotherDb).asOne();