embedded-postgres写单元测试

我们会用DAO类去操作数据库，比如说下面的PersonDAO类，包含对person表的增删查。你们是怎么对这些DAO类写单元测试的呢？该不会直连测试库跑test case吧。

public class PersonDAO {
    private final DAO dao;

    public PersonDAO(DAO dao) {
        this.dao = dao;
    }

    public Person getById(int personId) throws SQLException {
        String sql = "select person_id,last_name,first_name,address,city from person where person_id=?";
        return dao.queryForObject(sql, ps -> ps.setInt(1, personId),
                rs -> Person.builder()
                        .personId(rs.getInt("person_id"))
                        .firstName(rs.getString("first_name"))
                        .lastName(rs.getString("last_name"))
                        .address(rs.getString("address"))
                        .city(rs.getString("city")).build());
    }

    public int add(Person person) throws SQLException {
        String sql = "insert into person (person_id,last_name,first_name,address,city) values (?,?,?,?,?)";
        return dao.modify(sql, ps -> {
            ps.setInt(1, person.personId());
            ps.setString(2, person.lastName());
            ps.setString(3, person.firstName());
            ps.setString(4, person.address());
            ps.setString(5, person.city());
        });
    }

    public int delete(int personId) throws SQLException {
        String sql = "delete from person where person_id=?";
        return dao.modify(sql, ps -> ps.setInt(1, personId));
    }
}

我的做法是在test开始的时候启动一个In-Memory DB （内存数据库），准备一些测试数据，然后运行test case。In-Memory DB有很多种啦，常见的有HSLQDB / SQLite / H2 等等，我习惯使用embedded-postgres，因为我生产上使用的是postgres，而embedded-postgres的语法跟postgres是一模一样的，这能最大限度地保证我的代码上线是work的。

embedded-postgres的地址在这里：

GitHub - zonkyio/embedded-postgres: Java embedded PostgreSQL component for testing

废话少说，上代码：

首先你要在pom.xml配置如下dependency

        <dependency>
            <groupId>io.zonky.test</groupId>
            <artifactId>embedded-postgres</artifactId>
            <version>2.0.1</version>
            <scope>test</scope>
        </dependency>

然后编写PersonDAOTest 

package org.sample;

import io.zonky.test.db.postgres.embedded.FlywayPreparer;
import io.zonky.test.db.postgres.junit5.EmbeddedPostgresExtension;
import io.zonky.test.db.postgres.junit5.PreparedDbExtension;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.RegisterExtension;

import java.sql.SQLException;

import static org.junit.jupiter.api.Assertions.*;

class PersonDAOTest {
    private DAO daoEmbeddedPg;
    private PersonDAO underTest;

    @RegisterExtension
    public PreparedDbExtension db = EmbeddedPostgresExtension.preparedDatabase(FlywayPreparer.forClasspathLocation("db/migration"));

    @BeforeEach
    void setUp() {
        daoEmbeddedPg = new DAO(db.getTestDatabase());
        underTest = new PersonDAO(daoEmbeddedPg);
    }

    @Test
    void shouldGetPersonById() throws SQLException {
        // add test person id=10
        DBUtils.executeSQL(db.getTestDatabase().getConnection(), "insert into person(person_id,first_name,last_name) values (10,'John','Doe')");
        Person actual = underTest.getById(10);
        assertEquals(10, actual.personId());
        assertEquals("John", actual.firstName());
        assertEquals("Doe", actual.lastName());
    }

    @Test
    void shouldAddPerson() throws SQLException {
        Person testPerson = Person.builder().personId(10).firstName("John").lastName("Doe").build();
        int howManyAdded = underTest.add(testPerson);
        Person actualAdded = underTest.getById(10);
        assertEquals(1, howManyAdded);
        assertEquals("John", actualAdded.firstName());
        assertEquals("Doe", actualAdded.lastName());
    }

    @Test
    void shouldDeletePerson() throws SQLException {
        Person testPerson = Person.builder().personId(10).firstName("John").lastName("Doe").build();
        underTest.add(testPerson);
        int howManyDeleted = underTest.delete(10);
        assertEquals(1, howManyDeleted);
    }
}

这是我demo的完整代码 GitHub - bin9wei/play-embedded-posgtres: Demo to use https://github.com/zonkyio/embedded-postgres in unit test