examdao.java

培训时做的学生管理系统.基于J2SE平台开发

package cn.com.dao.chivementdao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import cn.com.util.DBConnection;
import cn.com.util.DBSql;
import cn.com.vo.chivementvo.ChivementVo;

public class ExamDao {

	private Connection conn = DBConnection.getConnectionOracle();

	private ChivementVo examVo;

	public ExamDao() {

	}

	public ExamDao(ChivementVo examVo) {
		super();
		this.examVo = examVo;
	}

	/**
	 * 全部查询
	 */
	public Object[][] selectAll() {
		Object date[][] = null;
		int max = 0;
		int i = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(DBSql.SELECT_ALL);
			rs = ps.executeQuery();
			// 得到列数
			max = rs.getMetaData().getColumnCount();
			date = new Object[getnumberAll(DBSql.SELECT_ALL_COUNT)][max];
			while (rs.next()) {
				for (int j = 0; j < max; j++) {
					date[i][j] = rs.getObject(j + 1);
				}
				i++;
			}
			// rs.close();
			// ps.close();
			// conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return date;
	}

	/**
	 * 根据学号查询
	 */
	public Object[][] selectBySid() {
		Object date[][] = null;
		int max = 0;
		int i = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(DBSql.SELECT_BY_S_ID);
			ps.setInt(1, examVo.getS_id());
			rs = ps.executeQuery();
			// 得到列数
			max = rs.getMetaData().getColumnCount();
			date = new Object[getnumber(DBSql.SELECT_BY_S_ID_COUNT, examVo
					.getS_id())][max];
			while (rs.next()) {
				for (int j = 0; j < max; j++) {
					date[i][j] = rs.getObject(j + 1);
				}
				i++;
			}
			// rs.close();
			// ps.close();
			// conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return date;
	}

	/**
	 * 根据组号查询
	 */
	public Object[][] selectByGid() {
		Object date[][] = null;
		int max = 0;
		int i = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(DBSql.SELECT_BY_G_ID);
			ps.setInt(1, examVo.getG_id());
			rs = ps.executeQuery();
			// 得到列数
			max = rs.getMetaData().getColumnCount();
			date = new Object[getnumber(DBSql.SELECT_BY_G_ID_COUNT, examVo
					.getG_id())][max];
			while (rs.next()) {
				for (int j = 0; j < max; j++) {
					date[i][j] = rs.getObject(j + 1);
				}
				i++;
			}
			// rs.close();
			// ps.close();
			// conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return date;
	}

	/**
	 * 根据课程号查询
	 */
	public Object[][] selectByCid() {
		Object date[][] = null;
		int max = 0;
		int i = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(DBSql.SELECT_BY_C_ID);
			ps.setInt(1, examVo.getC_id());
			rs = ps.executeQuery();
			// 得到列数
			max = rs.getMetaData().getColumnCount();
			date = new Object[getnumber(DBSql.SELECT_BY_C_ID_COUNT, examVo
					.getC_id())][max];
			while (rs.next()) {
				for (int j = 0; j < max; j++) {
//					System.out.println( examVo.getG_id());
					date[i][j] = rs.getObject(j+1);
				}
				i++;
			}
			// rs.close();
			// ps.close();
			// conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return date;
	}

	/**
	 * 根据姓名模糊查询
	 * 
	 * @return
	 */
	public Object[][] selectByName() {
		Object date[][] = null;
		int max = 0;
		int i = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(DBSql.SELECT_BY_S_NAME);
			ps.setString(1, examVo.getS_name());
			ps.setString(2, "%" + examVo.getS_name() + "%");
			ps.setString(3, "%" + examVo.getS_name());
			ps.setString(4, examVo.getS_name() + "%");
			rs = ps.executeQuery();
			// 得到列数
			max = rs.getMetaData().getColumnCount();
			date = new Object[getnumberByName(DBSql.SELECT_BY_S_NAME_COUNT,
					examVo.getS_name())][max];
			while (rs.next()) {
				for (int j = 0; j < max; j++) {
					date[i][j] = rs.getObject(j + 1);
				}
				i++;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return date;
	}

	/**
	 * 根据课程名称模糊查询
	 * 
	 * @return
	 */
	public Object[][] selectByClassName() {
		Object date[][] = null;
		int max = 0;
		int i = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(DBSql.SELECT_BY_CLASS_NAME);
			ps.setString(1, examVo.getClass_name());
			ps.setString(2, "%" + examVo.getClass_name() + "%");
			ps.setString(3, "%" + examVo.getClass_name());
			ps.setString(4, examVo.getClass_name() + "%");
			rs = ps.executeQuery();
			// 得到列数
			max = rs.getMetaData().getColumnCount();
			date = new Object[getnumberByName(DBSql.SELECT_BY_CLASS_COUNT,
					examVo.getClass_name())][max];
			while (rs.next()) {
				for (int j = 0; j < max; j++) {
					date[i][j] = rs.getObject(j + 1);
				}
				i++;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return date;
	}

	/**
	 * 修改选中学生的成绩
	 * 
	 */
	public void updatSelectClass() {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(DBSql.UPDATE_EXAM_BY_STUID);
			ps.setInt(1, examVo.getClassExamChivement());
			ps.setInt(2, examVo.getS_id());
			ps.setInt(3, examVo.getC_id());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	/**
	 * 得到所有课程号和课程名
	 * 
	 * @return
	 */

	public String[] getClassNoName() {
		String[] classNoName = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int j = 0;
		try {
			int i = getnumberAll(DBSql.SELECT_CLASS_NAME_COUNT);
			classNoName = new String[i + i];
			ps = conn.prepareStatement(DBSql.SELECT_CLASS_NAME);
			rs = ps.executeQuery();
			while (rs.next()) {
				classNoName[j] = rs.getString(1);
				classNoName[j + i] = rs.getString(2);
				j++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return classNoName;
	}

	/**
	 * 根据科目修改成绩 查询学号 姓名 成绩
	 * 
	 */
	public void SelectClassStuName() {

		int i = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int j = getnumberBySelectClassName(
				DBSql.SELECT_CLASS_STU_SNO_SNAME_EXAM_COUNT, examVo.getC_id());
		int[] sNum = new int[j];
		String[] sName = new String[j];
		int[] classExam = new int[j];
		try {
			ps = conn.prepareStatement(DBSql.SELECT_CLASS_STU_SNO_SNAME_EXAM);

			ps.setInt(1, examVo.getC_id());
			rs = ps.executeQuery();
			while (rs.next()) {
				sNum[i] = rs.getInt(1);
				sName[i] = rs.getString(2);
				classExam[i] = rs.getInt(3);
				i++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		examVo.setSid(sNum);
		examVo.setSname(sName);
		examVo.setClassExam(classExam);

	}

	/**
	 * 根据科目修改成绩 查询学号 姓名 成绩 后修改成绩
	 * 
	 */
	public void UpdateClassStuName() {
//		int i = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int j = getnumberBySelectClassName(
				DBSql.SELECT_CLASS_STU_SNO_SNAME_EXAM_COUNT, examVo.getC_id());
		try {
			ps = conn.prepareStatement(DBSql.UPDATE_CHIVEMENT_BY_CLASS);
			for(int i =0;i<j;i++){
				ps.setInt(1, examVo.getClassExam()[i]);
				ps.setInt(2, examVo.getSid()[i]);
				ps.setInt(3, examVo.getC_id());
				ps.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 全部查询获得行数
	 * 
	 * @return
	 */
	public int getnumberAll(String str) {
		int number = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(str);
			rs = ps.executeQuery();
			rs.next();
			number = rs.getInt(1);
			// rs.close();
			// ps.close();
			// conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return number;
	}

	/**
	 * 根据学号 根据组号 根据课程号查询 获得行数
	 * 
	 * @return
	 */
	public int getnumber(String str, int i) {
		int number = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(str);
			ps.setInt(1, i);
			rs = ps.executeQuery();
			rs.next();
			number = rs.getInt(1);
			// rs.close();
			// ps.close();
			// conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return number;
	}

	/**
	 * 根据姓名 课程名 查询 获得行数
	 * 
	 */

	public int getnumberByName(String str, String i) {
		int number = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(str);
			ps.setString(1, i);
			ps.setString(2, "%" + i + "%");
			ps.setString(3, "%" + i);
			ps.setString(4, i + "%");
			rs = ps.executeQuery();
			rs.next();
			number = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return number;
	}

	// /**
	// * 根据课程名查询 获得行数
	// *
	// */
	//
	// public int getnumberByClassName(String str, String i) {
	// int number = 0;
	// PreparedStatement ps = null;
	// ResultSet rs = null;
	// try {
	// ps = conn.prepareStatement(str);
	// ps.setString(1, i);
	// ps.setString(2, "%" + i + "%");
	// ps.setString(3, "%" + i);
	// ps.setString(4, i + "%");
	// rs = ps.executeQuery();
	// rs.next();
	// number = rs.getInt(1);
	// } catch (SQLException e) {
	// e.printStackTrace();
	// }
	// return number;
	// }
	/**
	 * 
	 * 根据课程名修改成绩获得行数
	 * 
	 * @param str
	 * @param i
	 * @return
	 */
	public int getnumberBySelectClassName(String str, int i) {
		int number = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(str);
			ps.setInt(1, i);
			rs = ps.executeQuery();
			rs.next();
			number = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return number;
	}
}