RaakaAineDao - Lefteros/Tikape-smoothiearkisto GitHub Wiki

private Database database;

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

@Override
public RaakaAine findOne(Integer key) throws Exception {
    throw new UnsupportedOperationException("Not supported yet.");
}

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

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

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

    return RaakaAines;
}

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

    String query1 = "SELECT annosraakaAine.maara, annosraakaAine.ohje, annosraakaAine.jarjestys FROM AnnosRaakaAine, raakaAine, annos"
            + "              WHERE annos.id = AnnosRaakaAine.annos_id"
            + "                  AND raakaAine.id = AnnosRaakaAine.raaka_aine_id"
            + "                  AND annos.id = ?";
    List<RaakaAine> RaakaAines = new ArrayList<>();

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

        while (result.next() && result1.next()) {
            RaakaAines.add(new RaakaAine(result.getInt("id"), result.getString("nimi"), result1.getString("ohje"), result1.getString("maara"), result1.getInt("jarjestys")));
        }
    }

    return RaakaAines;
}

public int findMaara(Integer id) throws Exception {
    String query = "SELECT COUNT (DISTINCT annos.id) as total FROM AnnosRaakaAine, raakaAine, annos"
            + "              WHERE annos.id = AnnosRaakaAine.annos_id"
            + "                  AND raakaAine.id = AnnosRaakaAine.raaka_aine_id"
            + "                  AND raakaAine.id = ?";

    int uusi = 0;
    try (Connection conn = database.getConnection()) {
        PreparedStatement stmt = conn.prepareStatement(query);
        stmt.setInt(1, id);
        ResultSet result = stmt.executeQuery();
        uusi = result.getInt("total");
    }

    return uusi;
}

public String findAlaMuuta(Integer id) throws Exception {
    String query = "SELECT annosRaakaAine.maara FROM annosRaakaAine, annos, raakaAine"
            + "              WHERE annos.id = AnnosRaakaAine.annos_id"
            + "                  AND raakaAine.id = AnnosRaakaAine.raaka_aine_id"
            + "                  AND annos.id = ?";

    String uusi = new String();
    try (Connection conn = database.getConnection()) {
        PreparedStatement stmt = conn.prepareStatement(query);
        ResultSet result = stmt.executeQuery();

    }

    return uusi;
}

public List<RaakaAine> findAllNotAssigned() throws Exception {
    List<RaakaAine> RaakaAines = new ArrayList<>();

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

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

    return RaakaAines;
}

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

    if (findBynimi(object.getNimi()) != null) {
        return null;
    }

    RaakaAine bynimi = findBynimi(object.getNimi());

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

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

    return findBynimi(object.getNimi());

}

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

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

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

private Integer findByAnnosnimi(String nimi) throws Exception {
    try (Connection conn = database.getConnection()) {
        PreparedStatement stmt = conn.prepareStatement("SELECT id FROM annos WHERE nimi = ?");
        stmt.setString(1, "pläjäys");

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

        return result.getInt("id");
    }
}

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

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

        return result.getInt("id");
    }
}

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

public List<RaakaAine> findLkm() throws Exception {
    String query = "SELECT COUNT (DISTINCT annos.id) as total FROM AnnosRaakaAine, raakaAine, annos"
            + "              WHERE annos.id = AnnosRaakaAine.annos_id"
            + "                  AND raakaAine.id = AnnosRaakaAine.raaka_aine_id"
            + "                  AND raakaAine.id = ?";
    String query1 = "SELECT raakaAine.nimi FROM raakaAine"
            + "                  WHERE raakaAine.id = ?";

    List<RaakaAine> aineet = new ArrayList<>();
    try (Connection conn = database.getConnection()) {
        for (int i = 1; i < findAll().size() + 1; i++) {
            PreparedStatement stmt = conn.prepareStatement(query);
            stmt.setInt(1, i);
            ResultSet result = stmt.executeQuery();
            PreparedStatement stmt1 = conn.prepareStatement(query1);
            stmt1.setInt(1, i);
            ResultSet result1 = stmt1.executeQuery();
            while (result.next() && result1.next()) {
            aineet.add(new RaakaAine(i, result1.getString("nimi"), result.getInt("total")));
            }
        }
    }
    return aineet;
}

public void saveOrUpdate1(RaakaAine raakaAine) throws Exception {

    Integer annosId = Integer.parseInt(raakaAine.getSmoothie());
    Integer aineId = Integer.parseInt(raakaAine.getNimi());
    try (Connection conn = database.getConnection()) {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO annosRaakaAine (raaka_aine_id, annos_id, jarjestys, maara, ohje) VALUES (?, ?, ?, ?, ?)");
        stmt.setInt(1, aineId);
        stmt.setInt(2, annosId);
        stmt.setInt(3, raakaAine.getJarjestys());
        stmt.setString(4, raakaAine.getMaara());
        stmt.setString(5, raakaAine.getOhje());
        stmt.executeUpdate();
    }
}