import java.awt.*;

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

class B_delete extends JFrame implements ActionListener
{
	JLabel l=new JLabel("輸入要刪除的學號:");
	JTextField tf=new JTextField();
	JButton b=new JButton("刪除");
	JTextArea ta=new JTextArea();
	Connection con;
	Statement sta;
	String conURL="jdbc:mysql://localhost/login?user=root&password=root";
	B_delete()
	{
		super("學生信息刪除");
		setBounds(200,200,400,300);
		setBounds(200,200,400,300);
		JPanel p=new JPanel();
		p.setLayout(new GridLayout(1,3));
		p.add(l);p.add(tf);p.add(b);
		setLayout(new BorderLayout());
		add(p,"North");
		add(ta,"Center");
		ConSql();
		b.addActionListener(this);
		setVisible(true);
	}
	public void actionPerformed(ActionEvent e) {
	    
	    ta.setText("");
	    Delete();
	    tf.setText("");
	}
	public void Delete()
	{
		String s1=tf.getText().trim();
		
        try{
        	con=DriverManager.getConnection(conURL);
        	ResultSet rs=sta.executeQuery("select s_number,d_id,d_count,d_true from student,dormitory where student.d_number=dormitory.d_number");
        	while(rs.next())
        	{
        	   String s2=rs.getString("s_number");
        	   String s5=rs.getString("d_id");
        	   int s3=rs.getInt("d_count");
        	   boolean s4=rs.getBoolean("d_true");
        	   if(s2.equals(s1))
        	   {
        		   PreparedStatement ps=con.prepareStatement("delete from student where s_number=?");
        	       ps.setString(1,""+s1+"");
        	       ps.executeUpdate();
        	       ps=con.prepareStatement("update dormitory set d_count=? where d_id=?");
        	       ps.setInt(1, s3-1);
        	       ps.setString(2, ""+s5+"");
        	       ps.executeUpdate();
        	       ps=con.prepareStatement("update dormitory set d_true=? where d_id=?");
        	       ps.setBoolean(1, false);
        	       ps.setString(2, ""+s5+"");
        	       ps.executeUpdate();
        	       ps.close();
        	       rs.close();
        	       sta.close();
        	       con.close();
        	       this.dispose();
        	       new JOptionPane().showMessageDialog(null, "刪除成功");
        	       
        	       break;
                }
        	   else
        	   {
        		   ta.setText("沒有學生記錄");
        	   }
        	}
        }
        catch(SQLException e)
        {
        	e.printStackTrace();
        }
		
		
	}
	public void ConSql()
	{
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con=DriverManager.getConnection(conURL);
			sta=con.createStatement();
		}
		catch(ClassNotFoundException e){
			e.printStackTrace();
		}
		catch(SQLException e){
			e.printStackTrace();
		}
	}
}