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

import javax.swing.*;
import javax.swing.table.*;

public class Swing04 extends JFrame
{
        
        Connection conn; // 연결 객체
        Statement stmt;
         DefaultTableModel model ;
         JTable table;
        
         String query;

        Swing04()
        {
                // 생성자에서 변수 초기화 (쿼리문)
                query = "select s.id, s.name 이름, b.title 도서명, b.rDate 대여일자"
                              + " from student s, bookRent b"
                              + " where s.id=b.id";
               
                try
               {
                        Class. forName("com.mysql.jdbc.Driver");
                        conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/sampledb" , "root" , "1234" );
                        stmt = conn.createStatement();
               }
                catch (Exception e1 )
               {
                        e1.printStackTrace();
               } // jdbc 드라이버 로드
               
               setTitle( "학생관리" );
                setDefaultCloseOperation( JFrame. EXIT_ON_CLOSE);
               
               setLayout( null); // 레이아웃 설정 - 사용안함
               
                JLabel l_dept = new JLabel( "학과");
                l_dept.setBounds(10, 10, 50, 30);
               add( l_dept);
               
                String [] dept = { "전체", "스마트컨텐츠개발" , "스마트컨텐츠기획" , "스마트컨텐츠마케팅" , "컴퓨터공학" };
                JComboBox cb_dept = new JComboBox( dept);
                cb_dept.setBounds(70, 10, 150, 30);
               add( cb_dept);
               
                cb_dept.addActionListener( new ActionListener()
               {      
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                               JComboBox cb = (JComboBox)e .getSource();
                              
                                System. out.println(cb .getSelectedIndex()); // 인덱스번호를 콘솔창에 출력
                              
                               int deptIndex = cb.getSelectedIndex();
                              
                               query = "select s.id, s.name 이름, b.title 도서명, b.rDate 대여일자"
                                                     + " from student s, bookRent b"
                                                     + " where s.id=b.id";

                               if( deptIndex == 0)
                              {
                                       // '전체' 선택 시 모두 출력
                                       query += " order by id;";
                                      
                                       // List 메소드 호출 (목록버튼을 누를 필요가 없어진다.)
                                      List();
                              }
                               else if (deptIndex == 1)
                              {
                                       query += " and s.dept='스마트컨텐츠개발'" + " order by id;";
                                      List();
                              }
                               else if (deptIndex == 2)
                              {
                                       query += " and s.dept='스마트컨텐츠기획'" + " order by id;";
                                      List();
                              }
                               else if (deptIndex == 3)
                              {
                                       query += " and s.dept='스마트컨텐츠마케팅'" + " order by id;" ;
                                      List();
                              }
                               else if (deptIndex == 4)
                              {
                                       query += " and s.dept='컴퓨터공학'" + " order by id;" ;
                                      List();
                              }
                       }
               });
               
                JButton list = new JButton( "조회"); // // 'List'버튼 생성
                list.setBounds(234, 10, 80, 30);
               add( list);
               
                JButton out = new JButton( "종료"); // 'out'버튼 생성
                out.setBounds(378, 10, 80, 30);
               add( out);
        
                // Table의 Header 이름 지정
                String colName[] = { "학번", "이름" , "도서명" , "대여일자" };
             
                // Table에 들어갈 데이터 목록들(컬럼명, 추가될 Row 개수)
                model = new DefaultTableModel(colName , 0);        
                // Table 생성
                table = new JTable( model);       
                table.setPreferredScrollableViewportSize( new Dimension(470, 200));
          
               add( table);
                JScrollPane sp = new JScrollPane(table);
                sp.setBounds(10, 50, 450, 195);
               add( sp);

               
                // 종료 이벤트 처리
               addWindowListener( new WindowAdapter()
               {
                          public void windowClosing(WindowEvent e)
                          {
                                 try
                                 {
                                         if( conn != null)
                                         {
                                         conn.close();
                                         }
                                 }
                          catch (SQLException e1 )
                                 {
                                         e1.printStackTrace();
                                 }
                                
                                 JOptionPane.showMessageDialog( null, "Good Bye", "Exit Dialog" , JOptionPane.INFORMATION_MESSAGE );
                          }
               });

               
                // ----------------------------------------------------------------------------------
               
                // 익명 클래스 이용 (이벤트 처리) -> 목록 처리
                list.addActionListener( new ActionListener()
               {
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                              List(); // 목록 갱신 메소드 호출
                       }
               });
               
               
                // 익명 클래스 이용6 (이벤트 처리) -> 종료 처리
                out.addActionListener( new ActionListener()
               {      
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                               try
                                 {
                                         if( conn != null)
                                         {
                                         conn.close();
                                         }
                                 }
                                 catch (SQLException e1 )
                                 {
                                         e1.printStackTrace();
                                 }
                                               
                                 JOptionPane.showMessageDialog( null, "Good Bye", "Exit Dialog" , JOptionPane.INFORMATION_MESSAGE );
                                 dispose();
                       }
               });
               
               
                // ----------------------------------------------------------------------------------
               
                // setResizable(false); // 화면 크기 고정
               setSize(488, 300); // 화면 크기 설정
               setVisible( true); // 화면 출력 설정
               
               List(); // List 메소드 호출 -> 프레임이 출력되는 동시에 전체목록이 출력된다.
        }
        
         public void List()
        {
                try
               {
                        System. out.println("JDBC Connection Complete!");
                       
                       ResultSet rs = stmt.executeQuery( query);
                                      
                        // JTable 초기화
                        model.setNumRows(0);
                       
                        while(rs .next())
                       {
                               String[] row = new String[4]; // 컬럼의 개수가 4
                               row[0] = rs.getString( "id");
                               row[1] = rs.getString( "이름");
                               row[2] = rs.getString( "도서명");
                               row[3] = rs.getString( "대여일자" );
                              
                               model.addRow( row);
                       }
                        rs.close(); // ResultSet을 닫는다.
               }
                catch(Exception ex )
               {      
                        System. out.println(ex .getMessage());
               }
        }
        
         public static void main(String[] args)
        {
                new Swing04();
        }

}