/*  
 *   MyAccess.java
 *  杨彩 http://blog.sina.com.cn/m/yangcai
 *  最后修改于2007.1.15
 *
 */

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

public class MyAccess extends JFrame implements ActionListener,ListSelectionListener
{
 private static Connection conn;
 private static Statement comm;
 private static ResultSet rs;
 private  static int total=0,k=10,row=0,i=0;
 JLabel jl,jl2;
 JTable jt;
 JTextField jid,jname,jscore,se;
 static JButton go,add,drop,del,jbse,flush;
 JFrame jf;
 JScrollPane s;
 Vector vect=new Vector();
 String[] data = {"学号=","学号>","学号<", "姓名=", "分数=","分数>=","分数<"};
  JComboBox dataList = new JComboBox(data);
 String[] columnNames = {"学号","姓名","分数"};
 AbstractTableModel tm = new AbstractTableModel()
  {
          public int getColumnCount(){ return 3;}
          public int getRowCount(){ return k;}
          public Object getValueAt(int row, int col){ return ((Vector)vect.get(row)).get(col); }
          public String getColumnName(int column){    return columnNames[column];   }
      };
 
 public MyAccess()
 {
  jf=new JFrame("考感学院04级JAVA成绩管理系统");
  jf.setSize(490,570);
  jf.locate(200,200);
  jf.setResizable(false);
  jf.getContentPane().setLayout(new FlowLayout()); 
  jl=new JLabel("考感学院04级JAVA成绩");
  jid=new JTextField(6);
  jname=new JTextField(5);
  jscore=new JTextField(3);
  se=new JTextField(9);
  go=new JButton("分数(低->高)");
  go.addActionListener(this);
  add=new JButton("添加");
  add.addActionListener(this);
  drop=new JButton("修改");
  drop.addActionListener(this);
  del=new JButton("删除");
  del.addActionListener(this);
  jbse=new JButton("搜索");
  jbse.addActionListener(this);
  flush=new JButton("刷新");
  flush.addActionListener(this);
  jl2=new JLabel("");
  jl2.setForeground(Color.red);
  jf.getContentPane().add(jl);
  jt=new JTable(tm);
  jt.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
  this.jt.getSelectionModel().addListSelectionListener(this);

  
  
  s = new JScrollPane(jt);  
  jf.getContentPane().add(s);
  jf.getContentPane().add(new JLabel("学号:"));
  jf.getContentPane().add(jid);
  jf.getContentPane().add(new JLabel("姓名:"));
  jf.getContentPane().add(jname);
  jf.getContentPane().add(new JLabel("分数:"));
  jf.getContentPane().add(jscore);
  jf.getContentPane().add(add);
  jf.getContentPane().add(drop);
  jf.getContentPane().add(del);
  jf.getContentPane().add(new JLabel("搜索学生:"));
  jf.getContentPane().add(dataList);
  jf.getContentPane().add(se);
  jf.getContentPane().add(jbse);
  jf.getContentPane().add(flush);
  jf.getContentPane().add(go);
  jf.getContentPane().add(jl2);
  
 }
public void exce(String exce)
{
 try
 { this.jt.getSelectionModel().removeListSelectionListener(this);
  rs = execQuery(exce);
  vect.removeAllElements();
  tm.fireTableDataChanged();
  total=0;
  while(rs.next())
  { 
   Vector rec_vector=new Vector();
   rec_vector.addElement(rs.getString(1));
   rec_vector.addElement(rs.getString(2));
   rec_vector.addElement(rs.getString(3));
   vect.addElement(rec_vector);
      total++;     
  }
  k=total;
  this.closeDB();
  System.out.println("vect是面有"+total+"记录执行操作");
  System.out.println("成功执行:"+exce);
  this.jt.getSelectionModel().addListSelectionListener(this);  
    
 }
 catch(Exception ee)
 {
  jl2.setText("无法执行,请填入正确的数据");
  System.out.println("执行失败,可能查询为空");
  k=0;
  
 }
}

public void excesql(String sql)
{
 try
 {
 conBuild();
 Statement stm=conn.createStatement();
 stm.executeUpdate(sql);
 jf.repaint();
 jl2.setText("操作已执行");
 System.out.println("成功执行:"+sql);
 this.closeDB();
 }
 catch(Exception ee)
 {
  jl2.setText("无法执行,请填入正确的数据");
  System.out.println(sql+"无法执行");
 }
} 

public static void regDriver()//register JDBC 桥接 driver
{
      try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();//关键句1
      System.out.println("驱动注册成功");
      }
       catch(Exception e)
     {
     System.out.println("无法创建驱动程序实体!");
    }

}
   //建立数据库连接
public static void conBuild()//建立JDBC连接
  {
   try{
     MyAccess.regDriver();
     conn=DriverManager.getConnection("jdbc:odbc:myDSN","","");//关键句2
     conn.setAutoCommit(true);
     System.out.println("成功连接数据库");
     }
     catch(Exception e)
     {
      System.out.println(e.getMessage()) ;
      System.out.println("无法连接数据库Connection!，运行之前请先设置数据源 MyDSN");
     }
}

  public static ResultSet execQuery(String stmt1)//执行查询语句
    {
     try{
     conBuild();
      comm=conn.createStatement();
      rs=comm.executeQuery(stmt1);
      return rs;
     }
     catch(Exception e)
      {
       System.out.println("无法创建Statement!");return null;
      }
}   

public static void closeDB()
{
        try{
          comm.close();
          conn.close();
          System.out.println("关闭记录集，断开数据库");
          }
          catch(Exception e)
         {
          System.out.println(e.getMessage());
         }
 }

public void actionPerformed(ActionEvent ee)// 实现ActionListener中的唯一方法
{
 if(ee.getSource()==go)
 {
  try
  {
   
   if(i==0)
   {
    exce("SELECT * FROM 成绩 order by 分数");
    i=1; 
    go.setText("分数(高->低)");
   }

   else
   {
    exce("SELECT * FROM 成绩 order by 分数 desc");
    i=0;
    go.setText("分数(低->高)"); 
   }
   
   jl2.setText("已按分数排序");
      
  }
  catch(Exception go)
  {
   System.out.println("出现错误");
   jl2.setText("提示:出现错误");
  }
  

  
 }
 if(ee.getSource()==add)
 {
  this.excesql("insert into 成绩 values('"+jid.getText()+"','"+jname.getText()+"','"+jscore.getText()+"')");
  exce("SELECT * FROM 成绩 order by 学号");
  jid.setText("");
  jname.setText("");
  jscore.setText("");
  jf.repaint();
    
  System.out.println("执行添加操作");
  
   
 }

 if(ee.getSource()==drop)
 {
  this.excesql("update 成绩 set 姓名='"+jname.getText()+"' ,分数="+jscore.getText()+" where 学号='"+jid.getText()+"'" );
  exce("SELECT * FROM 成绩 order by 学号"); 
 
 }
 if(ee.getSource()==del)
 {
  this.excesql("delete from 成绩 where 学号='"+jid.getText()+"'");
  exce("SELECT * FROM 成绩 order by 学号");
  jl2.setText("提示:成功删除ID为"+jid.getText()+"的数据");
  System.out.println("执行删除操作"); 
   
 }
 if(ee.getSource()==jbse)
 {
  if(dataList.getSelectedIndex()>=4)
  {
     String sql="SELECT *  FROM 成绩 WHERE "+dataList.getSelectedItem()+se.getText();
   exce(sql);
  }
  else
  {
   String sql="SELECT *  FROM 成绩 WHERE "+dataList.getSelectedItem()+"'"+se.getText()+"'";
   exce(sql);
  }
  jl2.setText("搜索结果:共"+k+"条数据");
  System.out.println(k+"执行搜索操作");
  
  jf.repaint();
 }
 
 if(ee.getSource()==flush)
 {
  exce("SELECT * FROM 成绩 order by 学号");
  jid.setText("");
  jname.setText("");
  jscore.setText("");
  se.setText("");
  jf.repaint();
  jl2.setText("提示:刷新成功");
  
 }
 
}

public void valueChanged(ListSelectionEvent el)  //每当选择值发生更改时调用。
{
  row=0;
  row=jt.getSelectedRow();
  if(row<0) row=0;  
  Object row_id=jt.getValueAt(row,0);
  Object row_name=jt.getValueAt(row,1);
  Object row_score=jt.getValueAt(row,2);
  
  jid.setText(row_id.toString());
  jname.setText(row_name.toString());
  jscore.setText(row_score.toString());
  System.out.println("选择已改变"+k+"  "+total);
  
  
}

public static void main(String s[])
      {
     MyAccess ma=new MyAccess();
  ma.exce("SELECT * FROM 成绩 order by 学号");
     ma.jf.setVisible(true);
    
      }

}