我们会用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