【Java开源代码栏目提醒】:网学会员--在 Java开源代码编辑为广大网友搜集整理了:AbstractDbOperate.java绩等信息,祝愿广大网友取得需要的信息,参考学习。
package cn.com.chengang.sms.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import cn.com.chengang.sms.Constants;
import cn.com.chengang.sms.model.Course;
import cn.com.chengang.sms.model.Exam;
import cn.com.chengang.sms.model.Grade;
import cn.com.chengang.sms.model.IUser;
import cn.com.chengang.sms.model.SchoolClass;
import cn.com.chengang.sms.model.Student;
import cn.com.chengang.sms.model.StudentScore;
import cn.com.chengang.sms.model.Teacher;
public class AbstractDbOperate implements DbOperate {
//根据考试参数得到此考试下的所有学生记录
public List<StudentScore> getStudentScore(Exam exam) {
Connection con = null;
PreparedStatement sm = null;
ResultSet rs = null;
try {
con = ConnectManager.getConnection();
StringBuilder sb = new StringBuilder();
sb.append("SELECT a.*,b.userId,b.password,b.name,b.latestonline,b.schoolclass_id ");
sb.append("FROM studentscore a LEFT JOIN iuser b ON a.iuser_id=b.id ");
sb.append("WHERE exam_id=" + exam.getId());
System.out.println(sb.toString());
sm = con.prepareStatement(sb.toString());
rs = sm.executeQuery();
List<StudentScore> list = new ArrayList<StudentScore>();
while (rs.next()) {
StudentScore studentScore = new StudentScore();
studentScore.setId(new Long(rs.getInt("id")));
studentScore.setExam(exam);
studentScore.setScore(rs.getFloat("score"));
{
Student stu = new Student();
stu.setId(new Long(rs.getLong("iuser_id")));
stu.setUserId(rs.getString("userid"));
stu.setPassword(rs.getString("password"));
stu.setName(rs.getString("name"));
stu.setLatestOnline(rs.getDate("latestOnline"));
{// 设置学生的班级属性
SchoolClass schoolClass = new SchoolClass();
schoolClass.setId(new Long(rs.getInt("schoolclass_id")));
stu.setSchoolclass(schoolClass);
}
studentScore.setStudent(stu);
}
list.add(studentScore);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs);
close(sm);
close(con);
}
return Collections.emptyList();
}
//根据考试课程和考试班级得到考试。如果course为空表示取所有课程考试,同样,
//如果schoolClass为空表示取所有班级的考试
public List<Exam> getExam(Course course, SchoolClass schoolClass) {
Connection con = null;
PreparedStatement sm = null;
ResultSet rs = null;
try {
con = ConnectManager.getConnection();
StringBuilder sb = new StringBuilder();
sb.append("SELECT a.*, ");
sb.append("b.name schoolclass_name,b.grade_id schoolclass_grade_id, ");
sb.append("c.name course_name, ");
sb.append("d.name iuser_name,d.userid iuser_userid,d.password iuser_pass
word, d.latestonline iuser_latestonline ");
sb.append("from exam a LEFT JOIN schoolclass b on a.schoolclass_id=b.id ");
sb.append("LEFT JOIN course c ON a.course_id =c.id ");
sb.append("LEFT JOIN iuser d ON a.iuser_id =d.id ");
if (course != null && schoolClass != null)
sb.append("where a.course_id = " + course.getId() + " and a.schoolclass_id = " + schoolClass.getId());
else if (course == null && schoolClass != null)
sb.append("where a.schoolclass_id = " + schoolClass.getId());
else if (course != null && schoolClass == null)
sb.append("where a.course_id = " + course.getId());
System.out.println(sb.toString());
sm = con.prepareStatement(sb.toString());
rs = sm.executeQuery();
List<Exam> list = new ArrayList<Exam>();
while (rs.next()) {
Exam exam = new Exam();
//将数据设置到实体类中
exam.setId(new Long(rs.getInt("id")));
exam.setName(rs.getString("name"));
exam.setDate(rs.getDate("date"));
{
SchoolClass o = new SchoolClass();
o.setId(new Long(rs.getInt("schoolclass_id")));
o.setName(rs.getString("schoolclass_name"));
Grade o2 = new Grade();
o2.setId(new Long(rs.getInt("schoolclass_grade_id")));
o.s