What is the good habit of Java agile integration testing Dao – > database?

What is the current good practice of Dao agile integration testing for real database schema in Java environment?

By 'agile', I mean what is the most simplified, automated and simple solution to this problem

I hope to realize the automation of testing and prove that the data access layer is seamlessly integrated with the actual running instance of the database It is important that we test custom SQL against a specific vendor database 1. E. if we write T-SQL, we want to test SQL server

A single database is dedicated to all running tests but not submitted? Or should each test environment have a dedicated database?

How do people deal with setup and disassembly? Is Dbunit still popular? How can spring help?

Solution

Therefore, you should create a set of tests for each vendor database

@RunWith(Suite.class)
@Suite.SuiteClasses({})
public class sqlServerTestSuite {

    @BeforeClass
    public static void setUpClass() throws Exception {

    }

    @AfterClass
    public static void tearDownClass() throws Exception {
        // clean database
    }

}

It is recommended that you use the rollback command to execute the SQL command, because if you use commit, you can change the state of the current test and the state of other tests Otherwise, you can handle unexpected behavior

For each developer, it is recommended to use database sandbox This allows each user to modify the database and run the application using tests in any way they think fit without worrying about any interaction between their tests and those of other users

public class PersonTest {

    @Before
    public void setUp() {
        // set up state to run the test
    }

    @After
    public void teardown() {
       // Transaction rollback
    }

}

The advantage of transaction rollback pattern is that it makes the database in exactly the same state as when we started testing, no matter what changes we made to the database content

Dbunit is used to compare the database table with the contained XML file You should remember that Dbunit handles manual encoding settings However, although this method is more thorough, developing and maintaining these types of tests is very cumbersome In addition, the test does not detect missing mappings for newly added fields or attributes

Spring has built-in support for transaction rollback mode and supports named parameters (sqlparametersource), which allows you to externalize common JDBC queries in multiple rows and readable XML files of each vendor database, such as

<?xml version="1.0" encoding="UTF-8"?>
<queries>
    <query name="PERSON_BY_ID">
        <![CDATA[
            select 
                *
            from 
                PERSON
            where
                PERSON.ID = :integerId
        ]]>
    </query>
</queries>

Pay attention to the query parameters It follows the JavaScript code style, and you declare parameter types as prefixes Now you can create a class that defines a named query set

public class sqlServerQuery {

    public static final String PERSON_BY_ID = "PERSON_BY_ID";

}

For dynamic queries, it is often important to detect errors in the query logic, such as using < instead of < = To catch these types of errors, we need to write tests, populate the database with test data, execute queries, and verify that it returns the expected objects Unfortunately, these types of tests are time - consuming to write and execute