teacherdao.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.TeacherVo;

public class TeacherDao {

	private Connection conn = DBConnection.getConnectionOracle();

	private TeacherVo teacherVo;

	public TeacherDao() {

	}

	public TeacherDao(TeacherVo teacherVo) {
		super();
		this.teacherVo = teacherVo;
	}

	/**
	 * 全部查询
	 */
	public Object[][] selectAll() {
		Object date[][] = null;
		int max = 0;
		int i = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(DBSql.SELECT_TEACHER_ALL);
			rs = ps.executeQuery();
			// 得到列数
			max = rs.getMetaData().getColumnCount();
			date = new Object[getnumberAll(DBSql.SELECT_TEACHER_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_TEACHER_BY_T_ID);
			ps.setInt(1, teacherVo.getT_id());
			rs = ps.executeQuery();
			// 得到列数
			max = rs.getMetaData().getColumnCount();
			date = new Object[getnumber(DBSql.SELECT_TEACHER_BY_T_ID_COUNT,
					teacherVo.getT_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[][] selectByDuty() {
		Object date[][] = null;
		int max = 0;
		int i = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(DBSql.SELECT_TEACHER_BY_DUTY);
			ps.setString(1, teacherVo.getT_duty());
			ps.setString(2, "%" + teacherVo.getT_duty() + "%");
			ps.setString(3, "%" + teacherVo.getT_duty());
			ps.setString(4, teacherVo.getT_duty() + "%");
			rs = ps.executeQuery();
			// 得到列数
			max = rs.getMetaData().getColumnCount();
			date = new Object[getnumberByName(DBSql.SELECT_TEACHER_BY_DUTY_COUNT,
					teacherVo.getT_duty())][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[][] selectByName() {
		Object date[][] = null;
		int max = 0;
		int i = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(DBSql.SELECT_TEACHER_BY_T_NAME);
			ps.setString(1, teacherVo.getT_name());
			ps.setString(2, "%" + teacherVo.getT_name() + "%");
			ps.setString(3, "%" + teacherVo.getT_name());
			ps.setString(4, teacherVo.getT_name() + "%");
			rs = ps.executeQuery();
			// 得到列数
			max = rs.getMetaData().getColumnCount();
			date = new Object[getnumberByName(DBSql.SELECT_TEACHER_BY_T_NAME_COUNT,
					teacherVo.getT_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_TEACHER_BY_T_ID);
			ps.setInt(1, teacherVo.getT_id());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 增加老师信息
	 * 
	 */
	public void addInfo() {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(DBSql.INSERT_TEACHER);
			ps.setInt(1, teacherVo.getT_id());
			ps.setString(2, teacherVo.getT_name());
			ps.setString(3, teacherVo.getT_sex());
			ps.setInt(4, teacherVo.getT_age());
			ps.setString(5, teacherVo.getT_duty());
			ps.setLong(6, teacherVo.getT_qq());
			ps.setString(7, teacherVo.getT_email());
			ps.setLong(8, teacherVo.getT_tel());
			ps.executeUpdate();
		} catch (SQLException e) {
			javax.swing.JOptionPane.showMessageDialog(null, e.getMessage());
			e.printStackTrace();
		}

	}
	
	
	/**
	 * 修改老师信息
	 * 
	 */
	public void updateInfo() {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(DBSql.UPDATE_TEACHER);
			ps.setString(1, teacherVo.getT_name());
			ps.setString(2, teacherVo.getT_sex());
			ps.setInt(3, teacherVo.getT_age());
			ps.setString(4, teacherVo.getT_duty());
			ps.setLong(5, teacherVo.getT_qq());
			ps.setString(6, teacherVo.getT_email());
			ps.setLong(7, teacherVo.getT_tel());
			ps.setInt(8, teacherVo.getT_id());
			ps.executeUpdate();
		} catch (SQLException e) {
			javax.swing.JOptionPane.showMessageDialog(null, e.getMessage());
			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;
	}

}