package at.fhj.itm.dao;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
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 org.apache.log4j.Logger;
import at.fhj.itm.model.Point;
import at.fhj.itm.model.Trip;
public class MySqlPointDAO implements PointDAO {
private static final String INSERT_POINT = "INSERT INTO points (`order`, lng, lat,duration, trip_id) VALUES(?,?,?,?,?)";
private static final String UPDATE_POINT = "UPDATE points SET `order`=?, lng = ?, lat=?, duration = ?, trip_id= ? WHERE ID = ?";
private static final String GET_BY_ID = "SELECT id, `order`, lng, lat, duration, trip_id FROM points WHERE id = ?";
private static final String GET_ALL = "SELECT id, `order`, lng, lat, duration, trip_id FROM points";
private static final String GET_BY_TRIP_ID = "SELECT id, `order`,lng, lat, duration, trip_id FROM points WHERE trip_id = ? ORDER BY `order` ASC";
private static final String DELETE_BY_ID = "DELETE FROM points WHERE id = ?";
private static final String DELETE_BY_TRIP = "DELETE FROM points WHERE trip_id = ?";
@Override
public String getIdentifier() {
return "MYSQL";
}
@Override
public void update(Point entity, Connection connection) {
try {
if (entity.getId() < 0) {
PreparedStatement insertStmt = connection
.prepareStatement(INSERT_POINT);
insertStmt = this.setUpStatement(insertStmt, entity, false);
insertStmt.execute();
insertStmt.close();
int id = MySqlUtil.getLastInsertedID(connection);
entity.setId(id);
} else if (entity.getId() >= 0) {
PreparedStatement insertStmt = connection
.prepareStatement(UPDATE_POINT);
insertStmt = this.setUpStatement(insertStmt, entity, true);
insertStmt.execute();
insertStmt.close();
}
} catch (SQLException e) {
throw new DAOException("Error deleting/updating point", e);
}
}
@Override
public void delete(Point entity, Connection connection) {
try {
PreparedStatement stmt = connection.prepareStatement(DELETE_BY_ID);
stmt.setInt(1, entity.getId());
stmt.execute();
stmt.close();
entity.setId(-1);
} catch (SQLException e) {
throw new DAOException("Error deleting point", e);
}
}
private Point getFromResultSet(ResultSet set) throws SQLException {
int id = set.getInt("id");
int order = set.getInt("order");
double lng = set.getDouble("lng");
double lat = set.getDouble("lat");
int duration = set.getInt("duration");
int tripId = set.getInt("trip_id");
return new Point(id, order, lng, lat, duration, tripId);
}
private List<Point> getPointsFromResultSet(ResultSet set)
throws SQLException {
List<Point> points = new ArrayList<Point>();
while (set.next()) {
Point p = getFromResultSet(set);
points.add(p);
}
return Collections.unmodifiableList(points);
}
public List<Point> selectAll(Connection connection) {
try {
PreparedStatement stmt = connection.prepareStatement(GET_ALL);
ResultSet set = stmt.executeQuery();
List<Point> points = getPointsFromResultSet(set);
set.close();
stmt.close();
return Collections.unmodifiableList(points);
} catch (SQLException e) {
throw new DAOException("Error retrieving all points", e);
}
}
@Override
public Point getByID(Integer id, Connection connection) {
try {
PreparedStatement stmt = connection.prepareStatement(GET_BY_ID);
stmt.setInt(1, id);
ResultSet set = stmt.executeQuery();
set.next();
Point p = getFromResultSet(set);
set.close();
stmt.close();
return p;
} catch (SQLException e) {
throw new DAOException("Error retrieving point by id.", e);
}
}
protected List<Point> getByTrip(int tripID, Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(GET_BY_TRIP_ID);
stmt.setInt(1, tripID);
ResultSet set = stmt.executeQuery();
List<Point> points = getPointsFromResultSet(set);
set.close();
stmt.close();
return Collections.unmodifiableList(points);
} catch (SQLException e) {
throw new DAOException("Error retrieving points by id.", e);
}
}
public List<Point> getByTrip(Trip trip, Connection connection) {
return getByTrip(trip.getId(), connection);
}
protected void deletePointsForTrip(int tripId, Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(DELETE_BY_TRIP);
stmt.setInt(1, tripId);
stmt.execute();
stmt.close();
} catch (SQLException e) {
throw new DAOException("Error deleting point", e);
}
}
@Override
public void deletePointsForTrip(Trip trip, Connection connection) {
deletePointsForTrip(trip.getId(), connection);
}
/**
* Helper Method to set up a PreparedStatement.
*
* @param insertStmt
* PreparedStatement which should be set up.
* @param entity
* Point which should be inserted.
* @param update
* True if a update statement should be prepared, False for
* inserting.
* @return configured PreparedStatement
* @throws SQLException
*/
private PreparedStatement setUpStatement(PreparedStatement insertStmt,
Point entity, boolean update) throws SQLException {
insertStmt.setInt(1, entity.getOrder());
insertStmt.setDouble(2, entity.getLng());
insertStmt.setDouble(3, entity.getLat());
insertStmt.setInt(4, entity.getDuration());
insertStmt.setInt(5, entity.getTripId());
if (update)
insertStmt.setInt(6, entity.getId());
insertStmt.addBatch();
return insertStmt;
}
/**
* Persists a List of points.
*
* WARNING: may not update the id-fields! Be sure not to call this more than
* once. If you need the ids you should retrieve the now persisted points
* from the dao.
*
* If the database doesn't support a batchUpdate, the points will be
* inserted one by one.
*
* @see DatabaseMetaData#supportsBatchUpdates
*/
@Override
public void insertPoints(List<Point> entities, Connection connection) {
Logger logger = Logger.getLogger(MySqlPointDAO.class);
boolean useBatchUpdate = false;
try {
useBatchUpdate = connection.getMetaData().supportsBatchUpdates();
logger.debug("useBatchUpdate=" + useBatchUpdate);
} catch (SQLException e1) {
Logger.getLogger(getClass()).info(
"error in retrieving MetaData -> using single updates");
}
PreparedStatement insertStmt = null;
try {
if (useBatchUpdate) {
insertStmt = connection.prepareStatement(INSERT_POINT);
for (int i = 0; i < entities.size(); i++) {
Point entity = entities.get(i);
insertStmt = this.setUpStatement(insertStmt, entity, false);
if ((i + 1) % 1000 == 0) {
insertStmt.executeBatch(); // Execute every 1000 items.
// some DB-systems have a
// maximum.
}
}
insertStmt.executeBatch();
} else {
for (Point entity : entities)
this.update(entity, connection);
}
} catch (SQLException e) {
throw new DAOException("Error inserting points", e);
} finally {
if (insertStmt != null)
try {
insertStmt.close();
} catch (SQLException e) {
logger.error("failed to close insert statement");
}
}
}
}