@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;
}
