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.Location;
import at.fhj.itm.model.User;
/**
* Implementation of the User Dao interface using a MySQl database as data
* source.
*
* @author Seuchter
*
*/
public class MySqlUserDAO implements UserDAO {
private final LocationDAO locationDAO;
private static final String GET_USER_BY_ID = "SELECT id,username,firstname,lastname,password,"
+ "email,phone,location,last_login,session from users WHERE id = ?";
private static final String GET_USER_BY_MAIL_AND_PHONE = "SELECT id,username,firstname,lastname,password,"
+ "email,phone,location,last_login,session from users WHERE email = ? AND phone = ?";
private static final String GET_USER_BY_MAIL_AND_USERNAME = "SELECT id,username,firstname,lastname,password,"
+ "email,phone,location,last_login,session from users WHERE email = ? AND username = ?";
private static final String GET_USER_BY_MAIL_AND_PASSWORD = "SELECT id,username,firstname,lastname,password,"
+ "email,phone,location,last_login,session from users WHERE email = ? AND password = ?";
private static final String GET_ALL_USERS = "SELECT id,username,firstname,lastname,password,"
+ "email,phone,location,last_login,session from users";
private static final String GET_USER_BY_SESSION_ID = "SELECT id,username,firstname,lastname,password,"
+ "email,phone,location,last_login,session from users WHERE `session`=?";
private static final String INSERT_USER = "INSERT INTO `users` "
+ "(`username`, `password`, `firstname`, `lastname`, `email`, `phone`, "
+ "`last_login`, `session`, `location`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?)";
private static final String UPDATE_USER = "UPDATE `users` SET `username`= ?, `password` = ?, `firstname` = ?,"
+ "`lastname`= ?, `email`=?, `phone`=?, `last_login` = ?, "
+ "`session`= ?, `location`= ? WHERE `id`= ?";
private static final String DELETE_USER = "DELETE FROM `locations` l INNER JOIN `drive_together`.`users` u ON l.id = u.location WHERE u.id = ?";
private static final String USER_VALID = "SELECT id,username,firstname,lastname,password,"
+ "email,phone,last_login,session,location from users WHERE id = ?";
private static final String MAIL_UNIQUE = "SELECT count(`id`) as 'count' FROM `users` WHERE `email`= ?";
private static final String PHONE_UNIQUE = "SELECT count(`id`) as 'count' FROM `users` WHERE `phone`= ?";
private static final String USERNAME_UNIQUE = "SELECT count(`id`) as 'count' FROM `users` WHERE `username`= ?";;
// private static final String GET_USER_BY_SESSION_ID =
// "SELECT id as 'count' FROM `drive_together`.`users` WHERE `session`=?";
public MySqlUserDAO(LocationDAO locationDAO) {
this.locationDAO = locationDAO;
}
private User getFromResultSet(ResultSet set, Connection connection)
throws SQLException {
int id = set.getInt("id");
String userName = set.getString("username");
String password = set.getString("password");
String firstName = set.getString("firstname");
String lastName = set.getString("lastname");
String eMail = set.getString("email");
String phone = set.getString("phone");
int location = set.getInt("location");
java.util.Date lastLogin = set.getTimestamp("last_login");
String session = set.getString("session");
Location tmp_location = this.locationDAO.getByID(location, connection);
return new User(id, firstName, lastName, userName, password, eMail,
phone, tmp_location, lastLogin, session);
}
@Override
public String getIdentifier() {
return "MYSQL";
}
@Override
public void update(User entity, Connection connection) {
try {
if (entity.getId() >= 0) {
// update
PreparedStatement updateStmt = connection
.prepareStatement(UPDATE_USER);
this.locationDAO.update(entity.getLocation(), connection);
updateStmt.setString(1, entity.getUsername());
updateStmt.setString(2, entity.getPassword());
updateStmt.setString(3, entity.getFirstName());
updateStmt.setString(4, entity.getLastName());
updateStmt.setString(5, entity.getEmail());
updateStmt.setString(6, entity.getPhone().replace("+", "00"));
if (entity.getLastLoginDate() == null)
updateStmt.setTimestamp(7, null);
else
updateStmt.setTimestamp(7, new Timestamp(entity
.getLastLoginDate().getTime()));
updateStmt.setString(8, entity.getSessionID());
updateStmt.setInt(9, entity.getLocation().getId());
updateStmt.setInt(10, entity.getId());
updateStmt.execute();
updateStmt.close();
} else {
// insert
PreparedStatement insertStmt = connection
.prepareStatement(INSERT_USER);
this.locationDAO.update(entity.getLocation(), connection);
insertStmt.setString(1, entity.getUsername());
insertStmt.setString(2, entity.getPassword());
insertStmt.setString(3, entity.getFirstName());
insertStmt.setString(4, entity.getLastName());
insertStmt.setString(5, entity.getEmail());
insertStmt.setString(6, entity.getPhone().replace("+", "00"));
insertStmt.setTimestamp(7, new Timestamp(entity
.getLastLoginDate().getTime()));
insertStmt.setString(8, entity.getSessionID());
insertStmt.setInt(9, entity.getLocation().getId());
insertStmt.execute();
int id = MySqlUtil.getLastInsertedID(connection);
entity.setId(id);
insertStmt.close();
}
} catch (SQLException e) {
throw new DAOException("Error updating or deleting.", e);
}
}
@Override
public void delete(User entity, Connection connection) {
if (entity.getId() < 0) {
throw new DAOException(
"Can't delete an entity that is not persisted.");
}
try {
this.locationDAO.delete(entity.getLocation(), connection);
entity.setId(-1);
entity.getLocation().setId(-1);
} catch (Exception e) {
throw new DAOException("Error updating or deleting.", e);
}
}
@Override
public List<User> selectAll(Connection connection) {
try {
List<User> users = new ArrayList<User>();
PreparedStatement stmt = connection.prepareStatement(GET_ALL_USERS);
ResultSet res = stmt.executeQuery();
while (res.next()) {
User u = getFromResultSet(res, connection);
users.add(u);
}
stmt.close();
return Collections.unmodifiableList(users);
} catch (SQLException ex) {
throw new DAOException("Error updating or deleting.", ex);
}
}
@Override
public User userValid(String email, String password, Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(GET_USER_BY_MAIL_AND_PASSWORD);
stmt.setString(1, email);
stmt.setString(2, password);
ResultSet res = stmt.executeQuery();
User user = null;
if (res.next()) {
user = getFromResultSet(res, connection);
}
res.close();
stmt.close();
return user;
} catch (SQLException e) {
throw new DAOException("Error retrieving data.", e);
}
}
@Override
public User getUserForSessionId(String sessionId, Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(GET_USER_BY_SESSION_ID);
stmt.setString(1, sessionId);
ResultSet res = stmt.executeQuery();
User user = null;
if (res.next()) {
user = getFromResultSet(res, connection);
}
res.close();
stmt.close();
return user;
} catch (SQLException e) {
throw new DAOException("Error retrieving data.", e);
}
}
@Override
public boolean mailAlreadyUsed(String mail, Connection connection) {
try {
PreparedStatement stmt = connection.prepareStatement(MAIL_UNIQUE);
stmt.setString(1, mail);
ResultSet res = stmt.executeQuery();
res.next();
int count = res.getInt("count");
res.close();
stmt.close();
return count > 0;
} catch (SQLException e) {
throw new DAOException("Error retrieving data.", e);
}
}
@Override
public boolean phoneAlreadyUsed(String phone, Connection connection) {
try {
PreparedStatement stmt = connection.prepareStatement(PHONE_UNIQUE);
stmt.setString(1, phone);
ResultSet res = stmt.executeQuery();
res.next();
int count = res.getInt("count");
res.close();
stmt.close();
return count > 0;
} catch (SQLException e) {
throw new DAOException("Error retrieving data.", e);
}
}
@Override
public boolean usernameAlreadyUsed(String username, Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(USERNAME_UNIQUE);
stmt.setString(1, username);
ResultSet res = stmt.executeQuery();
res.next();
int count = res.getInt("count");
res.close();
stmt.close();
return count > 0;
} catch (SQLException e) {
throw new DAOException("Error retrieving data.", e);
}
}
@Override
public boolean eraseLastLogin(User user, Connection connection) {
user.setLastLoginDate(null);
update(user, connection);
return true;
}
@Override
public User getUserWithEmailAndPhone(String email, String phone,
Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(GET_USER_BY_MAIL_AND_PHONE);
stmt.setString(1, email);
stmt.setString(2, phone);
ResultSet res = stmt.executeQuery();
User user = null;
if (res.next()) {
user = getFromResultSet(res, connection);
}
res.close();
stmt.close();
return user;
} catch (SQLException e) {
throw new DAOException("Error retrieving data.", e);
}
}
@Override
public User getByID(Integer id, Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(GET_USER_BY_ID);
stmt.setInt(1, id);
ResultSet res = stmt.executeQuery();
User user = null;
while (res.next()) {
user = getFromResultSet(res, connection);
}
res.close();
stmt.close();
if (user == null) {
throw new DAOException("No user with id " + id);
}
return user;
} catch (SQLException e) {
throw new DAOException("Error retrieving data.", e);
}
}
@Override
public User getUserWithEmailAndUserName(String email, String username,
Connection connection) {
try {
PreparedStatement stmt = connection
.prepareStatement(GET_USER_BY_MAIL_AND_USERNAME);
stmt.setString(1, email);
stmt.setString(2, username);
ResultSet res = stmt.executeQuery();
User user = null;
if (res.next()) {
user = getFromResultSet(res, connection);
}
res.close();
stmt.close();
return user;
} catch (SQLException e) {
throw new DAOException("Error retrieving data.", e);
}
}
}