jabrena / spring-boot-relational-database-poc Goto Github PK

View Code? Open in Web Editor NEW

A POC to review different ways to implement the data access layer for a relational database.

License: Apache License 2.0

Java 67.23% PLpgSQL 32.24% Dockerfile 0.26% Shell 0.26%

spring-boot-relational-database-poc's Introduction

Spring Boot - Relational Database - POC

A POC to review different ways to implement the data access layer for a relational database.

Entity Relational Diagram (ERD)

The Sakila database is a nicely normalised schema modelling a DVD rental store, featuring things like films, actors, film-actor relationships, and a central inventory table that connects films, stores, and rentals.

Features

Feature 1: Simple Query / List movies starting by A
Feature 2: CRUD / CRUD for the Entity Actor
Feature 3: Transactions with @Transactional
Feature 4: Complex queries including join operations
Feature 5: Distributed transactions in Modular Monoliths

Container stats:

CONTAINER ID   NAME                                CPU %     MEM USAGE / LIMIT     MEM %     NET I/O           BLOCK I/O         PIDS
73f16a8c3ad4   spring-boot-jdbc                    0.39%     171.3MiB / 7.764GiB   2.15%     18.6kB / 16.7kB   176kB / 676kB     38
01017c744be9   spring-data-jpa                     0.32%     223.7MiB / 7.764GiB   2.81%     42.6kB / 24.1kB   180kB / 651kB     39
357fe0f21c31   spring-data-jdbc                    0.36%     151.1MiB / 7.764GiB   1.90%     15.5kB / 13.3kB   156kB / 643kB     38
2eec46ce99d1   spring-boot-mybatis                 0.30%     176.5MiB / 7.764GiB   2.22%     1.23kB / 0B       0B / 651kB        38
ce37c7bf3509   spring-boot-postgresql-pgadmin-1    0.08%     141.8MiB / 7.764GiB   1.78%     415kB / 3.33MB    20.5kB / 12.3MB   12
967f606ef00a   spring-boot-jooq                    0.43%     207.9MiB / 7.764GiB   2.61%     15.5kB / 13.6kB   57.3kB / 668kB    38
c2305a197f88   spring-boot-postgresql-sakiladb-1   0.00%     84.61MiB / 7.764GiB   1.06%     192kB / 244kB     885kB / 426kB     48

How to build in local

sdk env
mvn verify

How to run the Docker Compose

cd docs/schema-spy/
docker build . -f Dockerfile -t schema-spy-local
cd ../../
docker-compose -f docker-compose.yml up -d
sleep 50
curl http://localhost:8081/api/v1/films
curl http://localhost:8082/api/v1/films
curl http://localhost:8083/api/v1/films
curl http://localhost:8084/api/v1/films
curl http://localhost:8085/api/v1/films
docker-compose -f docker-compose.yml stop

How to run pgAdmin?

docker-compose -f docker-compose.yml up
http://localhost:5050/
docker-compose -f docker-compose.yml stop

Note: Review the docker compose login & establish the connection

Dev Approach

Run a Docker image with the Schema + a Small Testing Data to develop the feature that you need
Develop the SQL Query
Implement the solution in your code

Other commands

mvn versions:display-dependency-updates
mvn versions:display-plugin-updates

References

Fetch single entity based on primary key Fetch list of entities based on condition Save new single entity and return primary key Batch insert multiple entities of the same type and return generated keys Update single existing entity - update all fields of entity at once Fetch many-to-one relation (Company for Department) Fetch one-to-many relation (Departments for Company) Update entities one-to-many relation (Departments in Company) - add two items, update two items and delete one item - all at once Complex select - construct select where conditions based on some boolean conditions + throw in some JOINs Call stored procedure/function and process results Execute query using JDBC simple Statement (not PreparedStatement) Remove single entity based on primary key

<plugin> <groupId>com.google.cloud.tools</groupId> <artifactId>jib-maven-plugin</artifactId> <version>${jib-maven-plugin.version}</version> <configuration> <container> <ports> <port>8080</port> </ports> </container> <from> <image>LATEST-IMAGE</image> </from> <to> <image>jabrena/bla-bla-bla:${project.version}</image> </to> </configuration> <executions> <execution> <phase>package</phase> <goals> <goal>build</goal> </goals> </execution> </executions> </plugin>

CREATE TABLE film ( film_id integer DEFAULT nextval('film_film_id_seq'::regclass) NOT NULL, CREATE TABLE film ( film_id bigint DEFAULT nextval('film_film_id_seq'::regclass) NOT NULL, CREATE TABLE actor ( actor_id integer DEFAULT nextval('actor_actor_id_seq'::regclass) NOT NULL, CREATE TABLE actor ( actor_id bigint DEFAULT nextval('actor_actor_id_seq'::regclass) NOT NULL,

Recommend Projects

jabrena / spring-boot-relational-database-poc Goto Github PK