subjectdao.java

来自「可以实现在线考试」· Java 代码 · 共 337 行

package dao;

import java.sql.*;
import java.util.ArrayList;

import po.*;
import vo.*;

public class SubjectDAO 
{
	private Connection conn = null;
	private Statement state = null;
	private ResultSet rs = null;
	
	//查询所有科目
	public ArrayList findAllSubject()
	{
		ArrayList array = new ArrayList();
		conn = Tools.getConnection();
		try {
			state = conn.createStatement();
			rs = state.executeQuery("select * from subject");
			while(rs.next())
			{
				SubjectPO spo = new SubjectPO();
				spo.setCid(rs.getInt("cid"));
				spo.setCname(rs.getString("cname"));
				spo.setAsknum(rs.getInt("asknum"));
				spo.setExamdate(rs.getString("examdate"));
				spo.setFlag(rs.getString("flag"));
				spo.setTotalscore(rs.getInt("totalscore"));
				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;
	}
	//根据科目查出一条记录
	public SubjectPO findByCname(String cname)
	{
		SubjectPO spo = null;
		conn = Tools.getConnection();
		try {
			state = conn.createStatement();
			rs = state.executeQuery("select * from subject where cname = '"+cname+"'");
			if(rs.next())
			{
				spo = new SubjectPO();
				spo.setCid(rs.getInt("cid"));
				spo.setCname(rs.getString("cname"));
				spo.setAsknum(rs.getInt("asknum"));
				spo.setExamdate(rs.getString("examdate"));
				spo.setFlag(rs.getString("flag"));
				spo.setTotalscore(rs.getInt("totalscore"));
			}
		} 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;
	}
	
	//根据科目ID查询
	public SubjectPO findByCid(int cid)
	{
		SubjectPO spo = null;
		conn = Tools.getConnection();
		try {
			state = conn.createStatement();
			rs = state.executeQuery("select * from subject where cid="+cid);
			if(rs.next())
			{
				spo = new SubjectPO();
				spo.setCid(rs.getInt("cid"));
				spo.setCname(rs.getString("cname"));
				spo.setAsknum(rs.getInt("asknum"));
				spo.setExamdate(rs.getString("examdate"));
				spo.setFlag(rs.getString("flag"));
				spo.setTotalscore(rs.getInt("totalscore"));
			}
		} 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;
	}
	
	//取科目ID号
	public int getNextID()
	{
		int myID = 0;
		conn = Tools.getConnection();
		try {
			state = conn.createStatement();
			rs = state.executeQuery("select max(cid) cid from subject");
			if(rs.next())
			{
				myID = rs.getInt("cid");
			}
		} 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 addSubject(SubjectPO subject)
	{
		boolean isok = true;
		conn = Tools.getConnection();
		try {
			state = conn.createStatement();
			int i = state.executeUpdate("insert into subject values("+subject.getCid()+",'"+subject.getCname()+"',"+subject.getTotalscore()+",to_date('"+subject.getExamdate()+"','yyyy-mm-dd'),"+subject.getAsknum()+",'"+subject.getFlag()+"')");
			if(i > 0)
			{
				isok = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return isok;
	}
	
	//删除科目_科目所对应的所有学生
	public boolean deleteSubject(int cid)
	{
		boolean isok = false;
		conn = Tools.getConnection();
		try {
			state = conn.createStatement();
			int i = state.executeUpdate("delete from subject where cid="+cid);
			if(i > 0)
			{
				isok = delete(cid);
			}
		} 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 cid)
	{
		boolean isok = false;
		conn = Tools.getConnection();
		try {
			state = conn.createStatement();
			rs = state.executeQuery("select * from relation where cid ="+cid);
			while(rs.next())
			{
				RelationPO rpo = new RelationPO();
				rpo.setCid(rs.getInt("cid"));
				rpo.setSid(rs.getInt("sid"));
				rpo.setExamflag(rs.getString("examflag"));
				rpo.setGid(rs.getInt("gid"));
				int i = state.executeUpdate("delete from subject where sid="+rpo.getSid());
				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;
	}
	
	//修改科目
	public boolean updateSubject(SubjectPO subject)
	{
		boolean isok = false;
		conn = Tools.getConnection();
		try {
			state = conn.createStatement();
			int i = state.executeUpdate("update subject set cname='"+subject.getCname()+"',totalscore="+subject.getTotalscore()+",examdate=to_date('"+subject.getExamdate()+"','yyyy-mm-dd'),asknum="+subject.getAsknum()+",flag='"+subject.getFlag()+"' where cid='"+subject.getCid()+"'");
			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 ArrayList efalg()
	{
		ArrayList array = new ArrayList();
		conn = Tools.getConnection();
		try {
			state = conn.createStatement();
			rs = state.executeQuery("select * from eflag");
			while(rs.next())
			{
				EflagPO epo = new EflagPO();
				epo.setFlag(rs.getString("flag"));
				array.add(epo);
			}
		} 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;
	}
	//遍历出难度——只用于查示
	public ArrayList difficultname()
	{
		ArrayList array = new ArrayList();
		conn = Tools.getConnection();
		try {
			state = conn.createStatement();
			rs = state.executeQuery("select * from difficult");
			while(rs.next())
			{
				DifficultPO dpo = new DifficultPO();
				dpo.setDid(rs.getInt("did"));
				dpo.setDname(rs.getString("dname"));
				array.add(dpo);
			}
		} 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;
	}
}