ECE366 - Lesson 4
More Databases, JDBC
Instructor: Professor Hong
## Designing a Tic-Tac-Toe 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 tictactoe;
GRANT ALL PRIVILEGES ON DATABASE tictactoe 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 tictactoe -f player.sql
$ psql -h localhost -U postgres -d tictactoe -f game.sql
$ psql -h localhost -U postgres -d tictactoe -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 tictactoe -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.chrishong.lil.jdbc</groupId>
<artifactId>learning-jdbc</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<java.version>19</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>postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.1-901-1.jdbc4</version>
</dependency>
</dependencies>
</project>
```
## 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 corerns 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.chrishong.lil.jdbc;
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.chrishong.lil.jdbc;
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",
"tictactoe", "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();
}
}
}
```
## DTO (in util)
DataTransferObject
```
package com.chrishong.lil.jdbc.util;
public interface DataTransferObject {
long getId();
}
```
## DAO (in util)
DataAccessObject
```
package com.chrishong.lil.jdbc.util;
import java.sql.*;
import java.util.List;
public abstract class DataAccessObject {
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.chrishong.lil.jdbc;
import com.chrishong.lil.jdbc.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.chrishong.lil.jdbc;
import com.chrishong.lil.jdbc.util.DataAccessObject;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class PlayerDAO extends DataAccessObject{
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.chrishong.lil.jdbc;
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",
"tictactoe", "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.chrishong.lil.jdbc;
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",
"tictactoe", "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();
}
}
}
```
## 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```