package at.fhj.itm.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import at.fhj.itm.model.Location;
import at.fhj.itm.model.Trip;
import at.fhj.itm.model.User;
import at.fhj.itm.model.Waypoint;
/**
* Implementation of the Waypoint DAO for the MySql database
*
* @author Seuchter
*
*/
public class MySqlWaypointDAO implements WaypointDAO {
private static final String GET_ALL_WAYPOINTS = "SELECT id,from_location,to_location,user_id,comment,active FROM waypoints";
private static final String GET_WAYPOINTS_BY_ID = "SELECT id,from_location,to_location,user_id,comment,active FROM waypoints WHERE id = ?";
private static final String UPDATE_WAYPOINT = "UPDATE waypoints SET from_location=?, to_location=?, user_id=?, comment=?, active=? WHERE id = ?";
private static final String INSERT_WAYPOINT = "INSERT INTO waypoints(comment, active, from_location, to_location, user_id) VALUES(?, ?, ?, ?, ?)";
private static final String DELETE_WAYPOINT = "DELETE FROM waypoints WHERE id = ?";
private static final String COUNT_WAYPOINTS = "SELECT COUNT(id) FROM waypoints";
private static final String WAYPOINTS_FOR_USER = "SELECT id, from_location, to_location, comment, active, user_id FROM waypoints WHERE user_id = ?";
private static final String GET_WAYPOINTS_OF_TRIP = "SELECT w.id as id, from_location, to_location, user_id, `comment`, active, user_id FROM waypoints w INNER JOIN trips_waypoints tw ON w.id = tw.waypoint_id WHERE tw.trip_id = ?";
private static final String ADD_WAYPOINT_TO_TRIP = "INSERT INTO trips_waypoints(trip_id,waypoint_id) VALUES(?,?)";
private final LocationDAO locationDAO;
private final UserDAO userDAO;
public MySqlWaypointDAO(UserDAO userDAO, LocationDAO locationDAO) {
this.locationDAO = locationDAO;
this.userDAO = userDAO;
}
private Waypoint getFromResultSet(ResultSet set, Connection connection) throws SQLException {
int id = set.getInt("id");
int from_location = set.getInt("from_location");
int to_location = set.getInt("to_location");
int user_id = set.getInt("user_id");
String comment = set.getString("comment");
boolean active = set.getBoolean("active");
Location fromLocation = this.locationDAO.getByID(from_location,connection);
Location toLocation = this.locationDAO.getByID(to_location, connection);
User user = this.userDAO.getByID(user_id, connection);
return new Waypoint(id, fromLocation, toLocation, user, comment, active);
}
private List<Waypoint> waypointListFromResultSet(ResultSet set, Connection connection)
throws SQLException {
List<Waypoint> waypoints = new ArrayList<Waypoint>();
while (set.next()) {
waypoints.add(getFromResultSet(set, connection));
}
return Collections.unmodifiableList(waypoints);
}
@Override
public String getIdentifier() {
return "MYSQL";
}
@Override
public void update(Waypoint entity, Connection connection) {
try {
if (entity.getId() < 0) {
this.locationDAO.update(entity.getFromLocation(), connection);
this.locationDAO.update(entity.getToLocation(), connection);
PreparedStatement insertStmt = connection
.prepareStatement(INSERT_WAYPOINT);
insertStmt.setString(1, entity.getComment());
insertStmt.setBoolean(2, entity.isActive());
insertStmt.setInt(3, entity.getFromLocation().getId());
insertStmt.setInt(4, entity.getToLocation().getId());
insertStmt.setInt(5, entity.getUser().getId());
insertStmt.execute();
insertStmt.close();
int id = MySqlUtil.getLastInsertedID(connection);
entity.setId(id);
} else {
this.locationDAO.update(entity.getFromLocation(), connection);
this.locationDAO.update(entity.getToLocation(), connection);
PreparedStatement updateStmt = connection
.prepareStatement(UPDATE_WAYPOINT);
updateStmt.setInt(1, entity.getFromLocation().getId());
updateStmt.setInt(2, entity.getToLocation().getId());
updateStmt.setInt(3, entity.getUser().getId());
updateStmt.setString(4, entity.getComment());
updateStmt.setBoolean(5, entity.isActive());
updateStmt.setInt(6, entity.getId());
updateStmt.execute();
updateStmt.close();
}
} catch (SQLException e) {
throw new DAOException("Error updating", e);
}
}
@Override
public void delete(Waypoint entity, Connection connection) {
if (entity.getId() < 0) {
throw new DAOException("Entity is not persisted");
}
try {
PreparedStatement deleteStmt = connection
.prepareStatement(DELETE_WAYPOINT);
deleteStmt.setInt(1, entity.getId());
deleteStmt.execute();
deleteStmt.close();
this.locationDAO.delete(entity.getFromLocation(), connection);
this.locationDAO.delete(entity.getToLocation(), connection);
entity.setId(-1);
} catch (SQLException e) {
throw new DAOException("Error deleting.", e);
}
}
@Override
public List<Waypoint> selectAll(Connection connection) {
try {
List<Waypoint> waypoints = new ArrayList<Waypoint>();
PreparedStatement selectAllStmt = connection
.prepareStatement(GET_ALL_WAYPOINTS);
ResultSet set = selectAllStmt.executeQuery();
while (set.next()) {
Waypoint wp = getFromResultSet(set, connection);
waypoints.add(wp);
}
set.close();
selectAllStmt.close();
return Collections.unmodifiableList(waypoints);
} catch (SQLException e) {
throw new DAOException("Error deleting.", e);
}
}
@Override
public Waypoint getByID(Integer id, Connection connection) {
try {
PreparedStatement selectByIdStmt = connection
.prepareStatement(GET_WAYPOINTS_BY_ID);
selectByIdStmt.setInt(1, id);
ResultSet set = selectByIdStmt.executeQuery();
set.next();
Waypoint wp = getFromResultSet(set, connection);
set.close();
selectByIdStmt.close();
return wp;
} catch (SQLException e) {
throw new DAOException("Error retrieving.", e);
}
}
@Override
public int getTotalCount(Connection connection) {
try {
PreparedStatement countStmt = connection
.prepareStatement(COUNT_WAYPOINTS);
ResultSet set = countStmt.executeQuery();
set.next();
int count = set.getInt(1);
set.close();
countStmt.close();
return count;
} catch (SQLException e) {
throw new DAOException("Error retrieving count.", e);
}
}
@Override
public List<Waypoint> getWaypointsFromUser(User u, Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(WAYPOINTS_FOR_USER);
stmt.setInt(1, u.getId());
ResultSet set = stmt.executeQuery();
List<Waypoint> waypoints = waypointListFromResultSet(set, connection);
set.close();
stmt.close();
return waypoints;
} catch (SQLException e) {
throw new DAOException("Error while retrieving users for waypoint", e);
}
}
@Override
public List<Waypoint> getWaypointsForTrip(Trip trip, Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(GET_WAYPOINTS_OF_TRIP);
stmt.setInt(1, trip.getId());
ResultSet set = stmt.executeQuery();
List<Waypoint> waypoints = waypointListFromResultSet(set, connection);
set.close();
stmt.close();
return Collections.unmodifiableList(waypoints);
} catch (SQLException e) {
throw new DAOException("Error retrieving waypoints of trip", e);
}
}
@Override
public void addWaypointToTrip(Trip trip, Waypoint wp, Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(ADD_WAYPOINT_TO_TRIP);
stmt.setInt(1, trip.getId());
stmt.setInt(2, wp.getId());
stmt.execute();
stmt.close();
} catch (SQLException e) {
throw new DAOException("Error adding waypoint to trip", e);
}
}
}