Adventures in JDBC and the PostgreSQL JDBC driver: Part 1 - Installing PostgreSQL database and JDBC driver
Adventures in JDBC and the PostgreSQL JDBC driver: Part 1 - Installing PostgreSQL database and JDBC driver
I want to make a deep understanding on JDBC specification and how does a database vendor implement it in its driver. So I will use the PostgreSQL JDBC driver to do the investigate in this series of the articles. The first part will be focus on the installation of the PostgreSQL database server and its JDBC driver.
JDBC and the PostgreSQL JDBC driver
The fullname of JDBC is Java Database Connectivity, and it is a specification provided by JDK for the users to operate on the underlying databases. Databaes vendors need to implement the JDBC interfaces in their own driver libraries. For example, here is the PostgreSQL JDBC driver I downloaded from internet:
$ pwd
/Users/weli/projs/postgresql-jdbc-42.0-0.src
$ ls
CONTRIBUTING.md build.properties packaging release_notes.sh test-anorm-sbt
LICENSE certdir pgjdbc release_notes_filter.pl toolchains.xml
README.md codecov.yml pgjdbc-aggregate.iml settings.xml ubenchmark
backend_protocol_v4_wanted_features.md docs
The above output shows the PostgreSQL JDBC driver provided by PostgreSQL community. We can use the driver to connect to the database system and operate on databases. In this section, I’d like to introduce to you the basic usages of the PostgreSQL JDBC driver. The first step is to install the PostgreSQL database system and start the database server.
Installing the PostgreSQL database system
I am using the MacOS operation system, so I will use Homebrew to install the PostgreSQL. We can use the brew command to query the package:
$ brew info postgresql
postgresql: stable 9.6.2 (bottled), HEAD
Object-relational database system
https://www.postgresql.org/
The above command and its output shows the PostgreSQL that brew provides. Currently the version of postgresql in the repository is 9.6.2. The next step is to use brew install command to install it:
$ brew install postgresql
As the command shown above, it will install the postgresql into your system. After installing the package onto the operating system, we need to initialize the database files for it to be ready to use. First we need to create a directory to store the database files, and here is the command to do so:
$ sudo mkdir /usr/local/pgsql
The above command will create a pgsql directory to store the database files that will be generated. We need to adjust the permission of this directory to make the owner to be ourself. Here is the command to do so:
$ sudo chown weli /usr/local/pgsql
The above command will change the owner of the pgsql directory myself. Then we can start to initialize the database files. Here is the command:
$ initdb -D /usr/local/pgsql/data
The output of above command is like this:
The files belonging to this database system will be owned by user "weli".
This user must also own the server process.
The database cluster will be initialized with locales
COLLATE: C
CTYPE: UTF-8
MESSAGES: C
MONETARY: C
NUMERIC: C
TIME: C
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
creating directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /usr/local/pgsql/data -l logfile start
The output shows that the database files have been initialized, and at last it provides the command to start the database server. We can enter the provided command to start the server:
pg_ctl -D /usr/local/pgsql/data -l logfile start
If everything goes fine, we can see the output like this:
server starting
As the output shown above, the database is start. Now we can create a blank database by createdb command:
$ createdb weli
As the command shown above, I created a database named weli. Because postgresql client interface command psql will try to connect to a database with the same name as the login user by default, so it is convienient to create such a database for testing. Now we can use the psql command to connect to the database server:
$ psql -U weli
The above command will help us to login to the command line interface of the database server:
psql (9.6.2)
Type "help" for help.
weli=#
As the above output shows, I logged into the database server and connected to the weli database. Now I can use the \l command to list all the databases:
weli=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
postgres | weli | UTF8 | C | UTF-8 |
template0 | weli | UTF8 | C | UTF-8 | =c/weli +
| | | | | weli=CTc/weli
template1 | weli | UTF8 | C | UTF-8 | =c/weli +
| | | | | weli=CTc/weli
weli | weli | UTF8 | C | UTF-8 |
(4 rows)
From the above output, we can see there is a postgres database and two template databases. There are created by the initdb command by default. We will use our weli database for playing. Now we need to create a table named user_info in our database. Here is the SQL command to do so:
CREATE TABLE USER_INFO (
ID INT PRIMARY KEY NOT NULL,
NAME CHAR(50) NOT NULL,
AGE INT NOT NULL
);
We can see the table USER_INFO contains three fields: The ID field has INT type, and it is the primary key of the table; The NAME field has the type of CHAR(50); The AGE field has the type of INT. Here is how the above command executed in command line interface:
weli=# CREATE TABLE USER_INFO (
weli(# ID INT PRIMARY KEY NOT NULL,
weli(# NAME CHAR(50) NOT NULL,
weli(# AGE INT NOT NULL
weli(# );
CREATE TABLE
From the above output, we can see the CREATE TABLE command is executed without any problem. Now we can check the existance of the user_info table with \dt command:
weli=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+-------
public | user_info | table | weli
(1 row)
From the above command output, we can see the structure of the user_info. Until now we have prepared our database server. The next step is to get the JDBC driver.
Fetching the PostgreSQL JDBC driver
There are several ways to get the PostgreSQL JDBC driver. For common users, the most straigtforward way is to download the PostgreSQL JDBC driver from its website1. However, our goal in this chapter is to learn the implementation of the PostgreSQL JDBC driver, so we need to fetch the source code of the driver. The source code repository of the PostgreSQL JDBC driver is hosted on Github2. We can clone the pgjdbc repository from Github, and here is the command to do so:
$ git clone https://github.com/pgjdbc/pgjdbc.git
The cloned repository is like this:
$ ls pgjdbc
CONTRIBUTING.md codecov.yml release_notes_filter.pl
LICENSE docs settings.xml
README.md packaging ssltest.properties
backend_protocol_v4_wanted_features.md pgjdbc test-anorm-sbt
build.properties pom.xml toolchains.xml
certdir release_notes.sh ubenchmark
We can see the PostgreSQL JDBC driver is Maven based project. Let’s build it and install it to our local maven repository. Here is the command to compile and install the pgjdbc project:
mvn -Dmaven.test.skip=true install
The above command will compile the project and bypass all the tests, and then it will install the generated jar files into our local maven repository. Here is the output of the above command:
[INFO] PostgreSQL JDBC driver - JDBC 4.2 .................. SUCCESS [ 3.099 s]
[INFO] PostgreSQL JDBC driver - benchmarks ................ SUCCESS [ 44.638 s]
[INFO] PostgreSQL JDBC driver aggregate ................... SUCCESS [ 0.003 s]
After successfully compiling and installing the PostgreSQL JDBC driver, we can start to use it. I’ll put the usage of the driver in next article.