How to make unit test jdbc code in java?

Question

I would like to write some unit tests for some code that connects to the database, runs one or more queries and then processes the results. (Without actual use of the database)

Another developer wrote our own implementation of DataSource, Connection, Statement, PreparedStatement and ResultSet, which will return the corresponding objects based on the xml configuration file. (we could use a dummy data source and just run tests with returned result sets).

Are we inventing the wheel here? Does something like this already exist for unit testing? Are there other / better ways to test jdbc code?

Answer 1

You can use DBUnit along with HSQLDB , which can read its source data from CSV files, for example.

Answer 2

You have several options:

• Database layout using the Mock library, for example. JMock . The huge downside of this is that your queries and data will most likely not be tested at all.
• Use a lightweight database for tests like HSQLDB . If your queries are simple, this is probably the easiest way.
• Highlight a database for tests. DBUnit is a good option, or if you are using Maven, you can also use the sql-maven-plugin to properly configure and demolish the database (be careful with the dependencies between the tests). I recommend this option as it will give you the most confidence that the queries will work correctly with your db provider.

Sometimes it is necessary and useful to make these tests customizable so that these tests are only performed if the database is available. This can be accomplished, for example, by building properties.

Answer 3

I like to use a combination:

• DBUnit
• HSQLDB
• Unitils (in particular, database testing and maintenance modules )

You can get pretty far with DBUnit and HSQLDB. Unitils provides the last mile of management code and the state of the database reset. It also provides a good way to manage database schema changes and simplifies the use of specific RBDMS (Oracle, DB2, SQL Server, etc.). Finally, Unitils provides some good wrappers around DBUnit, which modernizes the API and simplifies working with DBUnit.

If you haven't checked Unitils yet, you definitely need to. Unilids are often overlooked and underestimated.

Answer 4

Use any of the Mock frameworks for such a task. ( jMock , etc.

Some examples

Answer 5

That is why you have derby (now called JavaDB) or sqlite - these are small, simple databases that you can create, load, test and destroy relatively quickly and easily.

Answer 6

I would say that HSQL is the way to go during unit tests. The goal of your test is to check your jdbc code and make sure it works. Adding custom classes or mocking jdbc calls can hide errors.

I mainly use mysql and when the tests run the driver class and the url changes to org.hsqldb.jdbcDriver and jdbc: hsqldb: mem: test.

Answer 7

I prefer to use EasyMock to test code that is not very easy to test.

Answer 8

There is DBUnit . This will not allow you to test your jdbc code without a database, but it looks like you can enter a different set of purchases by emulating the database.

Answer 9

Although the way to mock jdbc in your application, of course, depends on how you implemented your actual jdbc transactions.

If you use jdbc as it is, I would suggest that you write yourself a utility class to perform some tasks in the DBUtils.getMetadataFor(String tablename) row. This will mean that you will need to create a layout for this class, and that may be all you need. This would be a fairly simple solution for you, as you probably already have a series of mock objects related to jdbc. Please note that I assume that your jdbc code is not torn around the application - if so, refactoring !!!

If you use some kind of infrastructure to process the database (for example, Spring Framework JDBC Template classes), you can and should make fun of the interface class using EasyMock or another equivalent. Thus, you can have all the power in the world necessary to easily mock a compound.

And finally, if nothing works, you can do what others have already said and use DBUnit and / or derby.

Answer 10

If you want to perform unit tests and not integration tests, then you can use a very simple and simple approach using only Mockito, for example:

 public class JDBCLowLevelTest { private TestedClass tested; private Connection connection; private static Driver driver; @BeforeClass public static void setUpClass() throws Exception { // (Optional) Print DriverManager logs to system out DriverManager.setLogWriter(new PrintWriter((System.out))); // (Optional) Sometimes you need to get rid of a driver (eg JDBC-ODBC Bridge) Driver configuredDriver = DriverManager.getDriver("jdbc:odbc:url"); System.out.println("De-registering the configured driver: " + configuredDriver); DriverManager.deregisterDriver(configuredDriver); // Register the mocked driver driver = mock(Driver.class); System.out.println("Registering the mock driver: " + driver); DriverManager.registerDriver(driver); } @AfterClass public static void tearDown() throws Exception { // Let cleanup the global state System.out.println("De-registering the mock driver: " + driver); DriverManager.deregisterDriver(driver); } @Before public void setUp() throws Exception { // given tested = new TestedClass(); connection = mock(Connection.class); given(driver.acceptsURL(anyString())).willReturn(true); given(driver.connect(anyString(), Matchers.<Properties>any())) .willReturn(connection); given(connection.prepareCall(anyString())).willReturn(statement); } } 

Than you can test various scenarios, for example, in any other Mockito test, for example.

 @Test public void shouldHandleDoubleException() throws Exception { // given SomeData someData = new SomeData(); given(connection.prepareCall(anyString())) .willThrow(new SQLException("Prepare call")); willThrow(new SQLException("Close exception")).given(connection).close(); // when SomeResponse response = testClass.someMethod(someData); // then assertThat(response, is(SOME_ERROR)); } 

Answer 11

We use Mockrunner. http://mockrunner.sourceforge.net/ It contains false connections and data sources, so there is no need to implement them yourself.

Answer 12

The Acolyte driver can be used to simulate a JDBC connection, manage it during tests, and return data as a result set (with its type API with a list of strings): https://github.com/cchantep/acolyte