-
Christopher Seitz authoredChristopher Seitz authored
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
JDBCRepository.java 31.37 KiB
import java.sql.*;
import java.sql.Date;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalTime;
import java.util.*;
import static java.util.UUID.randomUUID;
/**
* The implemented Repository Interface for SQL-Databases.
*/
class JDBCRepository implements Repository
{
private final Connection conn;
private JDBCRepository(Connection conn){
this.conn = conn;
}
/**
* Sets up a connection with the repository database via the set url, user and password.
* @return the connected repository
*/
static JDBCRepository instance(){
try {
var conn =
DriverManager.getConnection(
System.getProperty("pms.repo.jdbc.url"),
System.getProperty("pms.repo.jdbc.user"),
System.getProperty("pms.repo.jdbc.password")
);
var repo = new JDBCRepository(conn);
repo.setup();
return repo;
} catch (SQLException e){
throw new RuntimeException(e);
}
}
//region CREATE TABLES
/**
* The structure of the operations table.
*/
private static final String CREATE_OPERATION_TABLE = """
CREATE TABLE IF NOT EXISTS operations(
id VARCHAR(50) PRIMARY KEY,
date DATE NOT NULL,
startTime TIME NOT NULL,
endTime TIME NOT NULL,
patientId VARCHAR(50),
operationTeamId VARCHAR(50),
lastUpdate TIMESTAMP NOT NULL,
FOREIGN KEY (operationTeamId) REFERENCES operationTeams(id)
);
""";
/**
* The structure of the operationTeams table.
*/
private static final String CREATE_OPERATION_TEAM_TABLE = """
CREATE TABLE IF NOT EXISTS operationTeams(
id VARCHAR(50) PRIMARY KEY,
teamName VARCHAR(50) NOT NULL,
lastUpdate TIMESTAMP NOT NULL );
""";
/**
* The structure of the opStaffs table.
*/
private static final String CREATE_OP_STAFF_TABLE = """
CREATE TABLE IF NOT EXISTS opStaffs(
id VARCHAR(50) PRIMARY KEY,
role VARCHAR(50) NOT NULL,
specialty VARCHAR(50) NOT NULL,
lastUpdate TIMESTAMP NOT NULL
);
""";
/**
* The structure of the opStaffsInOperationTeams table.
*/
private static final String CREATE_OP_STAFF_IN_OPERATION_TEAM_TABLE = """
CREATE TABLE IF NOT EXISTS opStaffsInOperationTeams(
operationTeamId VARCHAR(50),
opStaffId VARCHAR(50),
lastUpdate TIMESTAMP NOT NULL,
FOREIGN KEY (operationTeamId) REFERENCES operationTeams(id),
FOREIGN KEY (opStaffId) REFERENCES opStaffs(id),
PRIMARY KEY (operationTeamId, opStaffId)
);
""";
/**
* The structure of the opStaffs table.
*/
private static final String CREATE_PREPARATIONNOTE_TABLE = """
CREATE TABLE IF NOT EXISTS preparationNote(
id VARCHAR(50) PRIMARY KEY,
note VARCHAR(50) NOT NULL,
operationsId VARCHAR(50) NOT NULL,
lastUpdate TIMESTAMP NOT NULL,
FOREIGN KEY (operationsId) REFERENCES operations(id)
);
""";
private static final String CREATE_ROOM_TABLE = """
CREATE TABLE IF NOT EXISTS room(
id VARCHAR(50) PRIMARY KEY,
roomName VARCHAR(50) NOT NULL,
lastUpdate TIMESTAMP NOT NULL
);
""";
//endregion
//region SQL OPERATIONS
/**
* Sets up the database tables.
*/
void setup(){
try (var stmt = conn.createStatement()){
stmt.execute(CREATE_OPERATION_TEAM_TABLE);
stmt.execute(CREATE_OP_STAFF_TABLE);
stmt.execute(CREATE_OPERATION_TABLE);
stmt.execute(CREATE_OP_STAFF_IN_OPERATION_TEAM_TABLE);
stmt.execute(CREATE_PREPARATIONNOTE_TABLE);
stmt.execute(CREATE_ROOM_TABLE);
} catch (SQLException e){ throw new RuntimeException(e);
}
}
/**
* Sets quotes around the given string.
* @param s the string
* @return the quoted string
*/
private static String quoted(String s){
return String.format("'%s'",s);
}
/**
* Turns a given object into a SQL-value.
* @param obj the object
* @return the SQL-value of the object
*/
private static String sqlValue(Object obj){
return switch(obj){
case LocalDate date -> quoted(Date.valueOf(date).toString());
case LocalTime time -> quoted(Time.valueOf(time).toString());
case Instant t -> quoted(Timestamp.from(t).toString());
case Integer n -> Integer.toString(n);
case Long n -> Long.toString(n);
case Double n -> Double.toString(n);
default -> quoted(obj.toString());
};
}
/**
* Creates the insert statement of the given operation for SQL.
* @param operation the operation to turn into an insert SQL statement
* @return the SQL statement
*/
private static String insertSQL(Operation operation){
return
"INSERT INTO operations(" +
"id,date,startTime,endTime,patientId,operationTeamId,lastUpdate" +
") VALUES (" +
sqlValue(operation.id().value()) + "," +
sqlValue(operation.date()) + "," +
sqlValue(operation.startTime()) + "," +
sqlValue(operation.endTime()) + "," +
sqlValue(operation.patientId()) + "," +
sqlValue(operation.operationTeamId()) + "," +
sqlValue(operation.lastUpdate()) +
");";
}
/**
* Creates the update statement of the given operation for SQL.
* @param operation the operation to turn into an update SQL statement
* @return the SQL statement
*/
private static String updateSQL(Operation operation){
return
"UPDATE operations SET " +
"date = " + sqlValue(operation.date()) + "," +
"startTime = " + sqlValue(operation.startTime()) + "," +
"endTime = " + sqlValue(operation.endTime()) + "," +
"patientId = " + sqlValue(operation.patientId()) + "," +
"operationTeamId = " + sqlValue(operation.operationTeamId()) + "," +
"lastUpdate = " + sqlValue(operation.lastUpdate()) + " " +
"WHERE id = " + sqlValue(operation.id().value()) + ";";
}
/**
* Creates the insert statement of the given OPStaff for SQL. * @param opStaff the OPStaff to turn into an insert SQL statement
* @return the SQL statement
*/
private static String insertSQL(OPStaff opStaff){
return
"INSERT INTO opStaffs(" +
"id,role,specialty,lastUpdate" +
") VALUES (" +
sqlValue(opStaff.id().value()) + "," +
sqlValue(opStaff.role()) + "," +
sqlValue(opStaff.specialty()) + "," +
sqlValue(opStaff.lastUpdate()) +
");";
}
/**
* Creates the update statement of the given OPStaff for SQL.
* @param opStaff the opStaff to turn into an update SQL statement
* @return the SQL statement
*/
private static String updateSQL(OPStaff opStaff){
return
"UPDATE opStaffs SET " +
"role = " + sqlValue(opStaff.role()) + "," +
"specialty = " + sqlValue(opStaff.specialty()) + "," +
"lastUpdate = " + sqlValue(opStaff.lastUpdate()) + " " +
"WHERE id = " + sqlValue(opStaff.id().value()) + ";";
}
/**
* Creates the insert statement of the given operationTeam for SQL.
* @param operationTeam the operationTeam to turn into an insert SQL statement
* @return the SQL statement
*/
private static String insertSQL(OperationTeam operationTeam){
return
"INSERT INTO operationTeams(" +
"id,teamName,lastUpdate" +
") VALUES (" +
sqlValue(operationTeam.id().value()) + "," +
sqlValue(operationTeam.teamName()) + "," +
sqlValue(operationTeam.lastUpdate()) +
");";
}
/**
* Creates the update statement of the given operationTeam for SQL.
* @param operationTeam the operationTeam to turn into an update SQL statement
* @return the SQL statement
*/
private static String updateSQL(OperationTeam operationTeam){
return
"UPDATE operationTeams SET " +
"teamName = " + sqlValue(operationTeam.teamName()) + "," +
"lastUpdate = " + sqlValue(operationTeam.lastUpdate()) + " " +
"WHERE id = " + sqlValue(operationTeam.id().value()) + ";";
}
private String insertSQL(Id<OperationTeam> operationTeamId, Id<OPStaff> opStaffId) {
return
"INSERT INTO opStaffsInOperationTeams(" +
"operationTeamId,opStaffId,lastUpdate" +
") VALUES (" +
sqlValue(operationTeamId.value()) + "," +
sqlValue(opStaffId.value()) + "," +
sqlValue(Instant.now()) +
");";
}
/** * Creates an Operation object with the given result of a read SQL-Row.
* @param rs the result of a read SQL-Row
* @return the created operation object
* @throws SQLException Error while reading the SQL-Row
*/
private static Operation readOperationFromRow(ResultSet rs) throws SQLException {
return new Operation(
new Id<>(rs.getString("id")),
rs.getDate("date").toLocalDate(),
rs.getTime("startTime").toLocalTime(),
rs.getTime("endTime").toLocalTime(),
new Id<>(rs.getString("patientId")),
new Id<>(rs.getString("operationTeamId")),
rs.getTimestamp("lastUpdate").toInstant()
);
}
/**
* Creates an Operation object with the given result of a read SQL-Row.
* @param rs the result of a read SQL-Row
* @return the created operation object
* @throws SQLException Error while reading the SQL-Row
*/
private static OPStaff readOPStaffFromRow(ResultSet rs) throws SQLException {
return new OPStaff(
new Id<>(rs.getString("id")),
Role.valueOf(rs.getString("role")),
Specialty.valueOf(rs.getString("specialty")),
rs.getTimestamp("lastUpdate").toInstant()
);
}
private static OperationTeam readOperationTeamFromRow(ResultSet rs) throws SQLException{
return new OperationTeam(
new Id<>(rs.getString("id")),
new ArrayList<>(),
rs.getString("teamname"),
rs.getTimestamp("lastupdate").toInstant()
);
}
//endregion
//region REPOSITORY OPERATIONS
/**
* Returns a new generated ID in the operations-SQLTable
* If the generated ID is already used, generate another one.
* @return Operation ID
*/
@Override
public Id<Operation> operationId() {
var id = new Id<Operation>(randomUUID().toString());
return findOperation(id).isEmpty() ? id : operationId();
}
/**
* Connects to the database and checks if the ID of the given operation is already used.
* If yes, then update existing entry, else insert new entry.
* @param operation the Operation to save into the database
*/
@Override public void save(Operation operation) {
try (
var stmt = conn.createStatement()
){
var sql =
findOperation(operation.id()).isPresent() ?
updateSQL(operation) :
insertSQL(operation);
stmt.executeUpdate(sql);
} catch (SQLException e){
throw new RuntimeException(e);
}
}
/**
* Reads an Operation from the Operations-SQLTable with the given ID.
* If no Operation with this ID is found, it will return an empty Optional.
* @param id the ID to search for
* @return the found Operation
*/
@Override
public Optional<Operation> findOperation(Id<Operation> id){
try (
var result =
conn.createStatement()
.executeQuery("SELECT * FROM operations WHERE id = " + sqlValue(id.value()) + ";")
){
return
result.next() ?
Optional.of(readOperationFromRow(result)) :
Optional.empty();
} catch (SQLException e){
throw new RuntimeException(e);
}
}
/**
* Connects to the database and searches all filter criteria.
* Adds each found Operation to the list.
* @param filter the filter
* @return the matching entries
* TODO Ask how to remove "Optional[]" from filter.date()
*/
@Override
public List<Operation> findOperations(Operation.Filter filter) {
var sql = "";
if(filter.equals(Operation.Filter.NONE)) {
sql = "SELECT * FROM operations;";
} else {
sql = "SELECT * FROM operations WHERE date = " +
sqlValue(filter.date()).
replace("Optional", "").
replace("[", "").
replace("]", "") + ";";
}
try (
var resultSet =
conn.createStatement().executeQuery(sql)
){
var operations = new ArrayList<Operation>();
while(resultSet.next()){
operations.add(readOperationFromRow(resultSet));
}
return operations;
} catch (SQLException e){
throw new RuntimeException(e);
}
}
/**
* Connects to the database and deletes the operation with the given id.
* @param id the id of the Operation to delete
*/
@Override
public Operation deleteOperation(Id<Operation> id) {
var operation = findOperation(id);
if(operation.isPresent()) {
var sql = "DELETE FROM operations WHERE id = " + quoted(id.value()) + ";";
try {
conn.createStatement().executeUpdate(sql);
return operation.get();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
return null;
}
/**
* Returns a new generated ID in the opStaff-SQLTable
* If the generated ID is already used, generate another one.
* @return OPStaff ID
*/
@Override
public Id<OPStaff> opStaffId() {
var id = new Id<OPStaff>(randomUUID().toString());
return findOPStaff(id).isEmpty() ? id : opStaffId();
}
/**
* Connects to the database and checks if the ID of the given OPStaff is already used.
* If yes, then update existing entry, else insert new entry.
* @param opStaff the OPStaff to save into the database
*/
@Override
public void save(OPStaff opStaff) {
try (
var stmt = conn.createStatement()
){
var sql =
findOPStaff(opStaff.id()).isPresent() ?
updateSQL(opStaff) :
insertSQL(opStaff);
stmt.executeUpdate(sql);
} catch (SQLException e){
throw new RuntimeException(e);
}
}
/**
* Reads an OPStaff from the OPStaff-SQLTable with the given ID.
* If no OPStaff with this ID is found, it will return an empty Optional.
* @param id the ID to search for
* @return the found Operation
*/
@Override
public Optional<OPStaff> findOPStaff(Id<OPStaff> id){ try (
var result =
conn.createStatement()
.executeQuery("SELECT * FROM opStaffs WHERE id = " + sqlValue(id.value()) + ";")
){
return
result.next() ?
Optional.of(readOPStaffFromRow(result)) :
Optional.empty();
} catch (SQLException e){
throw new RuntimeException(e);
}
}
/**
* Reads all opStaffs in the opStaffs table
* @return all opStaffs
*/
@Override
public List<OPStaff> findAllOPStaffs(){
try (
var result =
conn.createStatement()
.executeQuery("SELECT * FROM opStaffs;")
){
var opStaffs = new ArrayList<OPStaff>();
while(result.next()){
opStaffs.add(readOPStaffFromRow(result));
}
return opStaffs;
} catch (SQLException e){
throw new RuntimeException(e);
}
}
/**
* Connects to the database and deletes the OPStaff with the given id.
* @param id the id of the OPStaff to delete
*/
@Override
public OPStaff deleteOPStaff(Id<OPStaff> id) {
var opStaff = findOPStaff(id);
if(opStaff.isPresent()) {
var sql = "DELETE FROM opStaffs WHERE id = " + quoted(id.value()) + ";";
try {
conn.createStatement().executeUpdate(sql);
return opStaff.get();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
return null;
}
/**
* Returns a new generated ID in the operationTeam-SQLTable
* If the generated ID is already used, generate another one.
* @return operationTeamID
*/
@Override
public Id<OperationTeam> operationTeamId() {
var id = new Id<OperationTeam>(randomUUID().toString());
return findOperationTeam(id).isEmpty() ? id : operationTeamId();
}
/**
* Connects to the database and checks if the ID of the given OperationTeam is already used.
* If yes, then update existing entry, else insert new entry.
* @param operationTeam the OPStaff to save into the database
*/
@Override
public void save(OperationTeam operationTeam) {
try (
var stmt = conn.createStatement()
){
var sql =
findOperationTeam(operationTeam.id()).isPresent() ?
updateSQL(operationTeam) :
insertSQL(operationTeam);
stmt.executeUpdate(sql);
} catch (SQLException e){
throw new RuntimeException(e);
}
}
/**
* Reads an operationTeam from the operationTeams-SQLTable with the given ID.
* If no OPStaff with this ID is found, it will return an empty Optional.
* @param id the ID to search for
* @return the found OperationTeam
*/
@Override
public Optional<OperationTeam> findOperationTeam(Id<OperationTeam> id){
try (
var resultOPStaffs =
conn.createStatement()
.executeQuery("SELECT DISTINCT opStaffs.id, opStaffs.role, opStaffs.specialty, opStaffs.lastUpdate " +
"FROM opStaffs " +
"JOIN opStaffsInOperationTeams " +
"ON (opStaffs.id = opStaffsInOperationTeams.opStaffId) " +
"WHERE opStaffsInOperationTeams.operationTeamId = " + quoted(id.value()) + ";");
var resultOperationTeam =
conn.createStatement()
.executeQuery("SELECT * FROM operationTeams WHERE id = " + sqlValue(id.value()) + ";")
){
System.out.println("Result: " + resultOperationTeam);
if(resultOperationTeam.next()) {
OperationTeam operationTeam = readOperationTeamFromRow(resultOperationTeam);
while(resultOPStaffs.next()) {
System.out.println("Result: " + resultOPStaffs);
operationTeam.opStaffs().add(readOPStaffFromRow(resultOPStaffs));
}
return Optional.of(operationTeam);
} else {
return Optional.empty();
}
} catch (SQLException e){
throw new RuntimeException(e);
}
}
public OperationTeam getOperationTeam(Id<OperationTeam> id){
try(
var result =
conn.createStatement()
.executeQuery("SELECT * FROM operationTeams WHERE id = " + sqlValue(id.value()) + ";") ) {
OperationTeam operationTeam = null;
while (result.next()) {
operationTeam = readOperationTeamFromRow(result);
}
return operationTeam;
}catch(SQLException e){
throw new RuntimeException(e);
}
}
@Override
public List<OperationTeam> findOperationTeams() {
try(
var result =
conn.createStatement()
.executeQuery("SELECT * FROM operationTeams;")
) {
var operationTeams = new ArrayList<OperationTeam>();
while (result.next()) {
operationTeams.add(readOperationTeamFromRow(result));
}
return operationTeams;
}catch(SQLException e){
throw new RuntimeException(e);
}
}
/**
* Connects to the database and deletes the OperationTeam with the given id.
* @param id the id of the OperationTeam to delete
*/
@Override
public OperationTeam deleteOperationTeam(Id<OperationTeam> id) {
var operationTeam = findOperationTeam(id);
if(operationTeam.isPresent()) {
var sql = "DELETE FROM operationTeams WHERE id = " + quoted(id.value()) + ";";
try {
conn.createStatement().executeUpdate(sql);
return operationTeam.get();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
return null;
}
/*
@Override
public Id<String> opStaffsInOperationTeamsId() {
var id = new Id<String>(randomUUID().toString());
return findOPStaffsInOperationTeam(id).isEmpty() ? id : opStaffsInOperationTeamsId();
}
*/
/*
@Override
public Optional<Map<OperationTeam, OPStaff>> findOPStaffsInOperationTeam(Id<String> id) {
try (
var resultOPStaff =
conn.createStatement() .executeQuery("SELECT DISTINCT opStaffs.id, opStaffs.role, opStaffs.specialty, opStaffs.lastUpdate " +
"FROM opStaffs " +
"JOIN opStaffsInOperationTeams " +
"ON (opStaffs.id = opStaffsInOperationTeams.opStaffId) " +
"WHERE opStaffsInOperationTeams.id = " + sqlValue(id.value()) + ";");
var resultOperationTeam =
conn.createStatement()
.executeQuery("SELECT DISTINCT operationTeams.id, operationTeams.teamName, operationTeams.lastUpdate " +
"FROM operationTeams " +
"JOIN opStaffsInOperationTeams " +
"ON (operationTeams.id = opStaffsInOperationTeams.operationTeamId) " +
"WHERE opStaffsInOperationTeams.id = " + sqlValue(id.value()) + ";")
){
if (resultOperationTeam.next() && resultOPStaff.next()) {
OperationTeam operationTeam = readOperationTeamFromRow(resultOperationTeam);
OPStaff opStaff = readOPStaffFromRow(resultOPStaff);
return Optional.of(Map.of(operationTeam, opStaff));
} else {
return Optional.empty();
}
} catch (SQLException e){
throw new RuntimeException(e);
}
}*/
//TODO Check what exactly happens when assigning the same staff to the same team twice.
@Override
public Boolean assignOPStaffToOperationTeam(Id<OperationTeam> operationTeamId, Id<OPStaff> opStaffId) {
try (
var stmt = conn.createStatement()
){
var sql =
findOperationTeam(operationTeamId).isPresent() &&
findOPStaff(opStaffId).isPresent() &&
!findOperationTeamOPStaff(operationTeamId, opStaffId) ?
insertSQL(operationTeamId, opStaffId) :
null;
if (sql != null) {
stmt.executeUpdate(sql);
return true;
} else {
return false;
}
} catch (SQLException e){
throw new RuntimeException(e);
}
}
@Override
public Boolean removeOPStaffInOperationTeam(Id<OperationTeam> operationTeamId, Id<OPStaff> opStaffId) throws Exception{
try(
var stmt = conn.createStatement()
){
var sql = "DELETE FROM opStaffsInOperationTeams WHERE operationTeamId = " + quoted(operationTeamId.value()) +
" AND opStaffId = " + quoted(opStaffId.value()) + ";";
stmt.executeUpdate(sql);
return true;
}catch(SQLException e){
throw new RuntimeException(e);
} }
@Override
public Boolean findOperationTeamOPStaff(Id<OperationTeam> operationTeamId, Id<OPStaff> opStaffId){
try(
var result =
conn.createStatement()
.executeQuery("SELECT * " +
"FROM opStaffsInOperationTeams " +
"WHERE opStaffsInOperationTeams.operationTeamId = " + quoted(operationTeamId.value()) + " " +
"AND opStaffsInOperationTeams.opStaffId = " + quoted(opStaffId.value()) + ";")
) {
return result.next();
}catch(SQLException e){
throw new RuntimeException(e);
}
}
@Override
public Boolean findOPStaffOperationTeams(Id<OPStaff> opStaffId){
try(
var result =
conn.createStatement()
.executeQuery("SELECT * " +
"FROM opStaffsInOperationTeams " +
"WHERE opStaffsInOperationTeams.opStaffId = " + quoted(opStaffId.value()) + ";")
) {
return result.next();
}catch(SQLException e){
throw new RuntimeException(e);
}
}
/*
@Override
public List<OPStaff> getOperationTeamOPStaffImpl(Id<OperationTeam> operationTeamId){
try(
var result =
conn.createStatement()
.executeQuery("SELECT DISTINCT opStaffs.id, opStaffs.role, opStaffs.specialty, opStaffs.lastupdate " +
"FROM opStaffs " +
"JOIN opStaffsInOperationTeams " +
"ON (opStaffs.id = opStaffsInOperationTeams.opstaffid) " +
"WHERE opStaffsInOperationTeams.operationteamid = '"+operationTeamId.value()+"';");
//SELECT DISTINCT opStaffs.id, opStaffs.role, opStaffs.specialty, opStaffs.lastupdate FROM opStaffs JOIN opStaffsInOperationTeams ON (opStaffs.id = '4444') WHERE opStaffsInOperationTeams.operationteamid = '31';S
) {
//SELECT DISTINCT opstaff.id, opstaff.role, opstaff.specialty, opstaff.lastupdate, teammember.operationteamid FROM opstaff JOIN teammember ON (opstaff.id = teammember.opstaffid);
//SELECT DISTINCT opstaff.id, opstaff.role, opstaff.specialty, opstaff.lastupdate, teammember.operationteamid FROM opstaff JOIN teammember ON (opstaff.id = teammember.opstaffid) WHERE teammember.operationteamid= '31313131';
var opStaffs = new ArrayList<OPStaff>();
while (result.next()) {
opStaffs.add(readOPStaffFromRow(result));
}
return opStaffs;
}catch(SQLException e){
throw new RuntimeException(e);
}
}
*/
//endregion
@Override
public Id<PreparationNote> preparationNoteId() {
var id = new Id<PreparationNote>(randomUUID().toString());
return findPreparationNote(id).isEmpty() ? id : preparationNoteId();
}
public Optional<PreparationNote> findPreparationNote(Id<PreparationNote> id){
try (
var result =
conn.createStatement()
.executeQuery("SELECT * FROM preparationNote WHERE id = " + sqlValue(id.value()) + ";")
){
return
result.next() ?
Optional.of(readPreparationNoteFromRow(result)) :
Optional.empty();
} catch (SQLException e){
throw new RuntimeException(e);
}
}
private static PreparationNote readPreparationNoteFromRow(ResultSet rs) throws SQLException {
return new PreparationNote(
new Id<>(rs.getString("id")),
rs.getString("note"),
new Id<>(rs.getString("operationsId")),
rs.getTimestamp("lastUpdate").toInstant()
);
}
@Override
public void save(PreparationNote preparationNote) {
try (
var stmt = conn.createStatement()
){
var sql =
findPreparationNote (preparationNote.id()).isPresent() ?
updateSQL(preparationNote) :
insertSQL(preparationNote );
stmt.executeUpdate(sql);
} catch (SQLException e){
throw new RuntimeException(e);
}
}
/*
CREATE TABLE IF NOT EXISTS preparationNote(
id VARCHAR(50) PRIMARY KEY,
note VARCHAR(50) NOT NULL,
operationsId VARCHAR(50) NOT NULL,
lastUpdate TIMESTAMP NOT NULL,
FOREIGN KEY (operationsId) REFERENCES operations(id)
);
*/
private static String updateSQL(PreparationNote preparationNote){
return
"UPDATE preparationNote SET " +
"note = " + sqlValue(preparationNote.note()) + "," +
"lastUpdate = " + sqlValue(preparationNote.lastUpdate()) + " " +
"WHERE id = " + sqlValue(preparationNote.id().value()) + ";";
}
private static String insertSQL(PreparationNote preparationNote){
return
"INSERT INTO preparationNote(" + "id,note,operationsId,lastUpdate" +
") VALUES (" +
sqlValue(preparationNote.id().value()) + "," +
sqlValue(preparationNote.note()) + "," +
sqlValue(preparationNote.operationsId()) + "," +
sqlValue(preparationNote.lastUpdate()) +
");";
}
@Override
public PreparationNote deletePreparationNote(Id<PreparationNote> id) {
var preparationNote = findPreparationNote(id);
if(preparationNote.isPresent()) {
var sql = "DELETE FROM preparationNote WHERE id = " + quoted(id.value()) + ";";
try {
conn.createStatement().executeUpdate(sql);
return preparationNote.get();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
return null;
}
@Override
public Id<Room> roomId() {
var id = new Id<Room>(randomUUID().toString());
return findRoom(id).isEmpty() ? id : roomId();
}
public Optional<Room> findRoom(Id<Room> id){
try (
var result =
conn.createStatement()
.executeQuery("SELECT * FROM room WHERE id = " +sqlValue(id.value()) + ";")
){
return
result.next() ?
Optional.of(readRoomFromRow(result)) :
Optional.empty();
} catch (SQLException e){
throw new RuntimeException(e);
}
}
private static Room readRoomFromRow(ResultSet rs) throws SQLException {
return new Room(
new Id<>(rs.getString("id")),
rs.getString("roomName"),
rs.getTimestamp("lastUpdate").toInstant()
);
}
@Override
public void save(Room room) {
try (
var stmt = conn.createStatement()
){
var sql =
findRoom(room.id()).isPresent() ?
updateSQL(room) :
insertSQL(room);
stmt.executeUpdate(sql);
} catch (SQLException e){
throw new RuntimeException(e);
}
}
private static String updateSQL(Room room){
return
"UPDATE room SET " +
"roomName = " + sqlValue(room.roomName()) + "," +
"lastUpdate = " + sqlValue(room.lastUpdate()) + " " +
"WHERE id = " + sqlValue(room.id().value()) + ";";
}
private static String insertSQL(Room room){
return
"INSERT INTO room(" +
"id,roomName,lastUpdate" +
") VALUES (" +
sqlValue(room.id().value()) + "," +
sqlValue(room.roomName()) + "," +
sqlValue(room.lastUpdate()) +
");";
}
@Override
public Room deleteRoom(Id<Room> id) {
var room = findRoom(id);
if(room.isPresent()) {
var sql = "DELETE FROM room WHERE id =" + quoted(id.value()) + ";";
try {
conn.createStatement().executeUpdate(sql);
return room.get();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
return null;
}
}