public ResultSet find(Query q) throws SQLException { List<String> criteria = new ArrayList<>(); for (Integer id : q.idPlanning) { criteria.add("planning = " + id); } for (Integer id : q.idProf) { criteria.add("prof = " + id); } for (DateFr dateFr : q.start) { criteria.add("date >= '" + dateFr + "'"); } for (DateFr dateFr : q.end) { DateFr endDate = new DateFr(dateFr); endDate.incDay(1); criteria.add("date < '" + endDate + "'"); } for (PlanningFact.Type type : q.type) { criteria.add("type = '" + type.toDBType() + "'"); } String whereClause = criteria.size() > 0 ? StringUtils.join(criteria, " AND ") : "1 = 1"; String query = "SELECT * FROM " + TABLE + " WHERE " + whereClause + " ORDER BY date ASC"; return dataConnection.executeQuery(query); }
public static int update(RehearsalPass card, DataConnection dc) throws SQLException { PreparedStatement ps = dc.prepareStatement(UPDATE_STATEMENT); ps.setString(1, escape(card.getLabel())); ps.setFloat(2, card.getAmount()); ps.setInt(3, card.getMin()); ps.setInt(4, card.getTotalTime()); ps.setInt(5, card.getId()); return ps.executeUpdate(); }
public static boolean isActive(int id, DataConnection dc) throws SQLException { String query = "SELECT count(DISTINCT id) FROM " + PersonSubscriptionCardIO.TABLE + " WHERE idpass = " + id; int n = 0; ResultSet rs = dc.executeQuery(query); while (rs.next()) { n = rs.getInt(1); } return n > 0; }
public static Vector<RehearsalPass> findAll(String where, DataConnection dc) throws SQLException { Vector<RehearsalPass> v = new Vector<RehearsalPass>(); String query = "SELECT " + COLUMNS + " FROM " + TABLE + " " + where; ResultSet rs = dc.executeQuery(query); while (rs.next()) { RehearsalPass c = new RehearsalPass(); c.setId(rs.getInt(1)); c.setLabel(unEscape(rs.getString(2))); c.setAmount(rs.getFloat(3)); c.setMin(rs.getInt(4)); c.setTotalTime(rs.getInt(5)); v.addElement(c); } return v; }
public void insert(PlanningFact fact) throws Exception { String query = String.format( "INSERT INTO planning_fact VALUES (DEFAULT, '%s', '%s', %d, %d, '%s', '%s', %d, %d, '%s')", toTimeStamp(fact.getDate()), fact.getType().toDBType(), fact.getPlanning(), fact.getProf(), escape(fact.getCommentaire()), minutesToPGInterval(fact.getDureeMinutes()), fact.getStatut(), fact.getNiveau(), escape(fact.getPlanningDescription())); dataConnection.executeUpdate(query); }
public static RehearsalPass find(int id, DataConnection dc) throws SQLException { String query = "SELECT " + COLUMNS + " FROM " + TABLE + " WHERE id = " + id; ResultSet rs = dc.executeQuery(query); if (!rs.next()) { return null; } RehearsalPass c = new RehearsalPass(); c.setId(rs.getInt(1)); c.setLabel(unEscape(rs.getString(2))); c.setAmount(rs.getFloat(3)); c.setMin(rs.getInt(4)); c.setTotalTime(rs.getInt(5)); return c; }
public void update(PlanningFact fact) throws Exception { if (fact.getId() == -1) throw new IllegalArgumentException("Cannot save transient fact " + fact); String query = String.format( "UPDATE planning_fact " + "SET date = '%s', type = '%s', planning = %d, prof = %d, commentaire = '%s', duree = '%s', statut = %d, niveau = %d, planning_desc = '%s' " + "WHERE id = " + fact.getId(), toTimeStamp(fact.getDate()), fact.getType().toDBType(), fact.getPlanning(), fact.getProf(), escape(fact.getCommentaire()), minutesToPGInterval(fact.getDureeMinutes()), fact.getStatut(), fact.getNiveau(), escape(fact.getPlanningDescription())); dataConnection.executeUpdate(query); }
public static boolean delete(int id, DataConnection dc) throws SQLException { int deleted = dc.executeUpdate(getDeleteQuery(id)); return deleted > 0; }
public static void insert(RehearsalPass card, DataConnection dc) throws SQLException { card.setId(nextId(SEQUENCE, dc)); dc.executeUpdate(getInsertQuery(card)); }
public void delete(long id) throws Exception { dataConnection.executeUpdate("DELETE FROM planning_fact WHERE id = " + id); }