09. GUI + SQL (JComboBox 쓰는 예제)

이번에는 이런식으로 콤보박스를 활용해서 만들어봄.

콤보박스에서 선택한 것을 어떻게 가져올지.

가져온 것에서 문자열 추출은 어떻게 할지.

가 중요한 부분.

package guiSql2;
 
import java.awt.BorderLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;
import javax.swing.*;
 
public class EMP extends JFrame {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    JTable table;
    DefaultTableModel model;
    JComboBox<String> job, mgr, deptno;        //JComboBox를 활용한 예제라는 점.
    JTextField empno, ename, sal, comm;
    
    public EMP() {
        setTitle("사원 관리 화면");
        JPanel container1 = new JPanel();
        JPanel container2 = new JPanel();
        JPanel container3 = new JPanel();
        
        //1. 컴포넌트들을 만들어 보자.
        //1-1 상단1 컨테이너 컴포넌트        
        JLabel label1 = new JLabel("사번: ");
        empno = new JTextField(4);
        JLabel label2 = new JLabel("이름: ");
        ename = new JTextField(10);
        JLabel label3 = new JLabel("담당업무: ");
        job = new JComboBox<String>();
        job.addItem("선택");
        
        //1-2 상단2 컨테이너 컴포넌트
        JLabel label4 = new JLabel("관리자No.: ");
        mgr = new JComboBox<String>();
        mgr.addItem("선택");
        JLabel label5 = new JLabel("급여: ");
        sal = new JTextField(5);
        JLabel label6 = new JLabel("보너스: ");
        comm = new JTextField(5);
        JLabel label7 = new JLabel("부서번호: ");
        deptno = new JComboBox<String>();
        deptno.addItem("선택");
        
        //1-3 중앙 컴포넌트
        String[] header = {"사번", "이름",  "담당업무", "관리자No.", "입사일", "급여", "보너스", "부서번호"};
        model = new DefaultTableModel(header, 0);
        table = new JTable(model);
        JScrollPane jsp = new JScrollPane(table, 
                ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED, ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);
        
        //1-4 하단 컨테이너 컴포넌트
        JButton list_button    = new JButton("사원 목록");
        JButton insert_button    = new JButton("사원 추가");
        JButton update_button    = new JButton("사원 수정");
        JButton del_button    = new JButton("사원 삭제");
        
        //2. 컨테이너에 컴포넌트 올리기
        //2-1 상단1 컨테이너에 1-1 컴포넌트 올리기
        container1.add(label1); container1.add(empno);
        container1.add(label2); container1.add(ename);
        container1.add(label3); container1.add(job);
        
        //2-2 상단2 컨테이너에 1-2 컴포넌트 올리기
        container2.add(label4); container2.add(mgr);
        container2.add(label5); container2.add(sal);
        container2.add(label6);    container2.add(comm);
        container2.add(label7); container2.add(deptno);
        
        //2-3 하단 컨테이너에 1-4 컴포넌트 올리기
        container3.add(list_button); container3.add(insert_button);
        container3.add(update_button);    container3.add(del_button);
        
        //새로운 컨테이너 생성 후 상단 컨테이너 그룹화
        JPanel group = new JPanel(new BorderLayout());
        group.add(container1, BorderLayout.NORTH); group.add(container2, BorderLayout.CENTER);
        
        //3. 프레임에 올리기
        add(group, BorderLayout.NORTH);
        add(jsp, BorderLayout.CENTER);
        add(container3, BorderLayout.SOUTH);
        
        //4. 담당업무, 관리자NO., 부서번호 화면에 띄우기
        connect();
        comboJob();
        comboMgr();
        comboDept();        
        
        setBounds(200, 200, 800, 500);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setVisible(true);
        
        //5. 이벤트 처리
        list_button.addActionListener(new ActionListener() {
            
            @Override
            public void actionPerformed(ActionEvent e) {
                connect();
                model.setRowCount(0);
                select();
            }
        });
        
        insert_button.addActionListener(new ActionListener() {
            
            @Override
            public void actionPerformed(ActionEvent e) {
                connect();
                insert();
                
                empno.setText(null); ename.setText(null);
                sal.setText(null); comm.setText(null);
                job.setSelectedIndex(0); //콤보박스를 초기화하는 방법.
                mgr.setSelectedIndex(0);
                deptno.setSelectedIndex(0);
                empno.requestFocus();
                
                model.setRowCount(0);
                select();
                
            }
        });
        
        update_button.addActionListener(new ActionListener() {
            
            @Override
            public void actionPerformed(ActionEvent e) {
                connect();
                update();
                
                empno.setText(null); ename.setText(null);
                sal.setText(null); comm.setText(null);
                job.setSelectedIndex(0);
                mgr.setSelectedIndex(0);
                deptno.setSelectedIndex(0);
                empno.requestFocus();
                
                model.setRowCount(0);
                select();
            }
        });
        
        del_button.addActionListener(new ActionListener() {
            
            @Override
            public void actionPerformed(ActionEvent e) {
                int result = JOptionPane.showConfirmDialog(null, 
                        "정말 삭제하시겠습니까?", "확인", JOptionPane.YES_NO_OPTION);
                if(result == JOptionPane.CLOSED_OPTION) {
                    JOptionPane.showMessageDialog(null, "취소를 누르셨습니다.");
                }else if(result == JOptionPane.YES_OPTION) {
                    connect();
                    delete();
                }else {
                    JOptionPane.showMessageDialog(null, "삭제 취소 버튼을 누르셨습니다.");
                }
            }
            
        });
    }
    
    void connect() {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "web";
        String password = "1234";
        
        try {            
            Class.forName(driver);
            con = DriverManager.getConnection(url, user, password);            
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    //emp 테이블에서 담당업무를 조회하는 메서드
    void comboJob() {        
                
        try {
            //1. DB에 전송할 쿼리문
            sql = "select distinct(job) from emp order by job";
            //2. sql 전송
            pstmt = con.prepareStatement(sql);
            //3. 실제 DB 상에서 쿼리문 실행
            rs = pstmt.executeQuery();
            while(rs.next()) {
                String job_list = rs.getString("job");
               job.addItem(job_list); //addItem() 메소드를 이용해서 콤보박스에 선택지 추가
            }
            
            rs.close(); pstmt.close();
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    //emp 테이블에서 관리자가 "manager"인 사원들을 조회하는 메서드
    void comboMgr() {
        
        try {
            sql = "select * from emp where job = ? order by empno";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, "MANAGER");
            rs = pstmt.executeQuery();
            while(rs.next()) {
                int empno_list = rs.getInt("empno");
                String ename_list = rs.getString("ename");
                mgr.addItem(empno_list + "[" + ename_list + "]");
            }
            
            rs.close(); pstmt.close();
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    //dept 테이블의 부서번호를 조회하는 메서드.
    void comboDept() {
        
        try {
            sql = "select * from dept order by deptno";
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                int deptno_list = rs.getInt("deptno");
                String dname = rs.getString("dname");
                deptno.addItem(deptno_list + "[" + dname + "]");
            }
            
            rs.close(); pstmt.close(); con.close();
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    void select() {
        
        try {
            sql = "select * from emp order by empno";
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                int empno_list = rs.getInt("empno");
                String ename_list = rs.getString("ename");
                String job_list = rs.getString("job");
                int mgr_list = rs.getInt("mgr");
                String hd_list = rs.getString("hiredate").substring(0, 10);
                int sal_list = rs.getInt("sal");
                int comm_list = rs.getInt("comm");
                int deptno_list = rs.getInt("deptno");
                Object[] data = {empno_list, ename_list, job_list, mgr_list, hd_list, sal_list, comm_list, deptno_list};
                model.addRow(data);                
            }
            
            rs.close(); pstmt.close(); con.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    void insert() {
        try {
            sql = "insert into emp values(?, ?, ?, ?, sysdate, ?, ?, ?)";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, Integer.parseInt(empno.getText()));
            pstmt.setString(2, ename.getText());
            pstmt.setString(3, (String)job.getSelectedItem());  //getSelectedItem()은 Object로 반환되기 때문에 (String)으로 형변환
            pstmt.setInt(4, Integer.parseInt(mgr.getSelectedItem().toString().substring(0, 4)));//getSelectedItem으로 가져온 값을 toString으로 형변환하고 substring()으로 앞자리4개 추출후//Integer.parseInt로 int타입으로 형변환.
            pstmt.setInt(5, Integer.parseInt(sal.getText()));
            pstmt.setInt(6, Integer.parseInt(comm.getText()));
            pstmt.setInt(7, Integer.parseInt(deptno.getSelectedItem().toString().substring(0, 2)));
            int result = pstmt.executeUpdate();
            if(result > 0) {
                JOptionPane.showMessageDialog(null, "사원 추가 성공!!");
            }else {
                JOptionPane.showMessageDialog(null, "사원 추가 실패...");
            }
            
            pstmt.close();
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    void update() {
        try {
            sql = "update emp set job = ?, mgr = ?, sal = ?, comm = ?, deptno = ? where empno = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, job.getSelectedItem().toString());
            pstmt.setInt(2, Integer.parseInt(mgr.getSelectedItem().toString().substring(0,4)));
            pstmt.setInt(3, Integer.parseInt(sal.getText()));
            pstmt.setInt(4, Integer.parseInt(comm.getText()));
            pstmt.setInt(5, Integer.parseInt(deptno.getSelectedItem().toString().substring(0,2)));
            pstmt.setInt(6, Integer.parseInt(empno.getText()));
            int result = pstmt.executeUpdate();
            if(result > 0) {
                JOptionPane.showMessageDialog(null, "사원 정보수정 완료!!!");
            }else {
                JOptionPane.showMessageDialog(null, "사원 정보수정 실패...");
            }
            
            pstmt.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    void delete() {
        try {
            sql = "delete from emp where empno = ?";
            pstmt = con.prepareStatement(sql);
            int row = table.getSelectedRow();
            pstmt.setInt(1, (int)(model.getValueAt(row, 0)));
            
            int result = pstmt.executeUpdate();
            if(result > 0) {
                JOptionPane.showMessageDialog(null, "사원정보 삭제 성공!!!");
            }else {
                JOptionPane.showMessageDialog(null, "사원정보 삭제 실패...");
            }
            
            model.removeRow(row);
            pstmt.close(); con.close();
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
 
    public static void main(String[] args) {
        new EMP();
    }
 
}