Working with Databases in Java

In this lecture note, you will learn how to connect your Java application into a database and perform CRUD (create, read, update, and delete) operations.

We will be using PostgreSQL as our example database server. Postgres is a powerful open source object-relational database system that has been in active development since 1986. A large number of organizations and businesses use PostgreSQL as their primary database for core products including Microsoft, Google, Amazon AWS, Instagram, Reddit, and VMware.↗

While we will use PostgreSQL database as an example, the same concepts and principles will also apply to any major relational database that has a supported JDBC driver (e.g., MySQL/MariaDB, Oracle, DB2, and Microsoft SQL Server).

To do list application

We will create a simple todo list application with tasks that can be marked as done.

This lecture notes was implemented on PostgreSQL 15.1 and Java 18.0. Other versions may still work given the fact that the used interfaces/APIs have been very stable.

1. Start the database server and create the database

The first step is to download the database server for postgres. Installing postgres itself is enough to work with the database server as it comes with a CLI client tool called psql. However, it’s often more beginner friendly to interact with the database using a GUI tool. pgAdmin is a web-based GUI tool used to interact with Postgres databases. In short, we will install the following:

1. Postgres database server
2. pgAdmin web interface
3. pgJDBC driver

They should be all installed using the Postgres installer, so you do not need to install them individually. Here’s how:

1. Install postgres: Download and install postgres from postgresql.org. Follow the on screen instructions and check all options including pgAdmin4 and stack builder.

• Enter the root password. You will need to remember this password

  

• Select all default options to complete the installation

2. Install PostgreSQL JDBC Driver: Next, the installer will launch stack builder, which should ask you to select additional tools and drivers to install with your postgres installation. We will need to install the PostgreSQL JDBC Driver, which allows Java programs to connect to a PostgreSQL database.

3. Start pgAdmin: Enter the root password you specified during installation. Next, select the database server from the left pane and enter the same root password.

4. Create a database: Expand the database server (Postgres 15) and right click to create a database and selecting a database name (e.g., tododb).

5. Create a table: On pgAdmin, expand the database tododb -> Schemas -> public -> Right click on Tables and select create Table. Create a table with the name (e.g., todo) and the following columns:

2. Crate a Maven project and add postgresql dependency

1. Create a maven project: We will create a maven project in the IDE using the quickstart archtype, maven-archetype-quickstart, which generates sample Maven project. For example in Intellij IDEA, this looks like:

2. Add the postgresql dependency into your pom.xl file.

• Open the pom.xml file and add the Postgres dependency:

  <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>42.5.1</version>
  </dependency>
  

• Right click on the pom.xml file and select Maven -> Reload project to download the new dependency.

3. Implement the Java database connection and clients

• Create the database connection class with the name DBConnection and replace your username and password values with the one you chose in the first step.

Note: The use of hard-coded passwords and secrets increases the possibility of security breaches in your application. You should never embed passwords and other secrets (e.g., API keys) in your source code. See the extra credit activity at the end of this note.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

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

public class DBConnection{
  private final String url;
  private final int port;
  private final String dbName;
  private Connection connection;

  private static DBConnection instance;

  private DBConnection() throws SQLException {
    this.dbName = "tododb";
    this.port = 5432;
    this.url = "jdbc:postgresql://localhost:" + Integer.toString(this.port) + "/" + this.dbName;
    Properties props = new Properties();
    props.setProperty("user","postgres");
    props.setProperty("password","secret");
    props.setProperty("ssl","false");
    this.connection = DriverManager.getConnection(url, props);
  }

  public Connection getConnection() {
    return this.connection;
  }

  public static DBConnection getInstance() throws SQLException {
    if(instance == null){
      instance = new DBConnection();
    }
    else if (instance.getConnection().isClosed()) {
      instance = new DBConnection();
    }
    return instance;
  }
}

• Create a client class named Task with the following implementation:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

import java.sql.*;

public class Task {
    private String name;
    private boolean isComplete;

    public Task(String name, boolean isComplete){
        this.name = name;
        this.isComplete = isComplete;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public boolean isComplete() {
        return isComplete;
    }

    public void setComplete(boolean complete) {
        isComplete = complete;
    }

    public void insertTask(){
        try {
            Connection dbConnection = DBConnection.getInstance().getConnection();
            Statement stmt = dbConnection.createStatement();
            PreparedStatement insertStmt =
                    dbConnection.prepareStatement("INSERT INTO todo (task, done) VALUES (?, ?);");
            insertStmt.setString(1, this.name);
            insertStmt.setBoolean(2, (this.isComplete));
            int rows = insertStmt.executeUpdate();
            System.out.println("Rows affected: " + rows);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void retrieveTasks(){
        try {
            Connection dbConnection = DBConnection.getInstance().getConnection();
            Statement stmt = dbConnection.createStatement();
            String query = "SELECT id, task, done FROM todo";
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next()){
                //Display values
                String row = "ID: " + rs.getInt("id") +
                        " Task: " + rs.getString("task") +
                        " Done: " + rs.getBoolean("done") + "\n";
                System.out.print(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void updateTask(){

    }

    public String toString(){
        return "Task: " + this.name + "\nDone: " + (this.isComplete ? "1": "0");
    }
}

• Add the following code to the main class that drives the program:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

import java.sql.SQLException;
public class App 
{
    public static void main(String[] args) {
        try {
            DBConnection db = DBConnection.getInstance();
            // Insert
            Task t = new Task("Do the laundry.", false);
            t.insertTask();
            // Retrieve all tasks
            t.retrieveTasks();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

• Run the program: You should see the following output:

Rows affected: 1
ID: 1 Task: Do the laundry. Done: false

• Open pgAdmin to see the values in the database:
• Right click on the todo table and select View/Edit Data -> All Rows
  • 
• You should see the values in the database table as listed below:
  • 
• You can also achieve the same result by executing SQL queries directly in pgAdmin using the Query Tool: Right Click on the database -> Query Tool and run the following SQL query:

  SELECT * FROM todo;
  

Extra credit

• Remove the hardcoded database credentials in the DBConnection.java class in the highlighted code lines (19-20) and use environment variables.
• Implement the update method (the highlighted code in the Task class)
• Add a delete method to delete a todo item by its id.