database 如何为数据库调用编写单元测试

How to write unit tests for database calls

提问by kdmurray

I'm near the beginning of a new project and (gasp!) for the first time ever I'm trying to include unit tests in a project of mine.

我即将开始一个新项目，并且（喘气！）这是我第一次尝试在我的项目中包含单元测试。

I'm having trouble devising some of the unit tests themselves. I have a few methods which have been easy enough to test (pass in two values and check for an expected output). I've got other parts of the code which are doing more complex things like running queries against the database and I'm not sure how to test them.

我在自己设计一些单元测试时遇到了麻烦。我有一些很容易测试的方法（传入两个值并检查预期的输出）。我有代码的其他部分正在做更复杂的事情，比如对数据库运行查询，但我不知道如何测试它们。

public DataTable ExecuteQuery(SqlConnection ActiveConnection, string Query, SqlParameterCollection Parameters)
{
    DataTable resultSet = new DataTable();
    SqlCommand queryCommand = new SqlCommand();
    try
    {
        queryCommand.Connection = ActiveConnection;
        queryCommand.CommandText = Query;

        if (Parameters != null)
        {
            foreach (SqlParameter param in Parameters)
            {
                 queryCommand.Parameters.Add(param);
            }
        }

        SqlDataAdapter queryDA = new SqlDataAdapter(queryCommand);
        queryDA.Fill(resultSet);
    }
    catch (Exception ex)
    {
        //TODO: Improve error handling
        Console.WriteLine(ex.Message);
    }

    return resultSet;
}

This method essentially takes in all the necessary bits and pieces to extract some data from the database, and returns the data in a DataTable object.

该方法本质上接受所有必要的零碎部分以从数据库中提取一些数据，并在 DataTable 对象中返回数据。

The first question is probably the most complex: What should I even test in a situation like this?

第一个问题可能是最复杂的：在这种情况下我应该测试什么？

Once that's settled comes the question of whether or not to mock out the database components or try to test against the actual DB.

一旦解决了这个问题，就会出现是否模拟数据库组件或尝试针对实际数据库进行测试的问题。

采纳答案by tpdi

What are you testing?

你在测试什么？

There are three possibilities, off the top of my head:

在我的脑海中，有三种可能性：

A. You're testing the DAO (data access object) class, making sure it's correctly marshaling the values/parameters being passed to the database,, and correctly marshaling/transforming/packaging results gotten frm the database.

A. 您正在测试 DAO（数据访问对象）类，确保它正确地编组传递给数据库的值/参数，并正确地编组/转换/打包从数据库中获得的结果。

In this case, you don't need to connect to the database at all; you just need a unit test that replaces the database (or intermediate layer, eg., JDBC, (N)Hibernate, iBatis) with a mock.

在这种情况下，您根本不需要连接到数据库；您只需要一个单元测试，用模拟替换数据库（或中间层，例如 JDBC、(N)Hibernate、iBatis）。

B. You're testing the syntactic correctness of (generated) SQL.

B. 您正在测试（生成的）SQL 的语法正确性。

In this case, because SQL dialects differ, you want to run the (possibly generated) SQL against the correct version of your RDBMS, rather than attempting to mock all quirks of your RDBMS (and so that any RDBMS upgrades that change functionality are caught by your tests).

在这种情况下，由于 SQL 方言不同，您希望针对 RDBMS 的正确版本运行（可能生成的）SQL，而不是尝试模拟 RDBMS 的所有怪癖（并且因此更改功能的任何 RDBMS 升级都被捕获）你的测试）。

C. You're testing the semanticcorrectness of your SQL, i.e, that for a given baseline dataset, your operations (accesses/selects and mutations/inserts and updates) produce the expected new dataset.

C. 您正在测试SQL的语义正确性，即对于给定的基线数据集，您的操作（访问/选择和突变/插入和更新）会产生预期的新数据集。

For that, you want to use something like dbunit (which allows you to set up a baseline and compare a result set to an expected result set), or possibly do your testing wholly in the database, using the technique I outline here: Best way to test SQL queries.

为此，您希望使用 dbunit 之类的东西（它允许您设置基线并将结果集与预期结果集进行比较），或者可能使用我在此处概述的技术完全在数据库中进行测试：最佳方法测试 SQL 查询。

回答by MusiGenesis

This is why (IMHO) unit tests can sometimes create a false sense of security on the part of developers. In my experience with applications that talk to a database, errors are commonly the result of data being in an unexpected state (unusual or missing values etc.). If you routinely mock up data access in your unit tests, you will think your code is working great when it is in fact still vulnerable to this kind of error.

这就是为什么（恕我直言）单元测试有时会给开发人员带来错误的安全感。根据我对与数据库对话的应用程序的经验，错误通常是数据处于意外状态（异常或缺失值等）的结果。如果您经常在单元测试中模拟数据访问，您会认为您的代码运行良好，而实际上它仍然容易受到此类错误的影响。

I think your best approach is to have a test database handy, filled with gobs of crappy data, and run your database component tests against that. All the while remembering that your users will be much much better than you are at screwing up your data.

我认为您最好的方法是手边有一个测试数据库，里面装满了大量糟糕的数据，然后针对它运行数据库组件测试。同时要记住，您的用户在处理数据方面比您要好得多。

回答by J?rg W Mittag

The whole point of a unit test is to test a unit(duh) in isolation. The whole point of a database call is to integratewith another unit (the database). Ergo: it doesn't make sense to unit test database calls.

单元测试的重点是孤立地测试一个单元（废话）。数据库调用的全部意义在于与另一个单元（数据库）集成。因此：对数据库调用进行单元测试是没有意义的。

You should, however, integration test database calls (and you can use the same tools you use for unit testing if you want).

但是，您应该集成测试数据库调用（如果需要，您可以使用用于单元测试的相同工具）。

回答by Meredith L. Patterson

For the love of God, don't test against a live, already-populated database. But you knew that.

看在上帝的份上，请不要针对已填充的实时数据库进行测试。但你知道。

In general you already have an idea of what sort of data each query is going to retrieve, whether you're authenticating users, looking up phonebook/org chart entries, or whatever. You know what fields you're interested in, and you know what constraints exist on them (e.g., UNIQUE, NOT NULL, and so on). You're unit testing your code that interacts with the database, not the database itself, so think in terms of how to test those functions. If it's possible for a field to be NULL, you should have a test that makes sure that your code handles NULLvalues correctly. If one of your fields is a string (CHAR, VARCHAR, TEXT, &c), test to be sure you're handling escaped characters correctly.

一般来说，您已经知道每个查询将检索什么样的数据，无论您是在验证用户、查找电话簿/组织图表条目，还是其他任何内容。您知道您对哪些字段感兴趣，并且您知道它们存在哪些约束（例如，UNIQUE、NOT NULL等）。您正在单元测试与数据库交互的代码，而不是数据库本身，因此请考虑如何测试这些功能。如果某个字段可能是NULL，则您应该进行测试以确保您的代码NULL正确处理值。如果您的字段之一是字符串 ( CHAR, VARCHAR, TEXT, &c)，请测试以确保您正确处理转义字符。

Assume that users will attempt to put anything* into the database, and generate test cases accordingly. You'll want to use mock objects for this.

假设用户将尝试将任何东西*放入数据库，并相应地生成测试用例。为此，您将需要使用模拟对象。

* Including undesirable, malicious or invalid input.

* 包括不受欢迎的、恶意的或无效的输入。

回答by azheglov

Strictly speaking, a test that writes/reads from a database or a file system is not a unit test. (Although it may be an integration test and it may be written using NUnit or JUnit). Unit-tests are supposed to test operations of a single class, isolating its dependencies. So, when you write unit-test for the interface and business-logic layers, you shouldn't need a database at all.

严格来说，从数据库或文件系统写入/读取的测试不是单元测试。（虽然它可能是一个集成测试，并且可能是使用 NUnit 或 JUnit 编写的）。单元测试应该测试单个类的操作，隔离其依赖项。因此，当您为接口和业务逻辑层编写单元测试时，您根本不需要数据库。

OK, but how do you unit-test the database access layer? I like the advice from this book: xUnit Test Patterns(the link points to the book's "Testing w/ DB" chapter. The keys are:

好的，但是如何对数据库访问层进行单元测试？我喜欢这本书的建议：xUnit 测试模式（链接指向本书的“使用 DB 进行测试”一章。关键是：

• use round-trip tests
• don't write too many tests in your data access test fixture, because they will run much slower than your "real" unit tests
• if you can avoid testing with a real database, test without a database

• 使用往返测试
• 不要在您的数据访问测试装置中编写太多测试，因为它们的运行速度会比您的“真实”单元测试慢得多
• 如果可以避免使用真实数据库进行测试，请在不使用数据库的情况下进行测试

回答by MatthewMartin

You can unit test everything except: queryDA.Fill(resultSet);

您可以对所有内容进行单元测试，除了： queryDA.Fill(resultSet);

As soon as you execute queryDA.Fill(resultSet), you either have to mock/fake the database, or you are doing integration testing.

一旦执行queryDA.Fill(resultSet)，您要么必须模拟/伪造数据库，要么正在进行集成测试。

I for one, don't see integration testing as being bad, it's just that it'll catch a different sort of bug, has different odds of false negatives and false positives, isn't likely to be done very often because it is so slow.

一方面，我认为集成测试并不糟糕，只是它会捕获不同类型的错误，具有不同的误报和误报几率，不太可能经常进行，因为它是如此减缓。

If I was unit testing this code, I'd be validating that the parameters are build correctly, does the command builder create the right number of parameters? Do they all have a value? Do nulls, empty strings and DbNull get handled correctly?

如果我对这段代码进行单元测试，我会验证参数是否正确构建，命令构建器是否创建了正确数量的参数？他们都有价值吗？空值、空字符串和 DbNull 是否得到正确处理？

Actually filling the dataset is testing your database, which is a flaky component out of the scope of your DAL.

实际上填充数据集是在测试你的数据库，这是一个超出你的 DAL 范围的片状组件。

回答by tvanfosson

For unit tests I usually mock or fake the database. Then use your mock or fake implementation via dependency injection to test your method. You'd also probably have some integration tests that will test constraints, foreign key relationships, etc. in your database.

对于单元测试，我通常模拟或伪造数据库。然后通过依赖注入使用您的模拟或假实现来测试您的方法。您可能还会有一些集成测试来测试数据库中的约束、外键关系等。

As to what you would test, you'd make sure that the method is using the connection from the parameters, that the query string is assigned to the command, and that your result set returned is the same as that you are providing via an expectation on the Fill method. Note -- it's probably easier to test a Get method that returns a value than a Fill method the modifies a parameter.

至于您要测试的内容，您将确保该方法使用来自参数的连接，将查询字符串分配给命令，并且返回的结果集与您通过期望提供的结果集相同在填充方法上。注意——测试返回值的 Get 方法可能比修改参数的 Fill 方法更容易。

回答by James Black

In order to do this properly though you would should use some dependency injection (DI), and for .NET there are several. I am currently using the Unity Framework but there are others that are easier.

为了正确执行此操作，您应该使用一些依赖项注入 (DI)，而对于 .NET，有几个。我目前正在使用 Unity 框架，但还有其他更简单的框架。

Here is one link from this site on this subject, but there are others: Dependency Injection in .NET with examples?

这是此站点上有关此主题的一个链接，但还有其他链接： .NET 中的依赖注入和示例？

This would enable you to more easily mock out other parts of your application, by just having a mock class implement the interface, so you can control how it will respond. But, this also means designing to an interface.

这将使您能够更轻松地模拟应用程序的其他部分，只需让模拟类实现接口，您就可以控制它的响应方式。但是，这也意味着针对界面进行设计。

Since you asked about best practices this would be one, IMO.

既然您询问了最佳实践，这将是一个，IMO。

Then, not going to the db unless you need to, as suggested is another.

然后，除非您需要，否则不要去数据库，正如建议的那样。

If you need to test certain behaviors, such as foreign key relationships with cascade delete then you may want to write database tests for that, but generally not going to a real database is best, esp since more than one person may run a unit test at a time and if they are going to the same database tests may fail as the expected data may change.

如果您需要测试某些行为，例如使用级联删除的外键关系，那么您可能需要为此编写数据库测试，但通常最好不要去真正的数据库，尤其是因为不止一个人可能会在一段时间，如果他们去同一个数据库测试可能会失败，因为预期的数据可能会改变。

Edit: By database unit test I mean this, as it is designed to just use t-sql to do some setup, test and teardown. http://msdn.microsoft.com/en-us/library/aa833233%28VS.80%29.aspx

编辑：我的意思是数据库单元测试，因为它旨在仅使用 t-sql 进行一些设置、测试和拆卸。 http://msdn.microsoft.com/en-us/library/aa833233%28VS.80%29.aspx

回答by acneto

The first question is probably the most complex: What should I even test in a situation like this?

第一个问题可能是最复杂的：在这种情况下我应该测试什么？

• Since your code code is basically a DAO/repository without any business logic you need an integration test, nota unit test.

• Unit test should test classes without external dependencies (like DB or calls to other remote services).

• You should always try to separate the business logic (your Domain Model) code from infrastructure code then it will be easy to use unit tests.

• Be careful with Mocks, it can be a signal of bad design. It means you business logic is mixed with infrastructure.

• Check these patterns: "Domain Model", "Hexagonal Architecture", "Functional Core, Imperative Shell"

• 由于您的代码代码基本上是一个没有任何业务逻辑的 DAO/存储库，因此您需要集成测试，而不是单元测试。

• 单元测试应该测试没有外部依赖的类（比如 DB 或对其他远程服务的调用）。

• 您应该始终尝试将业务逻辑（您的领域模型）代码与基础设施代码分开，这样就可以轻松使用单元测试。

• 小心 Mocks，它可能是糟糕设计的信号。这意味着您的业务逻辑与基础设施混合在一起。

• 检查这些模式：“领域模型”、“六边形架构”、“功能核心、命令式外壳”

回答by cchantep

On JDBC based project, JDBC connection can be mocked, so that tests can be executed without live RDBMS, with each test case isolated (no data conflict).

在基于 JDBC 的项目上，可以模拟 JDBC 连接，因此可以在没有实时 RDBMS 的情况下执行测试，每个测试用例都是隔离的（没有数据冲突）。

It allow to verify, persistence code passes proper queries/parameters (e.g. https://github.com/playframework/playframework/blob/master/framework/src/anorm/src/test/scala/anorm/ParameterSpec.scala) and handle JDBC results (parsing/mapping) as expected ("takes in all the necessary bits and pieces to extract some data from the database, and returns the data in a DataTable object").

它允许验证，持久性代码传递正确的查询/参数（例如https://github.com/playframework/playframework/blob/master/framework/src/anorm/src/test/scala/anorm/ParameterSpec.scala）并处理JDBC 结果（解析/映射）如预期（“获取所有必要的位和片段以从数据库中提取一些数据，并在 DataTable 对象中返回数据”）。

Framework like jOOQ or my framework Acolyte can be used for: https://github.com/cchantep/acolyte.

像 jOOQ 这样的框架或我的框架 Acolyte 可用于：https: //github.com/cchantep/acolyte。