ECE366 - Lesson 4
More Databases, JDBC, Docker Compose
Instructor: Professor Hong
## Designing a Rock-Paper-Scissors MMO Database
## What Tables Do We Want?
- Class Discussion
## ER Diagram
- Drawing
- Reference: [Lucid Chart](https://www.lucidchart.com/pages/er-diagrams)
## Starting Local Postgres
```
docker run --rm --name lil-postgres -e POSTGRES_PASSWORD=password -d -v $HOME/srv/postgres:/var/lib/postgresql -p 5432:5432 postgres
```
## Create a Database
```
psql -h localhost -U postgres
CREATE DATABASE rps;
GRANT ALL PRIVILEGES ON DATABASE rps TO postgres;
\c rps
```
## Player
```
CREATE SEQUENCE player_seq start with 1;
CREATE TABLE player (
player_id bigint NOT NULL DEFAULT nextval('player_seq'),
user_name varchar(50) NOT NULL UNIQUE,
password varchar(50) NOT NULL,
PRIMARY KEY (player_id)
);
```
## Game
```
CREATE SEQUENCE game_seq start with 1;
CREATE TABLE game (
game_id bigint NOT NULL DEFAULT nextval('game_seq'),
p1_id bigint NOT NULL,
p2_id bigint NOT NULL,
p1_score int NOT NULL DEFAULT 0,
p2_score int NOT NULL DEFAULT 0,
total_rounds int NOT NULL,
current_round int NOT NULL,
winner_id bigint DEFAULT NULL,
PRIMARY KEY (game_id),
FOREIGN KEY (p1_id) REFERENCES player(player_id) ON DELETE CASCADE,
FOREIGN KEY (p2_id) REFERENCES player(player_id) ON DELETE CASCADE,
CHECK (p1_id <> p2_id),
CHECK (winner_id = p1_id OR winner_id = p2_id)
);
```
## Game Round
```
CREATE TABLE game_round (
game_id bigint NOT NULL,
round_number int NOT NULL,
p1_choice varchar(50) NOT NULL,
p2_choice varchar(50) NOT NULL,
PRIMARY KEY (game_id, round_number),
FOREIGN KEY (game_id) REFERENCES game(game_id) ON DELETE CASCADE
);
```
## Creating our tables
- Create .sql files and run them w/ psql or use DBeaver
```
$ psql -h localhost -U postgres -d rps -f player.sql
$ psql -h localhost -U postgres -d rps -f game.sql
$ psql -h localhost -U postgres -d rps -f game_history.sql
```
## Inserting sample records
```
INSERT INTO player (user_name, password) VALUES ('Chris', 'password');
INSERT INTO player (user_name, password) VALUES ('Alpha', 'password');
INSERT INTO player (user_name, password) VALUES ('Beta', 'password');
INSERT INTO player (user_name, password) VALUES ('Gamma', 'password');
INSERT INTO player (user_name, password) VALUES ('Zeta', 'password');
INSERT INTO game (p1_id, p2_id, p1_score, p2_score, total_rounds, current_round, winner_id) VALUES (1, 2, 2, 1, 3, 3, 1);
INSERT INTO game_round (game_id, round_number, p1_choice, p2_choice) VALUES (1, 1, 'Scissors', 'Paper');
INSERT INTO game_round (game_id, round_number, p1_choice, p2_choice) VALUES (1, 2, 'Scissors', 'Paper');
INSERT INTO game_round (game_id, round_number, p1_choice, p2_choice) VALUES (1, 3, 'Scissors', 'Rock');
INSERT INTO game (p1_id, p2_id, p1_score, p2_score, total_rounds, current_round) VALUES (1, 3, 1, 1, 3, 2);
INSERT INTO game_round (game_id, round_number, p1_choice, p2_choice) VALUES (2, 1, 'Scissors', 'Paper');
INSERT INTO game_round (game_id, round_number, p1_choice, p2_choice) VALUES (2, 2, 'Scissors', 'Rock');
```
## Inserting sample records
```
$ psql -h localhost -U postgres -d rps -f sample_records.sql
```
## What is JDBC?
- Java Database Connectivity
- API for client-side access to a database
- RDBMS focused
- Basic Flow: Mount Driver -> Create Connection -> Execute SQL -> Commit/Rollback -> Close Connection
- All JDBC operations throw an SQLException
## What is Maven?
- A Yiddish word meaning accumulator of knowledge
- Standard way to build projects
- Clear definition of what the project consisted of
- An easy way to publish project information
- A way to share JARs (Java ARchive file) across several projects
## Data Access Object (DAO)
- Database code mixed with business logic is messy!
- DAO pattern separates database operations from the rest of your code
- Analogy: A librarian (DAO) who handles all book requests (database queries) so you don't have to know how the library storage system works
- Benefits:
- Change database without breaking your app
- Reuse database code
- Easier to test and debug
- Works with DTOs (Data Transfer Objects) to pass data around
## Database Connection Manager
```
package com.rps;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DatabaseConnectionManager {
private final String url;
private final Properties properties;
public DatabaseConnectionManager(String host, String databaseName, String username, String password) {
this.url = "jdbc:postgresql://" + host + "/" + databaseName;
this.properties = new Properties();
this.properties.setProperty("user", username);
this.properties.setProperty("password", password);
}
public Connection getConnection() throws SQLException {
return DriverManager.getConnection(this.url, this.properties);
}
}
```
## JDBC Executor
```
package com.rps;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCExecutor {
public static void main(String... args) {
DatabaseConnectionManager dcm = new DatabaseConnectionManager("localhost",
"rps", "postgres", "password");
try {
Connection connection = dcm.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM player");
while(resultSet.next()){
System.out.println(resultSet.getInt(1));
}
}
catch(SQLException e) {
e.printStackTrace();
}
}
}
```
## Possible Error: Type 10 not supported
Go to the pg_hba.conf file, which is in your ``` ~/srv/postgres``` directory and change the authentication method (last word in the last line) to "password".
Then you need to restart the container.
Read more [here](https://stackoverflow.com/questions/64210167/unable-to-connect-to-postgres-db-due-to-the-authentication-type-10-is-not-suppor)
## DTO (in util)
DataTransferObject
```
package com.rps.util;
public interface DataTransferObject {
long getId();
}
```
## Player
```
package com.rps;
import com.rps.util.DataTransferObject;
public class Player implements DataTransferObject {
private long playerId;
private String userName;
private String password;
public long getId() {
return playerId;
}
public long getPlayerId() {
return playerId;
}
public void setPlayerId(long playerId) {
this.playerId = playerId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "Player{" +
"playerId=" + playerId +
", userName='" + userName + '\'' +
", password='" + password + '\'' +
'}';
}
}
```
## Updated JDBC Executor
```
package com.rps;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCExecutor {
public static void main(String... args) {
System.out.println("Hello Learning JDBC");
DatabaseConnectionManager dcm = new DatabaseConnectionManager("localhost",
"rps", "postgres", "password");
try {
Connection connection = dcm.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM player");
while(resultSet.next()){
System.out.println(resultSet.getInt(1));
}
PlayerDAO playerDAO = new PlayerDAO(connection);
Player player = playerDAO.findById(1);
System.out.println(player.getPlayerId() + " " + player.getUserName() + " " + player.getPassword());
}
catch(SQLException e) {
e.printStackTrace();
}
}
}
```
## Creating a New Player
Add create to DataAccessObject
```
public abstract T create(T dto);
```
Add INSERT SQL statement to PlayerDAO
```
private static final String INSERT = "INSERT INTO player (user_name, password) " +
" VALUES (?, ?)";
```
## Creating a New Player
Add create override to PlayerDAO
```
@Override
public Player create(Player dto) {
try(PreparedStatement statement = this.connection.prepareStatement(INSERT);) {
// counts from 1!!
statement.setString(1, dto.getUserName());
statement.setString(2, dto.getPassword());
statement.execute();
int id = this.getLastVal(PLAYER_SEQUENCE);
return this.findById(id);
} catch(SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
```
## Update JDBC Executor
```
package com.rps;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCExecutor {
public static void main(String... args) {
System.out.println("Hello Learning JDBC");
DatabaseConnectionManager dcm = new DatabaseConnectionManager("localhost",
"rps", "postgres", "password");
try {
Connection connection = dcm.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM player");
while(resultSet.next()){
System.out.println(resultSet.getInt(1));
}
PlayerDAO playerDAO = new PlayerDAO(connection);
Player player = playerDAO.findById(1);
System.out.println(player.getPlayerId() + " " + player.getUserName() + " " + player.getPassword());
player = new Player();
player.setUserName("Star");
player.setPassword("password");
player = playerDAO.create(player);
System.out.println(player.getPlayerId() + " " + player.getUserName() + " " + player.getPassword());
}
catch(SQLException e) {
e.printStackTrace();
}
}
}
```
## Build JDBC Using Maven w/ Plugin
```
$ mvn package
```
- If you don't create an executable JAR, you will need to use the ```-cp``` class path flag when running Java
## Running the JAR File
```
$ java -jar target/rps-1.0-SNAPSHOT-spring-boot.jar
```
- This JAR file will run the main in the specified class in the pom.xml file
- It includes all dependencies
## Additional Functionalities
- Delete a player
- Add a game
- Add a game round
- Update a game
## Docker Compose Workflow
- Automated Container Orchestration
- Configuration File: docker-compose.yaml
- Startup: ```$ docker-compose up```
- Monitoring: Provided by Docker, Compose Daemon
- Teardown: ```$ docker-compose down```
Docker Compose Workflow
## Docker Compose with JDBC
Dockerfile
```
FROM maven:3-eclipse-temurin-25 AS build
ADD . /project
WORKDIR /project
RUN mvn -e package
FROM eclipse-temurin:25-jre
COPY --from=build /project/target /app/target
ENTRYPOINT java -jar /app/target/jdbc-1.0-SNAPSHOT.jar
```
## docker-compose.yaml
```
services:
db:
image: postgres
volumes:
- $HOME/srv/postgres:/var/lib/postgresql
environment:
- POSTGRES_DB=postgres
- POSTGRES_PASSWORD=password
expose:
- "5432"
ports:
- "5432:5432"
restart: always
app:
build: .
environment:
- POSTGRES_DB=postgres
- POSTGRES_PASSWORD=password
depends_on:
- db
```
- Note that this creates a network in which the dockers can communicate
- You can have multiple networks if desired
## Update to DCM
Update localhost to db in DatabaseConnectionManager
```
DatabaseConnectionManager dcm = new DatabaseConnectionManager("db",
"rps", "postgres", "password");
```
This will make the docker use the proper db docker
## Running Docker Compose
```
$ docker compose build
$ docker compose up
```