import java.awt.BorderLayout;
import java.awt.Container;
import java.awt.FlowLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import javax.swing.ButtonGroup;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JRadioButton;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

class JTableFrame extends JFrame implements ActionListener{
    JTable teacher=new JTable();//һհ׵JTable
    JButton bu1=new JButton("һҳ");
    JButton bu2=new JButton("һҳ");
    JPanel pu1=new JPanel();
    JPanel pu2=new JPanel();
    JPanel addpanel=new JPanel();
    JLabel lblname=new JLabel("");//ı
    JTextField txtname=new JTextField(10);
    JLabel lblage=new JLabel("䣺");//ı
    JTextField txtage=new JTextField(5);
    JLabel lblsex=new JLabel("Ա");//ѡť
    ButtonGroup bgr=new ButtonGroup();
    JRadioButton ckbmale=new JRadioButton("",true);
    JRadioButton ckbfamale=new JRadioButton("Ů");
    JLabel lblgrade=new JLabel("ְƣ");//б
    String grade[]={"","ʦ","",""};
    JComboBox jcbgrade=new JComboBox(grade);
    JLabel lbladdr=new JLabel("ַ");//ı
    JTextArea txaaddr=new JTextArea(2,44);
    JLabel lblemail=new JLabel("Email");//ı
    JTextField txtemail=new JTextField(20);
    JLabel lblphone=new JLabel("绰");//ı
    JTextField txtphone=new JTextField(20);
    JButton btnadd=new JButton("");
    JButton btndelete=new JButton("ɾ");
    JButton btnupdate=new JButton("޸");
    JButton btnexit=new JButton("˳");
    JPanel p1=new JPanel(new FlowLayout(FlowLayout.LEFT));
    JPanel p2=new JPanel(new FlowLayout(FlowLayout.LEFT));
    JPanel p3=new JPanel(new FlowLayout(FlowLayout.LEFT));
    JPanel p4=new JPanel(new FlowLayout(FlowLayout.CENTER));
    DataBase db=new DataBase();
    static int num=0;
    JTableFrame()throws Exception{
        super("ʦϢ");
        bind();//ݰ
        JScrollPane js=new JScrollPane(teacher);
        js.setBounds(0, 0, 200, 100);
        pu1.add(js);//JTableӵ
        bu1.addActionListener(this);
        bu2.addActionListener(this);
        pu2.add(bu1);
        pu2.add(bu2);
        addpanel.setLayout(new GridLayout(5,1));//˳
        p1.add(lblname);
        p1.add(txtname);
        p1.add(lblage);
        p1.add(txtage);
        p1.add(lblsex);
        p1.add(ckbmale);
        p1.add(ckbfamale);
        bgr.add(ckbmale);
        bgr.add(ckbfamale);
        p1.add(lblgrade);
        p1.add(jcbgrade);
        addpanel.add(p1);
p2.add(lblemail);
p2.add(txtemail);
p2.add(lblphone);
p2.add(txtphone);
addpanel.add(p2);
p3.add(lbladdr);
p3.add(txaaddr);
addpanel.add(p3);
	btnadd.addActionListener(this);//ע
	btndelete.addActionListener(this);
	btnupdate.addActionListener(this);
	btnexit.addActionListener(this);
	p4.add(btnadd);
	p4.add(btndelete);
	p4.add(btnupdate);
	p4.add(btnexit);
	addpanel.add(p4);
             //JTableǷԶС
            teacher.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS); 
            teacher.addMouseListener(new MouseAdapter() {//ӵJTable¼
            public void mouseClicked(MouseEvent evt) {
                int row=teacher.getSelectedRow();//õǰѡек
                txtname.setText(teacher.getValueAt(row, 1).toString());
            	   txtage.setText(teacher.getValueAt(row, 2).toString());
                if((teacher.getValueAt(row, 3).toString()).equals("")){
            		ckbmale.setSelected(true);
            		ckbfamale.setSelected(false);
            	}else{
            		ckbmale.setSelected(false);
            		ckbfamale.setSelected(true);
            	}
            	jcbgrade.setSelectedItem(teacher.getValueAt(row, 4));
            	txtemail.setText(teacher.getValueAt(row, 5).toString());
            	txtphone.setText(teacher.getValueAt(row, 6).toString());
            	txaaddr.setText(teacher.getValueAt(row, 7).toString());
            }
        });
        Container contentpane=getContentPane();
        contentpane.add(pu1,BorderLayout.CENTER);//JTableӵ
        contentpane.add(pu2,BorderLayout.NORTH);//JTableӵ
        contentpane.add(addpanel,BorderLayout.SOUTH);
        setSize(600,600);
        setVisible(true);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    }
    public void bind(){//
    	  String []s={"","","","Ա","","סַ","Email","绰"};
    	  String[][] stu;
	try {
	        stu = db.allTeacher(num);//ȡʦϢ
	        DefaultTableModel dtm=new DefaultTableModel(stu,s);//TableModel
	        teacher.setModel(dtm);//TableModel
		} catch (Exception e) {
			e.printStackTrace();
		}
    	}
    public void actionPerformed(ActionEvent e){
    	  JButton b=(JButton)e.getSource();
    	  try{
    	  if(b.equals(bu1)){//һҳ
    		    num=num-10;
    		    bind();
    		}
    	  if(b.equals(bu2)){//һҳ
    		    num=num+10;
    		    bind();
    		}
    	if(b.equals(btnadd)){//ӽʦϢ
    	     if(JOptionPane.showConfirmDialog(this, "Ҫ",
                             "ȷ",JOptionPane.OK_CANCEL_OPTION)==JOptionPane.YES_OPTION){
    		        Vector v = new Vector();
		v.add(txtname.getText());
		v.add(txtage.getText());
		if (ckbmale.isSelected())
		v.add("");
		else
		v.add("Ů");
		v.add(jcbgrade.getSelectedItem());
		v.add(txaaddr.getText());
		v.add(txtemail.getText());
		v.add(txtphone.getText());
		if (db.addTeacher(v)) {
		bind();
		JOptionPane.showMessageDialog(this, "ɹ");
		}
    	    }
    	}
    	if(b.equals(btndelete)){//ɾʦϢ
    	     if(JOptionPane.showConfirmDialog(this, "Ҫɾ",
                            "ȷɾ",JOptionPane.OK_CANCEL_OPTION)==JOptionPane.YES_OPTION){
    		int row=teacher.getSelectedRow();
    		if(db.deleteTeacher(Integer.parseInt(teacher.getValueAt(row, 0).toString()))){
    			bind();
    			JOptionPane.showMessageDialog(this, "ɾɹ");
    		}
    	          }
    	}
    	if(b.equals(btnupdate)){//޸ĽʦϢ
    	     if(JOptionPane.showConfirmDialog(this, "Ҫ޸",
                         "ȷ޸",JOptionPane.OK_CANCEL_OPTION)==JOptionPane.YES_OPTION){
    		int row=teacher.getSelectedRow();
    	      	Vector v=new Vector();
    		v.add(txtname.getText());
        		v.add(txtage.getText());
        		if(ckbmale.isSelected())
        		          v.add("");
        		else
        		         v.add("Ů");
        		v.add(jcbgrade.getSelectedItem());
        		v.add(txaaddr.getText());
        		v.add(txtemail.getText());
        		v.add(txtphone.getText());
    		if(db.updateTeacher(v,Integer.parseInt(teacher.getValueAt(row, 0).toString()))){
    			bind();
    			JOptionPane.showMessageDialog(this, "޸ĳɹ");
    			}
    		}
    	}
    	if(b.equals(btnexit)){//˳ϵͳ
    		System.exit(0);
    	}
    }
    catch(Exception ex){}
               }
}
class DataBase{
        Connection conn=null;
        public DataBase(){
                  try {//
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	        //ݿ  
	        conn=DriverManager.getConnection("jdbc:odbc:teacher","","");
		} catch (Exception e) {
			e.printStackTrace();
		} 
	}
	String[][] allTeacher(int numb)throws Exception{
        Statement stm=conn.createStatement();//Statement
        ResultSet rs=stm.executeQuery("select * from teacher");//ִSQL
        //жǷвѯʾ
        String [][]teacher=new String[10][8];
        int i=0,j=0;
        while(rs.next()){//
        	if(i>=numb){
        		if(i<numb+10){
                  teacher[j][0]=rs.getString(1);
                  teacher[j][1]=rs.getString(2);
                  teacher[j][2]=""+rs.getInt(3);
                  teacher[j][3]=rs.getString(4);
                  teacher[j][4]=rs.getString(5);
                  teacher[j][5]=rs.getString(6);
                  teacher[j][6]=rs.getString(7);
                  teacher[j][7]=rs.getString(8);
                  j++;
                  }
            else{
            	return teacher;}
           }
        	i++;
        	}
        	return teacher;
    	}
	public boolean addTeacher(Vector teacher){
	         try {//ִSQLĶ
		Statement st=conn.createStatement();
		String sql="insert into teacher(tname,age,sex,grade,address,email,phone) values
('"+teacher.get(0)+"',"+teacher.get(1)+",'"+teacher.get(2)+"','"+teacher.get(3)+"','"+teacher.get(4)+"','"+teacher.get(5)+"','"+teacher.get(6)+"')";
		st.executeUpdate(sql);
		return true;
	         } catch (SQLException e) {
		e.printStackTrace();
		return false;
	}
}
public boolean deleteTeacher(int tno){
	try {
		Statement st=conn.createStatement();
		String sql="delete from teacher where tno="+tno+"";
		st.executeUpdate(sql);
		return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	public boolean updateTeacher(Vector teacher,int tno){
		try {
		Statement st=conn.createStatement();
		String sql="update teacher set tname='"+teacher.get(0)+"',age="+teacher.get(1)+",
sex='"+teacher.get(2)+"',grade='"+teacher.get(3)+"',address='"+teacher.get(4)+"',email='"+teacher.get(5)+"',phone='"+teacher.get(6)+"' where tno="+tno+"";
		st.executeUpdate(sql);
		return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
}
public class ShiYan11_3{
	    public static void main(String args[])throws Exception{
                                JTableFrame h=new JTableFrame();}
}
