spesubdao.java

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

package com.hb.studentmanager.date;

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

import com.hb.studentmanager.connection.ConnectionDateBase;

public class SpeSubDAO {
	ConnectionDateBase cdb = new ConnectionDateBase();

	Connection con = cdb.getDateBaseConnection();

	PreparedStatement statement = null;

	ResultSet rs = null;

	public void speSubInsert(SpeSubDTO dto) {
		String speId = dto.getSpe_id();
		String subId=dto.getSub_id();
		String sql = "insert into spe_sub (spe_id,sub_id) values (?,?)";
		try {
			statement = con.prepareStatement(sql);
			statement.setString(1, speId);
			statement.setString(2, subId);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void speSubUpdate(SpeSubDTO dto,String firstSubId) {
		String speId = dto.getSpe_id();
		String subId=dto.getSub_id();
		String sql = "update spe_sub set sub_id=? where spe_id=? and sub_id=?";
		try {
			statement = con.prepareStatement(sql);
			statement.setString(1, subId);
			statement.setString(2, speId);
			statement.setString(3, firstSubId);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void speSubDelete(SpeSubDTO dto) {
		String speId= dto.getSpe_id();
		String subId=dto.getSub_id();
		String sql = "delete spe_sub where spe_id=? and sub_id=?";
		try {
			statement = con.prepareStatement(sql);
			statement.setString(1, speId);
			statement.setString(2, subId);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List speSubBlurrySelect(SpeSubDTO dto) {
		List list = new ArrayList();// 得到一个数组
		String speId = dto.getSpe_id();
		String speName=dto.getSpe_name();
		String subId=dto.getSub_id();
		String subName=dto.getSub_name();
		String subSco=dto.getSub_sco();
		String sql ;
		if(subSco.equals(""))
			sql= "select spe_sub.spe_id,specialty.spe_name,spe_sub.sub_id,subject.sub_name,subject.sub_sco from spe_sub inner join specialty on spe_sub.spe_id=specialty.spe_id inner join subject on spe_sub.sub_id=subject.sub_id where spe_sub.spe_id like '%"+speId+"%' and specialty.spe_name like '%"+speName+"%' and spe_sub.sub_id like '%"+subId+"%'and subject.sub_name like '%"+subName+"%'";
		else
			sql= "select spe_sub.spe_id,specialty.spe_name,spe_sub.sub_id,subject.sub_name,subject.sub_sco from spe_sub inner join specialty on spe_sub.spe_id=specialty.spe_id inner join subject on spe_sub.sub_id=subject.sub_id where spe_sub.spe_id like '%"+speId+"%' and specialty.spe_name like '%"+speName+"%' and spe_sub.sub_id like '%"+subId+"%'and subject.sub_name like '%"+subName+"%' and subject.sub_sco like '%"+subSco+"%'";
		try {
			statement = con.prepareStatement(sql);
			rs = statement.executeQuery();
			while (rs.next()) {
				dto = new SpeSubDTO();
				dto.setSpe_id(rs.getString("spe_id"));// 获得专业代号
				dto.setSpe_name(rs.getString("spe_name"));// 获得专业名称
				dto.setSub_id(rs.getString("sub_id"));// 获得科目代号
				dto.setSub_name(rs.getString("sub_name"));// 获得科目名称
				dto.setSub_sco(rs.getString("sub_sco"));// 获得科目学分
				list.add(dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
    
	public List speSubExactSelect(SpeSubDTO dto) {
		List list = new ArrayList();// 得到一个数组
		String speId = dto.getSpe_id();
		String subId=dto.getSub_id();
		String subSco=dto.getSub_sco();
		String sql ;
		int flag=0;
		if(speId.equals("")==false&&subId.equals("")&&subSco.equals(""))
			sql= "select spe_sub.spe_id,specialty.spe_name,spe_sub.sub_id,subject.sub_name,subject.sub_sco from spe_sub inner join specialty on spe_sub.spe_id=specialty.spe_id inner join subject on spe_sub.sub_id=subject.sub_id where spe_sub.spe_id=?";
		else if(speId.equals("")&&subId.equals("")==false&&subSco.equals("")){
			sql= "select spe_sub.spe_id,specialty.spe_name,spe_sub.sub_id,subject.sub_name,subject.sub_sco from spe_sub inner join specialty on spe_sub.spe_id=specialty.spe_id inner join subject on spe_sub.sub_id=subject.sub_id where spe_sub.sub_id=?";
		    flag=1;
		}
		else if(speId.equals("")&&subId.equals("")&&subSco.equals("")==false){
			sql= "select spe_sub.spe_id,specialty.spe_name,spe_sub.sub_id,subject.sub_name,subject.sub_sco from spe_sub inner join specialty on spe_sub.spe_id=specialty.spe_id inner join subject on spe_sub.sub_id=subject.sub_id where subject.sub_sco=?";
            flag=2;		
		}
		else if(speId.equals("")==false&&subId.equals("")==false&&subSco.equals("")){
			sql= "select spe_sub.spe_id,specialty.spe_name,spe_sub.sub_id,subject.sub_name,subject.sub_sco from spe_sub inner join specialty on spe_sub.spe_id=specialty.spe_id inner join subject on spe_sub.sub_id=subject.sub_id where spe_sub.spe_id=? and spe_sub.sub_id=?";
            flag=3;		
		}
		else if(speId.equals("")==false&&subId.equals("")&&subSco.equals("")==false){
			sql= "select spe_sub.spe_id,specialty.spe_name,spe_sub.sub_id,subject.sub_name,subject.sub_sco from spe_sub inner join specialty on spe_sub.spe_id=specialty.spe_id inner join subject on spe_sub.sub_id=subject.sub_id where spe_sub.spe_id=? and subject.sub_sco=?";
		    flag=4;
		}
		else if(speId.equals("")&&subId.equals("")==false&&subSco.equals("")==false){
			sql= "select spe_sub.spe_id,specialty.spe_name,spe_sub.sub_id,subject.sub_name,subject.sub_sco from spe_sub inner join specialty on spe_sub.spe_id=specialty.spe_id inner join subject on spe_sub.sub_id=subject.sub_id where spe_sub.sub_id=? and subject.sub_sco=?";
		    flag=5;
		}
		else {
			sql= "select spe_sub.spe_id,specialty.spe_name,spe_sub.sub_id,subject.sub_name,subject.sub_sco from spe_sub inner join specialty on spe_sub.spe_id=specialty.spe_id inner join subject on spe_sub.sub_id=subject.sub_id where spe_sub.spe_id=? and spe_sub.sub_id=? and subject.sub_sco=?";
		    flag=6;
		}
		try {
			statement = con.prepareStatement(sql);
			if(flag==0){
				statement.setString(1, speId);
			}else if(flag==1){
				statement.setString(1, subId);
			}else if(flag==2){
				statement.setString(1,subSco);
			}
			else if(flag==3){
				statement.setString(1,speId);
				statement.setString(2,subId);
			}
			else if(flag==4){
				statement.setString(1, speId);
				statement.setString(2,subSco);
			}
			else if(flag==5){
				statement.setString(1, subId);
				statement.setString(2,subSco);
			}
			else if(flag==6){
				statement.setString(1, speId);
				statement.setString(2, subId);
				statement.setString(3,subSco);
			}
			rs = statement.executeQuery();
			while (rs.next()) {
				dto = new SpeSubDTO();
				dto.setSpe_id(rs.getString("spe_id"));// 获得专业代号
				dto.setSpe_name(rs.getString("spe_name"));// 获得专业名称
				dto.setSub_id(rs.getString("sub_id"));// 获得科目代号
				dto.setSub_name(rs.getString("sub_name"));// 获得科目名称
				dto.setSub_sco(rs.getString("sub_sco"));// 获得科目学分
				list.add(dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public List init() {
		List list = new ArrayList();// 得到一个数组
		String sql = "select spe_sub.spe_id,specialty.spe_name,spe_sub.sub_id,subject.sub_name,subject.sub_sco from spe_sub inner join specialty on spe_sub.spe_id=specialty.spe_id inner join subject on spe_sub.sub_id=subject.sub_id";
		try {
			statement = con.prepareStatement(sql);
			rs = statement.executeQuery();
			while (rs.next()) {
				SpeSubDTO dto = new SpeSubDTO();
				dto.setSpe_id(rs.getString("spe_id"));// 获得专业代号
				dto.setSpe_name(rs.getString("spe_name"));// 获得专业名称
				dto.setSub_id(rs.getString("sub_id"));// 获得科目代号
				dto.setSub_name(rs.getString("sub_name"));// 获得科目名称
				dto.setSub_sco(rs.getString("sub_sco"));//获得科目学分
				list.add(dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
}