abstractdboperate.java

学生成绩管理系统 eclipse rcp开发 swt技术

package com.zdh.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 com.zdh.sms.model.*;


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 testDB.*,b.userId,b.password,b.name,b.latestonline,b.schoolclass_id ");
			sb
					.append("FROM studentscore testDB LEFT JOIN iuser b ON testDB.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 testDB.*, ");
			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_password, d.latestonline iuser_latestonline ");
			sb
					.append("from exam testDB LEFT JOIN schoolclass b on testDB.schoolclass_id=b.id ");
			sb.append("LEFT JOIN course c ON  testDB.course_id =c.id ");
			sb.append("LEFT JOIN iuser d ON  testDB.iuser_id =d.id ");
			if (course != null && schoolClass != null)
				sb
						.append("where testDB.course_id = " + course.getId()
								+ " and testDB.schoolclass_id = "
								+ schoolClass.getId());
			else if (course == null && schoolClass != null)
				sb.append("where testDB.schoolclass_id = "
						+ schoolClass.getId());
			else if (course != null && schoolClass == null)
				sb.append("where testDB.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.setGrade(o2);
					exam.setSchoolClass(o);
				}
				{
					Course o = new Course();
					o.setId(new Long(rs.getInt("course_id")));
					o.setName(rs.getString("course_name"));
					exam.setCourse(o);
				}
				{
					Teacher o = new Teacher();
					o.setId(new Long(rs.getInt("iuser_id")));
					o.setUserId(rs.getString("iuser_userid"));
					o.setPassword(rs.getString("iuser_password"));
					o.setName(rs.getString("iuser_name"));
					o.setLatestOnline(rs.getDate("iuser_latestonline"));
					exam.setTeacher(o);
				}
				list.add(exam);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(sm);
			close(con);
		}
		return Collections.emptyList();
	}

	// 修改用户记录
	public boolean modifyUser(IUser user) {
		Connection con = null;
		PreparedStatement sm = null;
		ResultSet rs = null;
		try {
			con = ConnectManager.getConnection();
			String sql = "update iuser set userid=?,password=?,name=?,latestOnline=?,schoolclass_id=? where id=?";
			sm = con.prepareStatement(sql);
			sm.setString(1, user.getUserId());
			sm.setString(2, user.getPassword());
			sm.setString(3, user.getName());
			Date latestOnline = user.getLatestOnline();
			if (latestOnline != null) {
				long dateValue = latestOnline.getTime();
				sm.setDate(4, new java.sql.Date(dateValue));
			}
			sm.setInt(6, user.getId().intValue());
			if (user instanceof Student) {
				SchoolClass schoolClass = ((Student) user).getSchoolclass();
				if (schoolClass == null)
					sm.setNull(5, java.sql.Types.BIGINT);
				else
					sm.setInt(5, schoolClass.getId().intValue());
			} else {
				sm.setNull(5, java.sql.Types.BIGINT);
			}
			sm.executeUpdate();
			// 如果是老师还要处理他的课程情况
			if (user instanceof Teacher) {
				con.setAutoCommit(false); // 禁止自动提交事务
				sm.setNull(5, java.sql.Types.BIGINT);
				sm.addBatch("delete from iuser_course where iuser_id="
						+ user.getId());
				Set<Course> set = ((Teacher) user).getCourses();
				for (Course course : set)
					sm.addBatch("insert into iuser_course values ("
							+ user.getId() + "," + course.getId() + ")");
				sm.executeBatch();
				con.commit(); // 提交
			}
		} catch (SQLException e) {
			e.printStackTrace();
			// 如出现异常则回滚
			try {
				con.rollback();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			return false;
		} finally {
			close(rs);
			close(sm);
			close(con);
		}
		return true;
	}

	// 得到所有班级的记录
	public List<SchoolClass> getAllSchoolClass() {
		Connection con = null;
		PreparedStatement sm = null;
		ResultSet rs = null;
		try {
			con = ConnectManager.getConnection();
			sm = con.prepareStatement("SELECT * from schoolclass");
			rs = sm.executeQuery();
			List<SchoolClass> list = new ArrayList<SchoolClass>();
			while (rs.next()) {
				SchoolClass schoolClass = new SchoolClass();
				schoolClass.setId(new Long(rs.getInt("id")));
				schoolClass.setName(rs.getString("name"));
				// 设置年级属性
				Grade grade = new Grade();
				grade.setId(new Long(rs.getInt("grade_id")));
				schoolClass.setGrade(grade);
				list.add(schoolClass);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(sm);
			close(con);
		}
		return Collections.emptyList();
	}

	// 得到所有课程记录
	public List<Course> getCourses() {
		Connection con = null;
		PreparedStatement sm = null;
		ResultSet rs = null;
		try {
			con = ConnectManager.getConnection();
			String sql = "SELECT * from course";
			sm = con.prepareStatement(sql);
			rs = sm.executeQuery();
			List<Course> list = new ArrayList<Course>();
			while (rs.next()) {
				Course course = new Course();
				course.setId(new Long(rs.getInt("id")));
				course.setName(rs.getString("name"));
				list.add(course);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(sm);
			close(con);
		}
		return Collections.emptyList();
	}

	// 插入一条用户记录
	public boolean insertUser(IUser user) {
		Connection con = null;
		PreparedStatement sm = null;
		ResultSet rs = null;
		try {