package org.mapache.data.mysql;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import org.mapache.business.MapacheException;
import org.mapache.business.blog.Blog;
import org.mapache.business.user.User;
import org.mapache.data.DAOException;
import org.mapache.data.UserDAO;
public class MySQLUserDAO implements UserDAO {
private Connection _oConn;
private PreparedStatement _authenticateUserStmt;
private static final String INSERT_NEW_USER =
"INSERT INTO USERS(Surname,Firstname,Nickname,Password,Email,Homepage) VALUES(?, ?, ?, ?, ?, ?)";
private static final String UPDATE_USER =
"UPDATE Users set Surname = ?, Firstname = ?, Nickname = ?, Password = ?, Email = ?, Homepage = ? WHERE UserNumber = ?";
private static final String DELETE_USER =
"DELETE FROM Users WHERE UserNumber = ?";
private static final String AUTHENTICATE_USER =
"SELECT UserNumber,Surname,Firstname,Nickname,Password,Email,Homepage FROM Users WHERE Nickname = ? AND Password = ?";
private static final String FIND_USER =
"SELECT UserNumber,Surname,Firstname,Nickname,Password,Email,Homepage FROM Users WHERE UserNumber = ?";
private static final String SELECT_USERS =
"SELECT UserNumber,Surname,Firstname,Nickname,Password,Email,Homepage FROM Users";
public MySQLUserDAO() {
}
public synchronized void insertUser(User newUser) throws DAOException {
PreparedStatement stmt;
try {
stmt =
MySQLDAOFactory.createConnection().prepareStatement(INSERT_NEW_USER);
stmt.setString(1, newUser.getSurname());
stmt.setString(2, newUser.getFirstname());
stmt.setString(3, newUser.getNickname());
stmt.setString(4, newUser.getPassword());
stmt.setString(5, newUser.getEmail());
stmt.setString(6, newUser.getHomepage());
stmt.execute();
} catch (SQLException e) {
throw new DAOException("Unable to create new user in database ",
e);
}
}
public synchronized void deleteUser(User user) throws DAOException {
PreparedStatement stmt;
try {
stmt =
MySQLDAOFactory.createConnection().prepareStatement(DELETE_USER);
stmt.setInt(1, user.getUserID());
stmt.execute();
} catch (SQLException e) {
throw new DAOException("Unable to delete User with UserNumber " +
user.getUserID(), e);
}
}
public synchronized User findUserById(int userNumber) throws DAOException {
User user = null;
PreparedStatement stmt;
try {
stmt =
MySQLDAOFactory.createConnection().prepareStatement(FIND_USER);
stmt.setInt(1, userNumber);
ResultSet found = stmt.executeQuery();
if (found.next())
user = fetchUser(found);
} catch (SQLException e) {
throw new DAOException("Unable to find User with UserNumber " +
userNumber, e);
}
return user;
}
public synchronized void updateUser(User user) throws DAOException {
PreparedStatement stmt;
try {
stmt =
MySQLDAOFactory.createConnection().prepareStatement(UPDATE_USER);
stmt.setString(1, user.getSurname());
stmt.setString(2, user.getFirstname());
stmt.setString(3, user.getNickname());
stmt.setString(4, user.getPassword());
stmt.setString(5, user.getEmail());
stmt.setString(6, user.getHomepage());
stmt.setInt(7, user.getUserID());
stmt.execute();
} catch (SQLException e) {
throw new DAOException("Unable to update User with UserNumber " +
user.getUserID(), e);
}
}
public synchronized Collection<User> searchUsers() throws DAOException {
Collection<User> list = null;
PreparedStatement stmt;
try {
stmt =
MySQLDAOFactory.createConnection().prepareStatement(SELECT_USERS);
ResultSet users = stmt.executeQuery();
list = fetchUsers(users);
} catch (SQLException e) {
throw new DAOException("Unable to collect Users", e);
}
return list;
}
public synchronized boolean authenticateUser(User user) throws MapacheException {
try {
_oConn =MySQLDAOFactory.createConnection();
_authenticateUserStmt = _oConn.prepareStatement("SELECT UserID,Surname,Firstname,Nickname,Password,Email,Homepage FROM Users WHERE Nickname = ? AND Password = ?");
_authenticateUserStmt.setString(1, user.getNickname());
_authenticateUserStmt.setString(2, user.getPassword());
ResultSet found = _authenticateUserStmt.executeQuery();
if (found.next())
return true;
else
return false;
} catch (SQLException e) {
throw new MapacheException("Unable to authenticate user",e);
} catch (DAOException e) {
throw new MapacheException("Unable to authenticate user",e);
} finally{
MySQLDAOFactory.freeConnection(_oConn);
}
}
private User fetchUser(ResultSet userRecord) throws SQLException {
User user = new User();
user.setUserID(userRecord.getInt("UserNumber"));
user.setSurname(userRecord.getString("Surname"));
user.setFirstname(userRecord.getString("Firstname"));
user.setNickname(userRecord.getString("Nickname"));
user.setPassword(userRecord.getString("Password"));
user.setEmail(userRecord.getString("Email"));
user.setHomepage(userRecord.getString("Homepage"));
return user;
}
private Collection<User> fetchUsers(ResultSet users) throws SQLException {
Collection<User> list = new ArrayList<User>();
while (users.next()) {
list.add(fetchUser(users));
}
return list;
}
public User findOwner(Blog blog) {
return null;
}
}