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

public class Menu0_2 extends JFrame implements ActionListener
{
	JLabel lbl1=new JLabel("姓名:");
	JLabel lbl2=new JLabel("性別:");
	JLabel lbl3=new JLabel("身份");
	JLabel lbl4=new JLabel("系");
	JLabel lbl5=new JLabel("證件號碼");
	JLabel lbl6=new JLabel("姓名:");

	JTextField txt1=new JTextField(10);
	JTextField txt2=new JTextField(10);
	JTextField txt3=new JTextField(10);
	JTextField txt4=new JTextField(10);

	JButton btn1=new JButton("注冊");
	JButton btn2=new JButton("更新");
	JButton btn3=new JButton("刪除");
	JButton btn4=new JButton("退出");
	JButton btn5=new JButton("查詢");
	
	JComboBox cmb1=new JComboBox();
	JComboBox cmb2=new JComboBox();	

	JRadioButton rb1=new JRadioButton("男");
	JRadioButton rb2=new JRadioButton("女");	

	String col[]={"姓名","性別","身份","系","證件號碼"};
	String rows[][]=new String[20][5];
	
	JTable tb;
	
	JScrollPane jsp;
	
	JTabbedPane tab=new JTabbedPane();

    public Menu0_2()
    {
   		this.setTitle("圖書證管理");
        init();
    	btn1.addActionListener(this);
    	btn2.addActionListener(this);
    	btn3.addActionListener(this);
    	btn4.addActionListener(this);
    	btn5.addActionListener(this);
    }
    public void init()
    {
    	
    	//-------------------Tab1-----------------------------
   		JPanel jp1=new JPanel();
   		JPanel jp7=new JPanel();
   		jp1.setLayout(new GridLayout(5,2));
   		ButtonGroup bg=new ButtonGroup();
   		bg.add(rb1);
   		bg.add(rb2);
   		jp7.add(rb1);
   		jp7.add(rb2);
   		
   		jp1.add(lbl1);
   		jp1.add(txt1);
   		
   		jp1.add(lbl2);
		jp1.add(jp7);
		   		
		jp1.add(lbl3);

   		cmb1.addItem("學生");
		cmb1.addItem("老師");
		cmb1.setEditable(true);
		
		jp1.add(cmb1);
		
		jp1.add(lbl4);
   		
   		cmb2.addItem("計算機科學系");
		cmb2.addItem("通信科學系");
		cmb2.addItem("電子科學系");
		cmb2.setEditable(false);
		
		jp1.add(cmb2);
		
		jp1.add(lbl5);
		jp1.add(txt3);
		
   		JPanel jp2=new JPanel();
    	jp2.add(btn1);
    	jp2.add(btn4);		
		
    	JPanel jp3=new JPanel();
    	jp3.setLayout(new BorderLayout());
    	jp3.add(jp1,BorderLayout.CENTER);
    	jp3.add(jp2,BorderLayout.SOUTH);
    	
       	//-------------------Tab2-----------------------------
    	
    	JPanel jp8=new JPanel();
    	JPanel jp4=new JPanel();
    	
    	jp8.add(lbl6);
    	jp8.add(txt4);
    	jp8.add(btn5);
    	
    	jp4.add(btn2);
    	jp4.add(btn3);
      	
      	tb=new JTable(rows,col);
    	jsp=new JScrollPane(tb);

    	JPanel jp5=new JPanel();
    	jp5.setLayout(new BorderLayout());
    	jp8.setLayout(new GridLayout(1,3));
    	jp5.add(jsp,BorderLayout.CENTER);
    	jp5.add(jp4,BorderLayout.SOUTH);
    	jp5.add(jp8,BorderLayout.NORTH);

    	//-------------------放置Tab-----------------------------

    	tab.add(jp3,"注冊");tab.add(jp5,"刪改查");
    	this.getContentPane().add(tab);
    	
    	this.setLocation(200,200);
    	this.setSize(400,300);
    	this.setVisible(true);
    
    }
    public void actionPerformed(ActionEvent e)
    {
    	//-------------------添加-----------------------------
    	
    	if(e.getSource()==btn1){
    	try{
    		    String userSex;
    		    if(rb1.isSelected())
    		    {
    		    	userSex=rb1.getText();
    		    }
    		    else
    		    userSex=rb2.getText();		
    			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    			Connection con=DriverManager.getConnection("jdbc:odbc:yyp","","");
    			Statement cmd=con.createStatement();
    			cmd.executeUpdate("insert into reader(rName,rSex,rType,rDept,rCardId) values('"
    			+txt1.getText()+"','"+userSex+"','"+cmb1.getSelectedItem()+"','"
    			+cmb2.getSelectedItem()+"','"+txt3.getText()+"')");
    			txt1.setText(null);
    		 	txt3.setText(null);
  	    		JOptionPane.showMessageDialog(this,"添加成功");

    		}catch(Exception ex)
    		{
    			System.out.println(ex);
    			}
    	}
    	//-------------------查詢-----------------------------

  		if(e.getSource()==btn5){
    	try{
    		int flag=0;
    		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			Connection con=DriverManager.getConnection("jdbc:odbc:yyp","","");
			Statement cmd=con.createStatement();
			ResultSet rs=cmd.executeQuery("select * from reader");
			while(rs.next())
			{
				String st1=rs.getString(2);
				if(st1.equals(txt4.getText()))
				{
					rows[flag][0]=st1;
					rows[flag][1]=rs.getString(3);
					rows[flag][2]=rs.getString(4);
					rows[flag][4]=rs.getString(5);
					rows[flag][3]=rs.getString(6);

					}
				}
					JOptionPane.showMessageDialog
(this,"姓名："+rows[flag][0]+"   性別:"+rows[flag][1]+"  身份:"+rows[flag][2]+"  系:"+rows[flag][3]+"  證件號碼:"+rows[flag][4]+"");
				cmd.close();
				con.close();
			}catch(Exception ex)
			{
				System.out.println(ex);
				}  
			} 
			
		//-------------------更新-----------------------------
    	
    	if(e.getSource()==btn2){
    		try{
    			int flag=0;
    			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				Connection con=DriverManager.getConnection("jdbc:odbc:yyp","","");
				Statement cmd=con.createStatement();
				ResultSet rs=cmd.executeQuery("select * from reader");
				while(rs.next())
				{
					String st1=rs.getString(2);
					if(st1.equals(txt4.getText()))
					{
					//{"姓名","性別","身份","系","證件號碼"};
						rows[flag][0]=st1;
						rows[flag][1]=rs.getString(3);
						rows[flag][2]=rs.getString(4);
						rows[flag][4]=rs.getString(5);
						rows[flag][3]=rs.getString(6);
						flag++;
						tb.setModel(new DefaultTableModel(rows,col));
						}
					}
					cmd.close();
					con.close();
				}catch(Exception ex)
				{
					System.out.println(ex);
					}  
				}
		
		//-------------------刪除-----------------------------
		
		if(e.getSource()==btn3)
		{
			try{
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				Connection con=DriverManager.getConnection("jdbc:odbc:yyp","","");
				Statement cmd=con.createStatement();
				cmd.executeUpdate("delete from reader where rName='"+txt4.getText()+"'");
				JOptionPane.showMessageDialog(this,"刪除成功");
				ResultSet rs=cmd.executeQuery("select * from reader");
				int flag1=0;
				String rows1[][]=new String[20][5];
				while(rs.next())
				{					
				rows1[flag1][0]=rs.getString(2);
				rows1[flag1][1]=rs.getString(3);
				rows1[flag1][2]=rs.getString(4);
				rows1[flag1][4]=rs.getString(5);
				rows1[flag1][3]=rs.getString(6);
				flag1++;	
				tb.setModel(new DefaultTableModel(rows,col));
				}
	     		for(int i=0;i<=flag1;i++)
    			{rows[i][0]=rows1[i][0];	
    			 rows[i][1]=rows1[i][1];
    			 rows[i][2]=rows1[i][2];
    			 rows[i][3]=rows1[i][3];
    			 rows[i][4]=rows1[i][4];
    			 }
					}catch( Exception ex)
    			{
    				System.out.println(ex);	
    			}
		}
  		//-------------------瀏覽全部-----------------------------
    	
    	if(e.getSource()==btn2)
			{
    		try{
    			int flag=0;
    			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				Connection con=DriverManager.getConnection("jdbc:odbc:yyp","","");
				Statement cmd=con.createStatement();
				ResultSet rs=cmd.executeQuery("select * from reader");
				while(rs.next())
				{					
						rows[flag][0]=rs.getString(2);//姓名
						rows[flag][1]=rs.getString(3);//性別
						rows[flag][2]=rs.getString(4);//身份
						rows[flag][4]=rs.getString(5);//系
						rows[flag][3]=rs.getString(6);//證件號碼
						flag++;	
						tb.setModel(new DefaultTableModel(rows,col));

				}
				cmd.close();
				con.close();
			}catch(Exception ex)
			{
				System.out.println(ex);
				}  
			}


		  	if(e.getSource()==btn4)
    		this.dispose();
    
    }

    public static void main(String arg[])
    {
    	JFrame.setDefaultLookAndFeelDecorated(true);
    	Menu0_2 frm=new Menu0_2();
    }
 }