/* ======================================================================= * Copyright 2004-2006 The OpenMentor Team. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. * ======================================================================== */ /* * Created on 13-Jan-2006 * */ package uk.org.openmentor.businesslogic.impl; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; /** * Much manipulated (and I hope not badly screwed up) by Ian Craw. * @author Hassan Sheikh - IET, The Open University */ public class SQLWrapper { /** * The database connection which, if not null, can be closed. */ private Connection theConnection; /** * Our usual logger. */ private static Log log = LogFactory.getLog(SQLWrapper.class); /** * Creates a new <code>SQLWrapper</code> instance, but privately! * So the effect is to stop the class being instantiated without * the full constructor. See Bloch, page12. */ private SQLWrapper() { } /** * Standard accessor. * * @return a <code>Connection</code> value */ final Connection getConnection() { return theConnection; } /** * Our only useful constructor. It does all the database * manipulation, which reflects how it is used in practice. * * @param driverClassName the JDBC class to use. * @param url the URL needed to access the database * @param username the name to use when accessing the database * @param password the password needed by the database. */ public SQLWrapper(final String driverClassName, final String url, final String username, final String password) { if (log.isDebugEnabled()) { log.debug("Setting jdbc connection parameters:" + "driverClassName: " + driverClassName + "; URL: " + url + "; User: " + username + "; Password: " + password); } try { Class.forName(driverClassName); } catch (Exception ex) { log.warn("Failed to find driver" + ex.getMessage()); } try { theConnection = DriverManager. getConnection(url, username, password); // Allow long responses from GROUP_CONCAT String theSQL = "SET SESSION group_concat_max_len = 32000"; PreparedStatement stmt = theConnection.prepareStatement(theSQL); stmt.executeQuery(); } catch (Exception ex) { log.warn("Failed to connect to database " + ex.getMessage()); } } /** * Release the resources and closeg the database connection. */ public final void closeDB() { if (log.isDebugEnabled()) { log.debug("Closing database connection."); } try { theConnection.close(); } catch (Exception ex) { log.warn("Unhandled exception " + ex.getMessage()); } } /** * The <code>sqlTeacherCommentsCount</code> method seems to * prepare SQL to count the number of comments for a given tutor. * * @param courseId the ID of the course * @param teacherId a <code>String</code> value * @return a <code>String</code> value */ final String sqlTeacherCommentsCount(final String courseId, final String teacherId) { StringBuilder b = new StringBuilder(); b.append(" SELECT tbl_submission_tutors.tutor_id AS ObjectID,"); b.append(" LEFT(tbl_comment_classes.category_name, 1) as CCode,"); b.append(" tbl_submission.grade AS Grade,"); b.append(" COUNT(DISTINCT tbl_comment.text) as CommentsCounter,"); b.append(" GROUP_CONCAT(DISTINCT tbl_comment.text"); b.append(" SEPARATOR '__SEP__') AS Comments"); b.append(" FROM tbl_submission_tutors"); b.append(" JOIN tbl_submission"); b.append(" ON tbl_submission_tutors.submission_id = tbl_submission.id"); b.append(" JOIN tbl_assignment ON"); b.append(" tbl_assignment.id = tbl_submission.assignment_id"); b.append(" JOIN tbl_comment"); b.append(" ON tbl_submission.id = tbl_comment.submission_id"); b.append(" JOIN tbl_comment_classes"); b.append(" ON tbl_comment.id = tbl_comment_classes.comment_id"); b.append(" WHERE tbl_assignment.course_id = '"); b.append(courseId); b.append("'"); b.append(" AND (tbl_submission_tutors.tutor_id in ('"); b.append(teacherId); b.append("') )"); b.append(" GROUP BY ObjectID, CCode, Grade"); b.append(" ORDER BY ObjectID, Ccode, Grade"); String theSQL = b.toString(); if (log.isDebugEnabled()) { log.debug("Generating SQL: " + theSQL); } return theSQL; } /** * Describe <code>sqlStudentCommentsCount</code> method here. * * @param courseId the course identifier * @param studentId a <code>String</code> value * @return a <code>String</code> value */ final String sqlStudentCommentsCount(final String courseId, final String studentId) { StringBuilder b = new StringBuilder(); b.append(" SELECT tbl_submission_students.student_id AS ObjectID,"); b.append(" LEFT(tbl_comment_classes.category_name, 1) as CCode,"); b.append(" tbl_submission.grade AS Grade,"); b.append(" COUNT(DISTINCT tbl_comment.text) as CommentsCounter,"); b.append(" GROUP_CONCAT(DISTINCT tbl_comment.text"); b.append(" SEPARATOR '__SEP__') AS Comments"); b.append(" FROM tbl_submission_students"); b.append(" JOIN tbl_submission"); b.append(" ON tbl_submission_students.submission_id"); b.append(" = tbl_submission.id"); b.append(" JOIN tbl_assignment ON"); b.append(" tbl_assignment.id = tbl_submission.assignment_id"); b.append(" JOIN tbl_comment"); b.append(" ON tbl_submission.id = tbl_comment.submission_id"); b.append(" JOIN tbl_comment_classes"); b.append(" ON tbl_comment.id = tbl_comment_classes.comment_id"); b.append(" WHERE tbl_assignment.course_id = '"); b.append(courseId); b.append("'"); b.append(" AND (tbl_submission_students.student_id in ('"); b.append(studentId); b.append("' ) )"); b.append(" GROUP BY ObjectID, CCode, Grade"); b.append(" ORDER BY ObjectID, CCode, Grade"); String theSQL = b.toString(); if (log.isDebugEnabled()) { log.debug("Generating SQL: " + theSQL); } return theSQL; } /** * The <code>sqlCourseCommentsCount</code> method returns the sql * statement which will fetch the total number of comments for * each assignment for a specified course here. * * @param courseId the ID of the course * @return the SQL */ final String sqlCourseCommentsCount(final String courseId) { StringBuilder b = new StringBuilder(); b.append(" SELECT tbl_assignment.course_id AS ObjectID,"); b.append(" LEFT(tbl_comment_classes.category_name, 1) as CCode,"); b.append(" tbl_submission.grade AS Grade,"); b.append(" COUNT(DISTINCT tbl_comment.text) as CommentsCounter,"); b.append(" GROUP_CONCAT(DISTINCT tbl_comment.text"); b.append(" SEPARATOR '__SEP__') AS Comments"); b.append(" FROM tbl_submission JOIN tbl_assignment ON"); b.append(" tbl_assignment.id = tbl_submission.assignment_id"); b.append(" JOIN tbl_comment"); b.append(" ON tbl_submission.id = tbl_comment.submission_id"); b.append(" JOIN tbl_comment_classes"); b.append(" ON tbl_comment.id = tbl_comment_classes.comment_id"); b.append(" WHERE tbl_assignment.course_id = '"); b.append(courseId); b.append("'"); b.append(" GROUP BY ObjectID, CCode, Grade"); b.append(" ORDER BY ObjectID, CCode, Grade"); String theSQL = b.toString(); if (log.isDebugEnabled()) { log.debug("Generating SQL: " + theSQL); } return theSQL; } /** * The <code>sqlAssignmentCommentsCount</code> method returns the * sql statement to fetch number of comments for each assignment * file for a specified assignment. * * @param assignmentID an <code>int</code> value * @return a <code>String</code> value */ final String sqlAssignmentCommentsCount(final int assignmentID) { StringBuilder b = new StringBuilder(); b.append(" SELECT tbl_assignment.id,"); b.append(" Left(tbl_BalesCategory.BalesCategoryCode, 1) as CCode,"); b.append(" Count(tbl_comment_classes.comment_id) as CommentsCounter"); b.append(" FROM tbl_assignment"); b.append(" Left Outer JOIN tbl_submission"); b.append(" ON tbl_assignment.id = tbl_submission.assignment_id"); b.append(" Left Outer JOIN tbl_submission_students"); b.append(" ON tbl_submission.id"); b.append(" = tbl_submission_students.submission_id"); b.append(" Left Outer JOIN tbl_comment"); b.append(" ON tbl_submission_students.submission_id"); b.append(" = tbl_comment.submission_id"); b.append(" INNER JOIN tbl_comment_classes"); b.append(" ON tbl_comment.id = tbl_comment_classes.comment_id "); b.append(" Right Outer Join tbl_BalesCategory"); b.append(" on tbl_comment_classes.category_name"); b.append(" = tbl_BalesCategory.BalesCategoryCode"); b.append(" WHERE (tbl_assignment.id in ("); b.append(assignmentID); b.append(" ) )"); b.append(" GROUP BY Left(tbl_BalesCategory.BalesCategoryCode, 1)"); b.append(" ORDER BY Left(tbl_BalesCategory.BalesCategoryCode, 1)"); String theSQL = b.toString(); if (log.isDebugEnabled()) { log.debug("Generating SQL: " + theSQL); } return theSQL; } /** * Finds the list of categories to be used. This should really come from * the evaluator bean. Actually, this code really generates the SQL to * find the list of categories to be used, rather than actually doing it. * * @return the SQL statement. */ final String sqlCommentsCategoriesList() { StringBuilder b = new StringBuilder(); b.append(" Select DISTINCT Left(BalesCategoryCode, 1) As CCode "); b.append(" From tbl_BalesCategory "); b.append(" Order by Left(BalesCategoryCode, 1) "); String theSQL = b.toString(); if (log.isDebugEnabled()) { log.debug("Generating SQL: " + theSQL); } return theSQL; } /** * The <code>sqlTeacherAssignmentList</code> method is used for * the first page of the report to show list of tutors and total * number of assignments they've marked. Feedback column will be * calculated using GetCommentCount method. * * @param selectedTeacherIDs a <code>String</code> value * @return the SQL statement. */ final String sqlTeacherAssignmentList(final String selectedTeacherIDs) { StringBuilder b = new StringBuilder(); b.append(" Select tbl_Tutor.ID,"); b.append(" Concat(tbl_Tutor.first_name, ' ' , tbl_Tutor.last_name)"); b.append(" as TeacherName,"); b.append(" Count(Submission_ID) as TotalAssignments, tbl_Tutor.Org_ID"); b.append(" from tbl_Tutor"); b.append(" Inner Join tbl_submission_tutors"); b.append(" on tbl_Tutor.ID = tbl_submission_tutors.tutor_id"); if (!selectedTeacherIDs.equals("")) { b.append(" Where tbl_Tutor.ID in ("); b.append(selectedTeacherIDs); b.append(")"); } b.append(" Group by tbl_Tutor.ID"); b.append(" Order by tbl_Tutor.ID"); String theSQL = b.toString(); if (log.isDebugEnabled()) { log.debug("Generating SQL: " + theSQL); } return theSQL; } /** * This function to be used for first page of report to show list * of assignment, course title and total number of student * submission for each assignment. Feedback column will be * calculated using GetCommentCount function * * @param selectedAssignmentIDs a <code>String</code> value * @return a <code>String</code> value */ final String sqlAssignmentCourseList(final String selectedAssignmentIDs) { StringBuilder b = new StringBuilder(); b.append("Select tbl_assignment.id,"); b.append(" tbl_assignment.assignment_title, tbl_assignment.course_id,"); b.append(" Course_Title, Count(tbl_submission.id) as TotalAssignments"); b.append(" From tbl_submission"); b.append(" Inner Join tbl_assignment"); b.append(" on tbl_submission.assignment_id = tbl_assignment.id"); b.append(" Inner Join tbl_Course"); b.append(" on tbl_assignment.course_id = tbl_Course.ID"); if (!selectedAssignmentIDs.equals("")) { b.append(" Where tblAssignment.AssignmentID in ("); b.append(selectedAssignmentIDs); b.append(")"); } b.append(" Group By tbl_assignment.id"); String theSQL = b.toString(); if (log.isDebugEnabled()) { log.debug("Generating SQL: " + theSQL); } return theSQL; } /** * Describe <code>sqlCourseAssignmentList</code> method here. This * function to be used for first page of report to show list of * courses, course title and total number of student submission * for each course. Feedback column will be calculated using * GetCommentCount function. * * @param selectedCourseIDs a <code>String</code> value * @return a <code>String</code> value */ final String sqlCourseAssignmentList(final String selectedCourseIDs) { StringBuilder b = new StringBuilder(); b.append(" Select tbl_assignment.course_id,"); b.append(" Course_Title, Count(tbl_submission.id) as TotalAssignments"); b.append(" From tbl_submission"); b.append(" Inner Join tbl_assignment"); b.append(" on tbl_submission.assignment_id = tbl_assignment.id"); b.append(" Inner Join tbl_Course"); b.append(" on tbl_assignment.course_id = tbl_Course.ID"); if (!selectedCourseIDs.equals("")) { b.append(" Where tbl_Course.ID in ("); b.append(selectedCourseIDs); b.append(")"); } b.append(" Group By tbl_Course.ID"); String theSQL = b.toString(); if (log.isDebugEnabled()) { log.debug("Generating SQL: " + theSQL); } return theSQL; } /** * The <code>sqlStudentAssignmentList</code> method builds an SQL * query to be used for first page of report to show list of * tutors and total number of assignments they've marked. Feedback * column will be calculated using GetCommentCount functionhere. * * @param selectedStudentIDs a <code>String</code> value * @return a <code>String</code> value */ final String sqlStudentAssignmentList(final String selectedStudentIDs) { StringBuilder b = new StringBuilder(); b.append(" Select tbl_Student.ID,"); b.append(" Concat(tbl_Student.first_name, ' ' ,tbl_Student.last_name)"); b.append(" as StudentName,"); b.append(" Count(tbl_submission_students.submission_id)"); b.append(" as TotalAssignments, tbl_Student.Org_ID"); b.append(" From tbl_student"); b.append(" Inner Join tbl_submission_students"); b.append(" on tbl_Student.ID = tbl_submission_students.student_id"); if (!selectedStudentIDs.equals("")) { b.append(" Where tbl_student.ID in ("); b.append(selectedStudentIDs); b.append(")"); } b.append(" Group by tbl_student.ID"); b.append(" Order by tbl_student.ID"); String theSQL = b.toString(); if (log.isDebugEnabled()) { log.debug("Generating SQL: " + theSQL); } return theSQL; } /** * The <code>sqlAvergageMarks</code> method this function returns * average marks SQL for the given function parameters. * * @param marksFor a <code>String</code> value * @param selectedID a <code>String</code> value * @return a <code>String</code> value */ final String sqlAvergageMarks(final String marksFor, final String selectedID) { StringBuilder b = new StringBuilder(); // all strings end the same way (after the if statement). if (marksFor.equals("tutor")) { b.append(" Select Round(Avg(tbl_submission_students.score_int),2)"); b.append(" as AverageMarks"); b.append(" From tbl_submission_tutors"); b.append(" Inner join tbl_submission_students"); b.append(" on tbl_submission_tutors.submission_id"); b.append(" = tbl_submission_students.submission_id"); b.append(" Where tbl_submission_tutors.tutor_id in ("); } else if (marksFor.equals("assignment")) { b.append(" Select Round(Avg(tbl_submission_students.Score_Int),2)"); b.append(" as AverageMarks"); b.append(" From tbl_submission_students"); b.append(" Inner join tbl_submission on"); b.append(" tbl_submission_students.submission_id"); b.append(" = tbl_submission.id"); b.append(" Where tbl_submission.assignment_id in ("); } else if (marksFor.equals("course")) { b.append(" Select Round(Avg(tbl_submission_students.Score_Int),2)"); b.append(" as AverageMarks "); b.append(" From tbl_assignment"); b.append(" Inner join tbl_submission on tbl_assignment.id"); b.append(" = tbl_submission.assignment_id"); b.append(" Inner join tbl_submission_students"); b.append(" on tbl_submission.id"); b.append(" = tbl_submission_students.submission_id"); b.append(" Where tbl_assignment.course_id in ("); } else { //otherwise for students b.append(" Select Round(Avg(Score_Int),2) as AverageMarks "); b.append(" From tbl_submission_students"); b.append(" Where Student_ID in ("); } b.append(selectedID); b.append(")"); String theSQL = b.toString(); if (log.isDebugEnabled()) { log.debug("Generating SQL: " + theSQL); } return theSQL; } /** * The <code>sqlFullComments</code> method generates the SQL to * extract categories and corresponding list of comments. * * @param marksFor a <code>String</code> value * @param selectedID a <code>String</code> value * @return a <code>String</code> value */ final String sqlFullComments(final String marksFor, final String selectedID) { StringBuilder b = new StringBuilder(); // All queries begin and end in the same way. b.append(" Select left(category_name,1) as CategoryName,"); b.append(" tbl_comment.text as CommentText"); if (marksFor.equals("tutor")) { b.append(" From tbl_submission_tutors"); b.append(" Inner join tbl_comment"); b.append(" on tbl_submission_tutors.submission_id"); b.append(" = tbl_comment.submission_id"); b.append(" Inner join tbl_comment_classes"); b.append(" on tbl_comment.id = tbl_comment_classes.comment_id"); b.append(" Where tbl_submission_tutors.tutor_id in ("); } else if (marksFor.equals("assignment")) { b.append(" From tbl_submission_students"); b.append(" Inner join tbl_comment"); b.append(" on tbl_submission_students.submission_id"); b.append(" = tbl_comment.submission_id"); b.append(" Inner join tbl_comment_classes"); b.append(" on tbl_comment.id = tbl_comment_classes.comment_id"); b.append(" Where tbl_submission.assignment_id in ("); } else if (marksFor.equals("course")) { b.append(" From tbl_assignment"); b.append(" Inner join tbl_submission"); b.append(" on tbl_assignment.id = tbl_submission.assignment_id"); b.append(" Inner join tbl_comment"); b.append(" on tbl_submission.id = tbl_comment.submission_id"); b.append(" Inner join tbl_comment_classes"); b.append(" on tbl_comment.id = tbl_comment_classes.comment_id"); b.append(" Where tbl_assignment.course_id in ("); } else { //otherwise for students b.append(" From tbl_submission_students"); b.append(" Inner join tbl_comment"); b.append(" on tbl_submission_stu.submission_id"); b.append(" = tbl_comment.submission_id"); b.append(" Inner join tbl_comment_classes"); b.append(" on tbl_comment.id = tbl_comment_classes.comment_id"); b.append(" Where Student_ID in ("); } b.append(selectedID); b.append(")"); b.append(" order by left(category_name,1)"); String theSQL = b.toString(); if (log.isDebugEnabled()) { log.debug("Generating SQL: " + theSQL); } return theSQL; } }