@Override public List<TimeSlot> findByDate(Date date) { List<TimeSlot> timeSlots = Lists.newArrayList(); SqlApi sqlApi = SqlApi.create(); Calendar calendar = GregorianCalendar.getInstance(); calendar.setTime(date); int day = calendar.get(Calendar.DAY_OF_WEEK); boolean isOdd = calendar.get(Calendar.WEEK_OF_YEAR) % 2 == 1; try { List<CallableStatement> stmts = sqlApi.prepareScript( "SELECT * FROM TimeSlot WHERE day=? AND is_odd=? ORDER BY start_min"); stmts.get(0).setInt(1, day); stmts.get(0).setInt(2, (isOdd) ? 1 : 2); ResultSet rs = stmts.get(0).executeQuery(); fetchTimeSlots(rs, timeSlots); return timeSlots; } catch (SQLException e) { e.printStackTrace(); } finally { sqlApi.dispose(); } return null; }
@Override public TimeSlot findById(int id) { SqlApi sqlApi = SqlApi.create(); try { List<CallableStatement> stmts = sqlApi.prepareScript("SELECT * FROM TimeSlot WHERE id=?;"); stmts.get(0).setInt(1, id); ResultSet rs = stmts.get(0).executeQuery(); List<TimeSlot> timeSlots = Lists.newArrayList(); fetchTimeSlots(rs, timeSlots); return timeSlots.get(0); } catch (SQLException e) { e.printStackTrace(); } finally { sqlApi.dispose(); } return null; }
@Override public Collection<TimeSlot> getAll() { List<TimeSlot> timeSlots = Lists.newArrayList(); SqlApi sqlApi = SqlApi.create(); try { List<CallableStatement> stmts = sqlApi.prepareScript("SELECT * FROM TimeSlot ORDER BY start_min;"); ResultSet rs = stmts.get(0).executeQuery(); fetchTimeSlots(rs, timeSlots); return timeSlots; } catch (SQLException e) { e.printStackTrace(); } finally { sqlApi.dispose(); } return null; }
@Override public List<TimeSlot> findByWeekDay(int day) { List<TimeSlot> timeSlots = Lists.newArrayList(); SqlApi sqlApi = SqlApi.create(); try { List<CallableStatement> stmts = sqlApi.prepareScript("SELECT * FROM TimeSlot WHERE day=? ORDER BY start_min"); stmts.get(0).setInt(1, day); ResultSet rs = stmts.get(0).executeQuery(); fetchTimeSlots(rs, timeSlots); return timeSlots; } catch (SQLException e) { e.printStackTrace(); } finally { sqlApi.dispose(); } return null; }
@Override public TimeSlot create( String name, TimeInstant start, TimeInstant finish, int day, EvenOddWeek flashing) { SqlApi sqlApi = SqlApi.create(); try { CallableStatement stmt = sqlApi .prepareScript( "SELECT * FROM TimeSlot WHERE name=? AND day=? AND (is_odd=? OR is_odd=?);") .get(0); stmt.setString(1, name); stmt.setInt(2, day); stmt.setInt(3, flashing.ordinal()); stmt.setInt(4, 0); int rowCount = 0; ResultSet resultSet = stmt.executeQuery(); for (boolean hasRow = resultSet.next(); hasRow; hasRow = resultSet.next()) { rowCount++; } if (rowCount > 0) { throw new IllegalStateException("The TimeSlot with this name is already exists"); } stmt = sqlApi .prepareScript( "INSERT INTO TimeSlot SET name=?, start_min=?, finish_min=?, day=?, is_odd=?;") .get(0); stmt.setString(1, name); stmt.setInt(2, start.getHour() * 60 + start.getMinute()); stmt.setInt(3, finish.getHour() * 60 + finish.getMinute()); stmt.setInt(4, day); stmt.setInt(5, flashing.ordinal()); stmt.execute(); stmt = sqlApi .prepareScript("SELECT id FROM TimeSlot WHERE name=? AND day=? AND is_odd=?;") .get(0); stmt.setString(1, name); stmt.setInt(2, day); stmt.setInt(3, flashing.ordinal()); resultSet = stmt.executeQuery(); if (!resultSet.next()) { throw new IllegalStateException("The TimeSlot with this name is not exist"); } int id = resultSet.getInt("id"); if (resultSet.next()) { throw new IllegalStateException("There are more than one TimeSlot with this name"); } TimeSlot ts = new TimeSlotImpl(id, name, start, finish, day, flashing, this); return ts; } catch (SQLException e) { e.printStackTrace(); } finally { sqlApi.dispose(); } return null; }