@Override
public Attraction getAttractionById(int id) {
Connection con = MySQLConnection.getWebInstance();
try (PreparedStatement pstm = con.prepareStatement(GET_ATTRACTION_BY_ID)) {
pstm.setInt(1, id);
ResultSet rs = pstm.executeQuery();
rs.relative(1);
return extractAtraction(rs);
} catch (SQLException ex) {
rollback(con);
throw new DBLayerException("Failed to get attraction by id=" + id, ex);
} finally {
commit(con);
}
}
@Override
public void deleteAttraction(Attraction attraction) {
Connection con = MySQLConnection.getWebInstance();
try (PreparedStatement pstm = con.prepareStatement(DELETE_ATTRACTION)) {
pstm.setInt(1, attraction.getId());
pstm.executeUpdate();
} catch (SQLException ex) {
rollback(con);
throw new DBLayerException("Failed to delete attraction" + attraction, ex);
} finally {
commit(con);
}
}
@Override
public List<Attraction> getAttractions() {
Connection con = MySQLConnection.getWebInstance();
List<Attraction> attractions = new ArrayList<>();
try {
ResultSet rs = con.createStatement().executeQuery(GET_ATTRACTIONS);
while (rs.next()) {
attractions.add(extractAtraction(rs));
}
return attractions;
} catch (SQLException ex) {
rollback(con);
throw new DBLayerException("Failed to get all attractions", ex);
} finally {
commit(con);
}
}
@Override
public void addAttraction(Attraction attraction) {
Connection con = MySQLConnection.getWebInstance();
try (PreparedStatement pstm = con.prepareStatement(ADD_ATTRACTION)) {
int k = 1;
pstm.setString(k++, attraction.getTitle());
pstm.setString(k++, attraction.getDescription());
pstm.setInt(k++, attraction.getHeight());
pstm.setString(k++, attraction.getImage());
pstm.setInt(k++, attraction.getAdultPrice());
pstm.setInt(k++, attraction.getChildPrice());
pstm.executeUpdate();
} catch (SQLException ex) {
rollback(con);
throw new DBLayerException("Failed to add attraction" + attraction, ex);
} finally {
commit(con);
}
}
