SmoothieDao - Lefteros/Tikape-smoothiearkisto GitHub Wiki

private Database database;

public SmoothieDao(Database database) {
    this.database = database;
}

@Override
public Smoothie findOne(Integer key) throws Exception {
    return findAll().stream().filter(u -> u.getId().equals(key)).findFirst().get();
}

@Override
public List<Smoothie> findAll() throws Exception {
    List<Smoothie> Smoothies = new ArrayList<>();

    try (Connection conn = database.getConnection();
            ResultSet result = conn.prepareStatement("SELECT id, nimi FROM annos").executeQuery()) {

        while (result.next()) {
            Smoothies.add(new Smoothie(result.getInt("id"), result.getString("nimi")));
        }
    }

    return Smoothies;
}

public List<Smoothie> findRaakaAineet(Integer id) throws Exception {
    String query = "SELECT raakaAine.nimi FROM AnnosRaakaAine, annos, raakaAine "
            + "              WHERE raakaAine.id = AnnosRaakaAine.raaka_aine_id "
            + "                  AND AnnosRaakaAine.annos_id = annos.id"
            + "                  AND Annos.id =" + id;

    List<Smoothie> Smoothies = new ArrayList<>();

    try (Connection conn = database.getConnection()) {
        PreparedStatement stmt = conn.prepareStatement(query);
        stmt.setInt(1, id);
        ResultSet result = stmt.executeQuery();

        while (result.next()) {
            Smoothies.add(new Smoothie(result.getInt("id"), result.getString("nimi")));
        }
    }

    return Smoothies;
}

public List<Smoothie> findAllNotAssigned() throws Exception {
    List<Smoothie> Smoothies = new ArrayList<>();

    try (Connection conn = database.getConnection();
            ResultSet result = conn.prepareStatement("SELECT id, nimi FROM Smoothie WHERE id NOT IN (SELECT Smoothie_id FROM SmoothieAssignment)").executeQuery()) {

        while (result.next()) {
            Smoothies.add(new Smoothie(result.getInt("id"), result.getString("nimi")));
        }
    }

    return Smoothies;
}

@Override
public Smoothie saveOrUpdate(Smoothie object) throws Exception {

    if (findBynimi(object.getNimi()) != null) {
        return null;
    }
    // simply support saving -- disallow saving if Smoothie with 
    // same nimi exists
    Smoothie bynimi = findBynimi(object.getNimi());

    if (bynimi != null) {
        return bynimi;
    }

    try (Connection conn = database.getConnection()) {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO annos (nimi) VALUES (?)");
        stmt.setString(1, object.getNimi());
        stmt.executeUpdate();
    }

    return findBynimi(object.getNimi());

}

private Smoothie findBynimi(String nimi) throws Exception {
    try (Connection conn = database.getConnection()) {
        PreparedStatement stmt = conn.prepareStatement("SELECT id, nimi FROM annos WHERE nimi = ?");
        stmt.setString(1, nimi);

        ResultSet result = stmt.executeQuery();
        if (!result.next()) {
            return null;
        }

        return new Smoothie(result.getInt("id"), result.getString("nimi"));
    }
}

@Override
public void delete(Integer key) throws SQLException {
    throw new UnsupportedOperationException("Not supported yet.");
}