DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Testing, Deployment, and Maintenance
  3. Testing, Tools, and Frameworks
  4. Practicing Code, Java, and Databases

Practicing Code, Java, and Databases

Partha Bhattacharjee user avatar by
Partha Bhattacharjee
·
Oct. 17, 12 · Interview
Like (0)
Save
Tweet
Share
12.68K Views

Join the DZone community and get the full member experience.

Join For Free
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.
Database Java (programming language) unit test application Test case

Published at DZone with permission of Partha Bhattacharjee, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Kubernetes vs Docker: Differences Explained
  • How To Check Docker Images for Vulnerabilities
  • Better Performance and Security by Monitoring Logs, Metrics, and More
  • A Brief Overview of the Spring Cloud Framework

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: