java JUnit 测试 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43747361/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JUnit testing SQL queries
提问by Luká? Václavek
I'm working on a project and we are required to create some test cases. I have a SQL database and parsing data there with queries like:
我正在做一个项目，我们需要创建一些测试用例。我有一个 SQL 数据库并使用以下查询解析数据：
public Contractor create(String name, String address, String email, String phone, String city, int cvr) throws SQLException{
Contractor contractor = new Contractor(name, address, email, phone, city, cvr);
String sql = String.format("INSERT INTO person (name, address, email, phone, city, category) VALUES ('%s', '%s', '%s', '%s', '%s', 2)", name, address, email, phone, city);
try{
Connection conn = DBConnection.getInstance().getDBcon();
conn.createStatement().executeUpdate(sql);
String sql2 = "SELECT TOP 1 id FROM Person ORDER BY id DESC";
ResultSet rs = conn.createStatement().executeQuery(sql2);
if(rs.next()) {
String sql3 = "INSERT INTO contractor (cvr, person_id) VALUES (2666,"+rs.getInt("id")+")";
conn.createStatement().executeUpdate(sql3);
}else{
throw new SQLException();
}
} catch (SQLException e){
e.printStackTrace();
} finally {
DBConnection.closeConnection();
}
return contractor;
}
How would the test with JUnits look like?
使用 JUnits 进行的测试会是什么样子？
回答by Little Santi
Well, the way Junit must be used is by testing each public method of your class, with as many different parametrizations as the number of experiments can success or fail.
嗯，必须使用 Junit 的方式是测试类的每个公共方法，使用与实验成功或失败的数量一样多的不同参数化。
Each testing method, then, must:
那么，每种测试方法必须：
- Chose a proper set of parameter values.
- (Optionally) Initialize the tested component in a required initial state.
- Invoke the tested method.
- Check that the returned result is equal to the expected result.
- Clean up the tested component not to let any "dirt" from the executed test.
- 选择一组合适的参数值。
- （可选）在所需的初始状态下初始化被测试的组件。
- 调用被测试的方法。
- 检查返回的结果是否等于预期的结果。
- 清理被测试的组件，不要让执行的测试中有任何“污垢”。
In your case, there is a difficult checking the results, because the connection is managed locally by your method. Even worse: The data is committed automatically into the connection, leaving dirty records in the database on each test.
在您的情况下，很难检查结果，因为连接是由您的方法在本地管理的。更糟糕的是：数据会自动提交到连接中，每次测试都会在数据库中留下脏记录。
To avoid this difficult, it would be enough with a little previous refactoring to ease the test development:
为了避免这种困难，只需稍加重构即可简化测试开发：
- Overload in your class the method
create, with a package-access version which recieves also a Connection, and put there all the business logic. - The public overload should only manage the connection and call the newly created overload.
- 在你的类中重载方法
create，使用包访问版本，它也接收一个连接，并将所有业务逻辑放在那里。 - 公共重载应该只管理连接并调用新创建的重载。
Then, you can make your test safely:
然后，您可以安全地进行测试：
- If your class is named
MyClass, create aMyClassTestin the same package (typically in a different source path, likesrc\test\javain a Maven project). - Create a method
createSinglePerson()which invokescreatewith an arbitrary set of parameters and one connection (not auto-commit). After that, you must check that there is one record more than initially in the Persontable, with a certain set of values, and one more record in the Contractortable with a certain set of values. To compare each value you must useAsserts.assertEquals(expected, real). At the end (in a finally clause), do a rollback to the connection and close it.
- 如果您的类名为
MyClass，请MyClassTest在同一个包中创建一个（通常在不同的源路径中，例如src\test\java在 Maven 项目中）。 - 创建一个使用任意一组参数和一个连接（非自动提交）
createSinglePerson()调用的方法create。之后，您必须检查Person表中是否比最初多一条记录，具有一组特定的值，以及在Contractor表中是否多一条记录具有一组特定的值。要比较每个值，您必须使用Asserts.assertEquals(expected, real). 最后（在 finally 子句中），回滚连接并关闭它。
You can run your test as many times as needed, knowing that it won't alter the database state.
您可以根据需要多次运行测试，知道它不会改变数据库状态。
(Note: Parameter cvris never used. Maybe you did it on purpose, I don't know).
（注意：参数cvr从来没有使用过。也许你是故意的，我不知道）。
Example
例子
public class MyClassTest
{
@Test
public void createSinglePerson()
{
MyClass myClass=new MyClass();
try(Connection connection=...)
{
try(Statement stCheck=connection.createStatement())
{
connection.setAutoCommit(false);
// Initial cleanup:
stCheck.executeUpdate("DELETE FROM person");
stCheck.executeUpdate("DELETE FROM contractor");
// Setting input parameters:
String name="a";
String address="b";
String email="[email protected]";
String phone="001";
String city="f";
int cvr=11;
// Do the call:
Contractor contractor=myClass.create(name, address, email, phone, city, cvr);
// Javabean Checks: Check the javabean contains the expected values:
assertEquals(name, contractor.getName());
assertEquals(address, contractor.getAddress());
...
// Database Checks:
int personId;
// Check the Person table contains one row with the expected values:
try(ResultSet rs=stCheck.executeQuery("SELECT * FROM person"))
{
assertTrue(rs.next());
personId=rs.getInt("id");
asssertEquals(name, rs.getString("name"));
asssertEquals(address, rs.getString("address"));
...
assertFalse(rs.next());
}
// Check the Contractor table contains one row with the expected values:
try(ResultSet rs=stCheck.executeQuery("SELECT * FROM contractor WHERE person_id="+personId))
{
assertTrue(rs.next());
asssertEquals(2666, rs.getInt("cvr"));
...
assertFalse(rs.next());
}
}
finally
{
// Undo the testing operations:
connection.rollback();
}
}
catch (SQLException e)
{
fail(e.toString());
}
}
}
