package at.fhj.itm.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import at.fhj.itm.model.Trip;
import at.fhj.itm.model.User;
import at.fhj.itm.model.Waypoint;
/**
* DAO Implementation for the Trip entity
*
* @author Seuchter
*
*/
public class MySqlTripDAO implements TripDAO {
public static final String GET_ALL_TRIPS = "SELECT id, user_id, departure, seats, waypoint, copyright FROM trips";
public static final String GET_ALL_TRIPS_FROM_USER = "SELECT id, departure, seats, waypoint, copyright FROM trips WHERE user_id = ?";
public static final String GET_TRIP_BY_ID = "SELECT id, user_id, departure, seats, waypoint, copyright FROM trips WHERE id = ?";
public static final String UPDATE_TRIP = "UPDATE trips SET user_id = ?, departure = ?, seats= ?, waypoint = ?, copyright= ? WHERE id = ?";
public static final String INSERT_TRIP = "INSERT INTO trips (user_id,departure,seats,waypoint,copyright) VALUES(?,?,?,?,?)";
public static final String DELETE_TRIP = "DELETE FROM trips WHERE id = ?";
public static final String GET_BOOKED_TRIPS_ID = "SELECT DISTINCT t.id FROM trips t INNER JOIN trips_waypoints tw ON t.id = tw.trip_id INNER JOIN waypoints w ON tw.waypoint_id = w.id WHERE w.active = 1 AND w.user_id = ? UNION SELECT t.id FROM trips t INNER JOIN waypoints w ON t.waypoint = w.id WHERE w.active = 1 AND w.user_id = ?";
// public static final String GET_TRIPS_TO_CITY =
// "SELECT t.id AS id, user_id, departure, seats, waypoint FROM trips t INNER JOIN waypoints w1 on from_waypoint = w1.id INNER JOIN waypoints w2 ON to_waypoint = w2.id WHERE UPPER(w2.city) = UPPER(?)";
public static final String GET_TRIPS_TO_CITY = "SELECT t.id AS id,lto.city FROM trips as t INNER JOIN waypoints as w on t.waypoint = w.id INNER JOIN locations as lto on w.to_location = lto.id WHERE lto.city like(?) UNION SELECT t.id,lto.city FROM waypoints as w INNER JOIN locations as lto on w.to_location = lto.id INNER JOIN trips_waypoints as tw on tw.waypoint_id = w.id INNER JOIN trips as t on tw.trip_id = t.id WHERE lto.city like (?) AND w.active = 1";
private final UserDAO userDao;
private final WaypointDAO waypointDAO;
/**
* Creates the dao with the given connection and a DAO factory to handle
* CRUD operations of referencing entity
*
* @param factory
* @param con
*/
public MySqlTripDAO(UserDAO userDao, WaypointDAO waypointDAO) {
this.userDao = userDao;
this.waypointDAO = waypointDAO;
}
/**
* Helper methods which creates an Trip entity from a result set
*
* @param set
* result set which must contain all relevant data
* @return the constructed trip class
* @throws DAOException
* @throws SQLException
*/
private Trip createTripFromResultSet(ResultSet set, Connection connection)
throws DAOException, SQLException {
int id = set.getInt("id");
java.util.Date departureDate = set.getTimestamp("departure");
int seats = set.getByte("seats");
int userId = set.getInt("user_id");
int waypoint = set.getInt("waypoint");
String copyright = set.getString("copyright");
User user = userDao.getByID(userId, connection);
Waypoint wp = waypointDAO.getByID(waypoint, connection);
return new Trip(id, user, departureDate, seats, wp, copyright);
}
/**
* Creates a collection trip class from the given result set
*
* @param set
* which contains all data to create an list of trip classes
* @return
* @throws DAOException
* @throws SQLException
*/
private List<Trip> createTripListFromResultSet(ResultSet set,
Connection connection) throws DAOException, SQLException {
List<Trip> trips = new ArrayList<Trip>();
while (set.next()) {
trips.add(createTripFromResultSet(set, connection));
}
return Collections.unmodifiableList(trips);
}
@Override
public String getIdentifier() {
return "MYSQL";
}
@Override
public void update(Trip entity, Connection connection) {
try {
// update referenced entity first
userDao.update(entity.getUser(), connection);
if (entity.getId() < 0) {
// insert
PreparedStatement stmt = connection
.prepareStatement(INSERT_TRIP);
waypointDAO.update(entity.getWaypoint(), connection);
stmt.setInt(1, entity.getUser().getId());
stmt.setTimestamp(2, new Timestamp(entity.getDeparture()
.getTime()));
stmt.setByte(3, (byte) entity.getSeats());
stmt.setInt(4, entity.getWaypoint().getId());
stmt.setString(5, entity.getCopyright());
stmt.execute();
stmt.close();
int id = MySqlUtil.getLastInsertedID(connection);
entity.setId(id);
} else if (entity.getId() >= 0) {
// update
PreparedStatement stmt = connection
.prepareStatement(UPDATE_TRIP);
waypointDAO.update(entity.getWaypoint(), connection);
stmt.setInt(1, entity.getUser().getId());
stmt.setTimestamp(2, new Timestamp(entity.getDeparture()
.getTime()));
stmt.setByte(3, (byte) entity.getSeats());
stmt.setInt(4, entity.getWaypoint().getId());
stmt.setString(5, entity.getCopyright());
stmt.setInt(6, entity.getId());
stmt.execute();
stmt.close();
}
} catch (SQLException e) {
throw new DAOException("Error inserting/updating trip.", e);
}
}
@Override
public void delete(Trip entity, Connection connection) {
if (entity.getId() == -1) {
throw new DAOException("Can't delete non persisted trip");
}
try {
PreparedStatement stmt = connection.prepareStatement(DELETE_TRIP);
stmt.setInt(1, entity.getId());
stmt.execute();
stmt.close();
waypointDAO.delete(entity.getWaypoint(), connection);
entity.setId(-1);
} catch (SQLException e) {
throw new DAOException("Error while deleting trip", e);
}
}
@Override
public List<Trip> selectAll(Connection connection) {
try {
PreparedStatement stmt = connection.prepareStatement(GET_ALL_TRIPS);
ResultSet set = stmt.executeQuery();
List<Trip> trips = createTripListFromResultSet(set, connection);
set.close();
stmt.close();
return trips;
} catch (SQLException e) {
throw new DAOException("Error retrieving data", e);
}
}
@Override
public List<Trip> allTripsTo(String town, Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(GET_TRIPS_TO_CITY);
stmt.setString(1, town);
stmt.setString(2, town);
ResultSet set = stmt.executeQuery();
List<Trip> trips = new ArrayList<Trip>();
while (set.next()) {
int id = set.getInt("id");
Trip t = getByID(id, connection);
trips.add(t);
}
set.close();
stmt.close();
return Collections.unmodifiableList(trips);
} catch (SQLException e) {
throw new DAOException("Error retrieving data", e);
}
}
@Override
public Trip getByID(Integer id, Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(GET_TRIP_BY_ID);
stmt.setInt(1, id);
ResultSet set = stmt.executeQuery();
set.next();
Trip trip = createTripFromResultSet(set, connection);
set.close();
stmt.close();
return trip;
} catch (SQLException e) {
throw new DAOException("Error retrieving trip from id", e);
}
}
@Override
public List<Trip> allTripsFromUser(int userID, Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(GET_ALL_TRIPS_FROM_USER);
stmt.setInt(1, userID);
ResultSet set = stmt.executeQuery();
List<Trip> trips = new ArrayList<Trip>();
while (set.next()) {
int id = set.getInt("id");
Trip t = getByID(id, connection);
trips.add(t);
}
set.close();
stmt.close();
return Collections.unmodifiableList(trips);
} catch (SQLException e) {
throw new DAOException("Error retrieving data", e);
}
}
@Override
public List<Trip> allBookedTripsByUser(int userID, Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(GET_BOOKED_TRIPS_ID);
stmt.setInt(1, userID);
stmt.setInt(2, userID);
ResultSet set = stmt.executeQuery();
List<Trip> trips = new ArrayList<Trip>();
while (set.next()) {
int id = set.getInt("id");
Trip t = getByID(id, connection);
trips.add(t);
}
set.close();
stmt.close();
return Collections.unmodifiableList(trips);
} catch (SQLException e) {
throw new DAOException("Error retrieving data", e);
}
}
}