Adventures in JDBC and the PostgreSQL JDBC driver: Part 2 - Using JDBC driver to communicate with database server
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.