{"id":130,"date":"2009-07-01T10:39:51","date_gmt":"2009-07-01T09:39:51","guid":{"rendered":"http:\/\/blog.jteam.nl\/?p=130"},"modified":"2009-07-01T10:39:51","modified_gmt":"2009-07-01T09:39:51","slug":"testing-the-database-layer","status":"publish","type":"post","link":"https:\/\/trifork.nl\/blog\/testing-the-database-layer\/","title":{"rendered":"Testing the database layer"},"content":{"rendered":"

The database is an integral part of many applications and writing queries is often hard. For that reason I have always written integration tests for the data access objects or DAO’s that I use to access the database. The way I write my DAO tests has changed a lot over the years and in this post I’d like to document the ways in which it has changed and why.<\/p>\n

<\/p>\n

DbUnit<\/h2>\n

The first integration tests I wrote for the database used DbUnit (www.dbunit.org<\/a>). DbUnit lets you to export a pre-populated database to an XML file. In the setup method of your integration test you then call a utility method that clears the database and initializes the database with the exported data. This worked relatively well most of the time but still I noticed a number of problems with this approach. First of all XML is a terrible format to export data to. It\u2019s overly verbose and XML’s escaping rules make manual editing of the exported data a non trivial task. Of course this doesn\u2019t matter if you always use tooling to recreate the dataset. However at least at the time the tooling left somewhat to be desired. In particular I was having problems with the ordering of the exported data. For instance if you had order lines that contained a foreign key that pointed to an order, DbUnit would sometimes place the order lines at the top of the file, leading to constraint violations when re-importing the file. At some later point the authors added something that I believe is called a DatabaseSequenceFilter that supposedly solves this issue. However it never really worked well for me. The second issue I had with it is that because DbUnit actually commits all the data to the database it is a) rather slow and b) opens up the possibility of inadvertently leaving data in the database that can break other tests if you do not practice proper testing hygiene.<\/p>\n

AbstractTransactionalDataSourceSpringContextTests<\/h2>\n

A number of years ago a collegue at JTeam introduced me to a class called AbstractTransactionalDataSourceSpringContextTests (nowadays deprecated in favor of AbstractJUnit38SpringContextTests and AbstractTransactionalJUnit4SpringContextTests), which is part of Spring (www.springframework.org<\/a>) the purpose of this class is twofold. First of all it contains a number of convenience methods such as executeSqlScript and deleteFromTables that make it easy to initialize a database to a known state. In a way this is a simpler alternative to some of the functionality that DbUnit offers.<\/p>\n

Second and more importantly it makes sure that for each test method a database transaction is started. All your testing code operates within this transaction and upon completion of the test the transaction is rolled back. This means that your modifications never get committed and your database stays clean. Because a rollback is a lot faster than actually committing the data and then removing it, this affects performance in a noticeable way.<\/p>\n

Note that AbstractTransactionalDataSourceSpringContextTests does in fact not rule out the use of DbUnit. If for some reason you like DbUnit you can still use DbUnit to import your data from within the onSetUpInTransaction method in the classic AbstractTransactionalDataSourceSpringContextTests or from a method annotated with @Before if you use one of the newer annotation based base classes.<\/p>\n

Personally not liking the XML format that DbUnit uses all that much, I never seriously considered it, rather than using DbUnit I initially created one inline fixture per method.<\/p>\n

Fixture per method<\/h3>\n

The class below shows an example of this<\/p>\n

\npublic class HibernateOrderDaoTest extends AbstractTransactionalDataSourceSpringContextTests {\n\n....\npublic void testGetById() {\n\/\/ ... many insert statements for all the related data, countries, shipping\njdbcTemplate.execute("INSERT INTO order (id, version, shipping_address_street, shipping_address_city, shipping_address_state, shipping_address_postcode, shipping_address_country_id, billing_address_street, billing_address_city, billing_address_state, billing_address_postcode, billing_address_country_id, account_id, submitted_on, total_price_currency, total_price_amount, total_price_amount_inclusive, active_status_type_name, last_exported, shipping_service_id)" +\n"VALUES (1, 0, 'Straatnaam 23', 'Eindhoven', 'Noord Brabant', '1234AB', '100', 'Straatnaam 23', 'Eindhoven', 'Noord Brabant', '1234AB', '100', 1, '2005-9-27',  'EUR', 100, 120, 'created', '2005-9-27', 1)");\n\nOrder order = dao.getById(1L);\n\n\/\/ verify all the fields\n}\n}\n<\/pre>\n
What I liked about this is that the SQL is in-lined in the test method. No more switching between an external file that contains the data and the test code.<\/p>\n
However there are also problems. Often there are interdependencies between tables. For instance, an order is placed by a user who lives in a country. So before I can create the order I must first have created the country and user.  This quickly adds up for non-trivial domains.<\/p>\n
What more because I am using a fixture per method I find that I am repeating myself over and over again. A fixture per method only works well if the object you\u2019re testing has no interdependencies.<\/p>\n
One way to solve this for many scenarios is to move the SQL for the interdependencies to a method that gets called before any test code is run<\/p>\n
Fixture per method + shared setup per class<\/h3>\n
The class below shows an example of this<\/p>\n
\npublic class HibernateOrderDaoTest extends AbstractTransactionalDataSourceSpringContextTests {\n\nprotected void doSetUpInTransaction() throws Exception {\njdbcTemplate.execute("INSERT INTO country(id, version, name, short_name) VALUES (100, 0, 'Netherlands', 'NL');");\n\njdbcTemplate.execute("INSERT INTO account (id, version, username,  password, first_name, last_name, email, phone_number, billing_address_street, billing_address_city, billing_address_state, billing_address_postcode, billing_address_country_id, shipping_address_street, shipping_address_city, shipping_address_state, shipping_address_postcode, shipping_address_country_id, last_signin, active, agreed_to_latest_terms) " +\n"VALUES (1, 0, 'leonard', 'c539eadb15ce2243242196e428986d70', 'Leonard',  'Wolters', 'leonard@jteam.nl', '+31 020-1234567', 'Straatnaam 23', 'Eindhoven', 'Noord Brabant', '1234AB', 100, 'Straatnaam 23', 'Eindhoven', 'Noord Brabant', '1234AB', 100, '2005-9-30', false, true);");\n\n\/\/ etc etc..\n}\n\npublic void testFindOrdersAboveOneHundredEuros() {\njdbcTemplate.execute("INSERT INTO order (id, version, shipping_address_street, shipping_address_city, shipping_address_state, shipping_address_postcode, shipping_address_country_id, billing_address_street, billing_address_city, billing_address_state, billing_address_postcode, billing_address_country_id, account_id, submitted_on, total_price_currency, total_price_amount, total_price_amount_inclusive, active_status_type_name, last_exported, shipping_service_id)" +\n"VALUES (1, 0, 'Straatnaam 23', 'Eindhoven', 'Noord Brabant', '1234AB', '100', 'Straatnaam 23', 'Eindhoven', 'Noord Brabant', '1234AB', '100', 1, '2005-9-27',  'EUR', 100, 120, 'created', '2005-9-27', 1)");\n\n\/\/ insert a few more orders with varying\n\nList<Order> orders = dao.FindOrdersAboveOneHundredEuros();\n}\n}\n<\/pre>\n
While this solves the problem outlined above there is still a lot of room for improvement<\/p>\n
First of all, every time you insert a row in the database you\u2019re effectively duplicating all the column names.  This means that if at some point a database column name changes you will have to go over each insert statement and update this statement by hand. This same problem arises when you introduce a new required column or remove a column. The removal of a column I found to be particularly time consuming and error prone in this approach  because of the difficulty you will have figuring out which column belongs to which value. It caused me to format my insert statements like this when I was using this approach.<\/p>\n
INSERT INTO order (id, version, shipping_address_street)\n           values (1,  0,       'Straatnaam 23');<\/pre>\n
Second, often a lot of the information contained in the insert statements is irrelevant to many tests.  If you\u2019re testing a method that returns all orders for 100 euros or more you are not interested in the customer\u2019s shipping details. It just clutters the code and distracts from the actual purpose of the test.<\/p>\n
The way we solved this initially was by creating a DatabaseHelper class that was used to insert rows into the database.<\/p>\n
Helper classes<\/h3>\n
The above example we could rewrite as:<\/p>\n
\npublic class HibernateOrderDaoTest extends AbstractTransactionalDataSourceSpringContextTests {\n\nprivate long countryId;\nprivate long accountId;\n\nprotected void doSetUpInTransaction() throws Exception {\ncountryId = databasehelper.createCountry("Netherlands", "NL");\naccountId = databasehelper.createAccount("username", "c539eadb15ce2243242196e428986d70", countryId);\n\/\/ etc etc..\n}\n\npublic void testFindOrdersAboveOneHundredEuros() {\ndatabasehelper.createOrderWithAmount(accountId, 101);\ndatabasehelper.createOrderWithAmount(accountId, 99);\n\n\/\/ insert a few more orders\n\nList<Order> orders = dao.FindOrdersAboveOneHundredEuros();\n}\n}\n<\/pre>\n
This looks much, much cleaner. The testFindOrdersAboveOneHundredEuros method is very concise and understandable. The fields that are not relevant are defaulted to some arbitrary valid value so you do not have to think about them.<\/p>\n
However in different test scenario’s different information is relevant. Suppose we also have a method on our DAO that returns all orders shipped to a particular city.<\/p>\n
In that case we either create a new method on the helper called createOrderWithCity(accountId, cityName) Or we rename the  createOrderWithAmount method to createOrder and add an additional argument to this method<\/p>\n
createOrder(accountId, cityName, amount);<\/pre>\n
Values will then be set to null depending on the test scenario.<\/p>\n
The former scenario has the potential of growing out of control when the number of test cases grows, while the latter scenario is less concise because it requires you to think about information that is not relevant to your testcase (eg. the values you have to null out) Also if you change the method signature of this method you will have to update all the code that references this method.<\/p>\n
Additionally i found that a single class that contains all your database inserts becomes a point of contention when working in a team.<\/p>\n
To remedy these problems, recently I have been using builder classes for creating my test data.<\/p>\n
Statement builders<\/h3>\n
These builder statements implement the following interface.<\/p>\n
\npublic interface InsertStatement<T> {\nT execute(SimpleJdbcTemplate template) throws DataAccessException;\n}\n<\/pre>\n
Here’s an example of an implementation<\/p>\n
\npublic class ProjectInsertStatement implements InsertStatement<Long> {\n\nprivate long organizationId;\nprivate String name;\nprivate String goal = "goal";\nprivate String description = "description";\nprivate String externalRisks = "external risks";\n\npublic ProjectInsertStatement(long organizationId, String name) {\nAssert.notNull(name);\n\nthis.organizationId = organizationId;\nthis.name = name;\n}\n\npublic ProjectInsertStatement setGoal(String goal) {\nthis.goal = goal;\nreturn this;\n}\n\npublic ProjectInsertStatement setDescription(String description) {\nthis.description = description;\nreturn this;\n}\n\npublic ProjectInsertStatement setExternalRisks(String externalRisks) {\nthis.externalRisks = externalRisks;\nreturn this;\n}\n\npublic Long execute(SimpleJdbcTemplate template) {\n\nJdbcInsertStatementBuilder builder = new JdbcInsertStatementBuilder("project");\nbuilder.addParameter("name", name);\nbuilder.addParameter("organization_id", organizationId);\nbuilder.addParameter("goal", goal);\nbuilder.addParameter("description", description);\nbuilder.addParameter("external_risks", externalRisks);\n\nbuilder.build().execute(template);\n\nreturn template.queryForLong("select max(id) from project");\n}\n}\n<\/pre>\n
And here’s an example of how to use this class:<\/p>\n
\nnew ProjectInsertStatement(organizationId, "my project")\n.setGoal("my goal")\n.execute(template);\n<\/pre>\n
As you can see the statement has two required constructor arguments, the organisationId because a project always has to be associated with an organization, and a name, which in this case has to be unique so it cannot be defaulted. Goal, description and externalRisks are required but do not have to be unique so we can default these. A setter is provided for each of these fields so if you want to deviate from the default this is possible.<\/p>\n
Some have commented on the fact that it is a lot of work to create these insert statement. While this is certainly true I do think the extra work its worth it in the long run.<\/p>\n
Do not rely on any of the defaults.<\/h3>\n
Suppose you wanted to test a method that counts all projects whose goal is 4 letters long you could write it like this:<\/p>\n
\nnew ProjectInsertStatement(organizationId, "my project 1")\n.setGoal("my goal")\n.execute(template);\n\nnew ProjectInsertStatement(organizationId, "my project 2")\n.execute(template);\n\nassertEquals(1, dao.countProjectsWithFourLetterGoal());\n<\/pre>\n
While this test would pass because that default goal is “goal” (4 letters) its not self documenting and it would  break if someone changed the default in the insert statement If something is relevant to what you are testing, be explicit about it.<\/p>\n
Use fixture classes<\/h3>\n
Often a fixture can be shared across multiple classes so these days I tend to move fixtures to an external class<\/p>\n
Here’s an example of a fixture class:<\/p>\n
\npublic class DonationFixture {\nprivate long organizationId;\nprivate long projectId;\n\nprivate DonationFixture(SimpleJdbcTemplate template) {\norganizationId = new OrganizationInsertStatement("Greenpeace")\n.execute(template);\nprojectId = new ProjectInsertStatement(organizationId, "Save the monkey")\n.execute(template);\n}\n\npublic long getProjectId() {\nreturn projectId;\n}\n\npublic long getOrganizationId() {\nreturn organization1Id;\n}\n\npublic static DonationFixture create(SimpleJdbcTemplate jdbcTemplate) {\nAssert.notNull(jdbcTemplate);\nreturn new DonationFixture(jdbcTemplate);\n}\n}\n<\/pre>\n
And some code that uses it:<\/p>\n
\n@Test\npublic void testFindById() {\nDonationFixture fixture = DonationFixture.create(simpleJdbcTemplate);\n\nString comment = "comment";\n\nlong id = new DonationInsertStatement(fixture.getProject1Id(), 10)\n.setComment(comment)\n.execute(simpleJdbcTemplate);\n\nDonation donation = donationDao.findById(id);\n\nassertEquals(id, (long) donation.getId());\nassertEquals(comment, donation.getComment());\nassertEquals(fixture.getProject1Id(), (long) donation.getProject().getId());\n}\n<\/pre>\n
The above code is taken from an example project that you can download from here<\/a>.
\nIt uses JPA for the DAO implementation and runs against an in memory H2 database.
\nSo you do not have to setup and external database to run the code.<\/p>\n","protected":false},"excerpt":{"rendered":"
The database is an integral part of many applications and writing queries is often hard. For that reason I have always written integration tests for the data access objects or DAO’s that I use to access the database. The way I write my DAO tests has changed a lot over the years and in this […]<\/p>\n","protected":false},"author":58,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"content-type":"","footnotes":""},"categories":[10],"tags":[22,73,142,11,143,120],"class_list":["post-130","post","type-post","status-publish","format-standard","hentry","category-development","tag-best-practices","tag-database","tag-dbunit","tag-java","tag-junit","tag-testing"],"acf":[],"yoast_head":"\nTesting the database layer - Trifork Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/trifork.nl\/blog\/testing-the-database-layer\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Testing the database layer - Trifork Blog\" \/>\n<meta property=\"og:description\" content=\"The database is an integral part of many applications and writing queries is often hard. For that reason I have always written integration tests for the data access objects or DAO’s that I use to access the database. The way I write my DAO tests has changed a lot over the years and in this […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/trifork.nl\/blog\/testing-the-database-layer\/\" \/>\n<meta property=\"og:site_name\" content=\"Trifork Blog\" \/>\n<meta property=\"article:published_time\" content=\"2009-07-01T09:39:51+00:00\" \/>\n<meta name=\"author\" content=\"Jelmer Kuperus\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jelmer Kuperus\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/trifork.nl\/blog\/testing-the-database-layer\/\",\"url\":\"https:\/\/trifork.nl\/blog\/testing-the-database-layer\/\",\"name\":\"Testing the database layer - Trifork Blog\",\"isPartOf\":{\"@id\":\"https:\/\/trifork.nl\/blog\/#website\"},\"datePublished\":\"2009-07-01T09:39:51+00:00\",\"author\":{\"@id\":\"https:\/\/trifork.nl\/blog\/#\/schema\/person\/c0ee9f25744015bf661fee1b797341f2\"},\"breadcrumb\":{\"@id\":\"https:\/\/trifork.nl\/blog\/testing-the-database-layer\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/trifork.nl\/blog\/testing-the-database-layer\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/trifork.nl\/blog\/testing-the-database-layer\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/trifork.nl\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Testing the database layer\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/trifork.nl\/blog\/#website\",\"url\":\"https:\/\/trifork.nl\/blog\/\",\"name\":\"Trifork Blog\",\"description\":\"Keep updated on the technical solutions Trifork is working on!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/trifork.nl\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/trifork.nl\/blog\/#\/schema\/person\/c0ee9f25744015bf661fee1b797341f2\",\"name\":\"Jelmer Kuperus\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/trifork.nl\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/fff87cf8073c776ffcbe26326f713998?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/fff87cf8073c776ffcbe26326f713998?s=96&d=mm&r=g\",\"caption\":\"Jelmer Kuperus\"},\"sameAs\":[\"http:\/\/www.dutchworks.nl\"],\"url\":\"https:\/\/trifork.nl\/blog\/author\/jelmer\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Testing the database layer - Trifork Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/trifork.nl\/blog\/testing-the-database-layer\/","og_locale":"en_US","og_type":"article","og_title":"Testing the database layer - Trifork Blog","og_description":"The database is an integral part of many applications and writing queries is often hard. For that reason I have always written integration tests for the data access objects or DAO’s that I use to access the database. The way I write my DAO tests has changed a lot over the years and in this […]","og_url":"https:\/\/trifork.nl\/blog\/testing-the-database-layer\/","og_site_name":"Trifork Blog","article_published_time":"2009-07-01T09:39:51+00:00","author":"Jelmer Kuperus","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Jelmer Kuperus","Est. reading time":"11 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/trifork.nl\/blog\/testing-the-database-layer\/","url":"https:\/\/trifork.nl\/blog\/testing-the-database-layer\/","name":"Testing the database layer - Trifork Blog","isPartOf":{"@id":"https:\/\/trifork.nl\/blog\/#website"},"datePublished":"2009-07-01T09:39:51+00:00","author":{"@id":"https:\/\/trifork.nl\/blog\/#\/schema\/person\/c0ee9f25744015bf661fee1b797341f2"},"breadcrumb":{"@id":"https:\/\/trifork.nl\/blog\/testing-the-database-layer\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/trifork.nl\/blog\/testing-the-database-layer\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/trifork.nl\/blog\/testing-the-database-layer\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/trifork.nl\/blog\/"},{"@type":"ListItem","position":2,"name":"Testing the database layer"}]},{"@type":"WebSite","@id":"https:\/\/trifork.nl\/blog\/#website","url":"https:\/\/trifork.nl\/blog\/","name":"Trifork Blog","description":"Keep updated on the technical solutions Trifork is working on!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/trifork.nl\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/trifork.nl\/blog\/#\/schema\/person\/c0ee9f25744015bf661fee1b797341f2","name":"Jelmer Kuperus","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/trifork.nl\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/fff87cf8073c776ffcbe26326f713998?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/fff87cf8073c776ffcbe26326f713998?s=96&d=mm&r=g","caption":"Jelmer Kuperus"},"sameAs":["http:\/\/www.dutchworks.nl"],"url":"https:\/\/trifork.nl\/blog\/author\/jelmer\/"}]}},"_links":{"self":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/posts\/130","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/users\/58"}],"replies":[{"embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/comments?post=130"}],"version-history":[{"count":0,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/posts\/130\/revisions"}],"wp:attachment":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/media?parent=130"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/categories?post=130"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/tags?post=130"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}