Ruling Database Testing with DBUnit Rules

		<dependency>
			<groupId>com.github.dbunit-rules</groupId>
			<artifactId>core</artifactId>
			<version>0.14.0</version>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.hsqldb</groupId>
			<artifactId>hsqldb</artifactId>
			<version>2.3.4</version>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-core</artifactId>
			<version>4.2.20.Final</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-entitymanager</artifactId>
			<version>4.2.20.Final</version>
			<scope>test</scope>
		</dependency>

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="rulesDB" transaction-type="RESOURCE_LOCAL">

            <provider>org.hibernate.ejb.HibernatePersistence</provider>
            <class>com.github.dbunit.rules.sample.User</class>

            <properties>
                <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect" />
                <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver" />
                <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:mem:test;DB_CLOSE_DELAY=-1" />
                <property name="javax.persistence.jdbc.user" value="sa" />
                <property name="javax.persistence.jdbc.password" value="" />
                <property name="hibernate.hbm2ddl.auto" value="create-drop" /> (1)
                <property name="hibernate.show_sql" value="true" />
            </properties>

    </persistence-unit>

</persistence>

  
1
2
3
4
5
6
7
8

  
@Entity
public class User {

    @Id
    @GeneratedValue
    private long id;

    private String name;

user: (1)
  - id: 1 (2)
    name: "@realpestano"
  - id: 2
    name: "@dbunit"

  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

  
@RunWith(JUnit4.class)
public class DBUnitRulesCoreTest {

    @Rule
    public EntityManagerProvider emProvider = EntityManagerProvider.instance("rulesDB");  (1)

    @Rule
    public DBUnitRule dbUnitRule = DBUnitRule.instance(emProvider.connection()); (2)


    @Test
    @DataSet("users.yml") (3)
    public void shouldListUsers() {
        List<User> users = em(). (4)
                createQuery("select u from User u").
                getResultList();
        assertThat(users).
                isNotNull().
                isNotEmpty().
                hasSize(2);
    }
}

  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

  
    @Test
    @DataSet(value="users.yml", disableConstraints=true)
    public void shouldUpdateUser() {
        User user = (User) em().
                createQuery("select u from User u  where u.id = 1").
                getSingleResult();
        assertThat(user).isNotNull();
        assertThat(user.getName()).isEqualTo("@realpestano");
        tx().begin(); (1)
        user.setName("@rmpestano");
        em().merge(user);
        tx().commit();
        assertThat(user.getName()).isEqualTo("@rmpestano");
    }

    @Test
    @DataSet("users.yml")
    public void shouldDeleteUser() {
        User user = (User) em().
                createQuery("select u from User u  where u.id = 1").
                getSingleResult();
        assertThat(user).isNotNull();
        assertThat(user.getName()).isEqualTo("@realpestano");
        tx().begin();
        em().remove(user);
        tx().commit();
        List<User> users = em().
                createQuery("select u from User u ").
                getResultList();
        assertThat(users).
                hasSize(1);
    }

user: (1)
  - id: 1 (2)
    name: "@realpestano"
  - id: 2
    name: "@dbunit"

user:
  - id: 2
    name: "@dbunit"

  
1
2
3
4
5
6
7
8
9
10
11
12

  
    @Test
    @DataSet("users.yml")
    @ExpectedDataSet(value = "expectedUser.yml",ignoreCols = "id") (1)
    public void shouldAssertDatabaseUsingExpectedDataSet() {
        User user = (User) em().
                createQuery("select u from User u  where u.id = 1").
                getSingleResult();
        assertThat(user).isNotNull();
        tx().begin();
        em().remove(user);
        tx().commit();
    }

  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

  
@Entity
public class Tweet {

    @Id
    @GeneratedValue
    private Long id;

    @Size(min = 1, max = 140)
    private String content;

    private Integer likes;

    @Temporal(TemporalType.DATE)
    private Date date;

    @ManyToOne(fetch = FetchType.LAZY)
    User user;

  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

  
package com.github.dbunit.rules.sample;

import com.github.dbunit.rules.DBUnitRule;
import com.github.dbunit.rules.api.dataset.DataSet;
import com.github.dbunit.rules.api.dataset.DataSetExecutor;
import com.github.dbunit.rules.configuration.DataSetConfig;
import com.github.dbunit.rules.connection.ConnectionHolderImpl;
import com.github.dbunit.rules.dataset.DataSetExecutorImpl;
import com.github.dbunit.rules.util.EntityManagerProvider;
import org.junit.Rule;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.JUnit4;

import static org.assertj.core.api.Assertions.assertThat;

/**
 * Created by pestano on 23/07/15.
 */

@RunWith(JUnit4.class)
public class MultipleDataBasesTest {

    @Rule
    public EntityManagerProvider emProvider = EntityManagerProvider.instance("pu1");

    @Rule
    public EntityManagerProvider emProvider2 = EntityManagerProvider.instance("pu2");

    @Rule
    public DBUnitRule rule1 = DBUnitRule.instance("rule1",emProvider.connection()); (1)

    @Rule
    public DBUnitRule rule2 = DBUnitRule.instance("rule2",emProvider2.connection());


    @Test
    @DataSet(value = "users.yml", executorId = "rule1") (2)
    public void shouldSeedDatabaseUsingPu1() {
        User user = (User) emProvider.em().
                createQuery("select u from User u where u.id = 1").getSingleResult();
        assertThat(user).isNotNull();
        assertThat(user.getId()).isEqualTo(1);
    }

    @Test
    @DataSet(value = "users.yml", executorId = "rule2")
    public void shouldSeedDatabaseUsingPu2() {
        User user = (User) emProvider2.em().
                createQuery("select u from User u where u.id = 1").getSingleResult();
        assertThat(user).isNotNull();
        assertThat(user.getId()).isEqualTo(1);
    }

    @Test (3)
    public void shouldSeedDatabaseUsingMultiplePus() {
        DataSetExecutor exec1 = DataSetExecutorImpl.
                instance("exec1", new ConnectionHolderImpl(emProvider.connection()));
        DataSetExecutor exec2 = DataSetExecutorImpl.
                instance("exec2", new ConnectionHolderImpl(emProvider2.connection()));

        //programmatic seed db1
        exec1.createDataSet(new DataSetConfig("users.yml"));

        exec2.createDataSet(new DataSetConfig("dataset-with-javascript.yml"));//seed db2

        //user comes from database represented by pu1
        User user = (User) emProvider.em().
                createQuery("select u from User u where u.id = 1").getSingleResult();
        assertThat(user).isNotNull();
        assertThat(user.getId()).isEqualTo(1);

        //tweets comes from pu2
        Tweet tweet = (Tweet) emProvider.em().createQuery("select t from Tweet t where t.id = 1").getSingleResult();
        assertThat(tweet).isNotNull();
        assertThat(tweet.getLikes()).isEqualTo(50);
    }

}

  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

  
    @Test
    @DataSet(cleanBefore=true)
    public void shouldExportYMLDataSetUsingDataSetExporter() throws SQLException, DatabaseUnitException{
            tx().begin();
            User u1 = new User();
            u1.setName("u1");
            em().persist(u1); /just insert a user and assert it is present in exported dataset
        tx().commit();
            tx().commit();
            DataSetExporterImpl.getInstance().
            export(new DatabaseConnection(emProvider.connection()), (1)
                new DataSetExportConfig().outputFileName("target/user.yml")); (2)
            File ymlDataSet = new File("target/user.yml");
          assertThat(ymlDataSet).exists();
          assertThat(contentOf(ymlDataSet)).
                  contains("USER:"+NEW_LINE +
                          "  - ID: 1"+NEW_LINE +
                          "    NAME: \"u1\""+NEW_LINE
                          );

    }

  
1
2
3
4
5
6

  
    @Test
    @DataSet("datasets/yml/users.yml") (1)
    @ExportDataSet(format = DataSetFormat.XML,outputName="target/exported/xml/allTables.xml")
    public void shouldExportAllTablesInXMLFormat() {

    }

  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

  
@RunWith(JUnit4.class)
@DBUnit(leakHunter = true)
public class LeakHunterIt {

    @Rule
    public DBUnitRule dbUnitRule = DBUnitRule.instance(new ConnectionHolderImpl(getConnection()));

    @Rule
    public ExpectedException exception = ExpectedException.none();

    @BeforeClass
    public static void initDB() {
        //trigger db initialization
        Persistence.createEntityManagerFactory("rules-it");
    }

    @Test
    @DataSet("yml/user.yml")
    public void shouldFindConnectionLeak() {
         exception.expect(LeakHunterException.class); (1)
         exception.expectMessage("Execution of method shouldFindConnectionLeak left 1 open connection(s).");
         createLeak();
     }

     @Test
     @DataSet("yml/user.yml")
     public void shouldFindTwoConnectionLeaks()  {
         exception.expect(LeakHunterException.class);
         exception.expectMessage("Execution of method shouldFindTwoConnectionLeaks left 2 open connection(s).");
         createLeak();
         createLeak();
     }

     @Test
     @DataSet("yml/user.yml")
     @DBUnit(leakHunter = false)
     public void shouldNotFindConnectionLeakWhenHunterIsDisabled() {
           createLeak();
     }

    private Connection getConnection() {
          try {
               return DriverManager.getConnection("jdbc:hsqldb:mem:test;DB_CLOSE_DELAY=-1", "sa", "");
           } catch (SQLException e) {
               e.printStackTrace();
               return null;
           }
        }

     private void createLeak() throws SQLException {
         Connection connection = getConnection();
         try (Statement stmt = connection.createStatement()) {
              ResultSet resultSet = stmt.executeQuery("select count(*) from user");
              assertThat(resultSet.next()).isTrue();
              assertThat(resultSet.getInt(1)).isEqualTo(2);
           }
     }

}