Creating a todo app with JDBC

Welcome to another post, today we will be looking about the JDBC, what is, how
can we use one, and most important creating a simple java application abouta todos app
that not only connects to a postgresql database.

But also implementing a really basic ORM similar to the eloquen one from laravel.

Before starting let's look about what is a JDBC.

What is JDBC?

JDBC is the Java Database Connectivity api, that allows us to access virtually
any data source, is comprised of two packages:

• java.sql
• javax.sql

That are installed by default when we get the Java SE 8.

How can we use one?

For use the JDBC api with a particular database management system, we need
a JDBC technology-based driver to mediate between JDBC tehcnology and the
database.

This driver can be written in pure java or in a mixture or java and java native
interface (JNI) native methods.

Today we will be using the Postgresql JDBC driver
for our project.

Requirements

For this project you will be need the next:

• Maven
• Java
• Postgresql (You can use docker)

Creating the project

For creating a new project with maven use the next command

mvn archetype:generate -DgroupId=com.mycompany.app -DartifactId=my-app -DarchetypeArtifactId=maven-archetype-quickstart -DarchetypeVersion=1.5 -DinteractiveMode=false

Here is the link of the docs if you want to learn more about this part Maven in 5 minutes

After the commands end, and you enter the folder let's create the database.

Creating the database

Postgresql installed in the OS

If you have installed postgresql in your system you only need to create a new database and
create the next table

create table tbl_jd_todos (
    id serial primary key,
    name varchar(255) not NULL,
    completed smallint default 0 not null,
    created_at timestamp default now()
)

After creating the table go to Creating an env file

Using docker

In case you want to use docker here is the docker-compose.yml file that we will
need:

services:
  jodos_db:
    image: postgres:15
    container_name: jodos_db
    env_file:
        - .env
    ports:
      - "${DB_PORT}:5432"
    volumes:
      - jodo-data:/var/lib/postgresql/data

volumes:
  jodo-data:
    name: jodo-data

We will need to create an .env file to specify the user, password, db and port of our app
so let's create one.

Creating an env file

In this section will be creating the .env file for our project.
For that let's create the file at the root of the project .env with
the next values:

DB_PORT=""
POSTGRES_USER=""
POSTGRES_PASSWORD=""
POSTGRES_DB=""

Fill the values of the env file and let's continue:

In case you are using docker, please run docker compose up and ensure
that you can connect to the database using a database manager tool like dbeaver

Installing dependencies

Let's continue and install all the dependencies for this project.

We will be using two the postgres-jdbc
and the dotenv java.

Let go to the above links and add the dependencies to your poml file. Your poml file
should be look like these:

 <dependencies>
     <!-- rest of dependencies -->
 <!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.7.5</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/io.github.cdimascio/dotenv-java -->
    <dependency>
        <groupId>io.github.cdimascio</groupId>
        <artifactId>dotenv-java</artifactId>
        <version>3.1.0</version>
    </dependency>
 </dependencies>

The java files for database and orm implementation

We will need two important things in this project one file for getting a connection
to our database and the base class for our models.

Let's begin creating the database file.

Create a DB.java file with the next code:

package com.caresle.jodos;

import java.sql.*;

import io.github.cdimascio.dotenv.Dotenv;

/**
 * DB
 */
public class DB {
  static String url;

  static {
    Dotenv dotenv = Dotenv.load();
    String port = dotenv.get("DB_PORT");
    String database = dotenv.get("POSTGRES_DB");
    String username = dotenv.get("POSTGRES_USER");
    String password = dotenv.get("POSTGRES_PASSWORD");
    url = "jdbc:postgresql://localhost:" + port + "/" + database + "?user=" + username + "&password=" + password; 
  }


  private static Connection connection;

  private DB() {}

  public static Connection getConnection() throws SQLException {
    if (connection == null || connection.isClosed()) {
      connection = DriverManager.getConnection(url);
    }
    return connection;
  }
}

In this file we are creating the url for connecting to our database, also definning
a method to get the connection of the database to be used by the model.

Next is time to create Model.java file.

This file will be in charge of providing the general methods to interact with the database
for our models. First of all this class will be an abstract class and we are going to define
the next props:

protected static String table;
protected Map<String, Object> attributes = new HashMap<>();

The first prop is the table name, and the second is the attributes of the model.

Let's now create the methods for set and get the attributes.

public void set(String key, Object value) {
  attributes.put(key, value);
}

public Object get(String key) {
  return attributes.get(key);
}

Remember that Object is the base class of all the java classes, so we can use it
to get the value of the attribute.

The first thing that we will create is the delete method, because is the easiest one.

public boolean delete() throws SQLException {
  String sql = "DELETE FROM " + table + " WHERE id = ?";

  try (Connection connection = DB.getConnection()) {
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setObject(1, attributes.get("id"));
    return statement.executeUpdate() > 0;
  }
}

Here we are usingt the id attribute of the model to delete the record from the database.
Also we are propagating the exception to the caller.

Now we will be creating a helper method for set the parrams of the model.

private void setParameters(PreparedStatement statement) throws SQLException {
  int index = 1;

  Set<String> keys = attributes.keySet();
  keys.remove("id");

  for (String key: keys) {
    statement.setObject(index++, attributes.get(key));
  }
}

This method will be used in the insert and update methods. So let's create the insert method.

private boolean insert() throws SQLException {
  String columns = String.join(", ", attributes.keySet());
  String valuesPlaceholder = String.join(", ", Collections.nCopies(attributes.size(), "?"));

  String sql = "INSERT INTO " + table + " (" + columns + ") VALUES (" + valuesPlaceholder + ")";

  try (Connection connection = DB.getConnection()) {
    PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    setParameters(statement);

    int affectedRows = statement.executeUpdate();

    if (affectedRows > 0) {
      ResultSet resultSet = statement.getGeneratedKeys();

      if (resultSet.next()) {
        attributes.put("id", resultSet.getLong(1));
      }
    }
    return affectedRows > 0;
  }
}

And now the update method.

private boolean update() throws SQLException {
  int id = (int)attributes.get("id");
  Set<String> keys = attributes.keySet();

  keys.remove("id");
  String setClause = String.join(" = ?, ", keys) + " = ?";
  String sql = "UPDATE " + table + " SET " + setClause + " WHERE id = ?";

  try ( Connection connection = DB.getConnection()) { 
    PreparedStatement statement = connection.prepareStatement(sql);
    setParameters(statement);
    statement.setObject(keys.size() + 1, id);
    return statement.executeUpdate() > 0;
  }
}

Finally we will create the method for use update and insert, because they are private and the only difference is
the id attribute.

public boolean save() throws SQLException {
  if (attributes.containsKey("id")) {
      return update();
  }

  return insert();
}

And here are some static methods for findById and findAll.

FindById

public static <T extends Model> T findById(Class<T> modelClass, long id) {
  String sql = "SELECT * FROM " + table + " WHERE id = ?";

  try (Connection connection = DB.getConnection()) {
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setLong(1, id);
    ResultSet resultSet = statement.executeQuery();

    if (resultSet.next()) {
      try {
        T instance = modelClass.getDeclaredConstructor().newInstance();

        ResultSetMetaData metaData = resultSet.getMetaData();

        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            instance.set(metaData.getColumnName(i), resultSet.getObject(i));
        }

        return instance;
      } catch (Exception e) {
        throw new RuntimeException("Error creating instance of " + modelClass.getName(), e);
      }
    }
  } catch (SQLException e) {
    System.err.println("SQL ERROR" + e.getMessage());
    e.printStackTrace();
  }
  return null;
}

FindAll

public static <T extends Model> ArrayList<T> all(Class<T> modelClass) {
  ArrayList<T> models = new ArrayList<>();
  try (Connection connection = DB.getConnection()) {
    String sql = "SELECT * FROM " + table;
    PreparedStatement statement = connection.prepareStatement(sql);

    ResultSet resultSet = statement.executeQuery();

    while (resultSet.next()) {
      try {
        T instance = modelClass.getDeclaredConstructor().newInstance();

        ResultSetMetaData metaData = resultSet.getMetaData();

        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            String columnName = metaData.getColumnName(i);
            Object value = resultSet.getObject(i);
            instance.set(columnName, value);
        }

        models.add(instance);
      } catch (Exception e) {
        throw new RuntimeException("Error creating instance of " + modelClass.getName(), e);
      }
    }
  } catch (SQLException e) {
    System.err.println("SQL ERROR" + e.getMessage());
    e.printStackTrace();
  }
  return models;
}

And here is the example of creating a Todo model.

package com.caresle.jodos;

import java.util.ArrayList;

/**
 * Todo
 */
public class Todo extends Model {
  static {
    table = "tbl_jd_todos";
  }

  public Todo() {}

  public static Todo findById(long id) {
    return (Todo) Model.findById(Todo.class, id);
  }

  public static ArrayList<Todo> all() {
    return (ArrayList<Todo>) Model.all(Todo.class);
  }
}

Creating the menu for the app

Let's create the class UI.java for handle the menu and their actions

package com.caresle.jodos;

import java.util.ArrayList;
import java.util.Scanner;

public class Ui {
  static int LIST = 1;
  static int CREATE = 2;
  static int EDIT = 3;
  static int COMPLETE = 4;
  static int DELETE = 5;
  static int EXIT = 6;

  public static void print() {
    System.out.println("=\tSELECT THE OPTION\t=");
    System.out.println("1) List all todos");
    System.out.println("2) Create a new todo");
    System.out.println("3) Edit a todo");
    System.out.println("4) Mark a todo as completed");
    System.out.println("5) Delete a todo");
    System.out.println("6) Exit");
  }

  public static void listTodos() {
    ArrayList<Todo> todos = Todo.all();

    for (Todo todo: todos) {
      System.out.println(todo);
    }
  }

  public static void createTodo(Scanner scanner) {
    System.out.println("Enter the todo:");
    scanner.nextLine();
    String todo = scanner.nextLine();
    try {
      Todo newTodo = new Todo();
      newTodo.set("name", todo);
      newTodo.set("completed", 0);
      newTodo.save();
    } catch (Exception e) {
      System.err.println("ERROR: " + e.getMessage());
    }
  }

  public static void editTodo(Scanner scanner) {
    System.out.println("Enter the id of the todo to edit:");
    long id = scanner.nextLong();
    Todo todo = Todo.findById(id);
    System.out.println("Enter the new todo:");
    scanner.nextLine();
    String todoName = scanner.nextLine();
    todo.set("name", todoName);
    try {
      todo.save();
    } catch (Exception e) {
      System.err.println("ERROR: " + e.getMessage());
    }
  }

  public static void completeTodo(Scanner scanner) {
    System.out.println("Enter the id of the todo to complete:");
    long id = scanner.nextLong();
    Todo todo = Todo.findById(id);
    todo.set("completed", 1);
    try {
      todo.save();
    } catch (Exception e) {
      System.err.println("ERROR: " + e.getMessage());
    }
  }

  public static void deleteTodo(Scanner scanner) {
    System.out.println("Enter the id of the todo to delete:");
    long id = scanner.nextLong();
    Todo todo = Todo.findById(id);
    try {
      todo.delete();
    } catch (Exception e) {
      System.err.println("ERROR: " + e.getMessage());
    }
  }
}

Joining all the parts

Now in the App.java file we will add all the parts to create the app.

package com.caresle.jodos;

import java.util.Scanner;

public class App {
  public static void main(String[] args) {
    Scanner scanner = new Scanner(System.in);
    int option = 0;

    while (option != Ui.EXIT) {
      Ui.print();
      option = scanner.nextInt();

      if (option == Ui.LIST) {
        Ui.listTodos();
        continue;
      }

      if (option == Ui.CREATE) {
        Ui.createTodo(scanner);
        continue;
      }

      if (option == Ui.EDIT) {
        Ui.editTodo(scanner);
        continue;
      }

      if (option == Ui.COMPLETE) {
        Ui.completeTodo(scanner);
        continue;
      }

      if (option == Ui.DELETE) {
        Ui.deleteTodo(scanner);
        continue;
      }
    }

    scanner.close();
  }
}

Conclusion

With this post we have learned how to create a simple java application with jdbc and
how to create a simple orm for our models.