/*
/*
* Copyright 2012 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.load;
import static java.sql.Types.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import javax.inject.Inject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
/**
* EventLoaderRepository implementation that loads Event data into a relational database using the JDBC API.
* @author CraigWalls
*/
@Repository
public class JdbcEventLoaderRepository implements EventLoaderRepository {
private static final Logger logger = LoggerFactory.getLogger(JdbcEventLoaderRepository.class);
private final JdbcTemplate jdbcTemplate;
@Inject
public JdbcEventLoaderRepository(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Transactional
public long loadEvent(EventData eventData, VenueData venueData) {
try {
// update if it already exists
int eventId = jdbcTemplate.queryForInt(SELECT_EXTERNAL_EVENT, eventData.getSourceId(), eventData.getSource()); // TODO: Fetch date for comparison check
jdbcTemplate.update(UPDATE_EVENT, eventData.getName(), eventData.getDescription(), eventData.getTimeZone(), eventData.getFirstDay(), eventData.getLastDay(), eventData.getAbbreviation(), eventId);
logger.info("Updated event (ID = " + eventId + ")");
int venueId = jdbcTemplate.queryForInt(SELECT_EVENT_VENUE, eventId);
jdbcTemplate.update(UPDATE_VENUE, venueData.getName(), venueData.getPostalAddress(), venueData.getLatitude(), venueData.getLongitude(), venueData.getLocationHint(), 1, venueId);
logger.info("Updated venue (ID = " + venueId + ")");
return eventId;
} catch (IncorrectResultSizeDataAccessException e) {
// insert if it doesn't exist
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreatorFactory(INSERT_EVENT, new int[] {VARCHAR, VARCHAR, VARCHAR, TIMESTAMP, TIMESTAMP, VARCHAR, BIGINT})
.newPreparedStatementCreator(new Object[] {eventData.getName(), eventData.getDescription(), eventData.getTimeZone(), eventData.getFirstDay(), eventData.getLastDay(), eventData.getAbbreviation(), eventData.getMemberGroupId()}), keyHolder);
Number eventId = keyHolder.getKey();
logger.info("Created event (ID = " + eventId + ")");
jdbcTemplate.update(new PreparedStatementCreatorFactory(INSERT_VENUE, new int[] {VARCHAR, VARCHAR, DOUBLE, DOUBLE, VARCHAR, BIGINT})
.newPreparedStatementCreator(new Object[] {venueData.getName(), venueData.getPostalAddress(), venueData.getLatitude(), venueData.getLongitude(), venueData.getLocationHint(), 1}), keyHolder);
Number venueId = keyHolder.getKey();
logger.info("Created vanue (ID = " + venueId + ")");
jdbcTemplate.update(INSERT_EVENT_VENUE, eventId, venueId);
jdbcTemplate.update(INSERT_EXTERNAL_EVENT, eventId, eventData.getSourceId(), eventData.getSource(), new Date());
return eventId.longValue();
}
}
@Transactional
public long loadLeader(LeaderData leaderData) {
try {
// update if it already exists
int leaderId = jdbcTemplate.queryForInt(SELECT_EXTERNAL_LEADER, leaderData.getSourceId(), leaderData.getSource());
jdbcTemplate.update(UPDATE_LEADER, leaderData.getName(), leaderData.getBio(), leaderData.getPersonalUrl(), leaderData.getTwitterId(), leaderId);
logger.info("Updated leader (ID = " + leaderId + ")");
return leaderId;
} catch (IncorrectResultSizeDataAccessException e) {
// insert if it doesn't exist
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreatorFactory(INSERT_LEADER, new int[] {VARCHAR, VARCHAR, VARCHAR, VARCHAR})
.newPreparedStatementCreator(new Object[] {leaderData.getName(), leaderData.getBio(), leaderData.getPersonalUrl(), leaderData.getTwitterId()}), keyHolder);
long leaderId = keyHolder.getKey().longValue();
jdbcTemplate.update(INSERT_EXTERNAL_LEADER, leaderId, leaderData.getSourceId(), leaderData.getSource(), new Date());
logger.info("Created leader (ID = " + leaderId + ")");
return leaderId;
}
};
@Transactional
public long loadTimeSlot(TimeSlotData timeSlotData) {
try {
// update if it already exists
int timeSlotId = jdbcTemplate.queryForInt(SELECT_EXTERNAL_TIMESLOT, timeSlotData.getSourceId(), timeSlotData.getSource());
jdbcTemplate.update(UPDATE_TIMESLOT, timeSlotData.getEventId(), timeSlotData.getLabel(), timeSlotData.getStartTime(), timeSlotData.getEndTime(), timeSlotId);
logger.info("Updated timeslot (ID = " + timeSlotId + ")");
return timeSlotId;
} catch (IncorrectResultSizeDataAccessException e) {
// insert if it doesn't exist
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreatorFactory(INSERT_TIMESLOT, new int[] {BIGINT, VARCHAR, TIMESTAMP, TIMESTAMP})
.newPreparedStatementCreator(new Object[] {timeSlotData.getEventId(), timeSlotData.getLabel(), timeSlotData.getStartTime(), timeSlotData.getEndTime()}), keyHolder);
long timeSlotId = keyHolder.getKey().longValue();
jdbcTemplate.update(INSERT_EXTERNAL_TIMESLOT, timeSlotId, timeSlotData.getSourceId(), timeSlotData.getSource(), new Date());
logger.info("Created timeslot (ID = " + timeSlotId + ")");
return timeSlotId;
}
}
@Transactional
public int loadEventSession(EventSessionData sessionData) {
try {
long[] sessionKey = jdbcTemplate.queryForObject(SELECT_EXTERNAL_SESSION,
new RowMapper<long[]>() {
public long[] mapRow(ResultSet rs, int rowNum) throws SQLException {
return new long[] {rs.getLong("event"), rs.getInt("sessionId")};
}
}, sessionData.getSource(), sessionData.getSourceId());
jdbcTemplate.update(UPDATE_SESSION,
sessionData.getTitle(), sessionData.getDescription(), sessionData.getHashtag(), sessionData.getVenue(), sessionData.getTimeslot(), sessionKey[0], sessionKey[1]);
logger.info("Updated session (EVENT = " + sessionKey[0] + ", ID = " + sessionKey[1] + ")");
return 1;
} catch (IncorrectResultSizeDataAccessException e) {
// insert if it doesn't exist
int newSessionId = jdbcTemplate.queryForInt("select max(id) from EventSession where event=?", sessionData.getEvent()) + 1;
jdbcTemplate.update(INSERT_SESSION, sessionData.getEvent(), newSessionId, sessionData.getTitle(), sessionData.getDescription(), sessionData.getHashtag(), sessionData.getVenue(), sessionData.getTimeslot());
jdbcTemplate.update(INSERT_EXTERNAL_SESSION, sessionData.getEvent(), newSessionId, sessionData.getSource(), sessionData.getSourceId(), new Date());
List<Long> leaderIds = sessionData.getLeaderIds();
int rank = 1;
for (Long leaderId : leaderIds) {
jdbcTemplate.update(INSERT_SESSION_LEADER, sessionData.getEvent(), newSessionId, leaderId, rank++);
}
logger.info("Created session (EVENT = " + sessionData.getEvent() + ", ID = " + newSessionId + ")");
return newSessionId;
}
}
private static final String SELECT_EXTERNAL_EVENT = "select event from ExternalEvent where sourceId = ? and source = ?";
private static final String INSERT_EVENT = "insert into Event (title, description, timeZone, startTime, endTime, slug, memberGroup) values (?, ?, ?, ?, ?, ?, ?)";
private static final String INSERT_VENUE = "insert into Venue (name, postalAddress, latitude, longitude, locationHint, createdBy) values (?, ?, ?, ?, ?, ?)";
private static final String INSERT_EVENT_VENUE = "insert into EventVenue (event, venue) values (?, ?)";
private static final String UPDATE_VENUE = "update venue set name = ?, postalAddress = ?, latitude = ?, longitude = ?, locationHint = ?, createdBy = ? where id = ?";
private static final String SELECT_EVENT_VENUE = "select venue from EventVenue where event=?";
private static final String INSERT_EXTERNAL_EVENT = "insert into ExternalEvent (event, sourceId, source, lastUpdated) values (?, ?, ?, ?)";
private static final String UPDATE_EVENT = "update Event set title = ?, description = ?, timeZone = ?, startTime = ?, endTime = ?, slug = ? where id = ?";
private static final String INSERT_EXTERNAL_LEADER = "insert into ExternalLeader (leader, sourceId, source, lastUpdated) values (?, ?, ?, ?)";
private static final String INSERT_LEADER = "insert into Leader (name, bio, personalUrl, twitterUsername) values (?, ?, ?, ?)";
private static final String UPDATE_LEADER = "update Leader set name=?, bio=?, personalUrl=?, twitterUsername=? where id=?";
private static final String SELECT_EXTERNAL_LEADER = "select leader from ExternalLeader where sourceId = ? and source = ?";
private static final String INSERT_TIMESLOT = "insert into EventTimeSlot (event, label, startTime, endTime) values (?, ?, ?, ?)";
private static final String INSERT_EXTERNAL_TIMESLOT = "insert into ExternalEventTimeSlot (timeSlot, sourceId, source, lastUpdated) values (?, ?, ?, ?)";
private static final String SELECT_EXTERNAL_TIMESLOT = "select timeSlot from ExternalEventTimeSlot where sourceId = ? and source = ?";
private static final String UPDATE_TIMESLOT = "update EventTimeSlot set event=?, label=?, startTime=?, endTime=? where id=?";
private static final String INSERT_SESSION = "insert into EventSession (event, id, title, description, hashtag, venue, timeslot) values (?, ?, ?, ?, ?, ?, ?)";
private static final String SELECT_EXTERNAL_SESSION = "select event, sessionId from ExternalEventSession where source=? and sourceId=?";
private static final String UPDATE_SESSION = "update EventSession set title=?, description=?, hashtag=?, venue=?, timeslot=? where event=? and id=?";
private static final String INSERT_EXTERNAL_SESSION = "insert into ExternalEventSession (event, sessionId, source, sourceId, lastUpdated) values (?, ?, ?, ?, ?)";
private static final String INSERT_SESSION_LEADER = "insert into EventSessionLeader (event, session, leader, rank) values (?, ?, ?, ?)";
}