dutydao.java

包含了学生管理系统的一些基本操作以及相关窗口页面实现。

package com.hb.studentmanager.date;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hb.studentmanager.connection.ConnectionDateBase;

public class DutyDAO {
	ConnectionDateBase cdb=new ConnectionDateBase();
	
	Connection con=cdb.getDateBaseConnection();
	
	PreparedStatement ps=null;
	
	ResultSet rs=null;
	
	/**
	 * 初始化出勤页面
	 * @return
	 */
	public List select(){
		List list = new ArrayList();
		try {
			String sql="select * from duty_stu order by stu_id";
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				DutyDTO dto=new DutyDTO();
				dto.setStu_id(rs.getString("stu_id"));
				dto.setStu_name(rs.getString("stu_name"));
				dto.setDut_date(rs.getString("dut_date"));
				dto.setDut_sta(rs.getString("dut_sta"));
				dto.setCla_id(rs.getString("cla_id"));
				list.add(dto);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 添加出勤记录
	 * @param dto
	 */
	public void AddDuty(DutyDTO dto){
		try {
			String sql="insert into duty (dut_sta,dut_date,dut_rem,stu_id) values (?,?,?,?)";
			ps=con.prepareStatement(sql);
			ps.setString(1, dto.getDut_sta());
			ps.setString(2, dto.getDut_date());
			ps.setString(3, dto.getDut_rem());
			ps.setString(4, dto.getStu_id());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 更新duty表中数据
	 * @param dto
	 */
	public void updateDuty(DutyDTO dto){
		try {
			String sql="update duty set dut_sta=?,dut_rem=? where stu_id=? and dut_date=?";
			ps=con.prepareStatement(sql);
			ps.setString(1, dto.getDut_sta());
			ps.setString(2, dto.getDut_rem());
			ps.setString(3, dto.getStu_id());
			ps.setString(4, dto.getDut_date());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	
	/**
	 * 得到一个学号，删除出勤记录
	 * @param stuid
	 */
	public void DeleteDuty(String stuid,String dutydate){
		try {
			String sql="delete from duty where stu_id=? and dut_date=?";
			ps=con.prepareStatement(sql);
			ps.setString(1,stuid);
			ps.setString(2,dutydate);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 *根据学号查询duty表并将结果传给DutyAction 
	 * @param stuid
	 */
	public List selectAll(String stuid,String dutydate){
		List list =new ArrayList();
		try {
			String sql="select * from duty where stu_id=? and dut_date=?";
			ps=con.prepareStatement(sql);
			ps.setString(1, stuid);
			ps.setString(2, dutydate);
			rs=ps.executeQuery();
			while(rs.next()){
				DutyDTO dto=new DutyDTO();
				dto.setStu_id(rs.getString("stu_id"));
				dto.setDut_date(rs.getString("dut_date"));
				dto.setDut_sta(rs.getString("dut_sta"));
				dto.setDut_rem(rs.getString("dut_rem"));
				list.add(dto);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 得到学生学号
	 *
	 */
	public List getStudentId(){
		List list =new ArrayList();
		try {
			String sql="select stu_id from student";
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				DutyDTO dto=new DutyDTO();
				dto.setCla_id(rs.getString("stu_id"));
				list.add(dto);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
     * 查询班级表，返回班号到StuAtion中
     * @return
     */
    public List getClassName(){
    	List list = new ArrayList();
    	try {
			ps=con.prepareStatement("select cla_id from class");//查询班级表
			rs=ps.executeQuery();
			while(rs.next()){
				DutyDTO dto =new DutyDTO();
				dto.setCla_id(rs.getString("cla_id"));//得到班号
	            list.add(dto);//将数组添加到数组中
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return list;
    }
    
   /**
    *得到Duty表中的主键 
    * @param dto
    * @return
    */
    public List selectDutyPk(DutyDTO dto){
    	List list =new ArrayList();
    	String stuid=dto.getStu_id();
    	String dutydate=dto.getDut_date();
    	try {
    		String sql="select * from duty where stu_id=? and dut_date=?";
			ps=con.prepareStatement(sql);
			ps.setString(1, stuid);
			ps.setString(2, dutydate);
			rs=ps.executeQuery();
			while(rs.next()){
				dto=new DutyDTO();
				dto.setStu_id(rs.getString("stu_id"));
				dto.setDut_date(rs.getString("dut_date"));
				list.add(dto);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
    }
    
    
    /**
     * 
     * @param sql
     * @return
     */
    public List selectById(String stuid){
		List list = new ArrayList();
		try {
			String sql="select * from duty_stu where stu_id=?";
			ps=con.prepareStatement(sql);
			ps.setString(1, stuid);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				DutyDTO dto=new DutyDTO();
				dto.setStu_id(rs.getString("stu_id"));
				dto.setStu_name(rs.getString("stu_name"));
				dto.setDut_date(rs.getDate("dut_date")+"");
				dto.setDut_sta(rs.getString("dut_sta"));
				dto.setCla_id(rs.getString("cla_id"));
				list.add(dto);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
    
    /**
     * 联合查询
     * @param stuid
     * @param name
     * @param date
     * @param chuqin
     * @param claid
     * @return
     */
    public List selectView(String stuid,String name,String date,String chuqin,String claid){
    	List list = new ArrayList();
    	try {
    		if(name.equals("")&&chuqin.equals("")&&claid.equals("")){
    			String sql="select * from duty_stu where stu_id like '%"+stuid+"%'  and dut_date like '%"+date+"%'";
    			ps=con.prepareStatement(sql);
    		}
    		else{
    			if(name.equals("")&&chuqin.equals("")&&!claid.equals("")){
    				String sql="select * from duty_stu where stu_id like '%"+stuid+"%'  and dut_date like '%"+date+"%' and cla_id like '%"+claid+"%'";
    	    		ps=con.prepareStatement(sql);
    			}
    			else{
    				if(name.equals("")&&!chuqin.equals("")&&claid.equals("")){
    					String sql="select * from duty_stu where stu_id like '%"+stuid+"%'  and dut_date like '%"+date+"%' and dut_sta like '%"+chuqin+"%'";
    		    		ps=con.prepareStatement(sql);
    				}
    				else{
    					if(!name.equals("")&&chuqin.equals("")&&claid.equals("")){
    						String sql="select * from duty_stu where stu_id like '%"+stuid+"%' and stu_name like '%"+name+"%' and dut_date like '%"+date+"%'";
    			    		ps=con.prepareStatement(sql);
    					}
    					else{
    						if(name.equals("")&&!chuqin.equals("")&&!claid.equals("")){
    							String sql="select * from duty_stu where stu_id like '%"+stuid+"%' and dut_date like '%"+date+"%' and dut_sta like '%"+chuqin+"%' and cla_id like '%"+claid+"%'";
    				    		ps=con.prepareStatement(sql);
    						}
    						else{
    							if(!name.equals("")&&chuqin.equals("")&&!claid.equals("")){
    								String sql="select * from duty_stu where stu_id like '%"+stuid+"%' and stu_name like '%"+name+"%' and dut_date like '%"+date+"%' and cla_id like '%"+claid+"%'";
    					    		ps=con.prepareStatement(sql);
    							}
    							else{
    								if(!name.equals("")&&!chuqin.equals("")&&claid.equals("")){
    									String sql="select * from duty_stu where stu_id like '%"+stuid+"%' and stu_name like '%"+name+"%' and dut_date like '%"+date+"%' and dut_sta like '%"+chuqin+"%'";
    						    		ps=con.prepareStatement(sql);
    								}
    								else{
    									String sql="select * from duty_stu where stu_id like '%"+stuid+"%' and stu_name like '%"+name+"%' and dut_date like '%"+date+"%' and dut_sta like '%"+chuqin+"%' and cla_id like '%"+claid+"%'";
    						    		ps=con.prepareStatement(sql);
    								}
    							}
    						}
    					}
    				}
    			}
    		}	
			rs=ps.executeQuery();
			while(rs.next()){
				DutyDTO dto=new DutyDTO();
				dto.setStu_id(rs.getString("stu_id"));
				dto.setStu_name(rs.getString("stu_name"));
				dto.setDut_date(rs.getString("dut_date"));
				dto.setDut_sta(rs.getString("dut_sta"));
				dto.setCla_id(rs.getString("cla_id"));
				list.add(dto);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return list;
    }
}