package com.dp.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
public class DoData_Exam {
private Connection con;
protected boolean updataGrade(int gradeId, int score){
con = LoadData.getConnection();
String uSQL = "update grade set exam_score = "+ score + ", tag_examine = 0 where id = " + gradeId;
System.out.println(uSQL);
try {
PreparedStatement ps = con.prepareStatement(uSQL);
ps.executeUpdate();
con.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if(con != null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return true;
}
protected ExamPaper getExamInfo(String id, boolean record){
ExamPaper examPapers = new ExamPaper();
ArrayList<Problem> examList = new ArrayList<>();
ArrayList<ProblemType> typeList = new ArrayList<>();
con = LoadData.getConnection();
String SQL_PROBLEM, SQL_PAPER, SQL_TYPES;
if(record){
SQL_PROBLEM = "select DISTINCT problems.id,problem_type,problem_contents,problem_answers,"
+ "problems.creater_id,problems.create_date, problems.problem_status "
+ "FROM test_paper INNER JOIN test_paper_problem on "
+ "test_paper_id =test_paper_problem.test_paper_id "
+ "INNER JOIN problems on test_paper_problem.problem_id=problems.id"
+ " where test_paper_id like '" + id + "'";
SQL_PAPER = "SELECT * FROM test_paper where id like '" + id + "'";
SQL_TYPES = "SELECT problem_type_id, subject_type.subject_type,score "
+ "FROM test_paper_score INNER JOIN subject_type on problem_type_id = subject_type.id "
+ "WHERE test_paper_id like '" + id + "'";
}
else{
SQL_PROBLEM = "select DISTINCT problems.id,problem_type,problem_contents,"
+ "problem_answers,problems.creater_id,problems.create_date,"
+ " problems.problem_status FROM test_paper INNER JOIN test_paper_problem "
+ "on test_paper_id =test_paper_problem.test_paper_id INNER JOIN problems "
+ "on test_paper_problem.problem_id=problems.id "
+ "WHERE test_paper_problem.test_paper_id = (SELECT test_paper.id "
+ "FROM test_paper WHERE `status`=1);";
SQL_PAPER = "SELECT * FROM test_paper where status = 1";
SQL_TYPES = "SELECT problem_type_id, subject_type.subject_type,score "
+ "FROM test_paper_score INNER JOIN subject_type on problem_type_id = subject_type.id "
+ "WHERE test_paper_id = (SELECT id FROM test_paper where status = 1)";
}
try {
System.out.println(SQL_PROBLEM);
PreparedStatement ps = con.prepareStatement(SQL_PROBLEM);
ResultSet rs = ps.executeQuery();
while(rs.next()){
Problem problem = new Problem();
problem.setId(rs.getInt(1));
problem.setTypes_id(rs.getInt(2));
problem.setContents(rs.getString(3));
problem.setAnswers(rs.getString(4));
problem.setCreaterId(rs.getInt(5));
problem.setCreateDate(rs.getString(6));
CheckStatus cs = null;
cs = CheckStatus.WAIT_CHECK;
problem.setCheckStatus(cs);
examList.add(problem);
}
examPapers.setExamList(examList);
examPapers.getTypeNum();
ps = con.prepareStatement(SQL_PAPER);
rs = ps.executeQuery();
while(rs.next()){
examPapers.setPaper_id(rs.getInt(1));
examPapers.setSynopsis(rs.getString(2));
examPapers.setCreate_date(rs.getDate(3));
examPapers.setCreater_id(rs.getInt(4));
examPapers.setPaper_score(rs.getInt(5));
examPapers.setDefine_time(rs.getInt(6));
examPapers.setStatus(rs.getInt(7));
}
ps = con.prepareStatement(SQL_TYPES);
rs = ps.executeQuery();
while(rs.next()){
ProblemType problemType = new ProblemType();
problemType.setId(rs.getInt(1));
problemType.setProblemType(rs.getString(2));
problemType.setType_score(rs.getInt(3));
typeList.add(problemType);
}
examPapers.setProblemTypes(typeList);
con.commit();
return examPapers;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if(con != null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return examPapers;
}
public ExamPaper getMarkingExam(int gradeId){
con = LoadData.getConnection();
String problem_SQL = "select DISTINCT problems.id,problem_type,problem_contents, " +
"problem_answers,problems.creater_id,problems.create_date, " +
"person_answer.problem_answer FROM grade " +
"INNER JOIN test_paper_problem on exam_id =test_paper_problem.test_paper_id " +
"INNER JOIN problems on test_paper_problem.problem_id=problems.id " +
"INNER JOIN person_answer ON grade.id = person_answer.grade_id AND " +
"test_paper_problem.problem_id = person_answer.problem_id " +
"WHERE test_paper_problem.test_paper_id = (SELECT grade.exam_id " +
"FROM grade WHERE `id`=" +gradeId + ") and person_answer.grade_id = " + gradeId;
String problemType_SQL = "SELECT problem_type_id, subject_type.subject_type,score "+
"FROM test_paper_score INNER JOIN subject_type on problem_type_id = subject_type.id " +
"WHERE test_paper_id = (SELECT grade.exam_id FROM grade where grade.id = " + gradeId +")";
ExamPaper examPaper = new ExamPaper();
ArrayList<Problem> examList = new ArrayList<>();
ArrayList<ProblemType> typeList = new ArrayList<>();
System.out.println(problem_SQL);
System.out.println(problemType_SQL);
try {
PreparedStatement ps2 = con.prepareStatement(problem_SQL);
ResultSet rs2 = ps2.executeQuery();
while(rs2.next()){
Problem problem = new Problem();
problem.setId(rs2.getInt(1));
problem.setTypes_id(rs2.getInt(2));
problem.setContents(rs2.getString(3));
problem.setAnswers(rs2.getString(4));
problem.setCreaterId(rs2.getInt(5));
problem.setCreateDate(rs2.getString(6));
problem.setSelfAnswers(rs2.getString(7));
examList.add(problem);
}
examPaper.setExamList(examList);
examPaper.getTypeNum();
PreparedStatement ps3 = con.prepareStatement(problemType_SQL);
ResultSet rs3 = ps3.executeQuery();
while(rs3.next()){
ProblemType problemType = new ProblemType();
problemType.setId(rs3.getInt(1));
problemType.setProblemType(rs3.getString(2));
problemType.setType_score(rs3.getInt(3));
typeList.add(problemType);
}
examPaper.setProblemTypes(typeList);
examPaper.setGradeId(gradeId);
con.commit();
return examPaper;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if(con != null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return examPaper;
}
public ArrayList<ExamPaper> getMarkingList(){
ArrayList<ExamPaper> markingList = new ArrayList<>();
String gradeSQL = "SELECT * from grade where tag_examine = 1";
con = LoadData.getConnection();
try {
PreparedStatement ps = con.prepareStatement(gradeSQL);
ResultSet rs = ps.executeQuery();
while(rs.next())
{
ExamPaper examPaper = new ExamPaper();
// examPaper = getMarkingListProblem(examPaper);
examPaper.setGradeId(rs.getInt(1));
examPaper.setExaminee(rs.getInt(2));
examPaper.setPaper_id(rs.getInt(3));
examPaper.setExam_score(rs.getInt(4));
评论0
最新资源