3/19 BookRent

import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

public class BookRent extends JFrame{

 Connection conn;//연결객체
 
  Statement stmt;
  DefaultTableModel model;
  JTable table;
 
  String query;
 
  BookRent(){
  
  query="select s.id, s.name, b.title, br.rDate" +
    " from student s, books b, bookRent br" +
    " where br.id=s.id" +
    " and br.bookNo=b.no";
   
  
  // DB연결
  ResultSet rs = null;    // select한 결과를 저장하는 객체
        
  String url = null;      // 서버 url
  String uid = "hr";       // ID
  String pw = "1234";     // PW
          
  url ="jdbc:oracle:thin:@localhost:1521:orcl";
          
  try{
      Class.forName("oracle.jdbc.driver.OracleDriver");// jdbc driver load
           
      conn = DriverManager.getConnection(url,uid,pw);// 연결  
      stmt=conn.createStatement();
  }catch(Exception e){
      e.printStackTrace();
  }
    
     setTitle("학생관리");
     setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);//x버튼 누르면 프로그램 종료
    
     setLayout(null);//레이아웃설정. 레이아웃 사용 안함.
    
     JLabel l_dept=new JLabel("학과");
     l_dept.setBounds(10, 10, 30, 20);
     add(l_dept);
    
    
     String[] dept={"전체","전자공학과","컴퓨터공학과","보안학과"};
     JComboBox cb_dept=new JComboBox(dept);
     cb_dept.setBounds(45, 10, 100, 20);
     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, br.rDate" +
      " from student s, books b, bookRent br" +
      " where br.id=s.id" +
      " and br.bookNo=b.no";
      
       if(deptIndex==0){ // 전체
        // Select문 실행
        query += " order by br.no";
        list();
       }else if(deptIndex==1){ // 전자공학과     
        query += " and s.departmentId='EL' ";
        query += " order by br.no";
        list();
       }else if(deptIndex==2){ // 컴퓨터공학과
        query += " and s.departmentId='CO' ";
        query += " order by br.no";
        list();
       }else if(deptIndex==3){ // 보안학과
        query += " and s.departmentId='SC' ";
        query += " order by br.no";
        list();
       }
      }});
    
     String colName[]={"학번","이름","도서명","대출일"};
     model=new DefaultTableModel(colName,0);
     table = new JTable(model);
     table.setPreferredScrollableViewportSize(new Dimension(470,200));
     add(table);
     JScrollPane sp=new JScrollPane(table);
     sp.setBounds(10, 40, 460, 250);
     add(sp);
    
     JButton ListButton=new JButton("조회");
     ListButton.setBounds(10, 300, 70, 20);
     add(ListButton);
    
    
     //종료이벤트 처리.윈도우가 종료될 때 DB연결을 close함.
     addWindowListener(new WindowAdapter(){
      public void windowClosing(WindowEvent e){
       try {
        if(conn!=null){
         conn.close(); // 연결종료.
        }
       } catch (SQLException e1) {
        e1.printStackTrace();
       }
      }
     });
    
    
     //목록버튼 이벤트 처리 
     ListButton.addActionListener(new ActionListener(){

      @Override
      public void actionPerformed(ActionEvent e) {
       list();//목록출력  
      }});
     
    
     //setResizable(false);//화면크기고정
     setSize(490, 400);//화면크기
     setVisible(true);
     list();
    }
      
    public void list(){
     try{
      System.out.println("연결되었습니다.....");
      System.out.println(query);       
      // Select문 실행     
      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("name");
       row[2]=rs.getString("title");
       row[3]=rs.getString("rDate");
       model.addRow(row);
      }
      rs.close();
     
     }
     catch(Exception e1){
      //e.getStackTrace();
      System.out.println(e1.getMessage());
     }
  }

 public static void main(String[] args) {
  new BookRent();

 }

}