tesdao.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 TesDAO {
	ConnectionDateBase cdb=new ConnectionDateBase();
	Connection con=cdb.getDateBaseConnection();
	PreparedStatement stmt = null;
	ResultSet rs = null;
//条件查询
	public List tesSelect(String tesid,String subid,String time1,String time2,String address,String tea) {
		List list = new ArrayList();// 得到一个数组

		String sql=null;
		try{
			if(address.equals("")&&tea.equals(""))
				sql="select * from test where  tes_id like '%"+tesid+"%' and sub_id like '%"+subid+"%'";
			if(!address.equals("")&&tea.equals(""))
				sql="select * from test where  tes_id like '%"+tesid+"%' and sub_id like '%"+subid+"%' and tes_add like '%"+address+"%'";
			if(address.equals("")&&!tea.equals(""))
				sql="select * from test where  tes_id like '%"+tesid+"%' and sub_id like '%"+subid+"%' and tes_tea like '%"+tea+"%'";
     		if(!address.equals("")&&!tea.equals(""))
				sql="select * from test where  tes_id like '%"+tesid+"%' and sub_id like '%"+subid+"%' and tes_add like '%"+address+"%' and tes_tea like '%"+tea+"%'";
			
     		if(time1.equals("")||time2.equals(""))
				sql=sql+" order by tes_id";
			else{
			    String str1=time1.substring(0,10)+"-"+time1.substring(11,13)+"-"+time1.substring(14,16);
			    String str2=time2.substring(0,10)+"-"+time2.substring(11,13)+"-"+time2.substring(14,16);
			    sql=sql+"and tes_date between to_date('"+str1+"','yy-MM-dd HH-mi') and to_date('"+str2+"','yy-MM-dd-HH-mi') order by tes_id";
			}
			stmt = con.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				TesDTO dto = new TesDTO();
				dto.setTes_id(rs.getString("tes_id"));// 获得考试编号
				dto.setSub_id(rs.getString("sub_id"));// 
				dto.setTes_add(rs.getString("tes_add"));//
				dto.setTes_tea(rs.getString("tes_tea"));//
				if(rs.getString("tes_date")==null){
					list.add(dto);
					continue;//去掉空值
				}
				else  {
					  dto.setTes_date(rs.getString("tes_date").substring(0,16));//
				      list.add(dto);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;		
	}

	//添加
	public void tesInsert(TesDTO dto){
		String sql="insert into test (tes_id,sub_id,tes_date,tes_add,tes_tea) values(?,?,to_date(?,'yy-MM-dd-HH-mi'),?,?)";
		try {
			stmt = con.prepareStatement(sql);
			stmt.setString(1,dto.getTes_id());//prp.set..(1...n,参数值)
			stmt.setString(2,dto.getSub_id());
			stmt.setString(3,dto.getTes_date());
			stmt.setString(4, dto.getTes_add());
			stmt.setString(5, dto.getTes_tea());
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
//	修改
	public void tesUpdate(String tesid,String subid,String tesdate,String tesadd,String testea){
		String sql="update test set sub_id=?,tes_date=to_date(?,'yy-MM-dd-HH-mi'),tes_add=?,tes_tea=? where tes_id=?";
		try {
			stmt = con.prepareStatement(sql);
			stmt.setString(1,subid);//prp.set..(1...n,参数值)
			stmt.setString(2,tesdate);
			stmt.setString(3,tesadd);
			stmt.setString(4, testea);
			stmt.setString(5, tesid);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}
//	删除
	public void tesDelete(String tesid){
		String sql="delete from test where tes_id=?";
		try {
			stmt = con.prepareStatement(sql);
			stmt.setString(1,tesid);//prp.set..(1...n,参数值)
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}
	
//	获取sub_id值
    public List selectsub_id(){
    	List list = new ArrayList();
    	String sql="select sub_id from subject order by sub_id";
    	try {
			stmt = con.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				SubDTO dto =new SubDTO();
				dto.setSub_id(rs.getString("sub_id"));
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
    	return list;
    	
    }
//  获取tes_date的值
    public List selecttes_date(int flag){
    	List list = new ArrayList();
    	String sql=null;
    	if(flag==0)
    	   sql="select tes_date from test order by tes_date";
    	if(flag==1)
    	   sql="select tes_date from test order by tes_date desc";
    	try {
			stmt = con.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				TesDTO dto =new TesDTO();
				if(rs.getString("tes_date")==null)continue;//去掉空值
				   dto.setTes_date(rs.getString("tes_date"));
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
    	return list;
    }
//  对添加内容判断是否重复
    public List judge(String tesid){
    	List list=new ArrayList();
    	String sql="select * from test where tes_id=?";
		try {
			stmt = con.prepareStatement(sql);
			stmt.setString(1,tesid);
			rs = stmt.executeQuery();
			while (rs.next()) {
				TesDTO dto = new TesDTO();
				dto.setTes_id(rs.getString("tes_id"));// 获得考试编号
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}	
       return list;
    }
    //刷新
    public List init() {
		List list = new ArrayList();// 得到一个数组
		String sql = "select * from test order by tes_id";//视图
		try {
			stmt = con.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				TesDTO dto = new TesDTO();
				dto.setTes_id(rs.getString("tes_id"));// 考试编号
				dto.setSub_id(rs.getString("sub_id"));// 科目代号
				dto.setTes_date(rs.getString("tes_date"));//考试时间
				dto.setTes_add(rs.getString("tes_add"));//考试地点
				dto.setTes_tea(rs.getString("tes_tea"));//监考人员
				list.add(dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

}