Java Programming: JDBC

Setup files and directories

Container files

create project a directory then create file and folder structure like this:

1
2
3
4
5
6
7


.
├── .devcontainer
│   ├── docker-compose.yaml
│   └── Dockerfile
└── dat
    ├── data.sql
    └── load-db.sh

The docker-compose file contain:

 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


version: '3.8'
volumes:
  postgres-data:
services:
  app:
    container_name: javadev
    build: 
      context: .
      dockerfile: Dockerfile
    environment:     
        POSTGRES_PASSWORD: P@ssw0rd!
        POSTGRES_USER: localdbuser
        POSTGRES_DB: localdb
        POSTGRES_HOSTNAME: postgresdb

    volumes:
      - ../..:/workspaces:cached      
    command: sleep infinity
    network_mode: service:db
  db:
    container_name: postgresdb
    image: postgres:latest
    restart: unless-stopped
    volumes:
      - postgres-data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: P@ssw0rd!
      POSTGRES_USER: localdbuser
      POSTGRES_DB: localdb
    ports:
      - 5432:5432

and then the Dockerfile

1
2
3


FROM mcr.microsoft.com/devcontainers/java:1-21-bullseye
RUN sudo apt-get update && sudo apt-get install -y postgresql-client
RUN su vscode -c "umask 0002 && . /usr/local/sdkman/bin/sdkman-init.sh && sdk install maven"

Database setup files

copy this script to the data.sql

and here is the script load-db.sh to restore the data to the database

1
2
3
4
5
6
7


#!/usr/bin/env bash
set -euo pipefail

cd "$(dirname "$0")"

# Execute psql inside the PostgreSQL container
docker exec -i postgresdb psql -U localdbuser -d localdb -a -f - < ./data.sql

Running the configuration

run the docker compose docker compose up
run the script chmod +x ./dat/load-db.sh then ./dat/load-db.sh
initialize the project in the root directory using maven

1

mvn archetype:generate -DgroupId=com.ananto.lil -DartifactId=learning-jdbc -DarchetypeArtifacId=maven-archetype-quickstart -DarchetypeVersion=1.5 -DinteractiveMode=false

add postgres dependency in the pom.xml file

1
2
3
4
5
6
7
8
9


  <dependencies>
    ...
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>42.6.0</version>
    </dependency>
    ...
  </dependencies>

Data Access Basic Tutorial

Create connection setup

create a package com.ananto.lil.data.util and a class DatabaseUtils in it.

 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


package com.ananto.lil.data.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Logger;

public class DatabaseUtils {
    private static final String URL = "jdbc:postgresql://localhost:5432/localdb";
    private static final String USERNAME = "localdbuser";
    private static final String PASSWORD = "P@ssw0rd!";
    private static final Logger LOGGER = Logger.getLogger(DatabaseUtils.class.getName());
    private static final String exceptionFormat = "exception in %s, message: %s, code: %s";
    private static Connection connection;

    public static Connection getConnection() {
        if (connection == null) {
            synchronized(DatabaseUtils.class) {
                if (connection==null) {
                    try {
                        connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
                    } catch (SQLException e) {
                        handleSQLException("DatabaseUtils.getConnection", e, LOGGER);
                    }
                }
            }
        }
        return connection;
    }

    public static void handleSQLException(String methodName, SQLException e, Logger logger) {
        logger.warning(String.format(exceptionFormat, methodName, e.getMessage(), e.getErrorCode()));
        throw new RuntimeException(e);
    }

}

Package entity

create class Service in package entity

 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


package com.ananto.lil.data.entity;

import java.math.BigDecimal;
import java.util.UUID;

public class Service {
    private UUID serviceId;
    private String name;
    private BigDecimal price;
    public UUID getServiceId() {
        return serviceId;
    }
    public void setServiceId(UUID serviceId) {
        this.serviceId = serviceId;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public BigDecimal getPrice() {
        return price;
    }
    public void setPrice(BigDecimal price) {
        this.price = price;
    }
    @Override
    public String toString() {
        return "Service [serviceId=" + serviceId + ", name=" + name + ", price=" + price + "]";
    }

}

Package DAO

Create interface Dao inside package dao

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


package com.ananto.lil.data.dao;

import java.util.List;
import java.util.Optional;
import java.util.UUID;

public interface Dao <T, Id extends UUID> {
    List<T> getAll();
    T create(T entity);
    Optional<T> getOne(Id id);
    T update(T entity);
    void delete(Id id);
}

Then create class ServiceDao in the same package

 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
70


package com.ananto.lil.data.dao;

import com.ananto.lil.data.entity.Service;
import com.ananto.lil.data.util.DatabaseUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.UUID;
import java.util.logging.Logger;

public class ServiceDao implements Dao<Service, UUID>{

    private static final Logger LOGGER = Logger.getLogger(ServiceDao.class.getName());
    private static final String GET_ALL = "select service_id, name, price from wisdom.services";

    @Override
    public Service create(Service entity) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void delete(UUID id) {
        // TODO Auto-generated method stub
        
    }

    @Override
    public List<Service> getAll() {
        List<Service> services = new ArrayList<>();
        Connection connection = DatabaseUtils.getConnection();
        try(Statement statement = connection.createStatement()) {
            ResultSet rs = statement.executeQuery(GET_ALL);
            services = this.processResultSet(rs);
        } catch (SQLException e) {
            DatabaseUtils.handleSQLException("ServiceDao.getAll", e, LOGGER);
        }
        return services;
    }

    @Override
    public Optional<Service> getOne(UUID id) {
        // TODO Auto-generated method stub
        return Optional.empty();
    }

    @Override
    public Service update(Service entity) {
        // TODO Auto-generated method stub
        return null;
    }

    private List<Service> processResultSet(ResultSet rs) throws SQLException {
        List<Service> services = new ArrayList<>();
        while(rs.next()) {
            Service service = new Service();
            service.setServiceId((UUID)rs.getObject("service_id", UUID.class));
            service.setName(rs.getString("name"));
            service.setPrice(rs.getBigDecimal("price"));
            services.add(service);
        }
        return services;
    }

}

Testing the code in App

inside App class put this code

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


package com.ananto.lil;

import java.util.List;

import com.ananto.lil.data.dao.ServiceDao;
import com.ananto.lil.data.entity.Service;

public class App {
    public static void main(String[] args) {
        ServiceDao serviceDao = new ServiceDao();
        List<Service> services = serviceDao.getAll();
        System.out.println("*** SERVICES ***");
        System.out.println("\n*** GET_ALL ***");
        services.forEach(System.out::println);
    }
}