Every once in a while, I put on some music, switch off distractions like my email and phone, and redo some basic programming. Things that I know and I have done a handful of times. The intent is to practice, get back to basics and gain some new insight. I later read about CodeKata and realized that what I had been doing was not entirely unheard of. For anyone who treats building software as a craft, I can safely recommend this practice.

Here, I want to talk about using Java to connect to a database. Elementary, you say. Absolutely, I concur. I highly recommend the links  Connecting to an Oracle Database,  Closing Database Connections in Java for a clear, concise read, in case you were looking to get those answers. However, if you are interested in a little more detailed, hands on, journey through the subject, I suggest you read on.

Tools 

Oracle. I have worked with a variety of RDBMS and/or NoSql ( link) based applications and have no sweeping opinion for / against any of those. However, I tend to choose Oracle ( express edition) even for my development environment. Call me old-fashioned, but till I don't get to log into a "database server" and run queries to confirm that what I "think" my app did in database, I don't feel confident about my code.

Maven + Eclipse. I would tend to agree with anyone who says Maven is an overkill for simple applications (under 10 classes). But again, it's my gut. I don't feel confident if I don't use tools that I will use in any enterprise grade application in my codekatas (for the lack of a better name). After all, when you practise your skills, if you don't use the proper tool set, the practise session is a bit pointless, isn't it.

Getting warmed up

As I mentioned I use Maven. I have a batch file with the basic Maven commands ( read more about it) that I use to create any new java application and then import it in Eclipse. Minus all the noise, this is how the batch file looks like.

File: \MavenCommands.bat

REM =============================
REM Set the env. variables. 
REM =============================
SET PATH=%PATH%;C:\ProgramFiles\apache-maven-3.0.4\bin;
SET JAVA_HOME=C:\ProgramFiles\Java\jdk1.7.0

REM =============================
REM Standalone java application. 
REM =============================
call mvn archetype:generate ^
    -DarchetypeArtifactId=maven-archetype-quickstart ^
    -DinteractiveMode=false ^
    -DgroupId=foo.bar ^
    -DartifactId=javadb001

This creates a complete java application, with a dummy class, a test case etc. The pom file contains most of the details. I tend to add a few standard elements in the pom file before I try anything else. In this case I will add the oracle thin client as well.

File:\javadb001\pom.xml

<properties>
 <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>


<dependencies>

 <!-- Unit test. -->
 <dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <version>4.10</version>
  <scope>test</scope>
 </dependency>

 <!-- Logging -->
 <dependency>
  <groupId>ch.qos.logback</groupId>
  <artifactId>logback-classic</artifactId>
  <version>1.0.7</version>
 </dependency>

 <!-- Oracle database driver. -->
 <dependency>
  <groupId>ojdbc</groupId>
  <artifactId>ojdbc</artifactId>
  <version>14</version>
 </dependency>

</dependencies>

<build>

 <!-- Compile and run with current Java version -->
 <plugins>
  <plugin>
   <groupId>org.apache.maven.plugins</groupId>
   <artifactId>maven-compiler-plugin</artifactId>
   <version>2.5.1</version>
   <configuration>
    <source>1.6</source>
    <target>1.6</target>
   </configuration>
  </plugin>
 </plugins>
</build>

If you run this "mvn -e clean install" this should download all dependencies and compile just fine.

The code (finally) 

In this case I think a JUnit test case would work just fine. We just need to create an instance of the driver (to connect to Oracle), give it some standard data, attempt a connect and then attempt a disconnect.

File: /javadb001/src/test/java/foo/bar/database/TestConnection.java

public class TestConnection {
 private final static Logger logger = LoggerFactory
   .getLogger(TestConnection.class);

 @Test
 public void test() {

  Connection connection = null;
  try {
   // Load the JDBC driver
   String driverName = "oracle.jdbc.driver.OracleDriver";
   Class.forName(driverName);

   // Create a connection to the database
   String serverName = "127.0.0.1";
   String portNumber = "1521";
   String sid = "XE";
   String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber
     + ":" + sid;
   String username = "funngames";
   String password = "funngames";
   connection = DriverManager.getConnection(url, username, password);
   assertNotNull(connection);
   logger.debug("All well");

  } catch (ClassNotFoundException e) {
   logger.debug(e.getMessage());
  } catch (SQLException e) {
   logger.debug(e.getMessage());
  } finally {
   if (connection != null) {
    try {
     connection.close();
    } catch (SQLException e) {
     logger.debug(e.getMessage());
     fail("The connection could not be closed.");
    }
   }
  }
 }
}

You will note that although it is the most basic (and useless functionality wise) piece of code, it is quite well behaved. It catches the required exceptions, and it attempts to close the connection. What is perhaps not as easily seen is that it is a well behaved code in enterprise applications scenario as well. It uses a standard and robust logging mechanism i.e. Logback. It uses a standard build, release (and more) tool i.e. Maven.

In a nutshell if you could think of a sort of virtual shelf, where your software engineers could walk up to and pick standard code templates, this is one of those. Impractical in it's present form but I hope you get the concept.

For those, who are still with me (congratulations by the way), and are interested in the whole codekata concept that I referred to at the beginning of the article, there is a wee bit more.

Can we bring down the boilerplate code?

I like Java for all it's stability and stuff. But, being a lazy human, I hate writing code that "feels like" could have been handled by the language itself. In this current code, multitude of try-catches are an eyesore. I understand why I need them but that does not necessarily mean I like writing them. Perhaps you have guessed already that I am referring to better resource management  introduced by Project Coin.

In a nutshell, Java 7 promises to handle the closing of resources i.e. Connection in this case, by itself. Let's give that a shot.

We need to tell maven that we want to use jdk 7.

File: /javadb001/pom.xml

<!-- Compile and run with current Java version -->
<plugins>
 <plugin>
  <groupId>org.apache.maven.plugins</groupId>
  <artifactId>maven-compiler-plugin</artifactId>
  <version>2.5.1</version>
  <configuration>
   <source>1.7</source>
   <target>1.7</target>
  </configuration>
 </plugin>
</plugins>

And we need to update our test case to create Connection within a try to let java close it by itself.

public void test() throws SQLException {
 String driverName; 

 try {
  // Load the JDBC driver
  driverName = "oracle.jdbc.driver.OracleDriver";
  Class.forName(driverName);
 } catch (ClassNotFoundException e) {
  logger.debug(e.getMessage());
  e.printStackTrace();
 }
 // Create a connection to the database
 String serverName = "127.0.0.1";
 String portNumber = "1521";
 String sid = "XE";
 String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber
   + ":" + sid;
 String username = "funngames";
 String password = "funngames";
 try(Connection connection = DriverManager.getConnection(url, username, password)){
  assertNotNull(connection);
  logger.debug("Connection made.");   
 }    
}

If you compare this piece of code with the previous one, we have got rid of at least three try-catch blocks. But this code is useless in a practical scenario, you say. Totally, I agree. As I had mentioned earlier, the intent is to practice writing code - something mundane - but in multiple (hopefully new ways) and learn by practising. The intent is not to produce production ready, reusable components.  The quality of the code, the craftsmanship is important in this context. The benefit will be in terms of more skilful engineers in the organization and the associated benefit to business.

Finally, my litmus test... 

this supposedly nice, high quality code, how good is it when we run it 10,000 times in 5 threads. I hate to break the news to you but it is not too good at all. It crashes after 20 - 25 executions. Why? And how to fix that? I have discussed the problem and solution at stackoverflow. I hope you find it interesting.

Re-factor

This article is getting longish and I should wrap up now. Let's re-factor this code to a position where stuff are not in unit tests any more. The code ought to be in some classes where they could be used by client code. How does the updated test code looks to you.

File: /javadb001/src/test/java/foo/bar/database/TestConnection.java

 
@Test
@PerfTest(invocations = 100, threads = 1)
@Required(max = 1200, average = 1000)
public void testConnectionFactoryAndUtil(){
 Connection connection = ConnectionFactory.getConnection(); 
 assertNotNull(connection); 
 Util.closeConnection(connection);  
}

Compare this with the earlier test. That was 24 lines. Assuming you are convinced that this is a better looking test, how do we refactor to make this happen. It's your call really and you could do it multiple ways. Here is my take.

File:/javadb001/src/main/java/foo/bar/database/ConnectionFactory.java

public class ConnectionFactory {
 private final static Logger logger = LoggerFactory
   .getLogger(ConnectionFactory.class);

 public static Connection getConnection() {
  return getConnection("oracle.jdbc.driver.OracleDriver", "127.0.0.1",
    "1521", "XE", "funngames", "funngames");
 }

 public static Connection getConnection(String driver, String host,
   String port, String sid, String user, String pass) {
  Connection connection = null;
  try {
   // Load the JDBC driver
   Class.forName(driver);
   String url = "jdbc:oracle:thin:@" + host + ":" + port + ":" + sid;
   connection = DriverManager.getConnection(url, user, pass);
   logger.info("Connection opened [{}]", connection.toString()); 
  } catch (ClassNotFoundException e) {
   logger.debug(e.getMessage());
   e.printStackTrace();
  } catch (SQLException e) {
   logger.debug(e.getMessage());
   e.printStackTrace();
  }
  return connection;
 }
} 

File:/javadb001/src/main/java/foo/bar/database/Util.java

public class Util {
 private final static Logger logger = LoggerFactory.getLogger(Util.class);

 public static void closeConnection(Connection connection){
  if (connection != null) {
   try {
    logger.info("Attempting connection close [{}]", connection.toString()); 
    connection.close();
    Thread.sleep(500); 
    logger.info("Connection succesfuly closed."); 
   } catch (SQLException e) {    
    logger.debug(e.getMessage());
    e.printStackTrace();
   } catch (InterruptedException e){
    logger.debug(e.getMessage());
    e.printStackTrace();
   }
  }
 }
}

Needless to say, any of this code is hardly new or ground breaking. You might want to check out this class for a much more comprehensive set of utility functions to close connection and related resources. Feel free to use that utility (it is from Apache) or roll your own.