abstractdboperate.java

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

			con = ConnectManager.getConnection();
			if (getUser(user.getUserId()) != null)// 检查数据库的userId是否重名
				return false;
			String sql = "insert into iuser (type,userid,password,name,schoolclass_id) VALUES (?,?,?,?,?);";
			sm = con.prepareStatement(sql);
			sm.setString(2, user.getUserId());
			sm.setString(3, user.getPassword());
			sm.setString(4, user.getName());
			if (user instanceof Student) {
				sm.setString(1, Constants.IUSER_STUDENT_TYPE);
				SchoolClass schoolClass = ((Student) user).getSchoolclass();
				if (schoolClass == null)
					sm.setNull(5, java.sql.Types.BIGINT);
				else
					sm.setInt(5, schoolClass.getId().intValue());
				sm.execute();
			} else if (user instanceof Teacher) {
				sm.setString(1, Constants.IUSER_TEACHER_TYPE);
				sm.setNull(5, java.sql.Types.BIGINT);
				sm.execute();// 将用户表的记录插入
				rs = sm.executeQuery("select id from iuser where userid='"
						+ user.getUserId() + "'");
				rs.next();
				int iuser_id = rs.getInt(1);
				// 如果是老师还要处理他的课程情况
				for (Course course : ((Teacher) user).getCourses()) {
					sql = "insert into iuser_course values (" + iuser_id + ","
							+ course.getId() + ")";
					sm.addBatch(sql);
				}
				sm.executeBatch();
			}
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback(); // 如出现异常则回滚
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		} finally {
			close(rs);
			close(sm);
			close(con);
		}
		return false;
	}

	// 删除一条用户记录
	public boolean removeUser(IUser user) {
		Connection con = null;
		Statement sm = null;
		ResultSet rs = null;
		boolean result = true;
		try {
			// 控制事务的方法：
			// （1）setAutoCommit(Boolean autoCommit):设置是否自动提交事务；
			// （2）commit();提交事务；
			// （3）rollback();撤销事务；
			con = ConnectManager.getConnection();
			con.setAutoCommit(false); // 禁止自动提交事务
			sm = con.createStatement();
			sm.addBatch("delete from iuser where id=" + user.getId());
			if (user instanceof Teacher)// 教师型用户还要删除其课程联接表的记录
				sm.addBatch("delete from iuser_course where iuser_id="
						+ user.getId());
			sm.executeBatch();
			con.commit(); // 提交
		} catch (SQLException e) {
			result = false;
			e.printStackTrace();
			try {
				con.rollback(); // 如出现异常则回滚
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		} finally {
			close(rs);
			close(sm);
			close(con);
		}
		return result;
	}

	// 根据分页信息类取得相应的用户记录
	public List<IUser> getUsers(QueryInfo qi) {
		Connection con = null;
		Statement sm = null;
		ResultSet rs = null;
		try {
			con = ConnectManager.getConnection();
			sm = con.createStatement();
			// 得到总记录数
			rs = sm.executeQuery("select count(id) from iuser");
			rs.next();
			qi.rsCount = rs.getInt(1);
			if (qi.rsCount == 0)// 等于0表示没有记录
				return Collections.emptyList();
			// 算出总页数
			if (qi.rsCount % qi.pageSize == 0)
				qi.pageCount = qi.rsCount / qi.pageSize;
			else
				qi.pageCount = (qi.rsCount / qi.pageSize) + 1;
			// 算出起始位置＝ (当前页号-1)*每页记录数
			int start = (qi.currentPage - 1) * qi.pageSize;
			rs = sm.executeQuery("select * from iuser limit " + start + ","
					+ qi.pageSize);
			List<IUser> list = new ArrayList<IUser>(qi.pageSize);
			while (rs.next()) {
				IUser user = createUserFromRs(rs);
				user.setId(new Long(rs.getLong("id")));
				user.setUserId(rs.getString("userid"));
				user.setPassword(rs.getString("password"));
				user.setName(rs.getString("name"));
				user.setLatestOnline(rs.getDate("latestOnline"));
				list.add(user);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(sm);
			close(con);
		}
		return Collections.emptyList();
	}

	// 根据用户名得到用户对象，如返回null则表示此用户不存在
	public IUser getUser(String userId) {
		Connection con = null;
		Statement sm = null;
		ResultSet rs = null;
		try {
			con = ConnectManager.getConnection();
			sm = con.createStatement();
			rs = sm.executeQuery("select * from iuser where userId='" + userId
					+ "'");
			if (rs.next()) {
				IUser user = createUserFromRs(rs);
				// 将数据设置到实体类中
				user.setId(new Long(rs.getLong("id")));
				user.setUserId(userId);
				user.setPassword(rs.getString("password"));
				user.setName(rs.getString("name"));
				user.setLatestOnline(rs.getDate("latestOnline"));
				return user;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(sm);
			close(con);
		}
		return null;
	}

	// 根据数据库记录rs中的Type字段值来创建实际的用户类型，并赋给其特有的值
	private IUser createUserFromRs(ResultSet rs) throws SQLException {
		String type = rs.getString("type");// 根据type值判断此记录属于什么类
		if (type.equalsIgnoreCase(Constants.IUSER_TEACHER_TYPE)) {
			Teacher o = new Teacher();
			Long iuser_id = new Long(rs.getLong("id"));
			o.setCourses(getCourses(iuser_id));
			return o;
		} else if (type.equalsIgnoreCase(Constants.IUSER_STUDENT_TYPE)) {
			Student o = new Student();
			Long schoolclass_id = new Long(rs.getLong("schoolclass_id"));
			o.setSchoolclass(getSchoolclass(schoolclass_id));
			return o;
		}
		return null;
	}

	// 根据用户的id字段，得到其相应的课程记录。不会返回null值
	public Set<Course> getCourses(Long iuser_id) {
		Connection con = null;
		Statement sm = null;
		ResultSet rs = null;
		try {
			con = ConnectManager.getConnection();
			sm = con.createStatement();
			// 由用户课程连接表得到此用户对应的所有课程ID值
			String subSql = "select course_id from iuser_course WHERE iuser_id="
					+ iuser_id;
			// 得到这些ID值对应的课程记录
			String sql = "SELECT * from course where id in (" + subSql + ")";
			rs = sm.executeQuery(sql);
			Set<Course> set = new HashSet<Course>();
			while (rs.next()) {
				Course course = new Course();
				course.setId(new Long(rs.getInt("id")));
				course.setName(rs.getString("name"));
				set.add(course);
			}
			return set;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(sm);
			close(con);
		}
		return Collections.emptySet();
	}

	// 根据班级的id得到班级对象。返回null表示不存在对应的班级
	public SchoolClass getSchoolclass(Long id) {
		Connection con = null;
		PreparedStatement sm = null;
		ResultSet rs = null;
		try {
			con = ConnectManager.getConnection();
			sm = con.prepareStatement("SELECT * from schoolclass where id="
					+ id);
			rs = sm.executeQuery();
			if (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);
				}
				return schoolClass;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(sm);
			close(con);
		}
		return null;
	}

	// 关闭ResultSet对象
	void close(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
	}

	// 关闭Statement对象
	void close(Statement sm) {
		if (sm != null) {
			try {
				sm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			sm = null;
		}
	}

	// 关闭Connection对象
	void close(Connection con) {
	}
}