subdao.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 SubDAO {
	ConnectionDateBase cdb = new ConnectionDateBase();

	Connection con = cdb.getDateBaseConnection();

	PreparedStatement statement = null;

	ResultSet rs = null;

	public void subInsert(SubDTO dto) {
		String subId = dto.getSub_id();
		String subName = dto.getSub_name();
		String subSco=dto.getSub_score();
		String sql = "insert into subject (sub_id,sub_name,sub_sco) values (?,?,?)";
		try {
			statement = con.prepareStatement(sql);
			statement.setString(1, subId);
			statement.setString(2, subName);
			statement.setString(3, subSco);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void subUpdate(SubDTO dto) {
		String subId = dto.getSub_id();
		String subName = dto.getSub_name();
		String subSco=dto.getSub_score();
		String sql = "update subject set sub_name=?,sub_sco=? where sub_id=?";
		try {
			statement = con.prepareStatement(sql);
			statement.setString(3, subId);
			statement.setString(2, subSco);
			statement.setString(1, subName);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void subDelete(String id) {
		String subId = id;
		String sql = "delete subject where sub_id=?";
		try {
			statement = con.prepareStatement(sql);
			statement.setString(1, subId);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List subBlurrySelect(SubDTO dto) {
		List list = new ArrayList();// 得到一个数组
		String subId = dto.getSub_id();
		String subName = dto.getSub_name();
		String subSco=dto.getSub_score();
		String sql ;
		if(subId.equals("")==false&&subName.equals("")&&subSco.equals("")){
			sql= "select *from subject where sub_id like '%"+subId+"%'";
		}
		else if(subId.equals("")&&subName.equals("")==false&&subSco.equals("")){
			sql= "select *from subject where sub_name like '%"+subName+"%'";
		}
		else if(subId.equals("")&&subName.equals("")&&subSco.equals("")==false){
			sql= "select *from subject where sub_sco like '%"+subSco+"%'";
		}
		else if(subId.equals("")==false&&subName.equals("")==false&&subSco.equals("")){
			sql= "select *from subject where sub_id like '%"+subId+"%' and sub_name like '%"+subName+"%'";
		}
		else if(subId.equals("")==false&&subName.equals("")&&subSco.equals("")==false){
			sql= "select *from subject where sub_id like '%"+subId+"%' and sub_sco like '%"+subSco+"%'";
		}
		else if(subId.equals("")&&subName.equals("")==false&&subSco.equals("")==false){
			sql= "select *from subject where sub_name like '%"+subName+"%' and sub_sco like '%"+subSco+"%'";
		}
		else{
			sql= "select *from subject where sub_id like '%"+subId+"%' and sub_name like '%"+subName+"%' and sub_sco like '%"+subSco+"%'";
		}
		try {
			statement = con.prepareStatement(sql);
			rs = statement.executeQuery();
			while (rs.next()) {
				dto = new SubDTO();
				dto.setSub_id(rs.getString("sub_id"));// 获得科目代号
				dto.setSub_name(rs.getString("sub_name"));// 获得科目名称
				dto.setSub_score(rs.getString("sub_sco"));//获得科目学分
				list.add(dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public List subExactSelect(SubDTO dto) {
		List list = new ArrayList();// 得到一个数组
		String subId = dto.getSub_id();
		String subName = dto.getSub_name();
		String subSco=dto.getSub_score();
		int flag=0;
		String sql ;
		if(subId.equals("")==false&&subName.equals("")&&subSco.equals("")){
			sql= "select *from subject where sub_id=?";
			flag=0;
		}
		else if(subId.equals("")&&subName.equals("")==false&&subSco.equals("")){
			sql= "select *from subject where sub_name=?";
			flag=1;
		}
		else if(subId.equals("")&&subName.equals("")&&subSco.equals("")==false){
			sql= "select *from subject where sub_sco=?";
			flag=2;
		}
		else if(subId.equals("")==false&&subName.equals("")==false&&subSco.equals("")){
			sql= "select *from subject where sub_id=? and sub_name=?";
			flag=3;
		}
		else if(subId.equals("")==false&&subName.equals("")&&subSco.equals("")==false){
			sql= "select *from subject where sub_id=? and sub_sco=?";
			flag=4;
		}
		else if(subId.equals("")&&subName.equals("")==false&&subSco.equals("")==false){
			sql= "select *from subject where sub_name=? and sub_sco=?";
			flag=5;
		}
		else{
			sql= "select *from subject where sub_id=? and sub_name=? and sub_sco=?";
			flag=6;
		}
		try {
			statement = con.prepareStatement(sql);
			if(flag==0){
				statement.setString(1, subId );
			}else if(flag==1){
				statement.setString(1, subName );
			}else if(flag==2){
				statement.setString(1, subSco );
			}else if(flag==3){
				statement.setString(1, subId );
				statement.setString(2, subName );
			}else if(flag==4){
				statement.setString(1, subId );
				statement.setString(2, subSco );
			}else if(flag==5){
				statement.setString(1, subName );
				statement.setString(2, subSco );
			}else if(flag==6){
				statement.setString(1, subId );
				statement.setString(2, subName );
				statement.setString(3, subSco );
			}
			rs = statement.executeQuery();
			while (rs.next()) {
				dto = new SubDTO();
				dto.setSub_id(rs.getString("sub_id"));// 获得科目代号
				dto.setSub_name(rs.getString("sub_name"));// 获得科目名称
				dto.setSub_score(rs.getString("sub_sco"));//获得科目学分
				list.add(dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public List selectAll() {
		List list = new ArrayList();// 得到一个数组
		String sql= "select *from subject";
		try {
			statement = con.prepareStatement(sql);
			rs = statement.executeQuery();
			while (rs.next()) {
				SubDTO dto = new SubDTO();
				dto.setSub_id(rs.getString("sub_id"));// 获得科目代号
				dto.setSub_name(rs.getString("sub_name"));// 获得科目名称
				dto.setSub_score(rs.getString("sub_sco"));//获得科目学分
				list.add(dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public List init() {
		List list = new ArrayList();// 得到一个数组
		String sql = "select *from subject order by sub_id asc";
		try {
			statement = con.prepareStatement(sql);
			rs = statement.executeQuery();
			while (rs.next()) {
				SubDTO dto = new SubDTO();
				dto.setSub_id(rs.getString("sub_id"));// 获得科目代号
				dto.setSub_name(rs.getString("sub_name"));// 获得科目名称
				dto.setSub_score(rs.getString("sub_sco"));//获得科目学分
				list.add(dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
}