java - 如何增加与数据库交互的类的测试覆盖率？

Question

我有一个名为 PatientRepositoryImpl 的 Java 类，它包含一些方法，可以在 MySql 数据库中插入、删除或更新数据。我还为这门课写了一些单元测试。当我检查我的测试的覆盖率时，我只得到 59%，尽管几乎每一行代码都被覆盖率工具标记为绿色，除了 SQL 异常。我是新来的，希望我做的一切都是正确的，如果有人能帮助我，我将不胜感激。

这里是我的存储库和测试的代码。

public class PatientRepositoryMySqlImpl implements PatientRepository {


private DatabaseConnection connection;
private PatientGenerator patientGenerator;


public PatientRepositoryMySqlImpl(DatabaseConnection connection, PatientGenerator patientGenerator) {
    this.connection = connection;
    this.patientGenerator = patientGenerator;
}

/* (non-Javadoc)
 * @see com.id.hl7sim.PatientRepository#insertPatient()
 */
@Override
public void insertPatient(Patient patient) {
    if (!patient.isValid()) { 
        throw new IllegalArgumentException("Incomplete Patient");
    } else {
        String insert = "INSERT INTO tbl_patient(lastname, firstname, gender, birthday) VALUES('"
        + patient.getLastname() + "', '" + patient.getFirstname() + "', '" + patient.getGender() + "', '"
        + patient.getBirthday().toString() + "');";
        try (Connection dbConnection = connection.getDBConnection();
             Statement statement = dbConnection.prepareStatement(insert)) {
            statement.executeUpdate(insert);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

/* (non-Javadoc)
 * @see com.id.hl7sim.PatientRepository#insertListOfPatients()
 */
@Override
public void insertListOfPatients(List<Patient> allPatients) {
    for (Patient patient : allPatients) {
        insertPatient(patient);
    }
}

/* (non-Javadoc)
 * @see com.id.hl7sim.PatientRepository#getRandomPatient()
 */
@Override
public Patient getRandomPatient() {
    Patient patient = new Patient.Builder().build();
    String query = "SELECT * FROM tbl_patient ORDER BY RAND() LIMIT 1";
    try (Connection dbConnection = connection.getDBConnection();
         Statement statement = dbConnection.createStatement();) {
        ResultSet rs = statement.executeQuery(query);
        rs.next();
        setPatientBasicData(patient, rs);
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
    return patient;
}

private void setPatientBasicData(Patient patient, ResultSet rs) {
    try {
        patient.setId(rs.getInt("id"));
        patient.setLastname(rs.getString("lastname"));
        patient.setFirstname(rs.getString("firstname"));
        patient.setGender(rs.getString("gender"));
        patient.setBirthday(parseBirthday(rs.getString("birthday")));
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
}

public LocalDate parseBirthday(String birthday) {
    LocalDate localDate = LocalDate.parse(birthday);
    return localDate;
}

/* (non-Javadoc)
 * @see com.id.hl7sim.PatientRepository#admitRandomPatient()
 */
@Override
public Patient admitRandomPatient() {
    Patient patient = getRandomPatient();
    patient.setDepartment(patientGenerator.getRandomDepartment());
    patient.setWard(patientGenerator.getRandomWard());
    patient.setAdmissionDateTime(LocalDateTime.now());
    patient.setStatus("I");
    String insert = "INSERT INTO tbl_inpatients(id, ward, department, admissionDate, patientStatus) VALUES('"
                    + patient.getId() + "', '" + patient.getWard() + "', '" + patient.getDepartment() + "', '"
                    + patient.getAdmissionDateTime().toString() + "', '" + patient.getStatus() + "')";
    try (Connection dbConnection = connection.getDBConnection();
         PreparedStatement statement = dbConnection.prepareStatement(insert)) {
        statement.executeUpdate(insert, Statement.RETURN_GENERATED_KEYS);
        ResultSet keys = statement.getGeneratedKeys();
        keys.next();
        patient.setCaseId(keys.getInt(1));
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
    return patient;
}

public Patient getRandomInpatient() {
    Patient patient = new Patient.Builder().build();
    String query = "SELECT * FROM tbl_inpatients ip, tbl_patient p WHERE p.id = ip.id  ORDER BY RAND() LIMIT 1";
    try (Connection dbConnection = connection.getDBConnection();
         Statement statement = dbConnection.createStatement();) {
        ResultSet rs = statement.executeQuery(query);
        rs.next();
        setPatientBasicData(patient, rs);
        setPatientCaseData(patient, rs);
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
    return patient;
}

public void setPatientCaseData(Patient patient, ResultSet rs) {
    try {
        patient.setWard(rs.getString("ward"));
        patient.setDepartment(rs.getString("department"));
        patient.setAdmissionDateTime(parseLocalDateTime(rs.getString("admissionDate")));
        patient.setStatus(rs.getString("patientStatus"));
        patient.setCaseId(rs.getInt("case"));
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
}

public LocalDateTime parseLocalDateTime(String localdatetime) {
    localdatetime = localdatetime.replace("T", "");
    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-ddHH:mm:ss.SSS");
    LocalDateTime formattedLocalDateTime = LocalDateTime.parse(localdatetime, formatter);
    return formattedLocalDateTime;
}

/* (non-Javadoc)
 * @see com.id.hl7sim.PatientRepository#transferRandomPatient()
 */
@Override
public Patient transferRandomPatient() {
    Patient patient = getRandomInpatient();
    patient.setPriorWard(patient.getWard());
    patient.setPriorDepartment(patient.getPriorDepartment());
    patient.setDepartment(patientGenerator.getRandomDepartment());
    patient.setWard(patientGenerator.getRandomWard());
    String update = "UPDATE tbl_inpatients SET ward='" + patient.getWard() + "', department='"
            + patient.getDepartment() + "' WHERE id='" + patient.getId() + "'";
    try (Connection dbConnection = connection.getDBConnection();
         Statement statement = dbConnection.prepareStatement(update)) {
        statement.executeUpdate(update);
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
    return patient;
}

/* (non-Javadoc)
 * @see com.id.hl7sim.PatientRepository#dischargeRandomPatient()
 */
@Override
public Patient dischargeRandomPatient() {
    Patient patient = getRandomInpatient();
    patient.setDischargeDateTime(LocalDateTime.now());
    insertFormerPatient(patient);
    String delete = "DELETE FROM tbl_inpatients WHERE `case`=" + patient.getCaseId();
    try (Connection dbConnection = connection.getDBConnection();
         Statement statement = dbConnection.prepareStatement(delete)) {
        statement.executeUpdate(delete);
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
    return patient;
}

public void insertFormerPatient(Patient patient) {
    String insert = "INSERT INTO tbl_formerpatients(`case`, `id`, ward, department, admissionDate, dischargeDate) VALUES('"
            + patient.getCaseId() + "', '" + patient.getId() + "', '" + patient.getWard() + "', '"
            + patient.getDepartment() + "', '" + patient.getAdmissionDateTime().toString() + "', '"
            + patient.getDischargeDateTime().toString() + "')";
    try (Connection dbConnection = connection.getDBConnection();
         Statement statement = dbConnection.prepareStatement(insert)) {
        statement.executeUpdate(insert);
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
}

public int countInpatients() {
    int numberOfPatients = 0;
    String query = "SELECT COUNT(id) AS numberOfPatients FROM tbl_inpatients";
    try (Connection dbConnection = connection.getDBConnection();
            Statement statement = dbConnection.createStatement();) {
        ResultSet rs = statement.executeQuery(query);
        while (rs.next()) {
            numberOfPatients = rs.getInt(1);
        }
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
    return numberOfPatients;
}

public int countPatients() {
    int numberOfPatients = 0;
    String query = "SELECT COUNT(id) AS numberOfPatients FROM tbl_patient";
    try (Connection dbConnection = connection.getDBConnection();
            Statement statement = dbConnection.createStatement();) {
        ResultSet rs = statement.executeQuery(query);
        while (rs.next()) {
            numberOfPatients = rs.getInt(1);
        }
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
    return numberOfPatients;
}

测试

public class PatientRepositoryMySqlImplTest {

PatientRepository testPatientRepository;

Patient testPatient;

Patient testPatientTwo;

List<Patient> testBothPatients;

DatabaseConnection testConnection;

PatientGenerator testPatientGenerator;

Firstnames testFirstnames;

Lastnames testLastnames;

Departments testDepartments;

Wards testWards;

@Before
public void setUp() throws Exception {

    testDepartments = JAXB.unmarshal(ClassLoader.getSystemResource("departments.xml"), Departments.class);
    testWards = JAXB.unmarshal(ClassLoader.getSystemResource("wards.xml"), Wards.class);
    testLastnames = JAXB.unmarshal(ClassLoader.getSystemResource("lastnames.xml"), Lastnames.class);
    testFirstnames = JAXB.unmarshal(ClassLoader.getSystemResource("firstnames.xml"), Firstnames.class);

    testPatientGenerator = new PatientGeneratorImpl(testFirstnames, testLastnames, testDepartments, testWards);

    testPatient = testPatientGenerator.randomizeNewPatient();

    testPatientTwo = testPatientGenerator.randomizeNewPatient();

    testBothPatients = new ArrayList<Patient>();

    testConnection = new MySqlConnection();

    testPatientRepository = new PatientRepositoryMySqlImpl(testConnection, testPatientGenerator);

    testPatientRepository.admitRandomPatient();

}

@Test
public void testAdmitRandomPatient() {

    testPatient = testPatientRepository.admitRandomPatient();

    assertTrue(testPatient.isValid());
}

@Test
public void testGetRandomInpatient() {

    testPatient = testPatientRepository.getRandomInpatient();

    assertTrue(testPatient.isValid());
}

@Test
public void testDischargeRandomPatientValid() {

    testPatient = testPatientRepository.dischargeRandomPatient();

    assertTrue(testPatient.isValid());
    assertTrue(testPatient.getCaseId() != 0);
}

@Test
public void testDischargeRandomPatientDatabase() {

    int beforeDischarge = testPatientRepository.countInpatients();
    testPatient = testPatientRepository.dischargeRandomPatient();
    int afterDischarge = testPatientRepository.countInpatients();

    assertTrue(afterDischarge == beforeDischarge - 1);

}

@Test(expected = IllegalArgumentException.class)
public void testInsertPatientWitIncompletePatient() {

    testPatient.setFirstname("");

    testPatientRepository.insertPatient(testPatient);
}

@Test
public void testTransferRandomPatient() {

    testPatient = testPatientRepository.transferRandomPatient();

    assertTrue(testPatient.getDepartment() != testPatient.getPriorDepartment());
}

@Test
public void testInsertPatient() {

    int numberOfPatients = testPatientRepository.countInpatients();

    testPatientRepository.insertPatient(testPatient);

    assertTrue(testPatientRepository.countInpatients() >= numberOfPatients);
}

@Test
public void testInsertListOfPatients() {

    testBothPatients.add(testPatient);
    testBothPatients.add(testPatientTwo);

    int countInpatientsBeforeInsertion = testPatientRepository.countPatients();

    testPatientRepository.insertListOfPatients(testBothPatients);

    int countInpatientsAfterInsertion = testPatientRepository.countPatients();
    assertTrue(countInpatientsAfterInsertion > countInpatientsBeforeInsertion);

}

编辑:

@Test
public void mockThrowsException() {

    PatientRepository testPatientRepository = mock(PatientRepositoryMySqlImpl.class);

    when(testPatientRepository.getRandomPatient()).thenThrow(SQLException.class);

    testPatientRepository.admitRandomPatient();

}

Answer 1

虽然我完全同意，为了增加覆盖率，您绝对应该“模拟”出现问题并抛出 SQLException 的场景，但让我介绍另一种也能回答这个问题的方法但希望能给你另一个视角。

JDBC 非常麻烦，而且会抛出 SQL 异常的测试可能不是最适合编写的测试。此外，我看到您并没有真正处理异常，只是将它们记录在控制台中。

因此，与其尝试解决这个问题，也许您应该考虑不直接使用 JDBC，而是使用一些库来为您解决 JDBC 使用的麻烦。例如这样的图书馆，看看 Spring JDBC Template ，我知道这是一个相当古老的东西，但是，嘿，直接使用 JDBC 可能也不是最现代的方法，所以我试图做更少的改变，但仍然获得了值(value)。此外，人们可能会说它陈旧而不花哨，我会说，这是一个经过实战检验的库，即使没有 Spring 本身也可以。

现在，由于它包装了 JDBC 异常处理等，关键是您根本不必涵盖这些情况。所以你的覆盖率自然会增加。

当然还有其他低级别和不那么低级别的替代方案(例如 JDBI 或 JOOQ 等等)，但这是一个不同的故事，所有这些都会增加某种意义上的覆盖率你问过的问题。