How to unit test an SQL query?

问题:

I have a class DBHandler which takes a query, runs it through the SQL server, checks for errors and returns the result. How can I unit test this class?

Edit: I'll try to be more precise: DBHandler is in charge of passing the query to the server. In order to test that it actually does that, throws the correct exceptions, etc., I want to connect it to a mock DB which I will populate. My question is - how to do that? How can I create a mock "server" that handles calls?

回答1:

Just pass a SQL query, and compare the returned result to expected result. Simple. JUnit is a unit test framework, you can utilise that.

For sophisticated database unit testing, look at DBUnit.

回答2:

I'd use dependency injection to pass in the database connection or something similar, so that the whole thing can be mocked out in the tests. Then you can write tests where the mock query throws exceptions, returns various errors or valid results. Then your tests are just checking that DBHandler performs correctly.

回答3:

You'll want to either use an in-memory test database that you create and populate on set-up or make all your tests transactional so they don't alter your test database.

You'll have to worry about the presence of data.

If you're using Spring, they have support for transactional unit tests.

It's not clear what you're asking. You already know about JUnit. What do you think you're missing?

回答4:

A quick solution for a mock db works like this:

• Setup an HSQLDB test server independent from your app.

• Populate with test data.

• Use conditional code where you connect to your real database, to connect to the test server. A property in your application can control this.

• Test the application