studentdao.java

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

package cn.com.dao.studentmanagerdao;

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.studentmanagervo.StudentVo;

public class StudentDao {

	private Connection conn = DBConnection.getConnectionOracle();

	private StudentVo studentVo;

	public StudentDao() {

	}

	public StudentDao(StudentVo studentVo) {
		super();
		this.studentVo = studentVo;
	}

	/**
	 * 全部查询
	 */
	public Object[][] selectAll() {
		Object date[][] = null;
		int max = 0;
		int i = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(DBSql.SELECT_STU_ALL);
			rs = ps.executeQuery();
			// 得到列数
			max = rs.getMetaData().getColumnCount();
			date = new Object[getnumberAll(DBSql.SELECT_STU_ALL_COUNT)][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 Object[][] selectBySid() {
		Object date[][] = null;
		int max = 0;
		int i = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(DBSql.SELECT_STU_BY_S_ID);
			ps.setInt(1, studentVo.getS_id());
			rs = ps.executeQuery();
			// 得到列数
			max = rs.getMetaData().getColumnCount();
			date = new Object[getnumber(DBSql.SELECT_STU_BY_S_ID_COUNT,
					studentVo.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_STU_BY_G_ID);
			ps.setInt(1, studentVo.getG_id());
			rs = ps.executeQuery();
			// 得到列数
			max = rs.getMetaData().getColumnCount();
			date = new Object[getnumber(DBSql.SELECT_STU_BY_G_ID_COUNT,
					studentVo.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;
	}

	/**
	 * 根据姓名模糊查询
	 * 
	 * @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_STU_BY_S_NAME);
			ps.setString(1, studentVo.getS_name());
			ps.setString(2, "%" + studentVo.getS_name() + "%");
			ps.setString(3, "%" + studentVo.getS_name());
			ps.setString(4, studentVo.getS_name() + "%");
			rs = ps.executeQuery();
			// 得到列数
			max = rs.getMetaData().getColumnCount();
			date = new Object[getnumberByName(DBSql.SELECT_STU_BY_S_NAME_COUNT,
					studentVo.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;
	}

	/**
	 * 删除所选学生的信息
	 * 
	 */
	public void deletestudentinfo() {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(DBSql.DELETE_STU_BY_S_ID);
			ps.setInt(1, studentVo.getS_id());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 增加学生信息
	 * 
	 */
	public void addInfo() {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(DBSql.INSERT_STU);
			ps.setInt(1, studentVo.getS_id());
			ps.setInt(2, studentVo.getG_id());
			ps.setString(3, studentVo.getS_name());
			ps.setString(4, studentVo.getS_sex());
			ps.setString(5, studentVo.getS_grade());
			ps.setString(6, studentVo.getS_school());
			ps.setString(7, studentVo.getS_professional());
			ps.setLong(8, studentVo.getS_tel());
			ps.setLong(9, studentVo.getS_qq());
			ps.setString(10, studentVo.getS_emali());
			rs = ps.executeQuery();
		} catch (SQLException e) {
			javax.swing.JOptionPane.showMessageDialog(null, e.getMessage());
			e.printStackTrace();
		}

	}

	/**
	 * 增加学生考试成绩信息
	 * 
	 */
	public void addInfoExamChivement() {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(DBSql.INSERT_STU_EXAM);
			ps.setInt(1, studentVo.getS_id());
			ps.setInt(2, studentVo.getG_id());
			ps.setInt(3, studentVo.getS_classnum());
			rs = ps.executeQuery();
		} catch (SQLException e) {
			javax.swing.JOptionPane.showMessageDialog(null, e.getMessage());
			e.printStackTrace();
		}

	}

	/**
	 * 增加学生平时成绩信息
	 * 
	 */
	public void addInfoOrdinaryChivement() {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(DBSql.INSERT_STU_OR);
			ps.setInt(1, studentVo.getS_id());
			ps.setInt(2, studentVo.getG_id());
			rs = ps.executeQuery();
		} catch (SQLException e) {
			javax.swing.JOptionPane.showMessageDialog(null, e.getMessage());
			e.printStackTrace();
		}
		try {
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * 修改学生信息
	 * 
	 */
	public void updateInfo() {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(DBSql.UPDATE_STU);
			ps.setInt(1, studentVo.getG_id());
			ps.setString(2, studentVo.getS_name());
			ps.setString(3, studentVo.getS_sex());
			ps.setString(4, studentVo.getS_grade());
			ps.setString(5, studentVo.getS_school());
			ps.setString(6, studentVo.getS_professional());
			ps.setLong(7, studentVo.getS_tel());
			ps.setLong(8, studentVo.getS_qq());
			ps.setString(9, studentVo.getS_emali());
			ps.setInt(10, studentVo.getS_id());
			rs = ps.executeQuery();
		} catch (SQLException e) {
			javax.swing.JOptionPane.showMessageDialog(null, e.getMessage());
			e.printStackTrace();
		}
		try {
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			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;
	}

}