tableframe.java

java程序 学生信息管理系统 数据库采用acce

package com.studentFrame;

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.table.*;
import java.sql.*;

/*管理员或教师登陆时信息的显示界面*/

public class TableFrame extends JFrame implements ActionListener           
{
	Object columnname [];   //表格的列名
	Object columndata[][];   //表格数据
	/*菜单*/
	MenuBar m_menubar;
	Menu menuEdit,menuHelp;
	MenuItem mi_edit_add,mi_edit_delete,mi_edit_modify;
	MenuItem mi_help_about;
	
	DefaultTableModel dtm;
	JScrollPane jscrollpane;
	private JTable table;
	Panel p;
	TextField grd,cls;   //班级与年级
	JButton inquire;
	Label hint1,hint2; //提示班级,年级的输入格式
	int index;
	String temp;   //根据构造函数的参数判断是那张表
    String url,username,password,sql; 
    static Connection con =null;
    static Statement stmt = null;
    static ResultSet rs = null;
    int count = 0;
    int colcount = 0;
	TableFrame(boolean editable,String tablekind)
	{
	   Container c = getContentPane();
	   initMenu();   //初始化菜单
	   /*如果是教师登录不可以修改任何信息*/
	   if(!editable)          
	   {
	     mi_edit_add.setEnabled(false);
		 mi_edit_delete.setEnabled(false);
		 mi_edit_modify.setEnabled(false); 
	   }
	   temp = tablekind;	  
	   String student[] = {"学号","姓名","性别","籍贯","地址","系别","年级","生日","入学时间",};
 	   String course[] = {"课程","性质","学时","学分","学期","班级","教师","地点","星期"};
 	   /*根据不同参数初始化列名*/
 	   if(tablekind.equals("学生基本信息表"))
 	   {
 	   	 columnname  = new Object[9];
 	   	 for(int i = 0;i<9;i++)
 	   	   columnname[i] = student[i];
 	   }
	   else if(tablekind.equals("课程表"))
	   {
	     columnname  = new Object[9];
 	   	 for(int i = 0;i<9;i++)
 	   	   columnname[i] = course[i];
	   }
	   /*初始化表格数据*/	           
	   columndata = new Object[20][columnname.length];
	   for(int i = 0;i<20;i++)
	     for(int j = 0;j<columnname.length;j++)
		     columndata[i][j] = "";	
		 if(editable)
		 {
			dtm = new DefaultTableModel(columndata,columnname)
			{
						public Class getColumnClass(int c)
						{
							return getValueAt(0,c).getClass();
						}
						public boolean isCellEditable(int row,int col)
						{
						      return true;		//行可编辑			      		
						}
	
			};
		}
		else
		{
			dtm = new DefaultTableModel(columndata,columnname)
			{
						public Class getColumnClass(int c)
						{
							return getValueAt(0,c).getClass();
						}
						public boolean isCellEditable(int row,int col)
						{
						      return false;		//行不可编辑			      		
						}
	
			};
		}
		table = new JTable(dtm);	
	    setTitle(tablekind);
		jscrollpane = new JScrollPane(table);
		p = new Panel();
		grd = new TextField("",6);
		cls = new TextField("",6);
		
		inquire = new JButton("查询");	
		if(tablekind.equals("学生基本信息表"))
		 hint1 = new Label("入学时间(例如:2004)");
		else 
		 hint1 = new Label("学期(例如2005-1)");
		hint2 = new Label("班级(例如:043)");
		
		p.add(hint1);
		p.add(grd);
		p.add(hint2);
		p.add(cls);
		p.add(inquire); 
		c.add(p,BorderLayout.NORTH);	
		this.setMenuBar(m_menubar);
		c.add(jscrollpane,BorderLayout.CENTER);
	    inquire.addActionListener(this);
		jscrollpane.setSize(650,400);
        /*连接数据库*/
        url="jdbc:odbc:javadbc";
	    username="";
	    password="";
	    try{
	         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	         con=DriverManager.getConnection(url,username,password);
	         stmt=con.createStatement();
	        }
	    catch(SQLException ex)
	        {
	   	     System.out.println(ex.getMessage());
	        }
	    catch(Exception e){} 
	    addWindowListener(new closeWin());
		setSize(700,500);
		setVisible(true);
	//	setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);WindowConstants.DO_NOTHING_ON_CLOSE
	}
	/*初始化菜单*/
	public void initMenu()   //
	{
	    m_menubar = new MenuBar();	
		menuEdit = new Menu("编辑");
		mi_edit_add = new MenuItem("添加",new MenuShortcut('a'));
		mi_edit_delete = new MenuItem("删除",new MenuShortcut('d'));
		mi_edit_modify = new MenuItem("修改",new MenuShortcut('m'));
		mi_edit_add.addActionListener(this);
		mi_edit_delete.addActionListener(this);
		mi_edit_modify.addActionListener(this);
		menuEdit.add(mi_edit_add);
		menuEdit.add(mi_edit_delete);
		menuEdit.add(mi_edit_modify);
		m_menubar.add(menuEdit);
		
		menuHelp = new Menu("帮助");
		mi_help_about = new MenuItem("关于...",new MenuShortcut('h'));
		mi_help_about.addActionListener(this);
		menuHelp.add(mi_help_about);
		m_menubar.add(menuHelp);
	}
	public void addData()   //添加数据函数
	{  
	   String sql1 = "";
	   int rows = count;        //rows用来跟踪插入的记录数
	   while(!(((String)dtm.getValueAt(rows,0)).equals("")))
	   {
	   	/*根据不同参数执行不同的sql语句*/
		    if(temp.equals("学生基本信息表"))
		     sql ="insert into student values('"+dtm.getValueAt(rows,0)+"','"+
		            dtm.getValueAt(rows,1)+"','"+dtm.getValueAt(rows,2)+"','"+
		            dtm.getValueAt(rows,3)+"','"+dtm.getValueAt(rows,4)+"','"+
		            dtm.getValueAt(rows,7)+"','"+dtm.getValueAt(rows,8)+"','"+
		            dtm.getValueAt(rows,6)+"','"+dtm.getValueAt(rows,5)+"','"+
		            dtm.getValueAt(rows,7)+"')";
		    else if(temp.equals("课程表"))
		    {
		     sql = "insert into course values('"+dtm.getValueAt(rows,0)+"','"+
		            dtm.getValueAt(rows,4)+"','"+dtm.getValueAt(rows,1)+"','"+
		            dtm.getValueAt(rows,2)+"','"+dtm.getValueAt(rows,3)+"')";
		     sql1 = "insert into cschedule values('"+dtm.getValueAt(rows,0)+"','"+
		            dtm.getValueAt(rows,5)+"','"+dtm.getValueAt(rows,6)+"','"+
		            dtm.getValueAt(rows,7)+"','"+dtm.getValueAt(rows,8)+"')";
		    }		
			if(index!=-1)
			{
			  try{
			  	  if(temp.equals("课程表"))
			  	    if(stmt.executeUpdate(sql1)== 0)
			  	      JOptionPane.showMessageDialog(null,"插入失败");
			      int i = stmt.executeUpdate(sql);
			       if(i == 0)
			       JOptionPane.showMessageDialog(null,"插入失败");
			      }
			  catch(SQLException ex)
			  {
			  	 System.out.println(ex.getMessage());
			  }     
		    }
		    rows++;
		}
		count = rows+1;    //返回记录总数
	}
	public void deleteData()    //删除数据函数
	{   
	    String sql1="";
	    index = table.getSelectedRow();
	    if(JOptionPane.showConfirmDialog(null, "确定要删除", "删除", JOptionPane.ERROR_MESSAGE)==0)
	    {
	    if(temp.equals("学生基本信息表"))
	     sql ="delete from student where id ='"+dtm.getValueAt(index,0)+"'";
	    else if(temp.equals("课程表"))
	    { 
	      sql ="delete from course where cname ='"+dtm.getValueAt(index,0)+"'";
	      sql1 = "delete from cschedule where cname ='"+dtm.getValueAt(index,0)
			      +"' and grade='"+dtm.getValueAt(index,5)+"' and teacher ='"
			      +dtm.getValueAt(index,6)+"' and place = '"
			      +dtm.getValueAt(index,7)+"' and week = '"
			      +dtm.getValueAt(index,8)+"'";
	    }		
		if(index!=-1)
		{
			try{
			     int i = stmt.executeUpdate(sql);
			     if(temp.equals("课程表"))
			      {
			       if(stmt.executeUpdate(sql1)==0)
			         JOptionPane.showMessageDialog(null,"删除失败");
			      }
		         if(i != 0)
		           dtm.removeRow(index);
		         else
		           JOptionPane.showMessageDialog(null,"删除失败"); 
			    }
			catch(SQLException ex)
			  {
			  	 System.out.println(ex.getMessage());
			  }     		
		}
		else 
		JOptionPane.showMessageDialog(null,"请选择要删除的行");
	 }
	}
	public void updateData()    //更新数据函数
	{
		String sql1 = "";
	    index = table.getSelectedRow();
	    /*根据不同的表执行不同的sql语句*/
	    if(temp.equals("学生基本信息表"))
	    {
	    	sql = "update student set name ='"+dtm.getValueAt(index,1)+"',sex ='"+
	    	       dtm.getValueAt(index,2)+"',nativeplace ='"+dtm.getValueAt(index,3)+
	    	       "',address ='"+dtm.getValueAt(index,4)+"',dept ='"+dtm.getValueAt(index,5)+
	    	       "',grade ='"+dtm.getValueAt(index,6)+"',birth ='"+dtm.getValueAt(index,7)+
	    	       "'where id ='"+dtm.getValueAt(index,0)+"'";
	    }	     
	    else if(temp.equals("课程表"))
	    {
	     	sql = "update course set cname ='"+dtm.getValueAt(index,0)+"',term ='"+
	     	       dtm.getValueAt(index,4)+"',property ='"+dtm.getValueAt(index,1)+"',studytime ="
	     	       +dtm.getValueAt(index,2)+",credit ="+dtm.getValueAt(index,3)+" where cname='"
	     	       +dtm.getValueAt(index,0)+"'";
	     	 try{
	     	 	  rs = stmt.executeQuery("select * from cschedule where cname ='"+dtm.getValueAt(index,0)+"'"); 
	             while(rs.next())
	             {
	             sql1 = "update cschedule set cname ='"+dtm.getValueAt(index,0)+"',grade ='"+
	     	       dtm.getValueAt(index,5)+"',teacher ='"+dtm.getValueAt(index,6)+"',place ='"
	     	       +dtm.getValueAt(index,7)+"',week ='"+dtm.getValueAt(index,8)+"' where cname ='"
	     	       +rs.getString(1)+"'and grade ='"+rs.getString(2)+"'and teacher ='"
	     	       +rs.getString(3)+"'and place ='"+rs.getString(4)+"'and week ='"+rs.getString(5)+"'";
	     	     //  System.out.println(sql1+"hhh");
	     	       }
	     	       if(stmt.executeUpdate(sql1)==0)
	           	     JOptionPane.showMessageDialog(null,"修改失败");	     	     
	     	     } 
	     	  catch(SQLException sqlx)
	     	  {
	     	  	System.out.println(sqlx.getMessage());
	     	  }    
	    }
	    	
		if(index!=-1)
		{
	      try{ 
		       int i = stmt.executeUpdate(sql);
		       if(i == 0)
		         JOptionPane.showMessageDialog(null,"修改失败"); 
		     }
		  catch(SQLException ex)
		  {
		  	System.out.println(ex.getMessage());
		  }   
		}
		else 
		 JOptionPane.showMessageDialog(null,"请选择要修改的行");
	}
	/*显示查询信息的函数*/
	public void displayInfo(String grd,String cls)   
	{
	  clearRecord();
	  count = colcount = 0;
      boolean hasrecord = false;
      if(temp.equals("学生基本信息表"))
      { 
       if(!grd.equals("")&&!cls.equals(""))
	    sql = "select id,name,sex,nativeplace,address,dept,grade,birth,entrytime from student"
	         +" where entrytime='"+grd+"'and grade ='"+cls+"'";
	   else if(!grd.equals(""))
	    sql = "select id,name,sex,nativeplace,address,dept,grade,birth,entrytime from student"
	         +" where entrytime='"+grd+"'";
	   else 
	    sql = "select id,name,sex,nativeplace,address,dept,grade,birth,entrytime from student"
	         +" where grade ='"+cls+"'";    
	   colcount = 9;
	  }
	  else if(temp.equals("课程表"))
	  {
	  	if(!grd.equals("")&&!cls.equals(""))
	  	  sql = "select course.cname,property,studytime,credit,term,grade,teacher,place,week"
	  	       +" from course,cschedule where course.cname = cschedule.cname and grade = '"+
	  	       grd+"' and term = '"+cls+"'";
	  	else if(!grd.equals(""))
	  	  sql = "select course.cname,property,studytime,credit,term,grade,teacher,place,week"
	  	       +" from course,cschedule where course.cname = cschedule.cname and term = '"+
	  	       grd+"'";
	  	else
	  	  sql = "select course.cname,property,studytime,credit,term,grade,teacher,place,week"
	  	       +" from course,cschedule where course.cname = cschedule.cname and grade='"+
	  	         cls+"'";
	  	      
	  	colcount = 9;
	  }   
	  try 
	  { 
	     rs=stmt.executeQuery(sql);	 
	     while(rs.next())
	     {
	       for(int j = 1;j<=colcount;j++)
	       {
	       	  dtm.setValueAt(rs.getString(j),count,j-1);	
	       }
	       hasrecord = true;
	       count++;
	     }
	     
	     if(hasrecord == false) 
	      JOptionPane.showMessageDialog(this, "无记录", "alert", JOptionPane.ERROR_MESSAGE);
	  } 
      catch(SQLException ex)
      {
      	System.out.println(ex.getMessage());
      }
	  rs = null;
	}
	/*显示信息函数的重载*/
	public void displayInfo()    
	{
	  clearRecord();
	  count = colcount = 0;
      if(temp.equals("学生基本信息表"))
      {  
	   sql = "select id,name,sex,nativeplace,address,dept,grade,birth,entrytime from student";      
	   colcount = 9;
	  }
	  else if(temp.equals("课程表"))
	  {
	  	sql = "select course.cname,property,studytime,credit,term,grade,teacher,place,week"
	  	       +" from course,cschedule where course.cname = cschedule.cname";
	  	colcount = 9;
	  }   
	  try 
	  { 
	     rs=stmt.executeQuery(sql);
	     while(rs.next())
	     {
	       for(int j = 1;j<=colcount;j++)
	       {
	       	  dtm.setValueAt(rs.getString(j),count,j-1);	
	       }
	       count++;
	     }
	  } 
      catch(SQLException ex)
      {
      	System.out.println(ex.getMessage());
      }
	  rs = null;	
	}
	public void clearRecord()    //清除记录
	{
		for(int i = 0;i<count;i++)
	     for(int j = 0;j<colcount;j++)
		     dtm.setValueAt("",i,j);
	}
	public static void closeConn()   //在关闭窗口时关闭数据库联接
	{
		try
	      {
	        if(stmt!=null)
              stmt.close();
            if(rs!=null)
              rs.close();
            con.close();
          }
      catch(SQLException ex)
          {
 	         ex.printStackTrace();
          }  
             
	}
	public void actionPerformed(ActionEvent e)
	{   
	    if(e.getSource() == inquire)
	    {
	      if(grd.getText().trim().equals("")&& cls.getText().trim().equals(""))
	        displayInfo();
	      else
	        displayInfo(grd.getText().trim(),cls.getText().trim());
	    }  
		else if(e.getActionCommand()== "添加")
		    addData();
		 else if(e.getActionCommand()=="删除")
		   deleteData();
		 else if(e.getActionCommand()=="修改")
		   updateData();
		 else
		   JOptionPane.showMessageDialog(this, "  在进行修改，插入操作时修改完最后\n"+
		                                 "一个字段后需点击同行其他字段\n"+"以便读入此字段值");
		
	}
}

class closeWin extends WindowAdapter   //关闭窗口时关闭数据库连接
{
	public void windowClosing(WindowEvent e)
	{
	  JFrame frm = (JFrame)(e.getSource());
	  TableFrame.closeConn();
	  frm.dispose();
	}
}