Database TDD Part 2

by Jeff Langr

October 10, 2005

Interrupted by a busy two-week engagement, I return to my code to find… a failing test. While it’s not acceptable to check failing tests into a team environment, it’s often a useful technique to jog your memory. Check in the stuff that passes all the tests, add a new test that fails. When you come back, you know where to pick up.

Following the ten-minute rule, I coded the following in the User class, in order to quickly get a green bar:

       public void save() throws SQLException {
          loadDriver();
          Connection connection = createConnection();
          Statement statement = connection.createStatement();
          String sql = String.format("insert into userdata (name, password) values ('%s', '%s')", name, password);
          statement.execute(sql);
          connection.close();
       }
    
       private static Connection createConnection() throws SQLException {
          return DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password=xyz");
       }
    
       private void loadDriver() throws SQLException {
          new Driver();
       }
    
       public static User find(String nameKey) throws SQLException {
          Connection connection = createConnection();
          Statement statement = connection.createStatement();
          String sql = String.format("select name, password from userdata where name = '%s'", nameKey);
          ResultSet results = statement.executeQuery(sql);
          results.next();
          String name = results.getString("name");
          String password = results.getString("password");
          results.close();
          connection.close();
          return new User(name, password);
       }

All sorts of bad practices, but this was the only way I was going to get the test passing within 10 minutes. I used MySQL since it’s a known quantity to me.

The test makes the presumption that there is already a database available with the userdata table defined. This is the start of where things could go very wrong. It’s one of the bigger issues I’ll discuss soon.

There are many things that may be of concern:

  • hardcoded literals strewn throughout

  • multiple connections being created

  • database code in domain class

  • hardcoded SQL strings

  • not protecting close statements with finally block

  • rerunning the test results in multiple entries in the userdata table (I have no constraints turned on)

  • loading the driver looks nonstandard (no Class.forName?)

  • save must be called before find, otherwise the driver’s not loaded

  • questionable error handling strategy

  • careless presumption that there is at least one result, and that it’s the result we’re looking for

All that in just 10 minutes worth of code. And of course duplication. Spot the duplicate code!

Next time out we’ll start tackling some of these issues. Starting, as always, with duplication.

Share your comment

Jeff Langr

About the Author

Jeff Langr has been building software for 40 years and writing about it heavily for 20. You can find out more about Jeff, learn from the many helpful articles and books he's written, or read one of his 1000+ combined blog (including Agile in a Flash) and public posts.