import java.awt.*;
import javax.swing.*;
import java.awt.event.*;
import java.sql.*;
public class StudentManagement extends JFrame implements ActionListener{
    /**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	JTabbedPane dbTabPane;
    JPanel JP1=new JPanel();//輸入面板信息	
    JPanel JP2=new JPanel();//瀏覽面板
    JPanel JP3=new JPanel();//查詢面板
    JPanel JP4=new JPanel();//刪除面板
    JButton JB1,JB2,JB3,JB4;
    JTextField JTF1,JTF2,JTF3,JTF4,JTF5,JTF6,JTF7;
    JTextArea JTA1,JTA2,JTA3;
    Choice c1;
    Connection conn;
    Statement stmt;
    public StudentManagement(){
        super("學生基本信息管理");
        setGUIComponent();
    }
    public void setGUIComponent(){
        Container c=getContentPane();
        c.setLayout(new BorderLayout());
        dbTabPane=new JTabbedPane();
        //定義錄入面板
        JP1.setLayout(new BorderLayout());
        Panel p1=new Panel();
        p1.setLayout(new GridLayout(3,4,10,10));
        JP1.setBackground(Color.green);
        JLabel JLb1=new JLabel("學號");
        JLabel JLb2=new JLabel("姓名");
        JLabel JLb3=new JLabel("年齡");
        JLabel JLb4=new JLabel("性別");
        JLabel JLb5=new JLabel("班級");
        JLabel JLb6=new JLabel("籍貫");
        JTF1=new JTextField(10);
        JTF2=new JTextField(10);
        JTF3=new JTextField(10);
        JTF4=new JTextField(10);
        JTF5=new JTextField(10);
        c1=new Choice();c1.add("男");c1.add("女");
        JB1=new JButton("確定");
        p1.add(JLb1);p1.add(JTF1);
        p1.add(JLb2);p1.add(JTF2);
        p1.add(JLb3);p1.add(JTF3);
        p1.add(JLb4);p1.add(c1);
        p1.add(JLb5);p1.add(JTF4);
        p1.add(JLb6);p1.add(JTF5);
        JP1.add(p1,BorderLayout.NORTH);
        JP1.add(JB1,BorderLayout.SOUTH);
        JB1.addActionListener(this);
        dbTabPane.add("輸入記錄",JP1);
        //定義瀏覽所有信息面板
        JP2.setLayout(new BorderLayout());
        JP2.setBackground(Color.green);
        JTA1=new JTextArea(6,35);
        JB2=new JButton("瀏覽所有學生的信息");
        JP2.add(new JScrollPane(JTA1),BorderLayout.CENTER);
        JP2.add(JB2,BorderLayout.SOUTH);
        JB2.addActionListener(this);
        dbTabPane.add("瀏覽記錄",JP2);
        //定義查詢面板
        JP3.setLayout(new FlowLayout());
        JLabel JLB7=new JLabel("輸入學號：");
        JTF6=new JTextField(10);
        JB3=new JButton("查詢");
        JTA2=new JTextArea(4,35);
        JP3.add(JLB7);
        JP3.add(JTF6);
        JP3.add(JB3);
        JP3.add(new JScrollPane(JTA2));
        JB3.addActionListener(this);
        dbTabPane.add("查詢記錄",JP3);
        //定義刪除面板
        JP4.setLayout(new FlowLayout());
        JLabel JB8=new JLabel("輸入學號：");
        JTF7=new JTextField(10);
        JB4=new JButton("刪除");
        JTA3=new JTextArea(4,35);
        JB4.addActionListener(this);
        JP4.add(JB8);
        JP4.add(JTF7);
        JP4.add(JB4);
        JP4.add(new JScrollPane(JTA3));
        dbTabPane.add("刪除記錄",JP4);
        c.add(BorderLayout.NORTH,dbTabPane);
    }
    public void connection(){//連接數據庫
        try{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url="jdbc:odbc:mysql";
            String user="sa";
            String password="";
            conn=DriverManager.getConnection(url,user,password);
            stmt=conn.createStatement();
        }catch(ClassNotFoundException e1){
            System.err.println("驅動程序裝載失敗！");
        }catch(SQLException e2){
            e2.getSQLState();
            e2.getMessage();
        }
    }
    public void close(){//關閉執行語句和數據庫
        try{
            if(stmt!=null)stmt.close();
            if(conn!=null)conn.close();
        }catch(SQLException e2){
            System.err.println("不能正常關閉");
        }
    }
    public void inputRecords(){//錄入學生信息
        String sno=JTF1.getText();
        String sname=JTF2.getText();
        String sage=JTF3.getText();
        String ssex=c1.getSelectedItem();//JTF4.getText();
        String sclass=JTF4.getText();
        String snative=JTF5.getText();
        try{
            connection();
            String InsSQL;
            InsSQL="insert into student(學號,姓名,年齡,性別,班級,籍貫) values(?,?,?,?,?,?)";
            PreparedStatement pstmt=conn.prepareStatement(InsSQL);
            pstmt.setString(1,sno);
            pstmt.setString(2,sname);
            pstmt.setString(3,sage);
            pstmt.setString(4,ssex);
            pstmt.setString(5,sclass);
            pstmt.setString(6,snative);
            int result=pstmt.executeUpdate();
            if(result==0){
                conn.rollback();
                return;
            }
        }catch(SQLException e1){
            System.err.println(e1.getSQLState());
        }
        finally{
            close();
        }
    }
    public void viewRecords(){//瀏覽該班所有學生的記錄
        try{
            String viewString="";
            connection();
            ResultSet rs=stmt.executeQuery("SELECT*From student");//
            ResultSetMetaData rsMeta=rs.getMetaData();
            int nums=rsMeta.getColumnCount();
            for(int i=1;i<=nums;i++){
                viewString+=rsMeta.getColumnName(i)+"\t";
            }
            viewString+="\n";
            while(rs.next()){
                for(int i=1;i<=nums;i++){
                    viewString+=rs.getString(i)+"\t";
                }
                viewString+="\n";
                JTA1.setText(viewString);
            }
        }catch(SQLException e1){
            System.err.println("瀏覽學生記錄失敗");
            System.err.println(e1.getSQLState()+e1.getMessage());
        }
        finally{
            close();
        }
    }
    public void viewSelectRecord(){//查詢指定學號的學生記錄
        try{
            connection();
            String selectString="";
            String str=JTF6.getText();
            String selectSQL="select * from student where 學號=?";
            PreparedStatement pstmt=conn.prepareStatement(selectSQL);
            pstmt.setString(1,str);
            ResultSet rs=pstmt.executeQuery();
            ResultSetMetaData rsMeta=rs.getMetaData();
            int nums=rsMeta.getColumnCount();
            for(int i=1;i<=nums;i++){
                selectString+=rsMeta.getColumnName(i)+"\t";
            }
            selectString+="\n";
            while(rs.next()){
                for(int i=1;i<=nums;i++){
                    selectString+=rs.getString(i)+"\t";
            }
            selectString+="\n";
            JTA2.setText(selectString);
        }
        }catch(SQLException e1){
            System.err.println("操作失敗");
            System.err.println(e1.getSQLState()+e1.getMessage());
        }
        finally{
            close();
        }
    }
    public void deleteRecord(){//刪除學號為no學生的記錄
        try{
            connection();
            String deleteSQL="delete from student where 學號=?";
            PreparedStatement pstmt=conn.prepareStatement(deleteSQL);
            pstmt.setString(1,JTF7.getText());
            pstmt.executeUpdate();
            
            JTA3.setText("學號為"+JTF7.getText()+"的學生信息刪除成功!");
            
        }catch(SQLException e1){
            System.err.println("操作失敗");
            System.err.println(e1.getSQLState()+e1.getMessage());
        }
        finally{
            close();
        }
    }
    public void actionPerformed(ActionEvent e){
        if(e.getSource()==JB1){
            inputRecords();
        }
        else if(e.getSource()==JB2){
            viewRecords();
        }
        else if(e.getSource()==JB3){
            viewSelectRecord();
        }
        else if(e.getSource()==JB4){
            deleteRecord();
        }
    }
    public static void main(String[]args){
        StudentManagement sm=new StudentManagement();
        sm.setSize(600,220);
        sm.setVisible(true);
        sm.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    }
}