/*
* Copyright 2007 Zhang, Zheng <oldbig@gmail.com> Xu, Chuan <xuchuan@gmail.com>
*
* This file is part of ZOJ.
*
* ZOJ is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as
* published by the Free Software Foundation; either revision 3 of the License, or (at your option) any later revision.
*
* ZOJ is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License along with ZOJ. if not, see
* <http://www.gnu.org/licenses/>.
*/
package cn.edu.zju.acm.onlinejudge.persistence.sql;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import cn.edu.zju.acm.onlinejudge.bean.UserPreference;
import cn.edu.zju.acm.onlinejudge.bean.UserProfile;
import cn.edu.zju.acm.onlinejudge.bean.enumeration.Country;
import cn.edu.zju.acm.onlinejudge.bean.request.UserCriteria;
import cn.edu.zju.acm.onlinejudge.persistence.PersistenceCreationException;
import cn.edu.zju.acm.onlinejudge.persistence.PersistenceException;
import cn.edu.zju.acm.onlinejudge.persistence.UserPersistence;
/**
* <p>
* UserPersistenceImpl implements UserPersistence interface
* </p>
* <p>
* UserPersistence interface defines the API used to manager the user profile related affairs in persistence layer.
* </p>
*
* @version 2.0
* @author Zhang, Zheng
* @author Xu, Chuan
*/
public class UserPersistenceImpl implements UserPersistence {
/**
* The query to get last id.
*/
private static final String GET_LAST_ID = "SELECT LAST_INSERT_ID()";
/**
* The query to select all countries.
*/
private static final String GET_ALL_COUNTRIES =
MessageFormat.format("SELECT {0}, {1} FROM {2}", new Object[] {DatabaseConstants.COUNTRY_COUNTRY_ID,
DatabaseConstants.COUNTRY_NAME,
DatabaseConstants.COUNTRY_TABLE});
/**
* The statement to create a user.
*/
private static final String INSERT_USER =
MessageFormat.format("INSERT INTO {0} ({1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, "
+ "{13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25}, {26}, {27}, {28}) "
+ "VALUES(?, MD5(?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 1, ?)",
new Object[] {DatabaseConstants.USER_PROFILE_TABLE,
DatabaseConstants.USER_PROFILE_HANDLE,
DatabaseConstants.USER_PROFILE_PASSWORD,
DatabaseConstants.USER_PROFILE_EMAIL_ADDRESS,
DatabaseConstants.USER_PROFILE_REG_DATE,
DatabaseConstants.USER_PROFILE_FIRST_NAME,
DatabaseConstants.USER_PROFILE_LAST_NAME,
DatabaseConstants.USER_PROFILE_ADDRESS_LINE1,
DatabaseConstants.USER_PROFILE_ADDRESS_LINE2,
DatabaseConstants.USER_PROFILE_CITY,
DatabaseConstants.USER_PROFILE_STATE,
DatabaseConstants.USER_PROFILE_COUNTRY_ID,
DatabaseConstants.USER_PROFILE_ZIP_CODE,
DatabaseConstants.USER_PROFILE_PHONE_NUMBER,
DatabaseConstants.USER_PROFILE_BIRTH_DATE,
DatabaseConstants.USER_PROFILE_GENDER,
DatabaseConstants.USER_PROFILE_SCHOOL,
DatabaseConstants.USER_PROFILE_MAJOR,
DatabaseConstants.USER_PROFILE_GRADUATE_STUDENT,
DatabaseConstants.USER_PROFILE_GRADUATION_YEAR,
DatabaseConstants.USER_PROFILE_STUDENT_NUMBER,
DatabaseConstants.USER_PROFILE_CONFIRMED,
DatabaseConstants.CREATE_USER,
DatabaseConstants.CREATE_DATE,
DatabaseConstants.LAST_UPDATE_USER,
DatabaseConstants.LAST_UPDATE_DATE,
DatabaseConstants.USER_PROFILE_SUPER_ADMIN,
DatabaseConstants.USER_PROFILE_ACTIVE,
"nickname"});
/**
* The statement to create a user.
*/
private static final String INSERT_TEACHER =
MessageFormat.format("INSERT INTO {0} ({1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, "
+ "{13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25}, {26}, {27}, {28}) "
+ "VALUES(?, MD5(?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 1, ?)",
new Object[] {"teacher",
DatabaseConstants.USER_PROFILE_HANDLE,
DatabaseConstants.USER_PROFILE_PASSWORD,
DatabaseConstants.USER_PROFILE_EMAIL_ADDRESS,
DatabaseConstants.USER_PROFILE_REG_DATE,
DatabaseConstants.USER_PROFILE_FIRST_NAME,
DatabaseConstants.USER_PROFILE_LAST_NAME,
DatabaseConstants.USER_PROFILE_ADDRESS_LINE1,
DatabaseConstants.USER_PROFILE_ADDRESS_LINE2,
DatabaseConstants.USER_PROFILE_CITY,
DatabaseConstants.USER_PROFILE_STATE,
DatabaseConstants.USER_PROFILE_COUNTRY_ID,
DatabaseConstants.USER_PROFILE_ZIP_CODE,
DatabaseConstants.USER_PROFILE_PHONE_NUMBER,
DatabaseConstants.USER_PROFILE_BIRTH_DATE,
DatabaseConstants.USER_PROFILE_GENDER,
DatabaseConstants.USER_PROFILE_SCHOOL,
DatabaseConstants.USER_PROFILE_MAJOR,
DatabaseConstants.USER_PROFILE_GRADUATE_STUDENT,
DatabaseConstants.USER_PROFILE_GRADUATION_YEAR,
DatabaseConstants.USER_PROFILE_STUDENT_NUMBER,
DatabaseConstants.USER_PROFILE_CONFIRMED,
DatabaseConstants.CREATE_USER,
DatabaseConstants.CREATE_DATE,
DatabaseConstants.LAST_UPDATE_USER,
DatabaseConstants.LAST_UPDATE_DATE,
DatabaseConstants.USER_PROFILE_SUPER_ADMIN,
DatabaseConstants.USER_PROFILE_ACTIVE,
"nickname"});
/**
* The statement to update a user.
*/
private static final String UPDATE_USER =
MessageFormat.format("UPDATE {0} SET {1}=?, {2}=MD5(?), {3}=?, {4}=?, {5}=?, {6}=?, {7}=?, {8}=?, {9}=?, "
+ "{10}=?, {11}=?, {12}=?, {13}=?, {14}=?, {15}=?, {16}=?, {17}=?, {18}=?, "
+ "{19}=?, {20}=?, {21}=?, {22}=?, {23}=? WHERE {24}=?",
new Object[] {DatabaseConstants.USER_PROFILE_TABLE,
DatabaseConstants.USER_PROFILE_HANDLE,
DatabaseConstants.USER_PROFILE_PASSWORD,
DatabaseConstants.USER_PROFILE_EMAIL_ADDRESS,
DatabaseConstants.USER_PROFILE_FIRST_NAME,
DatabaseConstants.USER_PROFILE_LAST_NAME,
DatabaseConstants.USER_PROFILE_ADDRESS_LINE1,
DatabaseConstants.USER_PROFILE_ADDRESS_LINE2,
DatabaseConstants.USER_PROFILE_CITY,
DatabaseConstants.USER_PROFILE_STATE,
DatabaseConstants.USER_PROFILE_COUNTRY_ID,
DatabaseConstants.USER_PROFILE_ZIP_CODE,
DatabaseConstants.USER_PROFILE_PHONE_NUMBER,
DatabaseConstants.USER_PROFILE_BIRTH_DATE,
DatabaseConstants.USER_PROFILE_GENDER,
DatabaseConstants.USER_PROFILE_SCHOOL,
DatabaseConstants.USER_PROFILE_MAJOR,
DatabaseConstants.USER_PROFILE_GRADUATE_STUDENT,
DatabaseConstants.USER_PROFILE_GRADUATION_YEAR,
DatabaseConstants.USER_PROFILE_STUDENT_NUMBER,
DatabaseConstants.USER_PROFILE_CONFIRMED,
DatabaseConstants.LAST_UPDATE_USER,
DatabaseConstants.LAST_UPDATE_DATE,
"nickname",
DatabaseConstants.USER_PROFILE_USER_PROFILE_ID});
/**
* The statement to update a user without password.
*/
private static final String UPDATE_USER_1 =
MessageFormat.format("UPDATE {0} SET {1}=?, {2}=?, {3}=?, {4}=?, {5}=?, {6}=?, {7}=?, {8}=?, "
+ "{9}=?, {10}=?, {11}=?, {12}=?, {13}=?, {14}=?, {15}=?, {16}=?, {17}=?, "
+ "{18}=?, {19}=?, {20}=?, {21}=?, {22}=? WHERE {23}=?",
new Object[] {DatabaseConstants.USER_PROFILE_TABLE,
DatabaseConstants.USER_PROFILE_HANDLE,
DatabaseConstants.USER_PROFILE_EMAIL_ADDRESS,
DatabaseConstants.USER_PROFILE_FIRST_NAME,
DatabaseConstants.USER_PROFILE_LAST_NAME,
DatabaseConstants.USER_PROFILE_ADDRESS_LINE1,
DatabaseConstants.USER_PROFILE_ADDRESS_LINE2,
DatabaseConstants.USER_PROFILE_CITY,
DatabaseConstants.USER_PROFILE_STATE,
DatabaseConstants.USER_PROFILE_COUNTRY_ID,
DatabaseConstants.USER_PROFILE_ZIP_CODE,
DatabaseConstants.USER_PROFILE_PHONE_NUMBER,
DatabaseConstants.USER_PROFILE_BIRTH_DATE,
DatabaseConstants.USER_PROFILE_GENDER,
DatabaseConstants.USER_PROFILE_SCHOOL,
DatabaseConstants.USER_PROFILE_MAJOR,
DatabaseConstants.USER_PROFILE_GRADUATE_STUDENT,
DatabaseConstants.USER_PROFILE_GRADUATION_YEAR,
DatabaseConstants.USER_PROFILE_STUDENT_NUMBER,
DatabaseConstants.USER_PROFILE_CONFIRMED,
DatabaseConstants.LAST_UPDATE_USER,
DatabaseConstants.LAST_UPDATE_DATE,
"nickname",
DatabaseConstants.USER_PROFILE_USER_PROFILE_ID});
/**
* The statement to delete a thread.
*/
private static final String DELETE_USER =
MessageFormat.format("UPDATE {0} SET {1}=0, {2}=?, {3}=? WHERE {4}=?",
new Object[] {DatabaseConstants.USER_PROFILE_TABLE,
DatabaseConstants.USER_PROFILE_ACTIVE,
DatabaseConstants.LAST_UPDATE_USER, DatabaseConstants.LAST_UPDATE_DATE,
DatabaseConstants.USER_PROFILE_USER_PROFILE_ID});
/**
* The query to get a user profile.
*/
private static final String GET_USER =
MessageFormat.format("SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, "
+ "{10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, " + "{19}, {20}, {21}, {22}, {23}, {24} FROM {25}",
new Object[] {DatabaseConstants.USER_PROFILE_USER_PROFILE_ID,
DatabaseConstants.USER_PROFILE_HANDLE,
DatabaseConstants.USER_PROFILE_PASSWORD,
DatabaseConstants.USER_PROFILE_EMAIL_ADDRESS,
DatabaseConstants.USER_PROFILE_REG_DATE,
DatabaseConstants.USER_PROFILE_FIRST_NAME,
DatabaseConstants.USER_PROFILE_LAST_NAME,
DatabaseConstants.USER_PROFILE_ADDRESS_LINE1,
DatabaseConstants.USER_PROFILE_ADDRESS_LINE2,
DatabaseConstants.USER_PROFILE_CITY,
DatabaseConstants.USER_PROFILE_STATE,
DatabaseConstants.USER_PROFILE_COUNTRY_ID,
DatabaseConstants.USER_PROFILE_ZIP_CODE,
DatabaseConstants.USER_PROFILE_PHONE_NUMBER,
DatabaseConstants.USER_PROFILE_BIRTH_DATE,
DatabaseConstants.USER_PROFILE_GENDER,
DatabaseConstants.USER_PROFILE_SCHOOL,
DatabaseConstants.USER_PROFILE_MAJOR,
DatabaseConstants.USER_PROFILE_GRADUATE_STUDENT,
DatabaseConstants.USER_PROFILE_GRADUATION_YEAR,
DatabaseConstants.USER_PROFILE_STUDENT_NUMBER,
DatabaseConstants.USER_PROFILE_CONFIRMED,
DatabaseConstants.USER_PROFILE_ACTIVE, "nickname", "old_email",
DatabaseConstants.USER_PROFILE_TABLE});
/**
* The query to get a user profile by id.
*/
private static final String GET_USER_BY_ID =
UserPersistenceImpl.GET_USER + " WHERE " + DatabaseConstants.USER_PROFILE_USER_PROFILE_ID + "=?";
/**
* The query to get a user profile by handle.
*/
private static final String GET_USER_BY_HANDLE =
UserPersistenceImpl.GET_USER + " WHERE " + DatabaseConstants.USER_PROFILE_HANDLE + "=?";
/**
* The query to get a user profile by create user.
*/
private static final String GET_USER_BY_CREATE_USER =
UserPersistenceImpl.GET_USER + " WHERE " + DatabaseConstants.CREATE_USER + "=?";
/**
* The query to login a user.
*/
private static final String LOGIN =
UserPersistenceImpl.GET_USER + " WHERE handle=? AND (password=MD5(?) OR password=ENCRYPT(?, ?))";
/**
* The query to get a user profile by email.
*/
private static final String GET_USER_BY_EMAIL =
UserPersistenceImpl.GET_USER + " WHERE " + DatabaseConstants.USER_PROFILE_EMAIL_ADDRESS + "=?";
/**
* The query to get a user profile by code.
*/
private static final String GET_USER_BY_CODE =
MessageFormat.format(UserPersistenceImpl.GET_USER + " WHERE {0} IN (SELECT {1} FROM {2} WHERE {3}=?)",
new Object[] {DatabaseConstants.USER_PROFILE_USER_PROFILE_ID,
DatabaseConstants.CONFIRMATION_USER_PROFILE_ID,
DatabaseConstants.CONFIRMATION_TABLE,
DatabaseConstants.CONFIRMATION_CODE});
/**
* The statement to create a user preference.
*/
private static final String INSERT_USER_PREFERENCE =
MessageFormat.format("INSERT INTO {0} ({1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}) "
+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
new Object[] {DatabaseConstants.USER_PREFERENCE_TABLE,
DatabaseConstants.USER_PREFERENCE_USER_PROFILE_ID,
DatabaseConstants.USER_PREFERENCE_PLAN,
DatabaseConstants.USER_PREFERENCE_PROBLEM_PAGING,
DatabaseConstants.USER_PREFERENCE_SUBMISSION_PAGING,
DatabaseConstants.USER_PREFERENCE_STATUS_PAGING,
DatabaseConstants.USER_PREFERENCE_USER_PAGING,
DatabaseConstants.USER_PREFERENCE_THREAD_PAGING,
DatabaseConstants.USER_PREFERENCE_POST_PAGING,
DatabaseConstants.CREATE_USER, DatabaseConstants.CREATE_DATE,
DatabaseConstants.LAST_UPDATE_USER, DatabaseConstants.LAST_UPDATE_DATE});
/**
* The statement to update a user preference.
*/
private static final String UPDATE_USER_PREFERENCE =
MessageFormat.format("UPDATE {0} SET {1}=?, {2}=?, {3}=?, {4}=?, {5}=?, {6}=?, {7}=?, {8}=?, {9}=? "
+ "WHERE {10}=?", new Object[] {DatabaseConstants.USER_PREFERENCE_TABLE,
DatabaseConstants.USER_PREFERENCE_PLAN,
DatabaseConstants.USER_PREFERENCE_PROBLEM_PAGING,
DatabaseConstants.USER_PREFERENCE_SUBMISSION_PAGING,
DatabaseConstants.USER_PREFERENCE_STATUS_PAGING,
DatabaseConstants.USER_PREFERENCE_USER_PAGING,
DatabaseConstants.USER_PREFERENCE_THREAD_PAGING,
DatabaseConstants.USER_PREFERENCE_POST_PAGING,
DatabaseConstants.LAST_UPDATE_USER, DatabaseConstants.LAST_UPDATE_DATE,
DatabaseConstants.USER_PREFERENCE_USER_PROFILE_ID});
/**
* The query to get a user preference.
*/
private static final String GET_USER_PREFERENCE =
MessageFormat.format("SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7} FROM {8} WHERE {0}=?",
new Object[] {DatabaseConstants.USER_PREFERENCE_USER_PROFILE_ID,
DatabaseConstants.USER_PREFERENCE_PLAN,
DatabaseConstants.USER_PREFERENCE_PROBLEM_PAGING,
DatabaseConstants.USER_PREFERENCE_SUBMISSION_PAGING,
DatabaseConstants.USER_PREFERENCE_STATUS_PAGING,
DatabaseConstants.USER_PREFERENCE_USER_PAGING,
DatabaseConstants.USER_PREFERENCE_THREAD_PAGING,
DatabaseConstants.USER_PREFERENCE_POST_PAGING,
DatabaseConstants.USER_PREFERENCE_TABLE});
/**
* The statement to create a confirmation code.
*/
private static final String INSERT_CODE =
MessageFormat.format("INSERT INTO {0} ({1}, {2}) VALUES(?, ?)",
new Object[] {DatabaseConstants.CONFIRMATION_TABLE,
DatabaseConstants.CONFIRMATION_USER_PROFILE_ID,
DatabaseConstants.CONFIRMATION_CODE});
/**
* The statement to update a confirmation code.
*/
private static final String UPDATE_CODE =
MessageFormat.format("UPDATE {0} SET {1}=? WHERE {2}=?",
new Object[] {DatabaseConstants.CONFIRMATION_TABLE,
DatabaseConstants.CONFIRMATION_CODE,
DatabaseConstants.CONFIRMATION_USER_PROFILE_ID});
/**
* The statement to delete a confirmation code.
*/
private static final String DELETE_CODE =
MessageFormat.format("DELETE FROM {0} WHERE {1}=?",
new Object[] {DatabaseConstants.CONFIRMATION_TABLE,
DatabaseConstants.CONFIRMATION_USER_PROFILE_ID});
/**
* The query to get a confirmation code.
*/
private static final String GET_CODE =
MessageFormat.format("SELECT {0} FROM {1} WHERE {2}=?",
new Object[] {DatabaseConstants.CONFIRMATION_CODE,
DatabaseConstants.CONFIRMATION_TABLE,
DatabaseConstants.CONFIRMATION_USER_PROFILE_ID});
/**
* The list containing all countries.
*/
private List<Country> allCountries = null;
/**
* UserPersistenceImpl.
*/
public UserPersistenceImpl() {
// empty
}
/**
* <p>
* Creates the specified user profile in persistence layer.
* </p>
*
* @param profile
* the UserProfile instance to create
* @param user
* the id of the user who made this modification
* @throws NullPointerException
* if argument is null
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public void createUserProfile(UserProfile profile, long user) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.INSERT_USER);
ps.setString(1, profile.getHandle());
ps.setString(2, profile.getPassword());
ps.setString(3, profile.getEmail());
ps.setTimestamp(4, new Timestamp(new Date().getTime()));
ps.setString(5, profile.getFirstName());
ps.setString(6, profile.getLastName());
ps.setString(7, profile.getAddressLine1());
ps.setString(8, profile.getAddressLine2());
ps.setString(9, profile.getCity());
ps.setString(10, profile.getState());
ps.setLong(11, profile.getCountry().getId());
ps.setString(12, profile.getZipCode());
ps.setString(13, profile.getPhoneNumber());
ps.setTimestamp(14, new Timestamp(profile.getBirthDate().getTime()));
ps.setString(15, "" + profile.getGender());
ps.setString(16, profile.getSchool());
ps.setString(17, profile.getMajor());
ps.setBoolean(18, profile.isGraduateStudent());
ps.setInt(19, profile.getGraduationYear());
ps.setString(20, profile.getStudentNumber());
ps.setBoolean(21, profile.isConfirmed());
ps.setLong(22, user);
ps.setTimestamp(23, new Timestamp(new Date().getTime()));
ps.setLong(24, user);
ps.setTimestamp(25, new Timestamp(new Date().getTime()));
ps.setString(26, profile.getNickName());
ps.executeUpdate();
} finally {
Database.dispose(ps);
}
try {
ps = conn.prepareStatement(UserPersistenceImpl.GET_LAST_ID);
ResultSet rs = ps.executeQuery();
rs.next();
profile.setId(rs.getLong(1));
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to create user.", e);
} finally {
Database.dispose(conn);
}
}
/**
* <p>
* Creates the specified user profile in persistence layer.
* </p>
*
* @param profile
* the UserProfile instance to create
* @param user
* the id of the user who made this modification
* @throws NullPointerException
* if argument is null
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public void createTeacher(UserProfile profile, long user) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.INSERT_TEACHER);
ps.setString(1, profile.getHandle());
ps.setString(2, profile.getPassword());
ps.setString(3, profile.getEmail());
ps.setTimestamp(4, new Timestamp(new Date().getTime()));
ps.setString(5, profile.getFirstName());
ps.setString(6, profile.getLastName());
ps.setString(7, profile.getAddressLine1());
ps.setString(8, profile.getAddressLine2());
ps.setString(9, profile.getCity());
ps.setString(10, profile.getState());
ps.setLong(11, profile.getCountry().getId());
ps.setString(12, profile.getZipCode());
ps.setString(13, profile.getPhoneNumber());
ps.setTimestamp(14, new Timestamp(profile.getBirthDate().getTime()));
ps.setString(15, "" + profile.getGender());
ps.setString(16, profile.getSchool());
ps.setString(17, profile.getMajor());
ps.setBoolean(18, profile.isGraduateStudent());
ps.setInt(19, profile.getGraduationYear());
ps.setString(20, profile.getStudentNumber());
ps.setBoolean(21, profile.isConfirmed());
ps.setLong(22, user);
ps.setTimestamp(23, new Timestamp(new Date().getTime()));
ps.setLong(24, user);
ps.setTimestamp(25, new Timestamp(new Date().getTime()));
ps.setString(26, profile.getNickName());
ps.executeUpdate();
} finally {
Database.dispose(ps);
}
try {
ps = conn.prepareStatement(UserPersistenceImpl.GET_LAST_ID);
ResultSet rs = ps.executeQuery();
rs.next();
profile.setId(rs.getLong(1));
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to create user.", e);
} finally {
Database.dispose(conn);
}
}
/**
* <p>
* Updates the specified user profile in persistence layer.
* </p>
*
* @param profile
* the UserProfile instance to update
* @param user
* the id of the user who made this modification
* @throws NullPointerException
* if argument is null
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public void updateUserProfile(UserProfile profile, long user) throws PersistenceException {
// TODO(xuchuan): refactor this function
if (profile.getPassword() == null || profile.getPassword().trim().length() == 0) {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.UPDATE_USER_1);
ps.setString(1, profile.getHandle());
ps.setString(2, profile.getEmail());
ps.setString(3, profile.getFirstName());
ps.setString(4, profile.getLastName());
ps.setString(5, profile.getAddressLine1());
ps.setString(6, profile.getAddressLine2());
ps.setString(7, profile.getCity());
ps.setString(8, profile.getState());
ps.setLong(9, profile.getCountry().getId());
ps.setString(10, profile.getZipCode());
ps.setString(11, profile.getPhoneNumber());
ps.setTimestamp(12, new Timestamp(profile.getBirthDate().getTime()));
ps.setString(13, "" + profile.getGender());
ps.setString(14, profile.getSchool());
ps.setString(15, profile.getMajor());
ps.setBoolean(16, profile.isGraduateStudent());
ps.setInt(17, profile.getGraduationYear());
ps.setString(18, profile.getStudentNumber());
ps.setBoolean(19, profile.isConfirmed());
ps.setLong(20, user);
ps.setTimestamp(21, new Timestamp(new Date().getTime()));
ps.setString(22, profile.getNickName());
ps.setLong(23, profile.getId());
ps.executeUpdate();
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to update user.", e);
} finally {
Database.dispose(conn);
}
} else {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.UPDATE_USER);
ps.setString(1, profile.getHandle());
ps.setString(2, profile.getPassword());
ps.setString(3, profile.getEmail());
ps.setString(4, profile.getFirstName());
ps.setString(5, profile.getLastName());
ps.setString(6, profile.getAddressLine1());
ps.setString(7, profile.getAddressLine2());
ps.setString(8, profile.getCity());
ps.setString(9, profile.getState());
ps.setLong(10, profile.getCountry().getId());
ps.setString(11, profile.getZipCode());
ps.setString(12, profile.getPhoneNumber());
ps.setTimestamp(13, new Timestamp(profile.getBirthDate().getTime()));
ps.setString(14, "" + profile.getGender());
ps.setString(15, profile.getSchool());
ps.setString(16, profile.getMajor());
ps.setBoolean(17, profile.isGraduateStudent());
ps.setInt(18, profile.getGraduationYear());
ps.setString(19, profile.getStudentNumber());
ps.setBoolean(20, profile.isConfirmed());
ps.setLong(21, user);
ps.setTimestamp(22, new Timestamp(new Date().getTime()));
ps.setString(23, profile.getNickName());
ps.setLong(24, profile.getId());
ps.executeUpdate();
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to update user.", e);
} finally {
Database.dispose(conn);
}
}
}
/**
* <p>
* Deletes the specified user profile in persistence layer.
* </p>
*
* @param id
* the id of the user profile to delete
* @param user
* the id of the user who made this modification
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public void deleteUserProfile(long id, long user) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.DELETE_USER);
ps.setLong(1, user);
ps.setTimestamp(2, new Timestamp(new Date().getTime()));
ps.setLong(3, id);
if (ps.executeUpdate() == 0) {
throw new PersistenceException("No such user profile.");
}
} finally {
Database.dispose(ps);
}
} catch (PersistenceException e) {
throw e;
} catch (SQLException e) {
throw new PersistenceException("Failed to delete user profile.", e);
} finally {
Database.dispose(conn);
}
}
/**
* <p>
* Gets the user profile with given id in persistence layer.
* </p>
*
* @param id
* the id of the user profile
* @return the user profile with given id in persistence layer
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public UserProfile getUserProfile(long id) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.GET_USER_BY_ID);
ps.setLong(1, id);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
return this.populateUserProfile(rs);
} else {
return null;
}
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to get the user profile with id " + id, e);
} finally {
Database.dispose(conn);
}
}
/**
* <p>
* Gets the user profile with given handle in persistence layer.
* </p>
*
* @param handle
* the handle of the user profile
* @return the user profile with given handle in persistence layer
* @throws NullPointerException
* if argument is null
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public UserProfile getUserProfileByHandle(String handle) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.GET_USER_BY_HANDLE);
ps.setString(1, handle);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
return this.populateUserProfile(rs);
} else {
return null;
}
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to get the user profile with handle " + handle, e);
} finally {
Database.dispose(conn);
}
}
/**
* <p>
* Gets the user profile with given handle in persistence layer.
* </p>
*
* @param handle
* the handle of the user profile
* @return the user profile with given handle in persistence layer
* @throws NullPointerException
* if argument is null
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public UserProfile login(String handle, String password) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.LOGIN);
ps.setString(1, handle);
ps.setString(2, password);
ps.setString(3, password);
ps.setString(4, handle.length() > 1 ? handle.substring(0, 2) : handle + handle);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
return this.populateUserProfile(rs);
} else {
return null;
}
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to login the user" + handle + " " + password, e);
} finally {
Database.dispose(conn);
}
}
/**
* <p>
* Gets the user profile with given email in persistence layer.
* </p>
*
* @param email
* the email of the user profile
* @return the user profile with given email in persistence layer
* @throws NullPointerException
* if argument is null
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public UserProfile getUserProfileByEmail(String email) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.GET_USER_BY_EMAIL);
ps.setString(1, email);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
return this.populateUserProfile(rs);
} else {
return null;
}
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to get the user profile with email " + email, e);
} finally {
Database.dispose(conn);
}
}
/**
* <p>
* Gets the user profile with given code in persistence layer.
* </p>
*
* @param code
* the code of the user profile
* @return the user profile with given code in persistence layer
* @throws NullPointerException
* if argument is null
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public UserProfile getUserProfileByCode(String code) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.GET_USER_BY_CODE);
ps.setString(1, code);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
return this.populateUserProfile(rs);
} else {
return null;
}
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to get the user profile with code " + code, e);
} finally {
Database.dispose(conn);
}
}
/**
* <p>
* Searches all user profiles according with the given criteria in persistence layer.
* </p>
*
* @return a list of user profiles according with the given criteria
* @param criteria
* the user profile search criteria
* @param offset
* the offset of the start position to search
* @param count
* the maximum number of user profiles in returned list
* @throws NullPointerException
* if argument is null
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public List<UserProfile> searchUserProfiles(UserCriteria criteria, int offset, int count) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = this.getUserSearchSql(conn, criteria, false, offset, count);
ResultSet rs = ps.executeQuery();
List<UserProfile> users = new ArrayList<UserProfile>();
while (rs.next()) {
users.add(this.populateUserProfile(rs));
}
return users;
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to search user.", e);
} finally {
Database.dispose(conn);
}
}
public int searchUserProfilesCount(UserCriteria criteria) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = this.getUserSearchSql(conn, criteria, true, 0, 0);
ResultSet rs = ps.executeQuery();
rs.next();
return rs.getInt(1);
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to search user.", e);
} finally {
Database.dispose(conn);
}
}
private void appendParameter(String name, Object value, StringBuilder sb, List<Object> values) {
if (value == null) {
return;
}
if (values.size() == 0) {
sb.append(" WHERE ");
} else {
sb.append(" AND ");
}
if (value instanceof String) {
sb.append(name + " like ?");
values.add("%" + value + "%");
} else {
sb.append(name + "= ?");
values.add(value);
}
}
private PreparedStatement getUserSearchSql(Connection conn, UserCriteria criteria, boolean isCount, int offset,
int count) throws SQLException {
List<Object> values = new ArrayList<Object>();
StringBuilder sb = new StringBuilder();
sb.append(isCount ? "SELECT count(*) FROM user_profile" : UserPersistenceImpl.GET_USER);
this.appendParameter("email_address", criteria.getEmail(), sb, values);
this.appendParameter("country_id", criteria.getCountryId(), sb, values);
this.appendParameter("first_name", criteria.getFirstName(), sb, values);
this.appendParameter("last_name", criteria.getLastName(), sb, values);
this.appendParameter("school", criteria.getSchool(), sb, values);
this.appendParameter("handle", criteria.getHandle(), sb, values);
if (criteria.getRoleId() != null) {
if (values.size() == 0) {
sb.append(" WHERE ");
} else {
sb.append(" AND ");
}
sb.append("user_profile_id IN (SELECT user_profile_id FROM user_role WHERE role_id=?)");
values.add(criteria.getRoleId());
}
if (!isCount) {
sb.append(" ORDER BY handle");
sb.append(" LIMIT " + offset + "," + count);
}
PreparedStatement ps = conn.prepareStatement(sb.toString());
for (int i = 0; i < values.size(); ++i) {
Object value = values.get(i);
if (value instanceof String) {
ps.setString(i + 1, (String) value);
} else {
ps.setLong(i + 1, ((Long) value).longValue());
}
}
return ps;
}
/**
* <p>
* Creates the specified user preference in persistence layer.
* </p>
*
* @param preference
* the UserPreference instance to create
* @param user
* the id of the user who made this modification
* @throws NullPointerException
* if argument is null
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public void createUserPreference(UserPreference preference, long user) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.INSERT_USER_PREFERENCE);
ps.setLong(1, preference.getId());
ps.setString(2, preference.getPlan());
ps.setInt(3, preference.getProblemPaging());
ps.setInt(4, preference.getSubmissionPaging());
ps.setInt(5, preference.getStatusPaging());
ps.setInt(6, preference.getUserPaging());
ps.setInt(7, preference.getThreadPaging());
ps.setInt(8, preference.getPostPaging());
ps.setLong(9, user);
ps.setTimestamp(10, new Timestamp(new Date().getTime()));
ps.setLong(11, user);
ps.setTimestamp(12, new Timestamp(new Date().getTime()));
ps.executeUpdate();
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to create preference.", e);
} finally {
Database.dispose(conn);
}
}
/**
* <p>
* Updates the specified user preference in persistence layer.
* </p>
*
* @param preference
* the UserPreference instance to update
* @param user
* the id of the user who made this modification
* @throws NullPointerException
* if argument is null
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public void updateUserPreference(UserPreference preference, long user) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.UPDATE_USER_PREFERENCE);
ps.setString(1, preference.getPlan());
ps.setInt(2, preference.getProblemPaging());
ps.setInt(3, preference.getSubmissionPaging());
ps.setInt(4, preference.getStatusPaging());
ps.setInt(5, preference.getUserPaging());
ps.setInt(6, preference.getThreadPaging());
ps.setInt(7, preference.getPostPaging());
ps.setLong(8, user);
ps.setTimestamp(9, new Timestamp(new Date().getTime()));
ps.setLong(10, preference.getId());
ps.executeUpdate();
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to create preference.", e);
} finally {
Database.dispose(conn);
}
}
/**
* <p>
* Gets the user preference with given id in persistence layer.
* </p>
*
* @param id
* the id of the user preference
* @return the user preference with given id in persistence layer
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public UserPreference getUserPreference(long id) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.GET_USER_PREFERENCE);
ps.setLong(1, id);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
UserPreference preference = new UserPreference();
preference.setId(rs.getLong(DatabaseConstants.USER_PREFERENCE_USER_PROFILE_ID));
preference.setPlan(rs.getString(DatabaseConstants.USER_PREFERENCE_PLAN));
preference.setPostPaging(rs.getInt(DatabaseConstants.USER_PREFERENCE_POST_PAGING));
preference.setProblemPaging(rs.getInt(DatabaseConstants.USER_PREFERENCE_PROBLEM_PAGING));
preference.setStatusPaging(rs.getInt(DatabaseConstants.USER_PREFERENCE_STATUS_PAGING));
preference.setSubmissionPaging(rs.getInt(DatabaseConstants.USER_PREFERENCE_SUBMISSION_PAGING));
preference.setThreadPaging(rs.getInt(DatabaseConstants.USER_PREFERENCE_THREAD_PAGING));
preference.setUserPaging(rs.getInt(DatabaseConstants.USER_PREFERENCE_USER_PAGING));
return preference;
} else {
return null;
}
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to get the user preference with id " + id, e);
} finally {
Database.dispose(conn);
}
}
/**
* <p>
* Creates a confirm code for given user in persistence layer.
* </p>
*
* @param id
* the id of the user
* @param code
* the confirm code
* @param user
* the id of the user who made this modification
* @throws NullPointerException
* if argument is null
* @throws IllegalArgumentException
* if argument is empty
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public void createConfirmCode(long id, String code, long user) throws PersistenceException {
if (code.trim().length() == 0) {
throw new IllegalArgumentException("code is empty");
}
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.UPDATE_CODE);
ps.setString(1, code);
ps.setLong(2, id);
int row = ps.executeUpdate();
if (row == 0) {
ps = conn.prepareStatement(UserPersistenceImpl.INSERT_CODE);
ps.setLong(1, id);
ps.setString(2, code);
ps.executeUpdate();
}
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to create code.", e);
} finally {
Database.dispose(conn);
}
}
/**
* <p>
* Deletes the confirm code of given user in persistence layer.
* </p>
*
* @param id
* the id of the user
* @param user
* the id of the user who made this modification
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public void deleteConfirmCode(long id, long user) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.DELETE_CODE);
ps.setLong(1, id);
ps.executeUpdate();
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to create code.", e);
} finally {
Database.dispose(conn);
}
}
/**
* <p>
* Gets the confirm code with given id in persistence layer.
* </p>
*
* @param id
* the id of the user
* @return the confirm code of given user
* @throws PersistenceException
* wrapping a persistence implementation specific exception
*/
public String getConfirmCode(long id) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.GET_CODE);
ps.setLong(1, id);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
return rs.getString(DatabaseConstants.CONFIRMATION_CODE);
} else {
return null;
}
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to get the forum with id " + id, e);
} finally {
Database.dispose(conn);
}
}
/**
* <p>
* Gets all countries from the persistence layer.
* </p>
*
* @return a list containing all country names
*/
public List<Country> getAllCountries() throws PersistenceException {
if (this.allCountries == null) {
synchronized (this) {
if (this.allCountries == null) {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.GET_ALL_COUNTRIES);
ResultSet rs = ps.executeQuery();
List<Country> countries = new ArrayList<Country>();
while (rs.next()) {
countries.add(new Country(rs.getLong(DatabaseConstants.COUNTRY_COUNTRY_ID),
rs.getString(DatabaseConstants.COUNTRY_NAME)));
}
this.allCountries = Collections.unmodifiableList(countries);
} finally {
Database.dispose(ps);
}
} catch (Exception e) {
throw new PersistenceCreationException("Failed to get all countries", e);
} finally {
Database.dispose(conn);
}
}
}
}
return this.allCountries;
}
/**
* Populates a UserProfile instance with the given ResultSet.
*
* @param rs
* the ResultSet
* @return the UserProfile instance
* @throws SQLException
* if any error occurs
*/
private UserProfile populateUserProfile(ResultSet rs) throws SQLException {
UserProfile profile = new UserProfile();
profile.setId(rs.getLong(DatabaseConstants.USER_PROFILE_USER_PROFILE_ID));
profile.setHandle(rs.getString(DatabaseConstants.USER_PROFILE_HANDLE));
profile.setPassword(rs.getString(DatabaseConstants.USER_PROFILE_PASSWORD));
profile.setEmail(rs.getString(DatabaseConstants.USER_PROFILE_EMAIL_ADDRESS));
profile.setRegDate(rs.getTimestamp(DatabaseConstants.USER_PROFILE_REG_DATE));
profile.setFirstName(rs.getString(DatabaseConstants.USER_PROFILE_FIRST_NAME));
profile.setLastName(rs.getString(DatabaseConstants.USER_PROFILE_LAST_NAME));
profile.setAddressLine1(rs.getString(DatabaseConstants.USER_PROFILE_ADDRESS_LINE1));
profile.setAddressLine2(rs.getString(DatabaseConstants.USER_PROFILE_ADDRESS_LINE2));
profile.setCity(rs.getString(DatabaseConstants.USER_PROFILE_CITY));
profile.setState(rs.getString(DatabaseConstants.USER_PROFILE_STATE));
profile.setCountry(new Country(rs.getLong(DatabaseConstants.USER_PROFILE_COUNTRY_ID), "foo"));
profile.setZipCode(rs.getString(DatabaseConstants.USER_PROFILE_ZIP_CODE));
profile.setPhoneNumber(rs.getString(DatabaseConstants.USER_PROFILE_PHONE_NUMBER));
profile.setBirthDate(rs.getDate(DatabaseConstants.USER_PROFILE_BIRTH_DATE));
String gender = rs.getString(DatabaseConstants.USER_PROFILE_GENDER);
profile.setGender(gender == null || gender.length() == 0 ? ' ' : gender.charAt(0));
profile.setSchool(rs.getString(DatabaseConstants.USER_PROFILE_SCHOOL));
profile.setMajor(rs.getString(DatabaseConstants.USER_PROFILE_MAJOR));
profile.setGraduateStudent(rs.getBoolean(DatabaseConstants.USER_PROFILE_GRADUATE_STUDENT));
profile.setGraduationYear(rs.getInt(DatabaseConstants.USER_PROFILE_GRADUATION_YEAR));
profile.setStudentNumber(rs.getString(DatabaseConstants.USER_PROFILE_STUDENT_NUMBER));
profile.setConfirmed(rs.getBoolean(DatabaseConstants.USER_PROFILE_CONFIRMED));
profile.setActive(rs.getBoolean(DatabaseConstants.USER_PROFILE_ACTIVE));
profile.setNickName(rs.getString("nickname"));
profile.setOldEmail(rs.getString("old_email"));
return profile;
}
public long getCreateUser(long userId) throws PersistenceException {
Connection conn = Database.createConnection();
PreparedStatement ps;
try {
ps = conn.prepareStatement("select create_user from user_profile where user_profile_id=?");
ps.setLong(1, userId);
ResultSet row = ps.executeQuery();
if(!row.next())
{
return 0;
}
return row.getLong("create_user");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
public List getStudents(long userId) throws PersistenceException {
Connection conn = null;
try {
conn = Database.createConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(UserPersistenceImpl.GET_USER_BY_CREATE_USER);
ps.setLong(1, userId);
ResultSet rs = ps.executeQuery();
List<UserProfile> users = new ArrayList<UserProfile>();
while (rs.next()) {
users.add(this.populateUserProfile(rs));
}
return users;
} finally {
Database.dispose(ps);
}
} catch (SQLException e) {
throw new PersistenceException("Failed to get the user profile with create user " + userId, e);
} finally {
Database.dispose(conn);
}
}
}