Cogs and Levers A blog full of technical stuff

JDBC

08 Feb 2016

JDBC (Java Database Connectivity) is a general purpose data access library baked into the Java development and runtime. This library’s purpose is to lower the level of complexity in connecting to different database vendors providing a consistent interface no matter what database you’re connecting to.

In today’s post, I’ll go through the basics of using this library. This blog post will be in context of connecting to a PostgreSQL database.

Drivers

JDBC is based on the premise of drivers. The driver code itself is what fills in the architecture with an implementation that your applications will use. To enumerate all of the drivers, currently in context of your application you can use the following:

Enumeration drivers = DriverManager.getDrivers();

while (drivers.hasMoreElements()) {
  Driver driver = (Driver) drivers.nextElement();
  System.out.println(driver.getClass());
}

I use the term “in context” because whilst you may have the required JAR installed on your system which will be a particular database vendor’s connection library for JDBC, you’ll need to make sure that it’s available on your class path.

For my example, I only have Postgres available to me:

class org.postgresql.Driver

The driver string that you saw in the section above plays an important role in establishing a connection to your database. Before you can start to work with Connection, Statement and ResultSet objects you first need to load in your vendor’s library implementation.

Class.forName("org.postgresql.Driver");

This will reflect your driver into your application ready for use.

Making a connection

To establish a connection with a database, you’ll need to specify a connection string with all of the attributes required to direct your application to the database.

JDBC has a uniform format for specifying its connections with each vendor. Postgres conncetions are no different.

A connection is established using the DriverManager class like so.

Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/crumbs?user=postgres&password=password");

Running queries

Running retrieves on your database normally comprises of three processes:

  • Preparing a statement to run
  • Executing the statement
  • Enumerating the results

The preparation of the statement is fairly straight forward. The createStatement method on the Connection object will allow you to create an empty statement, whereas prepareStatement will allow you to provide some SQL directly.

// prepare the statement 
Statement retrieveStatement = connection.createStatement();

// execute the statement
ResultSet streetTypes = retrieveStatement.executeQuery("SELECT * FROM \"StreetType\"");

// enumerate the result
while (streetTypes.next()) {
  int id = streetTypes.getInt(streetTypes.findColumn("ID"));
  String name = streetTypes.getString(streetTypes.findColumn("Name"));
  
  System.out.println(String.format("ID: %d, Name: %s\n", id, name));
}

A slightly more complex example where you’d pass in some parameters into your statement might look like this:

PreparedStatement retrieveStatement = 
  connection.prepareStatement("SELECT * FROM \"StreetType\" WHERE \"ID\" > ?");

retrieveStatement.setInt(1, 10);
ResultSet streetTypes = retrieveStatement.executeQuery();

Enumerating a ResultSet object can be achieved with a simple while loop:

while (streetTypes.next()) {
  int id = streetTypes.getInt(streetTypes.findColumn("ID"));
  String name = streetTypes.getString(streetTypes.findColumn("Name"));
  
  System.out.println(String.format("ID: %d, Name: %s\n", id, name));
}

Cleaning up

Finally, all objects should be cleaned up afterwards by using the close functions provided.

streetTypes.close();
retrieveStatement.close();
connection.close();

Other topics

This blog post is just enough to get up and running. There are plenty more complex topics inside of JDBC to be learned:

Related Posts