[大数据]金仓数据库KingbaseES客户端编程开发框架-DbUnit（3. Dbunit使用说明）

3.?Dbunit使用说明

以下为DbUnit使用的示例。DbUnit的jar包可以从官方网站下载，DbUnit所使用的JDBC包kingbase8-8.6.0.jar位于$KINGBASE_HOME/Interface/jdbc目录下。使用时将DbUnit包和JDBC包导入到项目的Libraries中并定义相关配置项即可。

1. 建表语句：

create table dbtest(id int primary key,name text,resume clob);

表中现有数据：

TEST=# select * from dbtest;
 id | name |          resume
----+------+--------------------------
  1 | Mike | 姓名：张三  英文名：Mike
  2 | Jack | 姓名：李四  英文名：Jack
(2 rows)

1. 准备测试所需数据文件：

插入数据文件dataset.xml

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
    <DBTEST id="003" name="Martin" resume="姓名：王五  英文名：Martin"/>
    <DBTEST id="004" name="Kevin" resume="姓名：赵六  英文名：Kevin"/>
</dataset>

更新数据文件dataset2.xml

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
    <DBTEST id="001" name="Mary" resume="姓名：张三  英文名：Mary"/>
    <DBTEST id="002" name="Lily" resume="姓名：李四  英文名：Lily"/>
</dataset>

1. 准备测试所需方法：

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.dbunit.DatabaseUnitException;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;

public class DBUnit {

    private IDatabaseConnection connection;

    private QueryDataSet queryDataSet;

    private Connection jdbcConnection;

    public DBUnit() {
    }

    public DBUnit(String driver, String url, String user, String password) {
        try {
            Class.forName(driver);
            jdbcConnection = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void close() throws SQLException {
        if (connection != null) {
            connection.close();
        }
    }

    public void setSchema(String schema) throws SQLException, DatabaseUnitException {
        connection = new DatabaseConnection(jdbcConnection, schema);
    }

    public void insertData(IDataSet dataSet) {
        try {
            DatabaseOperation.INSERT.execute(connection, dataSet);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void clean_insertData(IDataSet dataSet) {
        try {
            DatabaseOperation.CLEAN_INSERT.execute(connection, dataSet);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void deleteData(IDataSet dataSet) {
        try {
            DatabaseOperation.DELETE.execute(connection, dataSet);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void updateData(IDataSet dataSet) {
        try {
            DatabaseOperation.UPDATE.execute(connection, dataSet);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public QueryDataSet getQueryDataSet() {
        queryDataSet = new QueryDataSet(connection);
        return queryDataSet;
    }

    public IDataSet getDataSet(String path) {
        FlatXmlDataSet dataSet = null;
        try {
            dataSet = new FlatXmlDataSet(new FileInputStream(new File(path)));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dataSet;
    }
}

1. 测试用例：

import java.io.FileWriter;
import java.io.IOException;
import java.sql.SQLException;

import org.dbunit.DatabaseUnitException;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.Column;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class DBUnitTest {

    private String driver = "com.kingbase8.Driver";

    private String url = "jdbc:kingbase8://localhost:54321/TEST";

    private String user = "postgres";

    private String password = "";

    private static final String DATA_BACKUP_FILE = "backup_dbtest.xml";

    private DBUnit dbUnit;

    @Before
    public void setUp() throws SQLException, DatabaseUnitException, IOException {
        dbUnit = new DBUnit(driver, url, user, password);
        dbUnit.setSchema("PUBLIC");
        QueryDataSet dataSet = dbUnit.getQueryDataSet();
        dataSet.addTable("dbtest");
        FlatXmlDataSet.write(dataSet, new FileWriter(DATA_BACKUP_FILE));
        System.out.println("------------------------------");
        Query();
    }

    @After
    public void tearDown() throws SQLException, DatabaseUnitException {
        Query();
        System.out.println("------------------------------");
        IDataSet dataSet = dbUnit.getDataSet(DATA_BACKUP_FILE);
        dbUnit.clean_insertData(dataSet);
        dbUnit.close();
    }

    @Test
    public void TestInsert() throws SQLException, DatabaseUnitException {
        System.out.println("----------Insert----------");
        IDataSet dataSet = dbUnit.getDataSet("dataset.xml");
        dbUnit.insertData(dataSet);
    }

    @Test
    public void TestUpdate() throws SQLException, DatabaseUnitException {
        System.out.println("----------Update----------");
        IDataSet dataSet = dbUnit.getDataSet("dataset2.xml");
        dbUnit.updateData(dataSet);
    }

    @Test
    public void TestDelete() throws SQLException, DatabaseUnitException {
        System.out.println("----------Delete----------");
        IDataSet dataSet = dbUnit.getDataSet("dataset.xml");
        dbUnit.deleteData(dataSet);
    }

    public void Query() throws SQLException, DatabaseUnitException {
        QueryDataSet dataSet = dbUnit.getQueryDataSet();
        dataSet.addTable("dbtest");
        ITable it = dataSet.getTable("dbtest");
        Column[] columns = dataSet.getTable("dbtest").getTableMetaData().getColumns();
        for (int i = 0; i < it.getRowCount(); i++) {
            for (int j = 0; j < columns.length; j++) {
                System.out.print(it.getValue(i, columns[j].getColumnName()) + " ");
            }
            System.out.println();
        }
    }
}