/*??
?*?? 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);
????
????? }

}