dbunit 使用详解

pom.xml

<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils -->
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.7</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
</dependency>

</dependencies>

2.dbunit 工具类

(1)druid.properties   //DruidDataSourceFactory.createDataSource(properties);

driverClassName=com.mysql.jdbc.Driver

url=jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&useSSL=false
username=root
password=root
filters=stat
initialSize=2
maxActive=300
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
poolPreparedStatements=false
maxPoolPreparedStatementPerConnectionSize=200

(2)DruidConnection 

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DruidConnection {
    private static Properties properties = null;
    private static DataSource dataSource = null;
    private volatile static DruidConnection instatce = null;
    private Connection connection = null;


    //私有构造函数,防止实例化对象
    private DruidConnection() {
    }
    static {
    try {
            properties = new Properties();
            // 1.加载properties文件
            InputStream is = DruidConnection.class.getClassLoader().getResourceAsStream("druid.properties");
            
            // 2.加载输入流
            properties.load(is);
            
            // 3.获取数据源
            dataSource = getDatasource();
         
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 用简单单例模式确保只返回一个链接对象
     * 
     * @return
     */
    public static  DruidConnection getInstace() {
        if(instatce == null) {
            synchronized (DruidConnection.class) {
                if(instatce == null) {
                    instatce = new DruidConnection();
                }
            }
        }
        return instatce;
    }


    // 返回一个数据源
    public DataSource getDataSource() {
        return dataSource;
    }


    // 返回一个链接
    public Connection getConnection() {
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }


    // 加载数据源
    private static DataSource getDatasource() {
        DataSource source = null;
        try {
            source = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return source;
    }

}

(3)DruidUtils

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
public class DruidUtils {
    private static Connection connection = null;
    //获取元数据
    public static DataSource getDatasource() {
        DataSource dataSource = DruidConnection.getInstace().getDataSource();
        return dataSource;
    }
    //获取链接
    public static Connection getConnection() {
         connection = DruidConnection.getInstace().getConnection();
        return connection;
    }
    //归还资源
    public void release() {
        try {
            if(connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

3 junit测试用例

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.FixMethodOrder;
import org.junit.Test;
import org.junit.runners.MethodSorters;
import com.alibaba.fastjson.JSON;


@FixMethodOrder(MethodSorters.NAME_ASCENDING)
public class DbunitTest {


@BeforeClass
public static void setUpBeforeClass() throws Exception {
}


@AfterClass
public static void tearDownAfterClass() throws Exception {
}


@Before
public void setUp() throws Exception {
}


@After
public void tearDown() throws Exception {
}


@Test
public void aInsert() throws SQLException {
QueryRunner runner = new QueryRunner(DruidUtils.getDatasource());
String sql = "insert into user(name) values (?)";
int id = runner.update(sql, "abc");
// int[] ids = runner.batch(sql, new Object[][] {{"zhao"},{"xin"}});
String sql2 = "select @@identity";
// String sql3="SELECT LAST_INSERT_ID()";
String sql3 = "select * from user";
BigInteger num = (BigInteger) runner.query(sql2, new ScalarHandler());
// "使用ScalarHandler处理单行记录，只返回结果集第一行中的指定字段，如未指定字段，则返回第一个字段
Object num2 = runner.query(sql3, new ScalarHandler(2));
System.out.println(JSON.toJSONString(id) + ":插入id" + num + ":" + num2);
}

@Test 
public void bInserBatch() throws SQLException {
QueryRunner runner = new QueryRunner(DruidUtils.getDatasource());
String sql = "insert into user(name) values (?)";
int[] ids = runner.batch(sql, new Object[][] {{"zhao"},{"xin"}});
String sql2 = "select @@identity";
// String sql3="SELECT LAST_INSERT_ID()";
String sql3 = "select * from user";
BigInteger num = (BigInteger) runner.query(sql2, new ScalarHandler());
// "使用ScalarHandler处理单行记录，只返回结果集第一行中的指定字段，如未指定字段，则返回第一个字段
Object num2 = runner.query(sql3, new ScalarHandler(2));
System.out.println(JSON.toJSONString(ids) + ":插入id" + num + ":" + num2);
}

@Test
public void cSelect() throws SQLException {
// 获取链接
QueryRunner runner = new QueryRunner(DruidUtils.getDatasource());


// 编写sql语句
String sql = "select * from user";


// 返回查询值
List<User> user = runner.query(sql, new BeanListHandler<>(User.class));
System.out.println(JSON.toJSONString(user));
}
}