Adventures in JDBC and the PostgreSQL JDBC driver: Part 2 - Using JDBC driver to communicate with database server

This is the second part of the Adventures in JDBC and the PostgreSQL JDBC driver series of articles. In the first part, we have installed the PostgreSQL database server, and we have downloaded the PostgreSQL JDBC driver source and compile it. In this part we will create a sample application to use JDBC to interact with the underlying database system. The first step is the create a sample application.

Creating Sample Application

Now let’s write a sample application, and the project name will be play-with-jdbc. The first step is to create a directory for the project:

$ mkdir play-with-jdbc

The above command will create the play-with-jdbc directory. I’d like to use Gradle to manage this project, because Gradle can reuse the local Maven repository, and it’s more lightweight to start a small new project compared with Maven. Firstly we need to enter the play-with-jdbc directory:

$ cd play-with-jdbc/

After entering the directory, we need to run the gradle init command to create a Java project. Here is the command and its output:

$ gradle init --type java-library
Starting a new Gradle Daemon for this build (subsequent builds will be faster).
:wrapper
:init

BUILD SUCCESSFUL

Total time: 20.568 secs

The above command will create a project with type java-library. We can check the generated files in below:

$ tree
.
├── build.gradle
├── gradle
│   └── wrapper
│       ├── gradle-wrapper.jar
│       └── gradle-wrapper.properties
├── gradlew
├── gradlew.bat
├── settings.gradle
└── src
    ├── main
    │   └── java
    │       └── Library.java
    └── test
        └── java
            └── LibraryTest.java

7 directories, 8 files

From the above output, we can see the project created by gradle is following the Maven project structure. In addition, it provides a gradlew command to wrap and invoke Gradle in this project. Now let’s build the project with this command:

$ ./gradlew build

The above command will start to download Gradle into the project directory for the first time of running, and this is convenient for us to distribute our project to other people that don’t have Gradle installed on their machines. Here is the output of the above command:

Starting a new Gradle Daemon for this build (subsequent builds will be faster).
:compileJava
:processResources UP-TO-DATE
:classes
:jar
:assemble
:compileTestJava
:processTestResources UP-TO-DATE
:testClasses
:test
:check
:build

BUILD SUCCESSFUL

Total time: 16.79 secs

From the above output, we can see the classes are compiled and packaged into a jar file. The compiled files will be put into build directory. Here is the generated content of the build directory:

$ ls build
classes          libs             test-results
dependency-cache reports          tmp

The above output shows the content in build directory. We can find the final jar file in the libs directory:

$ ls build/libs/
play-with-jdbc.jar

The above jar file contains the classes in the project. Now we should configure the project to use the JDBC driver we compiled. Firstly we need to find the PostgreSQL JDBC driver we compiled. By convention, Maven puts its installed jar file into ~/.m2 directory, so I can find it here:

$ ls ~/.m2/repository/org/postgresql/postgresql/42.0.1-SNAPSHOT/
_remote.repositories           maven-metadata-local.xml       postgresql-42.0.1-SNAPSHOT.jar postgresql-42.0.1-SNAPSHOT.pom

The above postgresql-42.0.1-SNAPSHOT.jar is the library I will use in my play-with-jdbc project. Now we need to open the build.gradle file in play-with-jdbc, and add a line into the repositories section:

mavenLocal()

The above repository refers to the local ~/.m2 repository managed by Maven. Then we need to add a line into dependencies section of build.gradle:

compile 'org.postgresql:postgresql:42.0.1-SNAPSHOT'

The above line will make sure the postgresql-42.0.1-SNAPSHOT.jar can be used during the compilation process. Now that we have prepared the project to use JDBC driver to connect to the database, let’s start to write some sample code to use the JDBC driver.

Using the PostgreSQL JDBC driver

We can write a sample to use the JDBC interfaces and PostgreSQL driver to connect to the underlying database server. Here is the code:

package io.weinan.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

/**
 * Created by weli on 30/04/2017.
 */
public class DirectConnection {

    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver");

        String url = "jdbc:postgresql://localhost/weli";
        Properties props = new Properties();
        props.setProperty("user","weli");
        props.setProperty("password","");
        props.setProperty("ssl","false");

        Connection conn = DriverManager.getConnection(url, props);

        System.out.println(conn);

    }
}

The above code loads the driver into the memory of virtual machine, and connects to the database server with the information like user, password. We can put the above code into our project like this:

src/main/java/io/weinan/jdbc/DirectConnection.java

After adding the class file, we need to build the project using Gradle. To build the project, we need to add a fatJar task into build.gradle:

task fatJar(type: Jar) {
    baseName = project.name + '-all'
    from { configurations.compile.collect { it.isDirectory() ? it : zipTree(it) } }
    with jar
}

The above task can build a jar file with all the dependencies included, and we need this task to include the postgresql-42.0.1-SNAPSHOT.jar into the final jar for us. After the above task is added into build.gradle, we can build the project using the following command:

$ ./gradlew -q fatJar

The above command will build the whole project, and produce a jar file with dependencies included. Here is the output:

$ ls build/libs/
play-with-jdbc-all.jar

As the output shown above, we get the play-with-jdbc-all.jar. Now we can use the above jar to invoke our class:

$ java -cp build/libs/play-with-jdbc-all.jar io.weinan.jdbc.DirectConnection
org.postgresql.jdbc.PgConnection@6267c3bb

From the above output, we can see our code returns a connection to database, and the class that represents the connection is org.postgresql.jdbc.PgConnection. That’s all for this article. In the next article, we will examining the driver loading process to see how the PostgreSQL JDBC driver works internally.