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/data -p 5432:5432 postgres
```
## Create a Database
```
CREATE DATABASE rps;
GRANT ALL PRIVILEGES ON DATABASE rps TO postgres;
```
## 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('player_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
## Connecting to the DB in Java
- Create a new Maven project (you may need to install Maven)
- Edit the pom.xml file as follows:
```
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.rps</groupId>
<artifactId>learning-jdbc</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<java.version>21</java.version>
<maven.compiler.source>${java.version}</maven.compiler.source>
<maven.compiler.target>${java.version}</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.3.1</version>
</dependency>
</dependencies>
</project>
```
## 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)
- Provides an abstraction between JDBC and the rest of the code
- Can be just abstraction or a true object
- Most use data transfer objects (DTOs) with data access objects (DAOs)/abstractions
- Provides clear separation of concerns in code
- Concrete implementation that reacts on single-data domain and multiple tables
- Encapsulation of complex joins and aggregations
- DAO Factory - leverage common paths for basic CRUD operations
## 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();
}
}
}
```
## 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();
}
```
## DAO (in util)
DataAccessObject
```
package com.rps.util;
import java.sql.*;
import java.util.List;
public abstract class DataAccessObject <T extends DataTransferObject> {
protected final Connection connection;
protected final static String LAST_VAL = "SELECT last_value FROM ";
protected final static String PLAYER_SEQUENCE = "player_seq";
public DataAccessObject(Connection connection){
super();
this.connection = connection;
}
public abstract T findById(long id);
}
```
## 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 + '\'' +
'}';
}
}
```
## PlayerDAO
Extends DataAccessObject
```
package com.rps;
import com.rps.util.DataAccessObject;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class PlayerDAO extends DataAccessObject<Player>{
private static final String GET_ONE = "SELECT player_id, user_name, password " +
"FROM player WHERE player_id=?";
public PlayerDAO(Connection connection) {
super(connection);
}
@Override
public Player findById(long id) {
Player player = new Player();
try(PreparedStatement statement = this.connection.prepareStatement(GET_ONE);) {
statement.setLong(1, id);
ResultSet rs = statement.executeQuery();
while(rs.next()) {
player.setPlayerId(rs.getLong("player_id"));
player.setUserName(rs.getString("user_name"));
player.setPassword(rs.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
return player;
}
}
```
## 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();
}
}
}
```
## Building Projects with Maven
- Create a pom.xml file
```
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>jdbc_prep</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>21</maven.compiler.source>
<maven.compiler.target>21</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.3.1</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-dependency-plugin</artifactId>
<version>3.6.1</version>
<executions>
<execution>
<id>copy-dependencies</id>
<phase>prepare-package</phase>
<goals>
<goal>copy-dependencies</goal>
</goals>
<configuration>
<outputDirectory>
${project.build.directory}/libs
</outputDirectory>
</configuration>
</execution>
</executions>
</plugin>
<plugin>
<!-- Build an executable JAR -->
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-jar-plugin</artifactId>
<version>3.3.0</version>
<configuration>
<archive>
<manifest>
<addClasspath>true</addClasspath>
<classpathPrefix>libs/</classpathPrefix>
<mainClass>jdbc.JDBCExecutor</mainClass>
</manifest>
</archive>
</configuration>
</plugin>
</plugins>
</build>
</project>
```
- We copy dependencies and specify the main class
## Build JDBC Using Maven w/ Plugin
```
$ mvn package
```
- The Spring Boot plugin creates an executable JAR file with all dependencies built in
- 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.9.6-eclipse-temurin-21 AS build
ADD . /project
WORKDIR /project
RUN mvn -e package
FROM eclipse-temurin:latest
COPY --from=build /project/target /app/target
ENTRYPOINT java -jar /app/target/jdbc_prep-1.0-SNAPSHOT.jar
```
## docker-compose.yaml
```
services:
db:
image: postgres
volumes:
- $HOME/srv/postgres:/var/lib/postgresql/data
environment:
- POSTGRES_DB=postgres
- POSTGRES_PASSWORD=password
expose:
- 5432: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
```