crsbean.java

针对数据库操作制作的一个学生管理系统

package Cstudent;
import java.util.*;
import java.sql.*;
import javax.swing.*;

/**
 * 有关课程信息数据库操作的类
 */
public class CrsBean {
	String sql;
	ResultSet rs = null;

	String Cno;
	String Cname;
	String Cpno;
	String Ccredit;
	
	String Sno;

	String colName;//列名
	String colValue;//列值
	int crsId;//新课程的编号
	
	/**
	 * 添加课程信息
	 */
	public void crsAdd(String cno,String name, String pno, String credit){
		
		Database DB = new Database();
		this.Cno = cno;
		this.Cname = name;
		this.Cpno = pno;
		this.Ccredit = credit;
		

		if(Cname == null||Cname.equals("")){
			JOptionPane.showMessageDialog(null, "请输入课程名称", "错误", JOptionPane.ERROR_MESSAGE);
			return;
		}
		else{
			sql = "insert into Course(Cno,Cname,Cpno,Ccredit) values ('"+Cno+"','"+Cname+"','"+Cpno+"','"+Ccredit+"')";
			try{
				DB.OpenConn();
				DB.executeUpdate(sql);
				JOptionPane.showMessageDialog(null,"成功添加一条新的纪录！");

			}
			catch(Exception e){
				JOptionPane.showMessageDialog(null, "保存失败", "错误", JOptionPane.ERROR_MESSAGE); 
			}
			finally {
				DB.closeStmt();
				DB.closeConn();
			}
		}
	}

	/**
	 * 修改课程信息
	 */
	public void crsModify(String sno, String name, String pno, String credit){
		Database DB = new Database();
		this.Cno = sno;
		this.Cname = name;
		this.Cpno = pno;
		this.Ccredit = credit;
		

		if(Cname == null||Cname.equals("")){
			JOptionPane.showMessageDialog(null, "请输入课程名称", "错误", JOptionPane.ERROR_MESSAGE);
			return;
		}
		else{
			sql = "update Course set Cname = '"+Cname+"', Cpno = '"+Cpno+"', Ccredit = '"+Ccredit+"' where Cno = "+Integer.parseInt(Cno)+"";
			try{
				DB.OpenConn();
				DB.executeUpdate(sql);
				JOptionPane.showMessageDialog(null,"成功修改一条新的纪录！");
			}
			catch(Exception e){
				System.out.println(e);
				JOptionPane.showMessageDialog(null, "更新失败", "错误", JOptionPane.ERROR_MESSAGE); 
			}
			finally {
				DB.closeStmt();
				DB.closeConn();
			}
		}
	}

	/**
	 * 删除课程信息
	 */
	public void crsDel(String cno){
		Database DB = new Database();
		this.Cno = cno;
		
		sql = "delete from Course where Cno = "+Integer.parseInt(Cno)+"";
		try{
			DB.OpenConn();
			DB.executeUpdate(sql);
			JOptionPane.showMessageDialog(null,"成功删除一条新的纪录！");
		}
		catch(Exception e){
			System.out.println(e);
			JOptionPane.showMessageDialog(null, "删除失败", "错误", JOptionPane.ERROR_MESSAGE); 
		}
		finally {
			DB.closeStmt();
			DB.closeConn();
		}
	}

	/**
	 * 根据课程号，搜索课程名称等相关信息
	 */
	public String[] crsSearch(String cno){
		Database DB = new Database();
		this.Cno = cno;
		String[] s = new String[3];

		sql = "select * from Course where Cno = "+Integer.parseInt(Cno)+"";
		try{
			DB.OpenConn();
			rs = DB.executeQuery(sql);
			if(rs.next()){
				s[0] = rs.getString("Cname");
				s[1] = rs.getString("Cpno");
				s[2] = rs.getString("Credit");
				
			}
			else
				s = null;
		}
		catch(Exception e){
		}
		finally {
			DB.closeStmt();
			DB.closeConn();
		}
		return s;
	}

	/**
	 * 根据课程名称，搜索课程号
	 */
	public String[] crsNameSear(String name){
		Database DB = new Database();
		String[] s = new String[4];
		this.Cname = name;
		DB.toGBK(Cname);

		sql = "select * from Course where Cname = '"+Cname+"'";
		try{
			DB.OpenConn();
			rs = DB.executeQuery(sql);
			if(rs.next()){
				s[0] = rs.getString(1);
				s[1] = rs.getString(2);
				s[2] = rs.getString(3);
				s[3] = rs.getString(4);
				
			}
			else
				s = null;
		}
		catch(Exception e){
			System.out.println(e);
		}
		finally {
			DB.closeStmt();
			DB.closeConn();
		}
		return s;
	}


	/**
	 * 根据课程名称与选课人，搜索课程相关信息
	 */
	public String[] crsNameSearch(String name,String sno){
		
		Database DB = new Database();

		this.Cname = name;
		this.Sno = sno;

		String[] s = new String[4];
		sql = "select * from Course,SC where Cname = '"+Cname+"' and SC.Sno = "+Integer.parseInt(Sno)+" and Course.Cno = SC.Cno";
		try{
			DB.OpenConn();
			rs = DB.executeQuery(sql);
			if(rs.next()){
				s[0] = rs.getString("Cno");
				s[1] = rs.getString("Cpno");
				s[2] = rs.getString("Credit");
				s[3] = rs.getString("Grade");
			}
			else
				s = null;
		}
		catch(Exception e){
			System.out.println(e);
		}
		finally {
			DB.closeStmt();
			DB.closeConn();
		}
		return s;
	}

	
	/**
	 * 课程信息综合查询
	 */
	public String[][] crsAllSearch(String colname,String colvalue){
		this.colName = colname;
		this.colValue = colvalue;
		
		Database DB = new Database();
		String[][] cn = null;
		int row = 0;
		int i = 0;
		
		if(colValue == null||colValue.equals("")){
			sql = "select * from Course";
		}
		else{
			sql = "select * from Course where "+colName+" = '"+colValue+"'";
		}

		try{
			DB.OpenConn();
			rs = DB.executeQuery(sql);
			if(rs.last()){
				row = rs.getRow();
			}
			if(row == 0){
				cn = null;
			}
			else{
				cn = new String[row][4];
				rs.first();
				rs.previous();
				while(rs.next()){
					cn[i][0] = rs.getString(1);
					cn[i][1] = rs.getString(2);
					cn[i][2] = rs.getString(3);
					cn[i][3] = rs.getString(4);
					
					i++;
				}
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		finally {
			DB.closeStmt();
			DB.closeConn();
		}
		return cn;
	}
	
	/**
	 * 获得新的课程号
	 */
	public int getCrsId(){

		Database DB = new Database();
		
		sql = "select max(Cno) from Course";
		try{
			DB.OpenConn();
			rs = DB.executeQuery(sql);
			if(rs.next()){
				crsId = rs.getInt(1) + 1;
			}
			else
				crsId = 1;
		}
		catch(Exception e){
			System.out.println(e);
		}
		finally {
			DB.closeStmt();
			DB.closeConn();
		}
		return crsId;
	}

	/**
	 * 获得Course表中的所有课程号Cno
	 */
	public String[] getAllId(){
		
		String[] s = null;
		int row = 0;
		int i = 0;
		Database DB = new Database();

		sql = "select Cno from Course";
		try{
			DB.OpenConn();
			rs = DB.executeQuery(sql);
			if(rs.last()){
				row = rs.getRow();
			}
			if(row == 0){
				s = null;
			}
			else{
				s = new String[row];
				rs.first();
				rs.previous();
				while(rs.next()){
					s[i] = rs.getString(1);
					i++;
				}
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		finally {
			DB.closeStmt();
			DB.closeConn();
		}
		return s;
	}

	/**
	 * 获得course表中的所有课程名称
	 */
	public String[] getAllName(){
		String[] s = null;
		int row = 0;
		int i = 0;
		Database DB = new Database();

		sql = "select Cname from Course";
		try{
			DB.OpenConn();
			rs = DB.executeQuery(sql);
			if(rs.last()){
				row = rs.getRow();
			}

			if(row == 0){
				s = null;
			}
			else{
				s = new String[row];
				rs.first();
				rs.previous();
				while(rs.next()){
					s[i] = rs.getString(1);
					i++;
				}
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		finally {
			DB.closeStmt();
			DB.closeConn();
		}
		return s;
	}
}