Java tutorial
/* /* * Copyright 2010 the original author or authors. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package com.springsource.greenhouse.events; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.util.Date; import java.util.List; import javax.inject.Inject; import org.joda.time.DateTime; import org.joda.time.DateTimeZone; import org.joda.time.LocalDate; import org.joda.time.MutableDateTime; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.JoinRowMapper; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; import com.springsource.greenhouse.utils.Location; import com.springsource.greenhouse.utils.ResourceReference; import com.springsource.greenhouse.utils.SubResourceReference; /** * EventRepository implementation that stores Event data in a relational database using the JDBC API. * @author Keith Donald */ @Repository public class JdbcEventRepository implements EventRepository { private final JdbcTemplate jdbcTemplate; @Inject public JdbcEventRepository(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } public List<Event> findUpcomingEvents(Long afterMillis) { return jdbcTemplate.query(SELECT_UPCOMING_EVENTS, eventMapper.list(), new Date(afterMillis != null ? afterMillis : System.currentTimeMillis())); } public Event findEventBySlug(String groupSlug, Integer year, Integer month, String slug) { return jdbcTemplate.queryForObject(SELECT_EVENT_BY_SLUG, eventMapper.single(), groupSlug, year, month, slug); } public String findEventSearchString(Long eventId) { return jdbcTemplate.queryForObject( "select g.hashtag from Event e, MemberGroup g where e.id = ? and e.memberGroup = g.id", String.class, eventId); } public String findSessionSearchString(Long eventId, Integer sessionId) { return jdbcTemplate.queryForObject( "select (select g.hashtag from Event e, MemberGroup g where e.id = ? and e.memberGroup = g.id) || ' ' || hashtag from EventSession where event = ? and id = ?", String.class, eventId, eventId, sessionId); } @Transactional public List<EventSession> findSessionsOnDay(Long eventId, LocalDate day, Long attendeeId) { DateTimeZone eventTimeZone = DateTimeZone.forID( jdbcTemplate.queryForObject("select timezone from Event where id = ?", String.class, eventId)); DateTime dayStart = day.toDateTimeAtStartOfDay(eventTimeZone); DateTime dayEnd = dayStart.plusDays(1); return jdbcTemplate.query(SELECT_SESSIONS_ON_DAY, eventSessionMapper.list(), attendeeId, eventId, dayStart.toDate(), dayEnd.toDate()); } public List<EventSession> findEventFavorites(Long eventId, Long attendeeId) { return jdbcTemplate.query(SELECT_EVENT_FAVORITES, eventSessionMapper.list(), eventId, attendeeId, eventId); } public List<EventSession> findAttendeeFavorites(Long eventId, Long attendeeId) { return jdbcTemplate.query(SELECT_ATTENDEE_FAVORITES, eventSessionMapper.list(), attendeeId, eventId); } @Transactional public boolean toggleFavorite(Long eventId, Integer sessionId, Long attendeeId) { boolean favorite = jdbcTemplate.queryForObject( "select exists(select 1 from EventSessionFavorite where event = ? and session = ? and attendee = ?)", Boolean.class, eventId, sessionId, attendeeId); if (favorite) { jdbcTemplate.update("delete from EventSessionFavorite where event = ? and session = ? and attendee = ?", eventId, sessionId, attendeeId); } else { jdbcTemplate.update("insert into EventSessionFavorite (event, session, attendee) values (?, ?, ?)", eventId, sessionId, attendeeId); } return !favorite; } @Transactional public Float rate(Long eventId, Integer sessionId, Long attendeeId, Rating rating) throws RatingPeriodClosedException { if (!isSessionEnded(eventId, sessionId)) { throw new RatingPeriodClosedException(eventId, sessionId); } boolean rated = jdbcTemplate.queryForObject( "select exists(select 1 from EventSessionRating where event = ? and session = ? and attendee = ?)", Boolean.class, eventId, sessionId, attendeeId); if (rated) { jdbcTemplate.update( "update EventSessionRating set rating = ?, comment = ? where event = ? and session = ? and attendee = ?", rating.getValue(), rating.getComment(), eventId, sessionId, attendeeId); } else { jdbcTemplate.update( "insert into EventSessionRating (event, session, attendee, rating, comment) values (?, ?, ?, ?, ?)", eventId, sessionId, attendeeId, rating.getValue(), rating.getComment()); } Float newAvgRating = jdbcTemplate.queryForObject( "select round(avg(cast(rating as double)) * 2, 0) / 2 from EventSessionRating where event = ? and session = ? group by event, session", Float.class, eventId, sessionId); jdbcTemplate.update("update EventSession set rating = ? where event = ? and id = ?", newAvgRating, eventId, sessionId); return newAvgRating; } @Transactional public long addEvent() { return 0; } // internal helpers private boolean isSessionEnded(Long eventId, Integer sessionId) { Date endTime = jdbcTemplate.queryForObject( "select ts.endTime from EventTimeSlot ts, EventSession s where s.event = ? and s.id = ? and ts.id = s.timeSlot", Date.class, eventId, sessionId); return new Date().after(endTime); } private final JoinRowMapper<Event, Long> eventMapper = new JoinRowMapper<Event, Long>() { protected Long mapId(ResultSet rs) throws SQLException { return rs.getLong("id"); } protected Event mapRoot(Long id, ResultSet rs) throws SQLException { String eventTimeZone = rs.getString("timeZone"); return new Event(id, rs.getString("title"), DateTimeZone.forID(eventTimeZone), adjustEventTimeToUTC(rs.getTimestamp("startTime"), eventTimeZone), adjustEventTimeToUTC(rs.getTimestamp("endTime"), eventTimeZone), rs.getString("slug"), rs.getString("description"), rs.getString("hashtag"), new ResourceReference<String>(rs.getString("groupSlug"), rs.getString("groupName"))); } protected void addChild(Event event, ResultSet rs) throws SQLException { event.addVenue( new Venue(rs.getLong("venueId"), rs.getString("venueName"), rs.getString("venuePostalAddress"), new Location(rs.getDouble("venueLatitude"), rs.getDouble("venueLongitude")), rs.getString("venueLocationHint"))); } }; private final JoinRowMapper<EventSession, Integer> eventSessionMapper = new JoinRowMapper<EventSession, Integer>() { protected Integer mapId(ResultSet rs) throws SQLException { return rs.getInt("id"); } protected EventSession mapRoot(Integer id, ResultSet rs) throws SQLException { String eventTimeZone = "America/New_York"; // HACK: For now hard-code to S2GX 2012's value while sorting this out. return new EventSession(id, rs.getString("title"), adjustEventTimeToUTC(rs.getTimestamp("startTime"), eventTimeZone), adjustEventTimeToUTC(rs.getTimestamp("endTime"), eventTimeZone), rs.getString("description"), rs.getString("hashtag"), rs.getFloat("rating"), new SubResourceReference<Long, Integer>(rs.getLong("venue"), rs.getInt("room"), rs.getString("roomName")), rs.getBoolean("favorite")); } protected void addChild(EventSession session, ResultSet rs) throws SQLException { session.addLeader(new EventSessionLeader(rs.getString("name"))); } }; private static DateTime adjustEventTimeToUTC(Timestamp timestamp, String eventTimeZone) { MutableDateTime mutableDateTime = new DateTime(timestamp).toMutableDateTime(); mutableDateTime.setZoneRetainFields(DateTimeZone.forID(eventTimeZone)); DateTime utcAdjustedDateTime = mutableDateTime.toDateTime().toDateTime(DateTimeZone.UTC); return utcAdjustedDateTime; } private static final String SELECT_FROM_EVENT_SESSION = "select s.id, s.title, ts.startTime, ts.endTime, s.description, s.hashtag, s.rating, s.venue, s.room, r.name as roomName, (f.attendee is not null) as favorite, l.name from EventSession s "; private static final String SELECT_EVENT = "select e.id, e.title, e.timeZone, e.startTime, e.endTime, e.slug, e.description, g.hashtag, g.slug as groupSlug, g.name as groupName, " + "v.id as venueId, v.name as venueName, v.postalAddress as venuePostalAddress, v.latitude as venueLatitude, v.longitude as venueLongitude, v.locationHint as venueLocationHint from Event e " + "inner join MemberGroup g on e.memberGroup = g.id " + "inner join EventVenue ev on e.id = ev.event " + "inner join Venue v on ev.venue = v.id"; private static final String SELECT_UPCOMING_EVENTS = SELECT_EVENT + " where e.endTime > ? order by e.startTime"; private static final String SELECT_EVENT_BY_SLUG = SELECT_EVENT + " where g.slug = ? and extract(year from e.startTime) = ? and extract(month from e.startTime) = ? and e.slug = ?"; private static final String SELECT_SESSIONS_ON_DAY = SELECT_FROM_EVENT_SESSION + "left outer join VenueRoom r on s.venue = r.venue and s.room = r.id " + "left outer join EventSessionFavorite f on s.event = f.event and s.id = f.session and f.attendee = ? " + "inner join EventSessionLeader sl on s.event = sl.event and s.id = sl.session " + "inner join Leader l on sl.leader = l.id " + "inner join EventTimeSlot ts on ts.id = s.timeSlot " + "where s.event = ? and ts.startTime >= ? and ts.endTime <= ? " + "order by ts.startTime, s.id, sl.rank"; private static final String SELECT_EVENT_FAVORITES = SELECT_FROM_EVENT_SESSION + "inner join (select top 10 session, count(*) as favoriteCount from EventSessionFavorite where event = ? group by session) top on s.id = top.session " + "left outer join VenueRoom r on s.venue = r.venue and s.room = r.id " + "left outer join EventSessionFavorite f on s.event = f.event and s.id = f.session and f.attendee = ? " + "inner join EventSessionLeader sl on s.event = sl.event and s.id = sl.session " + "inner join Leader l on sl.leader = l.id " + "inner join EventTimeSlot ts on ts.id = s.timeSlot " + "where s.event = ? " + "order by top.favoriteCount desc, s.id, sl.rank"; private static final String SELECT_ATTENDEE_FAVORITES = SELECT_FROM_EVENT_SESSION + "left outer join VenueRoom r on s.venue = r.venue and s.room = r.id " + "inner join EventSessionFavorite f on s.event = f.event and s.id = f.session and f.attendee = ? " + "inner join EventSessionLeader sl on s.event = sl.event and s.id = sl.session " + "inner join Leader l on sl.leader = l.id " + "inner join EventTimeSlot ts on ts.id = s.timeSlot " + "where s.event = ? " + "order by f.rank, s.id, sl.rank"; }