clascodao.java

包含了学生管理系统的一些基本操作以及相关窗口页面实现。

package com.hb.studentmanager.date;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.hb.studentmanager.connection.ConnectionDateBase;
public class ClaScoDAO {
	ConnectionDateBase cdb = new ConnectionDateBase();

	Connection con = cdb.getDateBaseConnection();

	PreparedStatement statement = null;

	ResultSet rs = null;
	public List claScoSelect(String claId,String subId,int minScore,int maxScore ) {
		List list = new ArrayList();// 得到一个数组
	    String sql="select score.stu_id,student.stu_name,test.tes_id,score.sco_sub from score inner join student on score.stu_id=student.stu_id inner join class on student.cla_id=class.cla_id inner join test on score.tes_id=test.tes_id inner join subject on test.sub_id=subject.sub_id  where subject.sub_id=? and class.cla_id=? and score.sco_sub>=? and score.sco_sub<=?";
		try {
			statement = con.prepareStatement(sql);
				statement.setString(1, subId);
				statement.setString(2, claId);
				statement.setInt(3, minScore);
				statement.setInt(4, maxScore);
			rs = statement.executeQuery();
			while (rs.next()) {
				ClaScoDTO dto = new ClaScoDTO();
				dto.setStuId(rs.getString("stu_id"));// 获得学生学号
				dto.setStuName(rs.getString("stu_name"));// 获得学生姓名
				dto.setTesId(rs.getString("tes_id"));   //获得考试编号
				dto.setStuSco(rs.getString("sco_sub"));// 获得科目成绩
				list.add(dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public List claScoSelectAll(String claId,String subId,int minScore,int maxScore ) {
		List list = new ArrayList();// 得到一个数组
		String sql=null;
		try {
			sql= "select min(score.sco_sub),max(score.sco_sub),avg(score.sco_sub) from score inner join student on student.stu_id=score.stu_id inner join class on student.cla_id=class.cla_id inner join test on score.tes_id=test.tes_id inner join subject on test.sub_id=subject.sub_id  where subject.sub_id=? and class.cla_id=? and score.sco_sub>=? and score.sco_sub<=?";
			statement = con.prepareStatement(sql);
			statement.setString(1, subId);
			statement.setString(2, claId);
			statement.setInt(3, minScore);
			statement.setInt(4, maxScore);
			rs = statement.executeQuery();
		while (rs.next()) {
			ClaScoDTO dto = new ClaScoDTO();
			dto.setMinScore(Integer.parseInt(rs.getString("min(score.sco_sub)")));// 获得最低分
			dto.setMaxScore(Integer.parseInt(rs.getString("max(score.sco_sub)")));// 获得最高分
			dto.setAveScore(Float.parseFloat(rs.getString("avg(score.sco_sub)")));// 获得平均分
			list.add(dto);
		}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
}