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
Software Requirements and Conventions Used
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.
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 theresult
variable into theresult_of_calculation
column of thecalc_results
table. We only specify the value of this single columns, so defaults apply:resid
is fetched from the sequence we
set, andrecord_date
defaults tonow()
, 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.
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.