JUnit测试SQL查询 [英] JUnit testing SQL queries

问题描述

我正在一个项目上，我们需要创建一些测试用例.我有一个SQL数据库，并使用以下查询来解析数据:

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:

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;
}    

使用JUnits进行测试会如何?

How would the test with JUnits look like?

推荐答案

Junit必须使用的方法是通过测试您的类的每个公共方法，参数的数量与数量一样多实验的成功或失败.

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.

每种测试方法都必须:

• 选择一组适当的参数值.
• (可选)以必需的初始状态初始化测试的组件.
• 调用测试的方法.
• 检查返回的结果是否等于预期的结果.
• 清理已测试的组件，不要让已执行的测试中出现任何污垢".

对于您来说，检查结果很困难，因为连接是由您的方法在本地管理的.更糟糕的是:数据会自动提交到连接中，每次测试都会在数据库中留下脏记录.

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:

• 在类中重载方法create，该方法具有一个程序包访问版本，该版本也接收一个Connection，并将所有业务逻辑放在此处.
• 公共重载应仅管理连接并调用新创建的重载.

• 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.

然后，您可以安全地进行测试:

Then, you can make your test safely:

• 如果您的类名为MyClass，请在同一包中创建一个MyClassTest(通常在不同的源路径中，例如Maven项目中的src\test\java).
• 创建方法createSinglePerson()，该方法使用任意一组参数和一个连接(不自动提交)调用create.此后，您必须检查 Person 表中是否有比原始记录多的记录，并且具有一定的值集，而 Contractor 表中是否有多条记录带有一个特定的值.某些值集.要比较每个值，必须使用Asserts.assertEquals(expected, real).最后(在finally子句中)对连接进行回滚并关闭它.

• If your class is named MyClass, create a MyClassTest in the same package (typically in a different source path, like src\test\java in a Maven project).
• Create a method createSinglePerson() which invokes create with 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 Person table, with a certain set of values, and one more record in the Contractor table with a certain set of values. To compare each value you must use Asserts.assertEquals(expected, real). At the end (in a finally clause), do a rollback to the connection and close it.

您可以根据需要多次运行测试，因为它不会改变数据库状态.

You can run your test as many times as needed, knowing that it won't alter the database state.

(注意:从未使用过参数cvr.也许您是故意这样做的，我不知道).

(Note: Parameter cvr is never used. Maybe you did it on purpose, I don't know).

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="c@d.e";
                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());
        }
    }
}

这篇关于JUnit测试SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！