public static void saveZutaten(Rezept r, Connection con) throws SQLException {
Set set = r.zutaten.keySet();
Iterator iter = set.iterator();
PreparedStatement stmt = null;
try {
String sql = "delete from zut2rez where rez_id = ?";
stmt = con.prepareStatement(sql);
stmt.setLong(1, r.getId());
stmt.execute();
stmt.close();
sql = "insert into zut2rez(zut_id, rez_id, menge) values(?,?,?)";
stmt = con.prepareStatement(sql);
while (iter.hasNext()) {
Long id = (Long) iter.next();
Long wert = r.zutaten.get(id);
stmt.setLong(1, id);
stmt.setLong(2, r.getId());
stmt.setLong(3, wert);
stmt.execute();
stmt.clearParameters();
}
} finally {
DbUtil.close(stmt);
}
}
protected static Rezept retrieveObject(ResultSet rs) throws SQLException {
Rezept r = new Rezept();
r.setId(rs.getLong("id"));
r.setName(rs.getString("name"));
r.setAnleitung(rs.getString("anleitung"));
r.setBenutzer(rs.getString("bzr_login"));
return r;
}
public static boolean nameExists(Rezept r, Connection con) throws SQLException {
String sql = "SELECT * FROM rezept WHERE name = ? AND ID != ?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setObject(1, r.getName());
stmt.setObject(2, r.getId());
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
return true;
} else return false;
}
public static void insertRezept(Rezept r, Connection con) throws SQLException {
String sql = "insert into rezept(id, name, anleitung, bzr_login) " + "values(?,?,?,?)";
PreparedStatement stmt = null;
try {
Long id = DbUtil.createId();
stmt = con.prepareStatement(sql);
stmt.setObject(1, id);
setStmtParams(r, stmt, 2);
stmt.setObject(4, r.getBenutzer());
stmt.execute();
r.setId(id);
} finally {
DbUtil.close(stmt);
}
}
public static void updateRezept(Rezept r, Connection con) throws SQLException {
String sql = "update rezept set name = ?, anleitung = ?" + " where id = ?)";
PreparedStatement stmt = null;
try {
stmt = con.prepareStatement(sql);
setStmtParams(r, stmt, 1);
stmt.setObject(3, r.getId());
int updateCount = stmt.executeUpdate();
if (updateCount < 1) {
throw new DasException("Rezept mit id " + r.getId() + " nicht gefunden");
}
} finally {
DbUtil.close(stmt);
}
}
public static float getBewertung(Rezept r, Connection con) throws SQLException {
float bew = 0.0f;
String sql = "SELECT AVG(rating) FROM bewertung WHERE rez_id = ?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setObject(1, r.getId());
ResultSet rs = stmt.executeQuery();
rs.next();
bew = rs.getFloat("avg");
return bew;
}
public static void bewerteRezept(Rezept r, Connection con, String login, int rating)
throws SQLException {
String sql = "SELECT * FROM bewertung WHERE rez_id = ? AND login = ?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setObject(1, r.getId());
stmt.setObject(2, login);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
// update
stmt = con.prepareStatement("UPDATE bewertung SET rating = ? WHERE rez_id = ? AND login = ?");
stmt.setObject(1, rating);
stmt.setObject(2, r.getId());
stmt.setObject(3, login);
stmt.execute();
} else {
// insert
stmt = con.prepareStatement("INSERT INTO bewertung(rating,rez_id,login) VALUES(?,?,?)");
stmt.setObject(1, rating);
stmt.setObject(2, r.getId());
stmt.setObject(3, login);
stmt.execute();
}
}
protected static void setStmtParams(Rezept r, PreparedStatement stmt, int index)
throws SQLException {
DbUtil.setString(stmt, index++, r.getName());
DbUtil.setString(stmt, index++, r.getAnleitung());
}