scodao.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 ScoDAO {
	ConnectionDateBase cdb=new ConnectionDateBase();
	Connection con=cdb.getDateBaseConnection();
	PreparedStatement stmt = null;
	ResultSet rs = null;
//条件查询
public List scoSelect(String tesid,String stuid,String stuname,String subname,String subsco,int sco1,int sco2){
		List list = new ArrayList();// 得到一个数组
		String sql=null;
		try{
				if(stuname.equals("")&&subname.equals("")&&subsco.equals(""))
					sql="select * from sco where tes_id like '%"+tesid+"%' and stu_id like '%"+stuid+"%' and sco_sub between "+sco1+" and "+sco2+"";
				
				if(stuname.equals("")&&subname.equals("")&&!subsco.equals(""))
					sql="select * from sco where tes_id like '%"+tesid+"%' and stu_id like '%"+stuid+"%' and sub_sco like '%"+subsco+"%' and sco_sub between "+sco1+" and "+sco2+"";
				
				if(stuname.equals("")&&!subname.equals("")&&subsco.equals(""))
					sql="select * from sco where tes_id like '%"+tesid+"%' and stu_id like '%"+stuid+"%' and sub_name like '%"+subname+"%' and sco_sub between "+sco1+" and "+sco2+"";
				
				if(!stuname.equals("")&&subname.equals("")&&subsco.equals(""))
					sql="select * from sco where tes_id like '%"+tesid+"%' and stu_id like '%"+stuid+"%' and stu_name like '%"+stuname+"%' and sco_sub between "+sco1+" and "+sco2+"";
			
				if(!stuname.equals("")&&!subname.equals("")&&subsco.equals(""))
					sql="select * from sco where tes_id like '%"+tesid+"%' and stu_id like '%"+stuid+"%' and stu_name like '%"+stuname+"%' and sub_name like '%"+subname+"%' and sco_sub between "+sco1+" and "+sco2+"";
				
				if(!stuname.equals("")&&subname.equals("")&&!subsco.equals(""))
					sql="select * from sco where tes_id like '%"+tesid+"%' and stu_id like '%"+stuid+"%' and stu_name like '%"+stuname+"%' and sub_sco like '%"+subsco+"%' and sco_sub between "+sco1+" and "+sco2+"";
				
				if(stuname.equals("")&&!subname.equals("")&&!subsco.equals(""))
					sql="select * from sco where tes_id like '%"+tesid+"%' and stu_id like '%"+stuid+"%' and sub_name like '%"+subname+"%' and sub_sco like '%"+subsco+"%' and sco_sub between "+sco1+" and "+sco2+"";
				
				if(!stuname.equals("")&&!subname.equals("")&&!subsco.equals(""))
					sql="select * from sco where tes_id like '%"+tesid+"%' and stu_id like '%"+stuid+"%' and stu_name like '%"+stuname+"%' and sub_name like '%"+subname+"%' and sub_sco like '%"+subsco+"%' and sco_sub between "+sco1+" and "+sco2+"";
			
		    sql=sql+" order by tes_id";
			stmt = con.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				ScoDTO dto = new ScoDTO();
				dto.setTes_id(rs.getString("tes_id"));// 获得考试编号
				dto.setStu_id(rs.getString("stu_id"));// 获得学生学号
				dto.setStu_name(rs.getString("stu_name"));//获得学生姓名
				dto.setSub_name(rs.getString("sub_name"));//获得科目名称
				dto.setSub_sco(rs.getString("sub_sco"));//获得科目学分
				dto.setSco_sub(rs.getInt("sco_sub"));//获得科目成绩
				list.add(dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
//添加
	public void scoInsert(ScoDTO dto){
		try {
			stmt = con.prepareStatement("insert into score (tes_id,stu_id,sco_sub) values(?,?,?)");
			stmt.setString(1,dto.getTes_id());//prp.set..(1...n,参数值)
			stmt.setString(2,dto.getStu_id());
			stmt.setInt(3,dto.getSco_sub());
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
//修改
	public void scoUpdate(String tesid,String stuid,String score){
		String sql="update score set sco_sub=? where tes_id=? and stu_id=?";
		try {
			stmt = con.prepareStatement(sql);
			stmt.setString(1,score);//prp.set..(1...n,参数值)
			stmt.setString(2,tesid);
			stmt.setString(3,stuid);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}
	
//删除对象
	public void scoDelete(String tes_id,String stu_id){
		String sql="delete from score where tes_id=? and stu_id=?";
		try {
			stmt = con.prepareStatement(sql);
			stmt.setString(1,tes_id);//prp.set..(1...n,参数值)
			stmt.setString(2,stu_id);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}
	
//从student表中获取学号stu_id值
    public List selectstu_id(){
    	List list = new ArrayList();
    	try {
			stmt = con.prepareStatement("select stu_id from student order by stu_id");
			rs = stmt.executeQuery();
			while(rs.next()){
				StuDTO dto =new StuDTO();
				dto.setStu_id(rs.getString("stu_id"));
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
    	return list;
    }
//从test表中获取tes_id值
    public List selecttes_id(){
    	List teslist = new ArrayList();
    	try {
			stmt = con.prepareStatement("select tes_id from test order by tes_id");
			rs = stmt.executeQuery();
			while(rs.next()){
				TesDTO dto =new TesDTO();
				dto.setTes_id(rs.getString("tes_id"));
				teslist.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
    	return teslist;
    	
    }
//对添加内容判断是否重复
    public List judge(String tesid,String stuid){
    	List list=new ArrayList();
    	String sql="select * from score where tes_id=? and stu_id=?";
		try {
			stmt = con.prepareStatement(sql);
			stmt.setString(1,tesid);//prp.set..(1...n,参数值)
			stmt.setString(2,stuid);
			rs = stmt.executeQuery();
			while (rs.next()) {
				ScoDTO dto = new ScoDTO();
				dto.setTes_id(rs.getString("tes_id"));// 获得考试编号
				dto.setStu_id(rs.getString("stu_id"));// 获得学生学号
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}	
       return list;
    }
    // 
    public boolean judgeupdate(String tesid) {
		List list = new ArrayList();
		int i=0;
		try {
			stmt=con.prepareStatement("select * from test where tes_id=?");
			stmt.setString(1,tesid);
			rs=stmt.executeQuery();
			while(rs.next()){
                if(rs.getString("sub_id")==null)i=1;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(i==1)return false;
		else return true;
	}
 //刷新
    public List init() {
		List list = new ArrayList();// 得到一个数组
		String sql = "select * from sco order by tes_id";//视图
		try {
			stmt = con.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				ScoDTO dto = new ScoDTO();
				dto.setTes_id(rs.getString("tes_id"));// 获得考试编号
				dto.setStu_id(rs.getString("stu_id"));// 获得学生学号
				dto.setStu_name(rs.getString("stu_name"));//获得学生姓名
				dto.setSub_name(rs.getString("sub_name"));//获得科目名称
				dto.setSub_sco(rs.getString("sub_sco"));//获得科目学分
				dto.setSco_sub(rs.getInt("sco_sub"));//获得科目成绩
				list.add(dto);
			}
			stmt=con.prepareStatement("select * from sco2");
			rs=stmt.executeQuery();
			while(rs.next()){
				ScoDTO dto = new ScoDTO();
				dto.setTes_id(rs.getString("tes_id"));// 获得考试编号
				dto.setStu_id(rs.getString("stu_id"));// 获得学生学号
				dto.setStu_name(rs.getString("stu_name"));//获得学生姓名
                if(rs.getString("sub_id")==null){
                	dto.setSco_sub(0);//获得科目成绩
    				dto.setSub_name("");//获得科目名称
    				dto.setSub_sco("");//获得科目学分
                	list.add(dto);;
                }
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
}