Index: lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dao/hibernate/AssessmentQuestionResultDAOHibernate.java =================================================================== RCS file: /usr/local/cvsroot/lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dao/hibernate/AssessmentQuestionResultDAOHibernate.java,v diff -u -r1.7.2.2 -r1.7.2.3 --- lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dao/hibernate/AssessmentQuestionResultDAOHibernate.java 12 Sep 2014 21:44:07 -0000 1.7.2.2 +++ lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dao/hibernate/AssessmentQuestionResultDAOHibernate.java 11 Nov 2014 22:48:35 -0000 1.7.2.3 @@ -25,67 +25,81 @@ import java.util.List; +import org.hibernate.HibernateException; +import org.hibernate.Query; +import org.hibernate.Session; import org.lamsfoundation.lams.tool.assessment.dao.AssessmentQuestionResultDAO; import org.lamsfoundation.lams.tool.assessment.model.AssessmentQuestionResult; import org.lamsfoundation.lams.tool.assessment.model.AssessmentResult; +import org.springframework.orm.hibernate4.HibernateCallback; public class AssessmentQuestionResultDAOHibernate extends BaseDAOHibernate implements AssessmentQuestionResultDAO { - private static final String FIND_BY_UID = "FROM " + AssessmentQuestionResult.class.getName() - + " AS r WHERE r.uid = ?"; + private static final String FIND_BY_UID = "FROM " + AssessmentQuestionResult.class.getName() + + " AS r WHERE r.uid = ?"; - private static final String FIND_BY_ASSESSMENT_QUESTION_AND_USER = "FROM " - + AssessmentQuestionResult.class.getName() - + " AS q, " - + AssessmentResult.class.getName() - + " AS r " - + " WHERE q.assessmentResult.uid = r.uid and r.assessment.uid = ? AND r.user.userId =? AND q.assessmentQuestion.uid =? ORDER BY r.startDate ASC"; + private static final String FIND_BY_ASSESSMENT_QUESTION_AND_USER = "FROM " + + AssessmentQuestionResult.class.getName() + + " AS q, " + + AssessmentResult.class.getName() + + " AS r " + + " WHERE q.assessmentResult.uid = r.uid and r.assessment.uid = ? AND r.user.userId =? AND q.assessmentQuestion.uid =? ORDER BY r.startDate ASC"; - private static final String FIND_WRONG_ANSWERS_NUMBER = "SELECT COUNT(q) FROM " - + AssessmentQuestionResult.class.getName() - + " AS q, " - + AssessmentResult.class.getName() - + " AS r " - + " WHERE q.assessmentResult.uid = r.uid AND r.assessment.uid = ? AND r.user.userId =? AND q.assessmentQuestion.uid =? AND q.mark < q.assessmentQuestion.defaultGrade"; - - private static final String GET_ANSWER_MARK = "SELECT q.mark FROM " - + AssessmentQuestionResult.class.getName() - + " AS q, " - + AssessmentResult.class.getName() - + " AS r " - + " WHERE q.assessmentResult.uid = r.uid AND r.assessment.uid = ? AND (r.finishDate != null) AND r.user.userId =? AND q.assessmentQuestion.sequenceId =? ORDER BY r.startDate DESC LIMIT 1"; + private static final String FIND_WRONG_ANSWERS_NUMBER = "SELECT COUNT(q) FROM " + + AssessmentQuestionResult.class.getName() + + " AS q, " + + AssessmentResult.class.getName() + + " AS r " + + " WHERE q.assessmentResult.uid = r.uid AND r.assessment.uid = ? AND r.user.userId =? AND q.assessmentQuestion.uid =? AND q.mark < q.assessmentQuestion.defaultGrade"; - @Override - public int getNumberWrongAnswersDoneBefore(Long assessmentUid, Long userId, Long questionUid) { - List list = getHibernateTemplate().find(FIND_WRONG_ANSWERS_NUMBER, new Object[] { assessmentUid, userId, questionUid }); - if (list == null || list.size() == 0) { - return 0; - } else { - return ((Number) list.get(0)).intValue(); + private static final String GET_ANSWER_MARK = "SELECT q.mark FROM " + + AssessmentQuestionResult.class.getName() + + " AS q, " + + AssessmentResult.class.getName() + + " AS r " + + " WHERE q.assessmentResult.uid = r.uid AND r.assessment.uid = ? AND (r.finishDate != null) AND r.user.userId =? AND q.assessmentQuestion.sequenceId =? ORDER BY r.startDate DESC"; + + @Override + public int getNumberWrongAnswersDoneBefore(Long assessmentUid, Long userId, Long questionUid) { + List list = getHibernateTemplate().find(FIND_WRONG_ANSWERS_NUMBER, + new Object[] { assessmentUid, userId, questionUid }); + if (list == null || list.size() == 0) { + return 0; + } else { + return ((Number) list.get(0)).intValue(); + } } - } - - @Override - @SuppressWarnings("unchecked") - public List getAssessmentQuestionResultList(Long assessmentUid, Long userId, Long questionUid) { - return (List) getHibernateTemplate().find(FIND_BY_ASSESSMENT_QUESTION_AND_USER, new Object[] { assessmentUid, userId, questionUid }); - } - - @Override - public AssessmentQuestionResult getAssessmentQuestionResultByUid(Long questionResultUid) { - List list = getHibernateTemplate().find(FIND_BY_UID, new Object[] { questionResultUid }); - if (list == null || list.size() == 0) - return null; - return (AssessmentQuestionResult) list.get(0); - } - @Override - public Float getQuestionResultMark(Long assessmentUid, Long userId, int questionSequenceId) { - List list = getHibernateTemplate().find(GET_ANSWER_MARK, new Object[] { assessmentUid, userId, questionSequenceId }); - if (list == null || list.size() == 0) { - return null; - } else { - return ((Number) list.get(0)).floatValue(); + @Override + @SuppressWarnings("unchecked") + public List getAssessmentQuestionResultList(Long assessmentUid, Long userId, Long questionUid) { + return (List) getHibernateTemplate().find(FIND_BY_ASSESSMENT_QUESTION_AND_USER, + new Object[] { assessmentUid, userId, questionUid }); } - } + + @Override + public AssessmentQuestionResult getAssessmentQuestionResultByUid(Long questionResultUid) { + List list = getHibernateTemplate().find(FIND_BY_UID, new Object[] { questionResultUid }); + if (list == null || list.size() == 0) + return null; + return (AssessmentQuestionResult) list.get(0); + } + + @Override + public Float getQuestionResultMark(Long assessmentUid, Long userId, int questionSequenceId) { + + return getHibernateTemplate().execute(new HibernateCallback() { + @Override + public Float doInHibernate(Session session) throws HibernateException { + Query q = session.createQuery(GET_ANSWER_MARK); + q.setParameter(0, assessmentUid); + q.setParameter(1, userId); + q.setParameter(2, questionSequenceId); + q.setMaxResults(1); + Object result = q.uniqueResult(); + return result != null ? ((Number) result).floatValue() : null; + } + }); + + } } Index: lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dao/hibernate/AssessmentResultDAOHibernate.java =================================================================== RCS file: /usr/local/cvsroot/lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dao/hibernate/AssessmentResultDAOHibernate.java,v diff -u -r1.9.2.2 -r1.9.2.3 --- lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dao/hibernate/AssessmentResultDAOHibernate.java 12 Sep 2014 21:44:07 -0000 1.9.2.2 +++ lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dao/hibernate/AssessmentResultDAOHibernate.java 11 Nov 2014 22:48:35 -0000 1.9.2.3 @@ -18,138 +18,158 @@ * * http://www.gnu.org/licenses/gpl.txt * **************************************************************** - */ - -/* $Id$ */ -package org.lamsfoundation.lams.tool.assessment.dao.hibernate; + */ +/* $Id$ */ +package org.lamsfoundation.lams.tool.assessment.dao.hibernate; + import java.util.List; +import org.hibernate.HibernateException; +import org.hibernate.Query; +import org.hibernate.Session; import org.lamsfoundation.lams.tool.assessment.dao.AssessmentResultDAO; import org.lamsfoundation.lams.tool.assessment.model.AssessmentResult; - +import org.springframework.orm.hibernate4.HibernateCallback; + public class AssessmentResultDAOHibernate extends BaseDAOHibernate implements AssessmentResultDAO { - - private static final String FIND_BY_ASSESSMENT_AND_USER = "FROM " - + AssessmentResult.class.getName() - + " AS r WHERE r.user.userId = ? AND r.assessment.uid=? ORDER BY r.startDate DESC LIMIT 1"; - - private static final String FIND_BY_ASSESSMENT_AND_USER_AND_FINISHED = "FROM " - + AssessmentResult.class.getName() - + " AS r WHERE r.user.userId = ? AND r.assessment.uid=? AND (r.finishDate != null) ORDER BY r.startDate ASC"; - - private static final String FIND_BY_ASSESSMENT_AND_USER_AND_FINISHED_LIMIT1 = "FROM " - + AssessmentResult.class.getName() - + " AS r WHERE r.user.userId = ? AND r.assessment.uid=? AND (r.finishDate != null) ORDER BY r.startDate DESC LIMIT 1"; - private static final String FIND_BY_SESSION_AND_USER_AND_FINISHED = "FROM " - + AssessmentResult.class.getName() - + " AS r WHERE r.user.userId = ? AND r.sessionId=? AND (r.finishDate != null) ORDER BY r.startDate ASC"; - - private static final String FIND_BY_SESSION_AND_USER_AND_FINISHED_LIMIT1 = "FROM " - + AssessmentResult.class.getName() - + " AS r WHERE r.user.userId = ? AND r.sessionId=? AND (r.finishDate != null) ORDER BY r.startDate DESC LIMIT 1"; + private static final String FIND_BY_ASSESSMENT_AND_USER = "FROM " + AssessmentResult.class.getName() + + " AS r WHERE r.user.userId = ? AND r.assessment.uid=? ORDER BY r.startDate DESC"; - private static final String FIND_ASSESSMENT_RESULT_COUNT_BY_ASSESSMENT_AND_USER = "select COUNT(*) FROM " - + AssessmentResult.class.getName() - + " AS r WHERE r.user.userId=? AND r.assessment.uid=? AND (r.finishDate != null)"; - - private static final String FIND_ASSESSMENT_RESULT_GRADE = "select r.grade FROM " - + AssessmentResult.class.getName() - + " AS r WHERE r.user.userId=? AND r.assessment.uid=? AND (r.finishDate != null)"; - - private static final String FIND_ASSESSMENT_RESULT_TIME_TAKEN = "select r.finishDate - r.startDate FROM " - + AssessmentResult.class.getName() - + " AS r WHERE r.user.userId=? AND r.assessment.uid=? AND (r.finishDate != null)"; + private static final String FIND_BY_ASSESSMENT_AND_USER_AND_FINISHED = "FROM " + + AssessmentResult.class.getName() + + " AS r WHERE r.user.userId = ? AND r.assessment.uid=? AND (r.finishDate != null) ORDER BY r.startDate ASC"; - private static final String FIND_BY_UID = "FROM " + AssessmentResult.class.getName() + " AS r WHERE r.uid = ?"; + private static final String FIND_BY_ASSESSMENT_AND_USER_AND_FINISHED_LIMIT1 = "FROM " + + AssessmentResult.class.getName() + + " AS r WHERE r.user.userId = ? AND r.assessment.uid=? AND (r.finishDate != null) ORDER BY r.startDate DESC"; - @Override - @SuppressWarnings("unchecked") - public List getAssessmentResults(Long assessmentUid, Long userId) { - return (List) getHibernateTemplate().find(FIND_BY_ASSESSMENT_AND_USER_AND_FINISHED, new Object[] { userId, assessmentUid }); - } + private static final String FIND_BY_SESSION_AND_USER_AND_FINISHED = "FROM " + AssessmentResult.class.getName() + + " AS r WHERE r.user.userId = ? AND r.sessionId=? AND (r.finishDate != null) ORDER BY r.startDate ASC"; - @Override - @SuppressWarnings("unchecked") - public List getAssessmentResultsBySession(Long sessionId, Long userId) { - return (List) getHibernateTemplate().find(FIND_BY_SESSION_AND_USER_AND_FINISHED, new Object[] { userId, sessionId }); - } - - @Override - public AssessmentResult getLastAssessmentResult(Long assessmentUid, Long userId) { - List list = getHibernateTemplate().find(FIND_BY_ASSESSMENT_AND_USER, new Object[] { userId, assessmentUid }); - if (list == null || list.size() == 0) { - return null; - } else { - return (AssessmentResult) list.get(0); + private static final String FIND_BY_SESSION_AND_USER_AND_FINISHED_LIMIT1 = "FROM " + + AssessmentResult.class.getName() + + " AS r WHERE r.user.userId = ? AND r.sessionId=? AND (r.finishDate != null) ORDER BY r.startDate DESC"; + + private static final String FIND_ASSESSMENT_RESULT_COUNT_BY_ASSESSMENT_AND_USER = "select COUNT(*) FROM " + + AssessmentResult.class.getName() + + " AS r WHERE r.user.userId=? AND r.assessment.uid=? AND (r.finishDate != null)"; + + private static final String FIND_ASSESSMENT_RESULT_GRADE = "select r.grade FROM " + + AssessmentResult.class.getName() + + " AS r WHERE r.user.userId=? AND r.assessment.uid=? AND (r.finishDate != null)"; + + private static final String FIND_ASSESSMENT_RESULT_TIME_TAKEN = "select r.finishDate - r.startDate FROM " + + AssessmentResult.class.getName() + + " AS r WHERE r.user.userId=? AND r.assessment.uid=? AND (r.finishDate != null)"; + + private static final String FIND_BY_UID = "FROM " + AssessmentResult.class.getName() + " AS r WHERE r.uid = ?"; + + @Override + @SuppressWarnings("unchecked") + public List getAssessmentResults(Long assessmentUid, Long userId) { + return (List) getHibernateTemplate().find(FIND_BY_ASSESSMENT_AND_USER_AND_FINISHED, + new Object[] { userId, assessmentUid }); } - } - - @Override - public AssessmentResult getLastFinishedAssessmentResult(Long assessmentUid, Long userId) { - List list = getHibernateTemplate().find(FIND_BY_ASSESSMENT_AND_USER_AND_FINISHED_LIMIT1, new Object[] { userId, assessmentUid }); - if (list == null || list.size() == 0) { - return null; - } else { - return (AssessmentResult) list.get(0); + + @Override + @SuppressWarnings("unchecked") + public List getAssessmentResultsBySession(Long sessionId, Long userId) { + return (List) getHibernateTemplate().find(FIND_BY_SESSION_AND_USER_AND_FINISHED, + new Object[] { userId, sessionId }); } - } - - @Override - public Float getLastFinishedAssessmentResultGrade(Long assessmentUid, Long userId) { - List list = getHibernateTemplate().find(FIND_ASSESSMENT_RESULT_GRADE, new Object[] { userId, assessmentUid }); - if (list == null || list.size() == 0) { - return null; - } else { - return ((Number) list.get(0)).floatValue(); + + @Override + public AssessmentResult getLastAssessmentResult(Long assessmentUid, Long userId) { + + return getHibernateTemplate().execute(new HibernateCallback() { + @Override + public AssessmentResult doInHibernate(Session session) throws HibernateException { + Query q = session.createQuery(FIND_BY_ASSESSMENT_AND_USER); + q.setParameter(0, userId); + q.setParameter(1, assessmentUid); + q.setMaxResults(1); + return (AssessmentResult) q.uniqueResult(); + } + }); } - } - - @Override - public Integer getLastFinishedAssessmentResultTimeTaken(Long assessmentUid, Long userId) { - - String FIND_ASSESSMENT_RESULT_TIME_TAKEN = "select UNIX_TIMESTAMP(r.finishDate) - UNIX_TIMESTAMP(r.startDate) FROM " - + AssessmentResult.class.getName() - + " AS r WHERE r.user.userId=? AND r.assessment.uid=? AND (r.finishDate != null)"; - - - List list = getHibernateTemplate().find(FIND_ASSESSMENT_RESULT_TIME_TAKEN, new Object[] { userId, assessmentUid }); - if (list == null || list.size() == 0) { - return null; - } else { - return ((Number) list.get(0)).intValue(); + + @Override + public AssessmentResult getLastFinishedAssessmentResult(Long assessmentUid, Long userId) { + + return getHibernateTemplate().execute(new HibernateCallback() { + @Override + public AssessmentResult doInHibernate(Session session) throws HibernateException { + Query q = session.createQuery(FIND_BY_ASSESSMENT_AND_USER_AND_FINISHED_LIMIT1); + q.setParameter(0, userId); + q.setParameter(1, assessmentUid); + q.setMaxResults(1); + return (AssessmentResult) q.uniqueResult(); + } + }); + } - } - - @Override - public AssessmentResult getLastFinishedAssessmentResultBySessionId(Long sessionId, Long userId) { - List list = getHibernateTemplate().find(FIND_BY_SESSION_AND_USER_AND_FINISHED_LIMIT1, new Object[] { userId, sessionId }); - if (list == null || list.size() == 0) { - return null; - } else { - return (AssessmentResult) list.get(0); + + @Override + public Float getLastFinishedAssessmentResultGrade(Long assessmentUid, Long userId) { + List list = getHibernateTemplate().find(FIND_ASSESSMENT_RESULT_GRADE, new Object[] { userId, assessmentUid }); + if (list == null || list.size() == 0) { + return null; + } else { + return ((Number) list.get(0)).floatValue(); + } } - } - @Override - public int getAssessmentResultCount(Long assessmentUid, Long userId) { - List list = getHibernateTemplate().find(FIND_ASSESSMENT_RESULT_COUNT_BY_ASSESSMENT_AND_USER, new Object[] { userId, assessmentUid }); - if (list == null || list.size() == 0) { - return 0; - } else { - return ((Number) list.get(0)).intValue(); + @Override + public Integer getLastFinishedAssessmentResultTimeTaken(Long assessmentUid, Long userId) { + + String FIND_ASSESSMENT_RESULT_TIME_TAKEN = "select UNIX_TIMESTAMP(r.finishDate) - UNIX_TIMESTAMP(r.startDate) FROM " + + AssessmentResult.class.getName() + + " AS r WHERE r.user.userId=? AND r.assessment.uid=? AND (r.finishDate != null)"; + + List list = getHibernateTemplate().find(FIND_ASSESSMENT_RESULT_TIME_TAKEN, + new Object[] { userId, assessmentUid }); + if (list == null || list.size() == 0) { + return null; + } else { + return ((Number) list.get(0)).intValue(); + } } - } - - @Override - public AssessmentResult getAssessmentResultByUid(Long assessmentResultUid) { - List list = getHibernateTemplate().find(FIND_BY_UID, new Object[] { assessmentResultUid }); - if (list == null || list.size() == 0) - return null; - return (AssessmentResult) list.get(0); - } -} + @Override + public AssessmentResult getLastFinishedAssessmentResultBySessionId(Long sessionId, Long userId) { + + return getHibernateTemplate().execute(new HibernateCallback() { + @Override + public AssessmentResult doInHibernate(Session session) throws HibernateException { + Query q = session.createQuery(FIND_BY_SESSION_AND_USER_AND_FINISHED_LIMIT1); + q.setParameter(0, userId); + q.setParameter(1, sessionId); + q.setMaxResults(1); + return (AssessmentResult) q.uniqueResult(); + } + }); + + } - \ No newline at end of file + @Override + public int getAssessmentResultCount(Long assessmentUid, Long userId) { + List list = getHibernateTemplate().find(FIND_ASSESSMENT_RESULT_COUNT_BY_ASSESSMENT_AND_USER, + new Object[] { userId, assessmentUid }); + if (list == null || list.size() == 0) { + return 0; + } else { + return ((Number) list.get(0)).intValue(); + } + } + + @Override + public AssessmentResult getAssessmentResultByUid(Long assessmentResultUid) { + List list = getHibernateTemplate().find(FIND_BY_UID, new Object[] { assessmentResultUid }); + if (list == null || list.size() == 0) + return null; + return (AssessmentResult) list.get(0); + } + +} Index: lams_tool_mindmap/src/java/org/lamsfoundation/lams/tool/mindmap/dao/hibernate/MindmapRequestDAO.java =================================================================== RCS file: /usr/local/cvsroot/lams_tool_mindmap/src/java/org/lamsfoundation/lams/tool/mindmap/dao/hibernate/MindmapRequestDAO.java,v diff -u -r1.5.2.1 -r1.5.2.2 --- lams_tool_mindmap/src/java/org/lamsfoundation/lams/tool/mindmap/dao/hibernate/MindmapRequestDAO.java 15 Aug 2014 09:30:46 -0000 1.5.2.1 +++ lams_tool_mindmap/src/java/org/lamsfoundation/lams/tool/mindmap/dao/hibernate/MindmapRequestDAO.java 11 Nov 2014 22:48:36 -0000 1.5.2.2 @@ -26,59 +26,69 @@ import java.util.List; +import org.hibernate.HibernateException; +import org.hibernate.Query; +import org.hibernate.Session; import org.lamsfoundation.lams.dao.hibernate.BaseDAO; import org.lamsfoundation.lams.tool.mindmap.dao.IMindmapRequestDAO; import org.lamsfoundation.lams.tool.mindmap.model.MindmapRequest; +import org.springframework.orm.hibernate4.HibernateCallback; /** * MindmapRequestDAO + * * @author Ruslan Kazakov */ public class MindmapRequestDAO extends BaseDAO implements IMindmapRequestDAO { - private static final String SQL_QUERY_FIND_REQUESTS_AFTER_GLOBAL_ID = - " from " + MindmapRequest.class.getName() + " mr where mr.globalId > ? and " + - " mr.mindmap.uid = ? and mr.user.uid <> ? and mr.user.mindmapSession.sessionId = ? order by mr.globalId "; - - private static final String SQL_QUERY_FIND_REQUEST_BY_UNIQUE_ID = - " from " + MindmapRequest.class.getName() + " mr where mr.uniqueId = ? and mr.user.uid = ? and " + - " mr.mindmap.uid = ? and mr.globalId > ? "; - - private static final String SQL_QUERY_FIND_LAST_GLOBAL_ID_BY_MINDMAP = - " select mr.globalId from " + MindmapRequest.class.getName() + " mr where mr.mindmap.uid = ? and " + - " mr.user.mindmapSession.sessionId = ? order by mr.globalId desc limit 1 "; - - private static final String SQL_QUERY_FIND_REQUESTS_BY_USER_ID = - " from " + MindmapRequest.class.getName() + " mr where mr.user.userId = ? "; - - public void saveOrUpdate(MindmapRequest mindmapRequest) { - this.getHibernateTemplate().saveOrUpdate(mindmapRequest); - } - - public List getLastRequestsAfterGlobalId(Long globalId, Long mindmapId, Long userId, Long sessionId) { - return this.getHibernateTemplate().find(SQL_QUERY_FIND_REQUESTS_AFTER_GLOBAL_ID, - new Object[]{globalId, mindmapId, userId, sessionId}); - } - - public MindmapRequest getRequestByUniqueId(Long uniqueId, Long userId, Long mindmapId, Long globalId) { - List list = this.getHibernateTemplate().find(SQL_QUERY_FIND_REQUEST_BY_UNIQUE_ID, - new Object[]{uniqueId, userId, mindmapId, globalId}); - if (list != null && list.size() > 0) - return (MindmapRequest) list.get(list.size()-1); - else - return null; - } - - public Long getLastGlobalIdByMindmapId(Long mindmapId, Long sessionId) { - List list = this.getHibernateTemplate().find(SQL_QUERY_FIND_LAST_GLOBAL_ID_BY_MINDMAP, - new Object[]{mindmapId, sessionId}); - if (list != null && list.size() > 0) - return ((Number) list.get(0)).longValue(); - else - return 0l; - } - - @SuppressWarnings("unchecked") - public List getRequestsByUserId(Long userId) { - return (List) this.getHibernateTemplate().find(SQL_QUERY_FIND_REQUESTS_BY_USER_ID, userId); - } + private static final String SQL_QUERY_FIND_REQUESTS_AFTER_GLOBAL_ID = " from " + MindmapRequest.class.getName() + + " mr where mr.globalId > ? and " + + " mr.mindmap.uid = ? and mr.user.uid <> ? and mr.user.mindmapSession.sessionId = ? order by mr.globalId "; + + private static final String SQL_QUERY_FIND_REQUEST_BY_UNIQUE_ID = " from " + MindmapRequest.class.getName() + + " mr where mr.uniqueId = ? and mr.user.uid = ? and " + " mr.mindmap.uid = ? and mr.globalId > ? "; + + private static final String SQL_QUERY_FIND_LAST_GLOBAL_ID_BY_MINDMAP = " select mr.globalId from " + + MindmapRequest.class.getName() + " mr where mr.mindmap.uid = ? and " + + " mr.user.mindmapSession.sessionId = ? order by mr.globalId desc"; + + private static final String SQL_QUERY_FIND_REQUESTS_BY_USER_ID = " from " + MindmapRequest.class.getName() + + " mr where mr.user.userId = ? "; + + public void saveOrUpdate(MindmapRequest mindmapRequest) { + this.getHibernateTemplate().saveOrUpdate(mindmapRequest); + } + + public List getLastRequestsAfterGlobalId(Long globalId, Long mindmapId, Long userId, Long sessionId) { + return this.getHibernateTemplate().find(SQL_QUERY_FIND_REQUESTS_AFTER_GLOBAL_ID, + new Object[] { globalId, mindmapId, userId, sessionId }); + } + + public MindmapRequest getRequestByUniqueId(Long uniqueId, Long userId, Long mindmapId, Long globalId) { + List list = this.getHibernateTemplate().find(SQL_QUERY_FIND_REQUEST_BY_UNIQUE_ID, + new Object[] { uniqueId, userId, mindmapId, globalId }); + if (list != null && list.size() > 0) + return (MindmapRequest) list.get(list.size() - 1); + else + return null; + } + + public Long getLastGlobalIdByMindmapId(Long mindmapId, Long sessionId) { + return getHibernateTemplate().execute(new HibernateCallback() { + @Override + public Long doInHibernate(Session session) throws HibernateException { + Query q = session.createQuery(SQL_QUERY_FIND_LAST_GLOBAL_ID_BY_MINDMAP); + q.setParameter(0, mindmapId); + q.setParameter(1, sessionId); + q.setMaxResults(1); + Object result = q.uniqueResult(); + return result != null ? ((Number) result).longValue() : null; + } + }); + + } + + @SuppressWarnings("unchecked") + public List getRequestsByUserId(Long userId) { + return (List) this.getHibernateTemplate().find(SQL_QUERY_FIND_REQUESTS_BY_USER_ID, userId); + } } Index: lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dao/hibernate/ScratchieAnswerVisitDAOHibernate.java =================================================================== RCS file: /usr/local/cvsroot/lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dao/hibernate/ScratchieAnswerVisitDAOHibernate.java,v diff -u -r1.11.2.1 -r1.11.2.2 --- lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dao/hibernate/ScratchieAnswerVisitDAOHibernate.java 15 Aug 2014 09:30:59 -0000 1.11.2.1 +++ lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dao/hibernate/ScratchieAnswerVisitDAOHibernate.java 11 Nov 2014 22:48:37 -0000 1.11.2.2 @@ -25,61 +25,73 @@ import java.util.List; +import org.hibernate.HibernateException; +import org.hibernate.Query; +import org.hibernate.Session; import org.lamsfoundation.lams.tool.scratchie.dao.ScratchieAnswerVisitDAO; import org.lamsfoundation.lams.tool.scratchie.model.ScratchieAnswer; import org.lamsfoundation.lams.tool.scratchie.model.ScratchieAnswerVisitLog; +import org.springframework.orm.hibernate4.HibernateCallback; public class ScratchieAnswerVisitDAOHibernate extends BaseDAOHibernate implements ScratchieAnswerVisitDAO { - private static final String FIND_BY_SESSION_AND_ANSWER = "from " + ScratchieAnswerVisitLog.class.getName() - + " as r where r.sessionId = ? and r.scratchieAnswer.uid=?"; - - private static final String FIND_BY_SESSION_AND_ITEM = "from " + ScratchieAnswerVisitLog.class.getName() - + " as r where r.sessionId=? and r.scratchieAnswer.scratchieItem.uid = ? order by r.accessDate asc"; - - private static final String FIND_FIRST_SCRATCHED_ANSWER_BY_SESSION_AND_ITEM = "SELECT r.scratchieAnswer from " + ScratchieAnswerVisitLog.class.getName() - + " as r where r.sessionId=? and r.scratchieAnswer.scratchieItem.uid = ? order by r.accessDate asc LIMIT 1;"; - - private static final String FIND_BY_SESSION = "from " + ScratchieAnswerVisitLog.class.getName() - + " as r where r.sessionId=? order by r.accessDate asc"; + private static final String FIND_BY_SESSION_AND_ANSWER = "from " + ScratchieAnswerVisitLog.class.getName() + + " as r where r.sessionId = ? and r.scratchieAnswer.uid=?"; - private static final String FIND_VIEW_COUNT_BY_SESSION = "select count(*) from " - + ScratchieAnswerVisitLog.class.getName() + " as r where r.sessionId=?"; - - @Override - public ScratchieAnswerVisitLog getLog(Long answerUid, Long sessionId) { - List list = getHibernateTemplate().find(FIND_BY_SESSION_AND_ANSWER, new Object[] { sessionId, answerUid }); - if (list == null || list.size() == 0) - return null; - return (ScratchieAnswerVisitLog) list.get(0); - } + private static final String FIND_BY_SESSION_AND_ITEM = "from " + ScratchieAnswerVisitLog.class.getName() + + " as r where r.sessionId=? and r.scratchieAnswer.scratchieItem.uid = ? order by r.accessDate asc"; - @Override - public int getLogCountTotal(Long sessionId) { - List list = getHibernateTemplate().find(FIND_VIEW_COUNT_BY_SESSION, new Object[] { sessionId}); - if (list == null || list.size() == 0) - return 0; - return ((Number) list.get(0)).intValue(); - } - - @SuppressWarnings("unchecked") - @Override - public List getLogsBySessionAndItem(Long sessionId, Long itemUid) { - return (List) getHibernateTemplate().find(FIND_BY_SESSION_AND_ITEM, new Object[] { sessionId, itemUid }); - } - - @SuppressWarnings("unchecked") - @Override - public List getLogsBySession(Long sessionId) { - return (List) getHibernateTemplate().find(FIND_BY_SESSION, new Object[] { sessionId }); - } - - @Override - public ScratchieAnswer getFirstScratchedAnswerBySessionAndItem(Long sessionId, Long itemUid) { - List list = getHibernateTemplate().find(FIND_FIRST_SCRATCHED_ANSWER_BY_SESSION_AND_ITEM, new Object[] { sessionId, itemUid }); - if (list == null || list.size() == 0) - return null; - return (ScratchieAnswer) list.get(0); - } + private static final String FIND_FIRST_SCRATCHED_ANSWER_BY_SESSION_AND_ITEM = "SELECT r.scratchieAnswer from " + + ScratchieAnswerVisitLog.class.getName() + + " as r where r.sessionId=? and r.scratchieAnswer.scratchieItem.uid = ? order by r.accessDate asc;"; + private static final String FIND_BY_SESSION = "from " + ScratchieAnswerVisitLog.class.getName() + + " as r where r.sessionId=? order by r.accessDate asc"; + + private static final String FIND_VIEW_COUNT_BY_SESSION = "select count(*) from " + + ScratchieAnswerVisitLog.class.getName() + " as r where r.sessionId=?"; + + @Override + public ScratchieAnswerVisitLog getLog(Long answerUid, Long sessionId) { + List list = getHibernateTemplate().find(FIND_BY_SESSION_AND_ANSWER, new Object[] { sessionId, answerUid }); + if (list == null || list.size() == 0) + return null; + return (ScratchieAnswerVisitLog) list.get(0); + } + + @Override + public int getLogCountTotal(Long sessionId) { + List list = getHibernateTemplate().find(FIND_VIEW_COUNT_BY_SESSION, new Object[] { sessionId }); + if (list == null || list.size() == 0) + return 0; + return ((Number) list.get(0)).intValue(); + } + + @SuppressWarnings("unchecked") + @Override + public List getLogsBySessionAndItem(Long sessionId, Long itemUid) { + return (List) getHibernateTemplate().find(FIND_BY_SESSION_AND_ITEM, + new Object[] { sessionId, itemUid }); + } + + @SuppressWarnings("unchecked") + @Override + public List getLogsBySession(Long sessionId) { + return (List) getHibernateTemplate().find(FIND_BY_SESSION, new Object[] { sessionId }); + } + + @Override + public ScratchieAnswer getFirstScratchedAnswerBySessionAndItem(Long sessionId, Long itemUid) { + return getHibernateTemplate().execute(new HibernateCallback() { + @Override + public ScratchieAnswer doInHibernate(Session session) throws HibernateException { + Query q = session.createQuery(FIND_FIRST_SCRATCHED_ANSWER_BY_SESSION_AND_ITEM); + q.setParameter(0, sessionId); + q.setParameter(1, itemUid); + q.setMaxResults(1); + return (ScratchieAnswer) q.uniqueResult(); + } + }); + } + }