需求

?编写全流程接口测试用例时，有时候需要进行一些数据操作，用于校验系统落地数据的准确性。

实现

1.编写构造函数和成员变量

private JdbcTemplate jdbcTemplate;
   public DBFixture(String info,String account) {
        logger.debug("db info: {}",info);
        logger.debug("db account: {}",account);
        DBInfo dbInfo=GsonUtil.parseJsonWithGson(info,DBInfo.class);
        DBAccount dbAccount = GsonUtil.parseJsonWithGson(account,DBAccount.class);
        String type=dbInfo.getType();
        String hostName=dbInfo.getHostName();
        String port=dbInfo.getPort();
        String instance=dbInfo.getInstance();
        String userName=dbAccount.getUserName();
        String passWord=dbAccount.getPassWord();
        logger.info("type: {} , hostName: {} , port: {} , instance: {} , userName: {} ,passWord: {}",
                type, hostName, port, instance, userName, passWord);
        initialDB(type, hostName, port, instance, userName, passWord);
    }

private void initialDB(String type, String hostName, String port, String instance, String userName, String passWord) {
        String key = type + hostName + port + instance + userName + passWord;
        if (DBContext.getInstance().jdbcTemplateMap.containsKey(key)) {//判断jdbcTemplate在上下文中是否已经存在，如果存在就获取从上下文中获取，如果不存在就新建并保存到上下文中
            logger.info("DB initial: {} exists in cache", key);
            jdbcTemplate = DBContext.getInstance().jdbcTemplateMap.get(key);
        } else {
            logger.info("DB initial: type: {} , hostName: {} , port: {} , instance: {} , userName: {} ,passWord: {}",
                    type, hostName, port, instance, userName, passWord);
            jdbcTemplate = new DBinit(type, hostName, port, instance, userName, passWord).getJdbcTemplate();
            DBContext.getInstance().jdbcTemplateMap.put(key, jdbcTemplate);
            logger.info("DB initial: put {} into cache", key);
        }
    }

2.上下文单例对象

public class DBContext {
    public Map<String,JdbcTemplate> jdbcTemplateMap;

    private DBContext() {
    }

    private static volatile DBContext dc = null;

    public static DBContext getInstance() {
        synchronized (DBContext.class) {
            if (dc == null) {
                dc = new DBContext();
                dc.jdbcTemplateMap=new HashMap<String,JdbcTemplate>();
            }
        }
        return dc;
    }
}

3.初始化 jdbcTemplate

package own.slim.db.initial;

import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;

public class DBinit {
    private final static Logger logger = LoggerFactory.getLogger(DBinit.class);

    private DruidDataSource druidDataSource;
    private JdbcTemplate jdbcTemplate;
    private String driverClassName = null;
    private String hostName = null;
    private String userName = null;
    private String passWord = null;
    private String url = null;
    private String instance = null;
    private String type = null;
    private String port;

    public DBinit(String type, String hostName, String port, String instance, String userName, String passWord) {
        this.type = type;
        this.hostName = hostName;
        this.port = port;
        this.instance = instance;
        this.userName = userName;
        this.passWord = passWord;
        switch (this.type.trim().toLowerCase()) {
            case "mysql":
                this.driverClassName = Driver.MYSQL.getDriverClassName();
                this.url = Driver.MYSQL.getUrlTemplate().replace(Driver.MYSQL.getReplaceContent(), this.hostName + ":" + this.port + "/" + this.instance);
                break;
            //"oracle":可以扩展oracle的分支            
        }
        setJdbcTemplate();
    }

    private void setJdbcTemplate() {

        this.druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(this.driverClassName);
        druidDataSource.setUrl(this.url);
        druidDataSource.setUsername(this.userName);
        druidDataSource.setPassword(this.passWord);
        druidDataSource.setMaxActive(10);
        druidDataSource.setInitialSize(3);


        // 创建JDBC模板
        this.jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(this.druidDataSource);
    }

    public JdbcTemplate getJdbcTemplate() {
        return this.jdbcTemplate;
    }
}

4.DB工具类

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import own.slim.db.DBFixture;
import java.util.List;
import java.util.Map;

public class DBUtil {
    private final static Logger logger = LoggerFactory.getLogger(DBUtil.class);
    private JdbcTemplate jdbcTemplate;

    public DBUtil(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public String add(String sql) {
        String result = null;
        int i = 0;
        if (sql.toLowerCase().contains("insert")) {
            i = this.jdbcTemplate.update(sql);
            if (i > 0) {
                result = "Successfully";
            } else {
                result = "Failed";
            }
        } else {
            result = "It should be an insert statement";
        }
        logger.info("statement: {} ,Affected rows: {} , result: {} ", sql, i, result);
        return result;
    }

    public String update(String sql) {
        String result = null;
        int i = 0;
        if (sql.toLowerCase().contains("update")) {
            i = this.jdbcTemplate.update(sql);
            if (i > 0) {
                result = "Successfully";
            } else {
                result = "Failed";
            }
        } else {
            result = "It should be an update statement";
        }
        logger.info("statement: {} ,Affected rows: {} , result: {} ", sql, i, result);
        return result;
    }

    public String del(String sql) {
        String result = null;
        int i = 0;
        if (sql.toLowerCase().contains("delete")) {
            i = this.jdbcTemplate.update(sql);
            if (i > 0) {
                result = "Successfully";
            } else {
                result = "Failed";
            }
        } else {
            result = "It should be an delete statement";
        }
        logger.info("statement: {} ,Affected rows: {} , result: {} ", sql, i, result);
        return result;
    }

    public String queryCount(String sql) throws EmptyResultDataAccessException {
        String result = null;
        int i = 0;
        if (sql.toLowerCase().contains("select") && sql.toLowerCase().contains("count")) {
            i = this.jdbcTemplate.queryForObject(sql, Integer.class);
            result = String.valueOf(i);
        } else {
            result = "It should be an select count statement";
        }
        logger.info("statement: {} , result: {} ", sql, result);
        return result;
    }

    public String queryMapToString(String sql) throws EmptyResultDataAccessException {
        String result = null;
        Map<String, Object> map;
        if (sql.toLowerCase().contains("select")) {
            map = this.jdbcTemplate.queryForMap(sql);
            if (null != map) {
                result = GsonUtil.toJsonWithGson(map);
            } else {
                result = "the result is null";
            }
        } else {
            result = "It should be an select statement";
        }
        logger.info("statement: {} , result: {} ", sql, result);
        return result;
    }

    public Map<String, Object> queryMap(String sql) {
        Map<String, Object> map = null;
        if (sql.toLowerCase().contains("select")) {
            map = this.jdbcTemplate.queryForMap(sql);
        }
        logger.info("statement: {} , result: {} ", sql, map.toString());
        return map;
    }

    public String queryListToString(String sql) throws EmptyResultDataAccessException {
        String result = null;
        List<Map<String, Object>> list;
        if (sql.toLowerCase().contains("select")) {
            list = this.jdbcTemplate.queryForList(sql);
            if (null != list) {
                result = GsonUtil.toJsonWithGson(list);
            } else {
                result = "the result is null";
            }
        } else {
            result = "It should be an select statement";
        }
        logger.info("statement: {} , result: {} ", sql, result);
        return result;
    }

    public List<Map<String, Object>> queryList(String sql) throws EmptyResultDataAccessException {
        List<Map<String, Object>> list = null;
        if (sql.toLowerCase().contains("select")) {
            list = this.jdbcTemplate.queryForList(sql);
        }
        logger.info("statement: {} , result: {} ", sql, list.toString());
        return list;
    }

}

5.实现方法（关键字）-以查询为例

public String queryOneRecordForJson(String sql) {
        logger.debug("sql:{}", sql);
        String result = null;
        DBUtil dbUtil = new DBUtil(this.jdbcTemplate);
        try{
            result = dbUtil.queryMapToString(sql);
        }catch (EmptyResultDataAccessException e) {
            logger.debug("EmptyResultDataAccessException:{}", e);
            result = "the result is null";
        }
        return result;
    }

使用

1.引入类对应package

|import        |
|own.slim.db      |

2.编写脚本

|script|DBFixture             |!-{"type": "mysql","hostName": "test-mysql-72.hbfintech.com","port": "3306","instance": "loan_test"}-!|!-{"userName": "loan_test","passWord": "loan_test123"}-!|
|$info=|queryOneRecordForJson;|SELECT credit_status,fk_credit_status,debit_credit_status,credit_no FROM `loan_credit_info` where usr_id_no=‘320412195012314651‘                               |

3.测试

总结

?实际用例编写过程中，还会使用到新增、更新、删除等操作。通过循环查询目标数据的变化，还可以实现两个接口之间的等待方法。