jdbc03 new

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 Jdbc03 extends JFrame {

Connection conn;//연결객체
 
 Statement stmt;
 DefaultTableModel model;
 JTable table;
 
 String query;
 
 Jdbc03(){
  
  query="select student.no,student.name,bookrent.bookno, bookrent.rdate "
      +" from student join bookrent "
      +" on student.no=bookrent.sno";
   
  
    // DB연결 
          ResultSet rs = null;    // select한 결과를 저장하는 객체
       
          String url = null;      // 서버 url
          String uid = "ora_user";       // ID
          String pw = "hong";     // 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 student.no,student.name,bookrent.bookno, bookrent.rdate "
         +" from student join bookrent "
         +" on student.no=bookrent.sno";
      
      if(deptIndex==0){ // 전체
       // Select문 실행
       query += " order by bookrent.bno";
       list();
      }else if(deptIndex==1){ // 컴퓨터시스템      
       query += " and student.dept_id='A001' ";
       query += " order by bookrent.bno";
       list();
      }else if(deptIndex==2){ // 멀티미디어
       query += " and student.dept_id='A002' ";
       query += " order by bookrent.bno";
       list();
      }else if(deptIndex==3){ // 컴퓨터공학
       query += " and student.dept_id='A003' ";
       query += " order by bookrent.bno";
       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("no");
      row[1]=rs.getString("name");
      row[2]=rs.getString("bookno");
      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 Jdbc03();

 }
}