====== Aplicación que conecta con Base de datos. Patrón DAO ======

===== Patrón DAO =====

<file java Database.java>
public class Database {

  private Connection connection;

  public Connection getConnection() {
    try {
      Class.forName(MYSQL_DRIVER);
      connection = DriverManager.getConnection(MYSQL_URL, USERNAME, PASSWORD);
      System.out.println("Conectado!");
    } catch (ClassNotFoundException cnfe) {
      System.out.println("No se ha podido cargar el driver de conexión. Verifique que los drivers están disponibles");
      cnfe.printStackTrace();
    } catch (SQLException sqle) {
      System.out.println("No se ha podido conectar con el servidor de base de datos. Comprueba que los datos son correctos y que el servidor se ha iniciado");
      sqle.printStackTrace();
    }

    return connection;
  }

  public void close() {
    try {
      connection.close();
    } catch (SQLException sqle) {
      System.out.println("No se ha podido conectar con el servidor de base de datos. Comprueba que los datos son correctos y que el servidor se ha iniciado");
      sqle.printStackTrace();
    }
  }
}
</file>

<file java BookDao.java>
public class BookDao {

  private Connection connection;

  public BookDao(Connection connection) {
    this.connection = connection;
  }

  public void add(Book book) {
    String sql = "INSERT INTO books (title, author, publisher) VALUES (?, ?, ?)";
    try {
      PreparedStatement statement = connection.prepareStatement(sql);
      statement.setString(1, book.getTitle());
      statement.setString(2, book.getAuthor());
      statement.setString(3, book.getPublisher());
      statement.executeUpdate();
    } catch (SQLException sqle) {
      System.out.println("No se ha podido conectar con el servidor de base de datos. Comprueba que los datos son correctos y que el servidor se ha iniciado");
      sqle.printStackTrace();
    }
  }

  public boolean delete(String title) {
    String sql = "DELETE FROM books WHERE title = ?";
    try {
      PreparedStatement statement = connection.prepareStatement(sql);
      statement.setString(1, title);
      int rows = statement.executeUpdate();

      return rows == 1;
    } catch (SQLException sqle) {
      System.out.println("No se ha podido conectar con el servidor de base de datos. Comprueba que los datos son correctos y que el servidor se ha iniciado");
      sqle.printStackTrace();
    }

    return false;
  }

  public boolean modify(String title, Book book) {
    String sql = "UPDATE books SET title = ?, author = ?, publisher = ? WHERE title = ?";
    try {
      PreparedStatement statement = connection.prepareStatement(sql);
      statement.setString(1, book.getTitle());
      statement.setString(2, book.getAuthor());
      statement.setString(3, book.getPublisher());
      statement.setString(4, title);
      int rows = statement.executeUpdate();
      return rows == 1;
    } catch (SQLException sqle) {
       System.out.println("No se ha podido conectar con el servidor de base de datos. Comprueba que los datos son correctos y que el servidor se ha iniciado");
      sqle.printStackTrace();
        }

        return false;
    }

    public ArrayList<Book> findAll() {
        String sql = "SELECT * FROM books ORDER BY title";
        ArrayList<Book> books = new ArrayList<>();
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Book book = new Book();
                book.setTitle(resultSet.getString("title"));
                book.setAuthor(resultSet.getString("author"));
                book.setPublisher(resultSet.getString("publisher"));
                books.add(book);
            }
        } catch (SQLException sqle) {
            System.out.println("No se ha podido conectar con el servidor de base de datos. Comprueba que los datos son correctos y que el servidor se ha iniciado");
            sqle.printStackTrace();
        }

        return books;
    }

    public Book findByTitle(String title) {
        String sql = "SELECT * FROM books WHERE title = ?";
        Book book = null;

        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, title);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                book = new Book();
                book.setTitle(resultSet.getString("title"));
                book.setAuthor(resultSet.getString("author"));
                book.setPublisher(resultSet.getString("publisher"));
            }
        } catch (SQLException sqle) {
            System.out.println("No se ha podido conectar con el servidor de base de datos. Comprueba que los datos son correctos y que el servidor se ha iniciado");
            sqle.printStackTrace();
        }

        return book;
    }
}
</file>
===== Librería Jdbi 3 =====

{{ jdbi.png }}

==== Creación de un DAO con Jdbi ====

<code java>
@Data
@RequiredArgsConstructor
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Vehicle {

  private int id;
  @NonNull
  private String licensePlate;
  @NonNull
  private String brand;
  @NonNull
  private String model;
  @NonNull
  private int kilometers;
  @NonNull
  private String image;
  @NonNull
  private LocalDate registrationDate;
}
</code>

<code java>
public class Database {

  public static Jdbi jdbi;
  public static Handle db;

  public static void connect() {
    jdbi = Jdbi.create(DATABASE_URL, DATABASE_USERNAME, DATABASE_PASSWORD);
    jdbi.installPlugin(new SqlObjectPlugin());
    db = jdbi.open();
  }

  public static void close() {
    db.close();
  }
}
</code>

<code java>
public interface VehicleDAO {

  @SqlQuery("SELECT * FROM vehicles")
  @UseRowMapper(VehicleMapper.class)
  List<Vehicle> getVehicles();

  @SqlQuery("SELECT * FROM vehicles WHERE brand = ? OR model = ?")
  @UseRowMapper(VehicleMapper.class)
  List<Vehicle> getVehicles(String search);

  @SqlQuery("SELECT * FROM vehicles WHERE brand = ? AND model = ?")
  @UseRowMapper(VehicleMapper.class)
  List<Vehicle> getVehicles(String brand, String model);

  @SqlQuery("SELECT * FROM vehicles WHERE id = ?")
  @UseRowMapper(VehicleMapper.class)
  Vehicle getVehicle(int id);

  @SqlUpdate("INSERT INTO vehicles (license_plate, brand, model, kilometers, image, registration_date) VALUES (?, ?, ?, ?, ?, ?)")
  void addVehicle(String licensePlate, String brand, String model, int kilometers, String image, Date registrationDate);

  @SqlUpdate("DELETE FROM vehicles WHERE id = ?")
  void removeVehicle(int id);

  @SqlUpdate("UPDATE vehicles SET license_plate = ?, brand = ?, model = ?, kilometers = ?, image = ? WHERE id = ?")
  void editVehicle(String licensePlate, String brand, String model, int kilometers, String image, int id);

  @SqlUpdate("UPDATE vehicles SET license_plate = ?, brand = ?, model = ?, kilometers = ? WHERE id = ?")
  void editVehicle(String licensePlate, String brand, String model, int kilometers, int id);
}
</code>

<code java>
public class VehicleMapper implements RowMapper<Vehicle> {

  @Override
  public Vehicle map(ResultSet rs, StatementContext ctx) throws SQLException {
    return new Vehicle(rs.getInt("id"),
        rs.getString("license_plate"),
        rs.getString("brand"),
        rs.getString("model"),
        rs.getInt("kilometers"),
        rs.getString("image"),
        DateUtils.getLocalDate(rs.getDate("registration_date"))
    );
  }
}
</code>

----

(c) 2023 Santiago Faci