Simple Sql Templates

Tue 02 January 2018

When you have a large code base you also need a large number of tests. Anything you can do to reduce the work required to create and main tests will bring you a lot of leverage. Shave an hour's development time off the creation of a single tests multiplied by a thousand tests equals half a developer per year. Secondly, and probably more importantly if tests get easier to write then they will get written - developers tend to balance effort vs benefit and stop writing test as they get harder to write. So one my main motivation for the test utilities that I wrote at my last banking contract was to make creating and maintaining tests easy so the code coverage could go up.

One technique was a simple SQL insert template for generating SQL statements for inserting test data. An SQLTemplate is constructed from an INSERT statement and then is supplied runtime values either as a Map or a vararg array of column=value pairs.

This is useful if you can find existing SQL INSERT statements from DAO classes or from sqls in properties files but is probably not worth it if you have to create lengthy insert statements from scratch.

Here is how it is used ...

final static SqlTemplate USERS = 
  new SqlTemplate("insert into USER (ID, NAME) " +
     " values (1, '<default_name>')");


private void insertUsers() throws Exception {
    jdbcTemplate.execute(USERS.sql("NAME='John Wakefield'"));
    jdbcTemplate.execute(USERS.sql("ID=2", "NAME='Mike Rillis'"));
    jdbcTemplate.execute(USERS.sql("ID=3", "NAME='William Constance'", 
            "ADDRESS='North Pole'"));

This helps with longer sql statements especially if the alternative is to use string concatenation or repeated object construction.

-- insert_projects.sql

    projectNumber , projectTitle, program, 
    category, location, region, 
    approvedDate, constructionStartDate ,constructionEndDate ,  federalContribution , 
    totalEligibleCost , ultimateRecipient  ,forecastedConstructionStartDate ,forecastedConstructionEndDate )
  VALUES ( 46916,'Haines Junction Lagoon upgrades', 'Clean Water and Wastewater Fund',
        'Wastewater','Haines Junction','yt','2016-06-22','2017-09-01','2017-11-11' ,3480750.0, 4641000.0  ,
        'Haines Junction Village of', '2017-08-01' ,  '2018-09-30' );

// Inside test class
final static SqlTemplate PROJECTS = new SqlTemplate(resourceAsString("/sql/insert_project.sql"));

The code is included below.

SQL Template Gist

Category: Java