How to persist data to PostgreSQL in Java

Java is perhaps the most widely used programming language nowadays. It’s robustness and platform-independent nature enables Java based applications to run on mostly anything. As is the case with any application, we need to store our data in some sort of reliable way – this need called databases to life.

In Java database connections are implemented by JDBC (Java Database Connectivity API), that let’s the programmer handle different kind of databases in almost the same way, which makes our lives much easier when we need to save or read data from a database.

In this tutorial we will create an example Java application that will be able to connect to a PostgreSQL database instance, and write data into it. To check that our data insertion is successful, we’ll also implement reading back and print the table we inserted data into.

In this tutorial you will learn:

  • How to setup the database for the application
  • How to import PostgreSQL JDBC Driver into your project
  • How to insert data into the database
  • How to run a simple query to read a database table’s content
  • How to print fetched data

Results of running the application

Results of running the application.

Software Requirements and Conventions Used

Software Requirements and Linux Command Line Conventions
Category Requirements, Conventions or Software Version Used
System Ubuntu 20.04
Software NetBeans IDE 8.2, PostgreSQL 10.12, jdk 1.8
Other Privileged access to your Linux system as root or via the sudo command.
Conventions # – requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command
$ – requires given linux commands to be executed as a regular non-privileged user

The setup



For the purposes of this tutorial we only need one workstation (desktop or laptop) to install all needed components. We will not cover installing JDK, the Netbeans IDE, or the installation of PostgreSQL database on the lab machine. We assume that the database called exampledb is up and running, and we can connect, read and write using password authentication, with the following credentials:

Username: exampleuser
Password: ExamplePass

This is an example setup, use strong passwords in a real world scenario! The database is set to listen on localhost, which will be needed when we construct the JDBC connection URL.

Our application’s main purpose is to show how to write to and read from the database, so for the valuable information we are so eager to persist, we’ll simply pick a random number between 1 and 1000, and will store that information with a unique ID of the calculation, and the exact time the data is recorded in the database.

The ID and the time of recording will be provided by the database, which let’s our application work on the real issue only (providing a random number in this case). This is on purpose, and we’ll cover the possibilities of this architecture at the end of this tutorial.

Setting up the database for the application

We have a running database service, and a database called exampledb we have rights to work on with the above mentioned credentials. To have a place where we can store our precious (random) data, we need to create a table, and also a sequence that will provide unique identifiers in a convenient way. Consider the following SQL script:

create sequence resultid_seq start with 0 increment by 1 no maxvalue minvalue 0 cache 1;
alter sequence resultid_seq owner to exampleuser;
create table calc_results (
        resid numeric primary key default nextval('resultid_seq'::regclass),
        result_of_calculation numeric not null,
        record_date timestamp default now()
);
alter table calc_results owner to exampleuser;

These instructions should speak for themselves. We create a sequence, set the owner to exampleuser, create a table called calc_results (standing for “calculation results”), set resid to be filled automatically with the next value of our sequence on every insert, and define result_of_calculation and record_date columns that will store our data. Finally, the table’s owner is also set to exampleuser.

To create these database objects, we switch to postgres user:

$ sudo su - postgres

And run the script (stored in a text file called table_for_java.sql) against the exampledb database:

$ psql -d exampledb < table_for_java.sql 
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE

With this, our database is ready.

Importing PostgreSQL JDBC Driver into the project



To build the application, we’ll use NetBeans IDE 8.2. The first few steps are handwork. We choose file menu, create new project. We’ll leave the defaults on the next page of the wizard, with Category of “Java” and Project on “Java Application”. We’ll press next. We give the application a name (and optionally define a non-default location). In our case it will be called persistToPostgres. This will make the IDE create a base Java project for us.

On the Projects pane we right-click “Libraries” and select “Add Library…”. A new window will show up, where we search and select PostgreSQL JDBC Driver, and add it as a library.

Adding the PostgreSQL JDBC Driver to the project

Adding the PostgreSQL JDBC Driver to the project.

Understanding the source code

We now add the following source code to our application’s main class, PersistToPostgres:

package persisttopostgres;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.ThreadLocalRandom;

public class PersistToPostgres {
    public static void main(String[] args) {
        int result = ThreadLocalRandom.current().nextInt(1, 1000 + 1);
        System.out.println("Result of the hard-to-solve calculation is: " + result);
        System.out.println("-----PostgreSQL JDBC Connection testing------");
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException cnfe) {
            System.out.println("No PostgreSQL JDBC Driver in library path!");
            cnfe.printStackTrace();
            return;
        }
        System.out.println("PostgreSQL JDBC Driver registered!");
        Connection conn = null;
        
        try {            
            conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/exampledb", "exampleuser", "ExamplePass");
        } catch (SQLException sqle) {
            System.out.println("Connection failed! Check output console");
            sqle.printStackTrace();
            return;
        }
        if (conn != null) {
            System.out.println("Database connection established");
            // building query
            try {
                Statement st = conn.createStatement();
                st.executeUpdate("Insert into calc_results (result_of_calculation) values(" + result + ")" );
                ResultSet rs = st.executeQuery("select resid, result_of_calculation, record_date from calc_results");
                System.out.println ("Results recorded in exampledb follows:\n-----------------------\n");
                while (rs.next()) {
                    System.out.println (rs.getString("resid") + "\t" + rs.getString("result_of_calculation") + "\t" + rs.getString("record_date"));
                }
                // clean on exit
                st.close();
                conn.close();
            } catch (SQLException sqle2) {
                System.out.println ("Error on query");
                sqle2.printStackTrace();
            }
        } else {
            System.out.println("Failed to make connection!");
        }
    }
}


  • At line 12 we calculate a random number, and store it in the result variable. This number represents the result of a heavy calculation that
    we need to store in the database.
  • At line 15 we try to register the PostgreSQL JDBC Driver. This will result in an error if the application does not find the driver at runtime.
  • At line 26 we build the JDBC connection string using the hostname the database is running on (localhost), the port the database listening on (5432, the default port for PostgreSQL), the database name (exampledb), and the credentials mentioned at the beginning.
  • At line 37 we execute the insert into SQL statement that inserts the value of the result variable into the result_of_calculation column of the calc_results table. We only specify the value of this single columns, so defaults apply: resid is fetched from the sequence we
    set, and record_date defaults to now(), which is the database time at the moment of the transaction.
  • At line 38 we construct a query that will return all data contained in the table, including our insert in the previous step.
  • From line 39 we present the data retrieved by printing it in a table-like fashion, free up resources, and exit.

Running the application

We can now clean, build and run the persistToPostgres application, from the IDE itself, or from command line. To run from the IDE, we can use the “Run Project” button on top. To run it from the command line, we need to navigate to the dist directory of the project, and invoke the JVM with the JAR package as an argument:

$ java -jar persistToPostgres.jar 
Result of the hard-to-solve calculation is: 173
-----PostgreSQL JDBC Connection testing------
Database connection established
Results recorded in exampledb follows:
-----------------------

0       145     2020-05-31 17:40:30.974246

Command line runs will provide the same output as the IDE console, but what is more important here is that each run (be it from the IDE or the command line) will insert another row into our database table with the given random number calculated at each run.

This is why we will also see a growing number of records in the application’s output: each run grows the table with one row. After a few runs we’ll see a long list of result rows in the table.

Database output shows the results of every execution of the application

Database output shows the results of every execution of the application.

Conclusion

While this simple application does hardly have any real world usage, it is perfect to demonstrate some real important aspects. In this tutorial we said we do an important calculation with the application, and inserted a random number each time, because the purpose of this tutorial is to show how to persist the data. This goal we completed: with each run, the application exits, and the results of the internal calculations would be lost, but the database preserves the data.

We executed the application from a single workstation, but if we would really need to solve some complicated calculation, we would only need to change the database connect URL to point to a remote machine running the database, and we could start the calculation on multiple computers at the same time creating hundreds or thousands of instances of this application, maybe solving small pieces of a greater puzzle, and store the results in a persistent way, enabling us to scale our computing power with a few lines of code, and a bit of planning.

Why planning is needed? To stay with this example: if we would not leave assigning row identifiers or timestamping to the database, our application would have been much bigger, much slower, and much more full of bugs – some of them only surface when we run two instance of the application at the same moment.



Comments and Discussions
Linux Forum