Try DbUnit

Introduction

It is a continuation of Trying from project creation by Maven to JUnit test in Eclipse. Let's move DbUnit and so on.

DbUnit Official: http://dbunit.sourceforge.net/

Modify Maven config file

--Added dependent library to pom.xml

pom.xml

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.46</version>
</dependency>
<dependency>
  <groupId>org.dbunit</groupId>
  <artifactId>dbunit</artifactId>
  <version>2.5.0</version>
</dependency>

--Reference - https://search.maven.org/#artifactdetails|org.dbunit|dbunit|2.5.4|jar - https://search.maven.org/#artifactdetails|mysql|mysql-connector-java|5.1.46|jar

Creating a database

create.ddl

drop database if exists maven_sample;
create database if not exists maven_sample;
connect maven_sample;
drop table if exists fruits;
create table fruits(
id integer primary key,
name varchar(20),
price integer
);
insert into fruits(id, name, price) values (1, 'apple', 120);
insert into fruits(id, name, price) values (2, 'banana', 100);
insert into fruits(id, name, price) values (3, 'orange', 150);

Creating sample code

FruitsMain.java

package com.example;

import java.util.List;

import com.example.dao.FruitsDao;
import com.example.dto.FruitsDto;

public class FruitsMain {
	public static void main(String[] args) {
		for (FruitsDto fruitsDto: getFruits()) {
			System.out.println(fruitsDto.getId());
			System.out.println(fruitsDto.getName());
			System.out.println(fruitsDto.getPrice());
		}
	}

	public static List<FruitsDto> getFruits () {
		FruitsDao fruitsDao = new FruitsDao();
		List<FruitsDto> fruits = fruitsDao.findAll();
		return fruits;
	}
}

FruitsDao.java

package com.example.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.example.dto.FruitsDto;

public class FruitsDao {

	private static final String DB_URL = "jdbc:mysql://localhost/maven_sample?useSSL=false";
	private static final String DB_USER = "root";
	private static final String DB_PASSWORD = "admin";

	public List<FruitsDto> findAll() {
		try(
			Connection con = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
			Statement st = con.createStatement()
		) {
			ResultSet rs = st.executeQuery("select * from fruits");
			List<FruitsDto> fruitsList = new ArrayList<>();
			while(rs.next()){
				FruitsDto fruitsDto = new FruitsDto();
				fruitsDto.setId(rs.getInt("id"));
				fruitsDto.setName(rs.getString("name"));
				fruitsDto.setPrice(rs.getInt("price"));
				fruitsList.add(fruitsDto);
			}
			return fruitsList;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

}

FruitsDto.java

package com.example.dto;

public class FruitsDto {

	private int id;
	private String name;
	private int price;

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
}

Change Java version (if needed)

In my case, the Java runtime environment (JRE) and Java compiler (JDK) version was 1.5, so some methods could not be used, so I fixed it.

--JDK fix (Java compiler)

Setting
↓
Java
↓
compiler
↓
Configure project-specific settings
↓
Select the target project (maven_sample）
↓
Compiler compliance level 1.Fixed to 8

--JRE modification (Java execution environment)

Select the target project (maven_sample）
↓
Build path
↓
Build path configuration
↓
Library
↓
JRE system library
↓
Execution environment 1.Fixed to 8

--Reference - https://www.intra-mart.jp/document/library/ebuilder/public/e_builder_setup_guide/texts/jre/index.html

--Specify the Java version of Maven compilation --Modified pom.xml

pom.xml

<properties>
  <java.version>1.8</java.version>
  <maven.compiler.target>${java.version}</maven.compiler.target>
  <maven.compiler.source>${java.version}</maven.compiler.source>
</properties>

Create test code with DbUnit

FruitsDaoTest.java

package com.example.dao;

import static org.hamcrest.CoreMatchers.*;
import static org.junit.Assert.*;

import java.io.FileInputStream;
import java.util.List;

import org.dbunit.DBTestCase;
import org.dbunit.PropertiesBasedJdbcDatabaseTester;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.junit.Test;

import com.example.dto.FruitsDto;

public class FruitsDaoTest extends DBTestCase {

	public FruitsDaoTest(String name) {
		super(name);
		System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS, "com.mysql.jdbc.Driver");
		System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL, "jdbc:mysql://localhost/maven_sample?useSSL=false");
		System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME, "root");
		System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD, "admin");
	}

	@Override
	protected IDataSet getDataSet() throws Exception {
		return new FlatXmlDataSetBuilder().build(new FileInputStream("dataset.xml"));
	}

	@Test
	public void test_findAll() throws Exception{
		int expectedRowCount = 3;
		FruitsDao sut = new FruitsDao();
		List<FruitsDto> actual = sut.findAll();
		assertThat(actual.size(), is(expectedRowCount));
	}

}

--Create dataset.xml directly under the project root

dataset.xml

<dataset>
	<fruits id="1" name="apple" price="100"/>
	<fruits id="2" name="banana" price="200"/>
	<fruits id="3" name="cherry" price="300"/>
</dataset>

in conclusion

I managed to use DbUnit. was difficult. .. .. I'm glad if you can use it as a reference. Next, I'll try Mockito. → Try Mockito