Connecting to a Database - Step by Step

The database driver

This example shows the different steps needed to connect to a database.
In the example the Microsoft SQL Server is used, but the steps applies to all types of database engines.
The first thing you need to do is to consider what driver to use. There are different types of drivers 
(the different types is not covered here) and the most efficient and widely used driver is a Type 4 driver.
A Type 4 driver is completely written in Java and is not dependent on any native code. Each database engine has its own driver, 
so you can only use a driver for that particular database engine.

Now, to make the driver available to the JVM you need to load the class:

Class.forName("[nameOfDriver]"); //For Microsoft SQL Server that would be Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

For the JVM to be able to find that class, you need to make sure the class is in your classpath. Usually the class is located within a .jar-file that can be downloaded from the database vendor.

Creating the database connection

To create the connection you use the static method getConnection() of the java.sql.DriverManager class. The method has several overloaded variants, and one of them takes an url, userid and password as arguments. The database url slightly differs from one type of database to another. For Microsoft SQL Server it looks like this:

//The values within square brackets are the ones to change to suite your environment, //and the square brackets should thus not be included Connection con = DriverManager.getConnection("jdbc:sqlserver://[server]:[port];databaseName=[nameofdatabase]",          "[userid]",          "[password]");

Create the Statement object and executing a query

Now that we have established a connection, we want to create a Statement object and execute an sql query.

Statement statement = con.createStatement(); ResultSet rs = statement.executeQuery("SELECT * FROM table");

Now we have retrieved the data from the table called 'table' into a ResultSet and all we have to do now is loop through it. Assume the table has two columns called id which is of type int, and value which is of type varchar:

//The next() mehtod jumps to the next row in the ResultSet. //When the last row has been processed, the method returns false. while (rs.next()) {    System.out.println(rs.getInt("id") + " - " + rs.getString("value"));     }

Cleaning up resources

Now all we have to do is close the connection. Actually we should close all of the instances of Connection, Statement and ResultSet and it's done in reverse order from which they were created:

if (rs != null)    rs.close(); if (statement != null)    statement.close(); if (con != null)    con.close();

For many of the code statements above it is required to handle an SQLException in case anything goes wrong. Thus you'll have to enclose much of the code in a try / catch block which were excluded in the example to make it more readable.

// Declare the variables outside the try block to be able to call them
// in a finally block where the closing should take place.
Connection con = null;
Statement stmt = null;
ResultSet rs = null;


try {

   //all code here

} catch (SQLException ex) {

   ex.printStackTrace();

} finally {

   //the code for closing here
}