I have added a method for delete definitively a User and his posts, topics, private Messages(From and To) and other data in the Db, i have tried to use all the methods that are just present in the code for deletion:
in UserAction:
// Delete Phisically
public void deletePhisically() {
String id = String.valueOf(this.request.getParameter("id"));
UserDAO um = DataAccessDriver.getInstance().newUserDAO();
int userId = Integer.parseInt(id);
if (um.isDeleted(userId)) {
um.undelete(userId);
} else {
String sessionId = SessionFacade.isUserInSession(userId);
if (sessionId != null) {
SessionFacade.remove(sessionId);
}
um.deletePhisically(userId);
}
this.list();
}
IN GenericUserDAO:
public void deletePhisically(int userId) {
if (userId != 1 && userId != 2) {
PreparedStatement p = null;
ResultSet rs = null;
try {
/**
* TODO implementare la cancellazione completa utente.
* Le Tabelle da considerare sono:
* posts
* topics
* privmsgs
* votes
* banlist
* userGroups
* attachment
*
*/
// Posts to delete By User
p = JForumExecutionContext.getConnection()
.prepareStatement(SystemGlobals.getSql("UserModel.selectPostsByUserId"));
p.setInt(1, userId);
rs = p.executeQuery();
PostDAO postDAO = DataAccessDriver.getInstance().newPostDAO();
while (rs.next()) {
Post post = postDAO.selectById(rs.getInt("post_id"));
postDAO.delete(post);
}
// Topics to delete By User
p = JForumExecutionContext.getConnection()
.prepareStatement(SystemGlobals.getSql("UserModel.selectTopicsByUserId"));
p.setInt(1, userId);
rs = p.executeQuery();
TopicDAO topicDAO = DataAccessDriver.getInstance().newTopicDAO();
while (rs.next()) {
Topic topic = topicDAO.selectById(rs.getInt("topic_id"));
topicDAO.delete(topic);
}
// Private Messager to delete By User
p = JForumExecutionContext.getConnection()
.prepareStatement(SystemGlobals.getSql("UserModel.selectPrivmsgByUserId"));
p.setInt(1, userId);
rs = p.executeQuery();
PrivateMessageDAO dao = DataAccessDriver.getInstance().newPrivateMessageDAO();
PrivateMessage[] deletePmsList = new PrivateMessage[getRowCount(rs)];
int indexRs = 0;
while (rs.next()) {
if (0 != rs.getInt("privmsgs_id")) {
PrivateMessage pm = dao.selectById(new PrivateMessage(rs.getInt("privmsgs_id")));
deletePmsList[indexRs] = pm;
indexRs++;
}
}
if (deletePmsList.length == 0) {
dao.delete(deletePmsList, userId);
deletePmsList = null;
}
// Private Message(from) to delete By User
p = JForumExecutionContext.getConnection()
.prepareStatement(SystemGlobals.getSql("UserModel.selectPrivmsgToByUserId"));
p.setInt(1, userId);
rs = p.executeQuery();
PrivateMessageDAO daoTo = DataAccessDriver.getInstance().newPrivateMessageDAO();
while (rs.next()) {
if (0 != rs.getInt("privmsgs_id")) {
PrivateMessage pm = dao.selectById(new PrivateMessage(rs.getInt("privmsgs_id")));
deletePmsList[indexRs] = pm;
indexRs++;
}
}
if (deletePmsList.length == 0) {
dao.delete(deletePmsList, userId);
deletePmsList = null;
}
// BanList to delete By User
p = JForumExecutionContext.getConnection()
.prepareStatement(SystemGlobals.getSql("UserModel.selectBanlistByUserId"));
p.setInt(1, userId);
rs = p.executeQuery();
BanlistDAO banlistDAO = DataAccessDriver.getInstance().newBanlistDAO();
while (rs.next()) {
banlistDAO.delete(rs.getInt("banlist_id"));
}
// Attachments to delete By User
p = JForumExecutionContext.getConnection()
.prepareStatement(SystemGlobals.getSql("UserModel.deleteAttachmentByUserId"));
p.setInt(1, userId);
p.executeUpdate();
// User Groups to delete By User
p = JForumExecutionContext.getConnection()
.prepareStatement(SystemGlobals.getSql("UserModel.deleteUserGroupsByUserId"));
p.setInt(1, userId);
p.executeUpdate();
// Users to delete By User
p = JForumExecutionContext.getConnection()
.prepareStatement(SystemGlobals.getSql("UserModel.deleteUserByUserId"));
p.setInt(1, userId);
p.executeUpdate();
}
catch (SQLException e) {
throw new DatabaseException(e);
}
finally {
DbUtils.close(rs, p);
}
}
}
and i have added the queries that i need in the generic_queries.sql:
# ##########
UserModel.selectPostsByUserId = SELECT po.post_id, u.user_id \
FROM jforum_users u \
LEFT JOIN jforum_posts po ON po.user_id = u.user_id \
WHERE u.user_id = ? \
GROUP BY po.post_id,u.user_id;
UserModel.selectTopicsByUserId = SELECT top.topic_id, u.user_id \
FROM jforum_users u \
LEFT JOIN jforum_topics top ON top.user_id = u.user_id \
WHERE u.user_id = ? \
GROUP BY top.topic_id,u.user_id;
UserModel.selectPrivmsgByUserId = SELECT pvm.privmsgs_id, u.user_id \
FROM jforum_users u \
LEFT JOIN jforum_privmsgs pvm ON pvm.privmsgs_from_userid = u.user_id \
WHERE u.user_id = ? \
GROUP BY pvm.privmsgs_id,u.user_id;
UserModel.selectPrivmsgToByUserId = SELECT pvm.privmsgs_id, u.user_id \
FROM jforum_users u \
LEFT JOIN jforum_privmsgs pvm ON pvm.privmsgs_to_userid = u.user_id \
WHERE u.user_id = ? \
GROUP BY pvm.privmsgs_id,u.user_id;
UserModel.selectBanlistByUserId = SELECT ban.banlist_id, ban.user_id from jforum_banlist ban \
WHERE ban.user_id = ? ;
UserModel.deleteAttachmentByUserId = DELETE FROM jforum_attach att WHERE att.user_id = ?;
UserModel.deleteUserGroupsByUserId = DELETE FROM jforum_user_groups ug WHERE ug.user_id = ?;
UserModel.deleteUserByUserId = DELETE FROM jforum_users ug WHERE ug.user_id = ?;
... i hope you find this useful
Federico
[originally posted on jforum.net by fscanu]
