Skip to content
Snippets Groups Projects
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
JDBCRepository.java 10.34 KiB
import java.sql.*;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

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);
    }
  }

  /**
   * Sets up the database tables.
   */
  void setup(){
    try (var stmt = conn.createStatement()){

      stmt.execute(CREATE_OPERATION_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());
    };

  }

  /*
  private static final String CREATE_PATIENT_TABLE = """
    CREATE TABLE IF NOT EXISTS patients(
      id VARCHAR(50) PRIMARY KEY,
      gender VARCHAR(10) NOT NULL,
      givenName VARCHAR(100) NOT NULL,
      familyName VARCHAR(100) NOT NULL,
      birthDate DATE NOT NULL,
      street VARCHAR(50) NOT NULL,
      house VARCHAR(50) NOT NULL,
      postalCode VARCHAR(50) NOT NULL,
      city VARCHAR(50) NOT NULL,
      healthInsurance VARCHAR(40) NOT NULL
    );
  """;

  private static Patient readPatientFromRow(ResultSet rs) throws SQLException {
    return new Patient(
      new Id<>(rs.getString("id")),
      Gender.valueOf(rs.getString("gender")),
      rs.getString("givenName"),
      rs.getString("familyName"),
      rs.getDate("birthDate").toLocalDate(),
      new Address(
              rs.getString("street"),
              rs.getString("house"),
              rs.getString("postalCode"),
              rs.getString("city")
      ),
      rs.getString("healthInsurance")
    );
  }


  private static String insertSQL(Patient patient){
    return
      "INSERT INTO patients(" + 
        "id,gender,givenName,familyName,birthDate," +
        "street,house,postalCode,city,healthInsurance" +
      ") VALUES (" + 
        sqlValue(patient.id().value()) + "," +
        sqlValue(patient.gender()) + "," +
        sqlValue(patient.givenName()) + "," +
        sqlValue(patient.familyName()) + "," +
        sqlValue(patient.birthDate()) + "," +
        sqlValue(patient.address().street()) + "," +
        sqlValue(patient.address().house()) + "," +
        sqlValue(patient.address().postalCode()) + "," +
        sqlValue(patient.address().city()) + "," +
        sqlValue(patient.healthInsurance()) +
      ");";
  }

  private static String updateSQL(Patient patient){
    return
      "UPDATE patients SET " +
        "gender = " + sqlValue(patient.gender()) + "," +
        "givenName = " + sqlValue(patient.givenName()) + "," +
        "familyName = " + sqlValue(patient.familyName()) + "," +
        "birthDate = " + sqlValue(patient.birthDate()) + "," +
        "street = " + sqlValue(patient.address().street()) + "," +
        "house = " + sqlValue(patient.address().house()) + "," +
        "postalCode = " + sqlValue(patient.address().postalCode()) + "," +
        "city = " + sqlValue(patient.address().city()) + "," +
        "healthInsurance = " + sqlValue(patient.healthInsurance()) + " " +
      "WHERE id = " + sqlValue(patient.id().value()) + ";";
  }

  @Override
  public Id<Patient> patientId(){

    var id = new Id<Patient>(randomUUID().toString());

    return findPatient(id).isEmpty() ? id : patientId();
  }

  @Override
  public void createPatient(Patient patient) throws SQLException {

    try (
      var stmt = conn.createStatement()
    ){
      var sql =
        findPatient(patient.id()).isPresent() ?
          updateSQL(patient) :
          insertSQL(patient);

      stmt.executeUpdate(sql);

    } catch (SQLException e){
      throw new RuntimeException(e);
    }

  }

  @Override
  public Optional<Patient> findPatient(Id<Patient> id){
    try (
      var result =
        conn.createStatement()
          .executeQuery("SELECT * FROM patients WHERE id = " + sqlValue(id.value()) + ";")
    ){
      return
        result.next() ?
          Optional.of(readPatientFromRow(result)) :
          Optional.empty();

    } catch (SQLException e){
      throw new RuntimeException(e);
    }
  }

  @Override
  public Optional<Patient> deletePatient(Id<Patient> id) throws SQLException {

    var patient = findPatient(id);

    patient.ifPresent(
      p -> {
        try {
          conn.createStatement()
           .executeUpdate("DELETE FROM patients WHERE id = " + quoted(id.value()) + ";");
        } catch (SQLException e){
          throw new RuntimeException(e);
        }
      }
    );

    return patient;
  }
  */

  // Hier dann die restlichen create/edit Methoden rein

  /**
   * 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
    );
  """;

  /**
   * 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" +
                    ") VALUES (" +
                    sqlValue(operation.id().value()) + "," +
                    sqlValue(operation.date()) + "," +
                    sqlValue(operation.startTime()) + "," +
                    sqlValue(operation.endTime()) +
                    ");";
  }

  /**
   * 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()) + " " +
                    "WHERE id = " + sqlValue(operation.id().value()) + ";";
  }

  /**
   * 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 = "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);
    }
  }

  /**
   * 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()
    );
  }
}