studentdao.java

可以实现在线考试

package business;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import po.RelationPO;
import po.StudentPO;

public class StudentDAO 
{
	private Connection conn = null;
	private Statement state = null;
	private ResultSet rs = null;
	
	//查看是否是有效的学生用户
	public boolean isStudent(String username,String password)
	{
		boolean isok = false;
		conn = dao.Tools.getConnection();
		try {
			state = conn.createStatement();
			System.out.println("select * from student where username='"+username+"' and password='"+password+"'");
			rs = state.executeQuery("select * from student where username='"+username+"' and password='"+password+"'");
			if(rs.next())
			{
				isok = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(rs != null)
				rs.close();
				if(state != null)
				state.close();
				if(conn != null)
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return isok;
	}
	
	//查出全部学生信息
	public ArrayList findAllStudent()
	{
		ArrayList array = new ArrayList();
		conn = dao.Tools.getConnection();
		try {
			state = conn.createStatement();
			rs = state.executeQuery("select * from student_base");
			while(rs.next())
			{
				StudentPO spo = new StudentPO();
				spo.setSid(rs.getInt("sid"));
				spo.setSname(rs.getString("sname"));
				spo.setSsex(rs.getString("ssex"));
				spo.setSaddress(rs.getString("saddress"));
				spo.setSbirthday(rs.getString("sbirthday"));
				spo.setStel(rs.getInt("stel"));
				array.add(spo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(rs != null)
				rs.close();
				if(state != null)
				state.close();
				if(conn != null)
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return array;
	}
	
	//取学生ID号
	public int getNextStudentID()
	{
		int myID = 0;
		conn = dao.Tools.getConnection();
		try {
			state = conn.createStatement();
			rs = state.executeQuery("select max(sid) sid from student_base");
			if(rs.next())
			{
				myID = rs.getInt("sid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(rs != null)
				rs.close();
				if(state != null)
				state.close();
				if(conn != null)
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ++myID;
	}
	
	//增加学生信息
	public boolean addStudent(StudentPO sbpo)
	{
		boolean isok = false;
		conn = dao.Tools.getConnection();
		try {
			state = conn.createStatement();
			int i = state.executeUpdate("insert into student_base values("+sbpo.getSid()+",'"+sbpo.getSname()+"','"+sbpo.getSsex()+"',to_date('"+sbpo.getSbirthday()+"','yyyy-mm-dd'),"+sbpo.getStel()+",'"+sbpo.getSaddress()+"')");
			if(i > 0)
			{
				isok = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(state != null)
				state.close();
				if(conn != null)
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return isok;
	}
	
	//按学号ID查询
	public StudentPO findBySid(int sid)
	{
		StudentPO spo = null;
		conn = dao.Tools.getConnection();
		try {
			state = conn.createStatement();
			rs = state.executeQuery("select * from student_base where sid = "+sid);
			if(rs.next())
			{
				spo = new StudentPO();
				spo.setSid(rs.getInt("sid"));
				spo.setSname(rs.getString("sname"));
				spo.setSsex(rs.getString("ssex"));
				spo.setSaddress(rs.getString("saddress"));
				spo.setSbirthday(rs.getString("sbirthday"));
				spo.setStel(rs.getInt("stel"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(rs != null)
				rs.close();
				if(state != null)
				state.close();
				if(conn != null)
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return spo;
	}
	
	//修改学生信息
	public boolean updateStudent(StudentPO sbpo)
	{
		boolean isok = false;
		conn = dao.Tools.getConnection();
		try {
			state = conn.createStatement();
			int i = state.executeUpdate("update student_base set sname='"+sbpo.getSname()+"',ssex='"+sbpo.getSsex()+"',sbirthday=to_date('"+sbpo.getSbirthday()+"','yyyy-mm-dd'),stel="+sbpo.getStel()+",saddress='"+sbpo.getSaddress()+"' where sid="+sbpo.getSid());
			if(i > 0)
			{
				isok = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(state != null)
				state.close();
				if(conn != null)
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return isok;
	}
	
	//删除学生——删除学生对应的科目
	public boolean deleteStudent(int sid)
	{
		boolean isok = false;
		conn = dao.Tools.getConnection();
		try {
			state = conn.createStatement();
			int i = state.executeUpdate("delete from student_base where sid="+sid);
			if(i > 0)
			{
				isok = delete(sid);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(state != null)
				state.close();
				if(conn != null)
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return isok;
	}
	public boolean delete(int sid)
	{
		boolean isok = false;
		conn = dao.Tools.getConnection();
		try {
			state = conn.createStatement();
			rs = state.executeQuery("select * from relation where sid="+sid);
			while(rs.next())
			{
				RelationPO rpo = new RelationPO();
				rpo.setCid(rs.getInt("cid"));
				rpo.setSid(rs.getInt("sid"));
				rpo.setGid(rs.getInt("gid"));
				rpo.setExamflag(rs.getString("examflag"));
				int i = state.executeUpdate("delete from subject where cid="+rpo.getCid());
				if(i > 0)
				{
					isok = true;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(rs != null)
				rs.close();
				if(state != null)
				state.close();
				if(conn != null)
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return isok;
	}
}