08. GUI사용해서 DB연동 예제

이런 GUI를 만들 거임.

전체목록을 누르면 DB의 STUDENT 테이블의 내용을 전체출력 해줌.

텍스트필드에 내용을 채우고 학생추가를 누르면 데이터가 추가됨.

학번 이름 제외한 컬럼은 학생수정을 눌러서 수정가능함.

JTable의 행을 하나 클릭하고 학생삭제를 누르면 데이터를 삭제할 수 있음.

 

**DefaultTableModel

-- 테이블을 만들고 난 뒤에 데이터를 추가, 수정, 삭제가 가능함.

-- JTable만으로는 한 번 객체를 생성하고 난 뒤에 수정하고 싶으면 새로운 객체를 생성해야 함.

package gui_sql;
 
import java.awt.BorderLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.ScrollPaneConstants;
import javax.swing.table.DefaultTableModel;
 
public class STUDENT extends JFrame {
    
    //메서드에서 호출해야 되는 것들은 멤버 변수로 미리 선언해둠.
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    DefaultTableModel model;
    JTextField jtf1, jtf2, jtf3, jtf4, jtf5, jtf6, jtf7;
    JTable table;
    
    public STUDENT() {
        setTitle("학생 테이블");
        JPanel container1 = new JPanel();
        JPanel container2 = new JPanel();
        JPanel container3 = new JPanel();
        
        JLabel label1 = new JLabel("학번: ");
        jtf1 = new JTextField(8);
        JLabel label2 = new JLabel("이름: ");
        jtf2 = new JTextField(5);
        JLabel label3 = new JLabel("학과: ");
        jtf3 = new JTextField(10);
        JLabel label4 = new JLabel("학년: ");
        jtf4 = new JTextField(2);
        JLabel label5 = new JLabel("나이: ");
        jtf5 = new JTextField(3);
        JLabel label6 = new JLabel("연락처: ");
        jtf6 = new JTextField(15);
        JLabel label7 = new JLabel("주소: ");
        jtf7 = new JTextField(8);
        
        String[] header = {"학번", "이름", "학과", "학년", "나이", "연락처", "주소"};
        model = new DefaultTableModel(header, 0); //DefaultTableModel(Object columnNames, int rowCount)
        table = new JTable(model);
        JScrollPane jsp = new JScrollPane(table, 
                ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED, 
                ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);
        
        JButton jb1 = new JButton("전체목록");
        JButton jb2 = new JButton("학생추가");
        JButton jb3 = new JButton("학생수정");
        JButton jb4 = new JButton("학생삭제");
        
        container1.add(label1); container1.add(jtf1);
        container1.add(label2); container1.add(jtf2);
        container1.add(label3); container1.add(jtf3);
        container1.add(label4); container1.add(jtf4);
        container2.add(label5); container2.add(jtf5);
        container2.add(label6); container2.add(jtf6);
        container2.add(label7); container2.add(jtf7);
        JPanel group = new JPanel(new BorderLayout());
        group.add(container1, BorderLayout.NORTH); 
        group.add(container2, BorderLayout.CENTER);
        
        container3.add(jb1); container3.add(jb2);
        container3.add(jb3); container3.add(jb4);        
        
        add(group, BorderLayout.NORTH);
        add(jsp, BorderLayout.CENTER);
        add(container3, BorderLayout.SOUTH);
        setBounds(200, 200, 700, 400);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setVisible(true);
        pack();
        
        jb1.addActionListener(new ActionListener() {
            
            @Override
            public void actionPerformed(ActionEvent e) {
                connect();
                model.setRowCount(0); 
                //Sets the number of rows in the model. 지정한 숫자가 만약 현재 행 개수 보다 크면 행을 추가해주고, 
                //작으면 지정한 숫자보다 큰 행은 다 지워버림. 0을 입력했으니 컬렴명 행만 남고 깨끗해짐.
                select();
            }
        });
        
        jb2.addActionListener(new ActionListener() {
            
            @Override
            public void actionPerformed(ActionEvent e) {
                connect();
                insert();
                
                jtf1.setText(null); jtf2.setText(null); jtf3.setText(null); 
                jtf4.setText(null); jtf5.setText(null); jtf6.setText(null);
                jtf7.setText(null); jtf1.requestFocus();
                
                model.setRowCount(0);                
                select();                
            }
        });
        
        jb3.addActionListener(new ActionListener() {
            
            @Override
            public void actionPerformed(ActionEvent e) {
                connect();
                update();
                
                jtf1.setText(null); jtf2.setText(null); jtf3.setText(null); 
                jtf4.setText(null); jtf5.setText(null); jtf6.setText(null);
                jtf7.setText(null); jtf1.requestFocus();
                
                model.setRowCount(0);                
                select();                
            }
        });
        
        jb4.addActionListener(new ActionListener() {
            
            @Override
            public void actionPerformed(ActionEvent e) {
                int result = JOptionPane.showConfirmDialog(null, "정말로 삭제하시겠습니까?", 
                        "확인", JOptionPane.YES_NO_OPTION);
                if(result == JOptionPane.CANCEL_OPTION) {
                    JOptionPane.showMessageDialog(null, "삭제 취소");
                }else if(result == JOptionPane.YES_OPTION) {
                    delete();
                }                
            }
        });        
    }
    
    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();
        }        
    }
    
    void select() {        
        try {
            String sql = "select * from student order by hakbun";
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                String hakbun = rs.getString("hakbun");
                String name = rs.getString("name");
                String major = rs.getString("major");
                int year = rs.getInt("year");
                int age = rs.getInt("age");
                String phone = rs.getString("phone");
                String addr = rs.getString("addr");
                
                Object[] data = {hakbun, name, major, year, age, phone, addr};
                model.addRow(data);
            }
            rs.close(); pstmt.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }        
    }
    
    void insert() {
        
        try {
            String sql = "insert into student(hakbun, name, major, year, age, phone, addr, regdate) "
                    + "values(?, ?, ?, ?, ?, ?, ?, sysdate)";
            pstmt = con.prepareStatement(sql);
            String hakbun = jtf1.getText();
            String name = jtf2.getText();
            String major = jtf3.getText();
            int year = Integer.parseInt(jtf4.getText());
            int age = Integer.parseInt(jtf5.getText());
            String phone = jtf6.getText();
            String addr = jtf7.getText();
            pstmt.setString(1, hakbun);
            pstmt.setString(2, name);
            pstmt.setString(3, major);
            pstmt.setInt(4, year);
            pstmt.setInt(5, age);
            pstmt.setString(6, phone);
            pstmt.setString(7, addr);
            
            int res = pstmt.executeUpdate();
            
            if(res > 0) {
                JOptionPane.showMessageDialog(null, "학생 추가 성공!!");
            }else {
                JOptionPane.showMessageDialog(null, "학생 추가 실패...");
            }
            
            pstmt.close();
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    void update() {
        
        try {
            String sql = "update student set major = ?, year = ?, age = ?, phone = ?, "
                    + "addr = ?  where hakbun = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, jtf3.getText());
            pstmt.setInt(2, Integer.parseInt(jtf4.getText()));
            pstmt.setInt(3, Integer.parseInt(jtf5.getText()));
            pstmt.setString(4, jtf6.getText());
            pstmt.setString(5, jtf7.getText());
            pstmt.setString(6, jtf1.getText());
            
            int res = pstmt.executeUpdate();
            if(res > 0) {
                JOptionPane.showMessageDialog(null, "학생정보 수정 성공!!");
            }else {
                JOptionPane.showMessageDialog(null, "학생정보 수정 실패...");
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    void delete() {
        
        try {
            String sql = "delete from student where hakbun = ?";
            pstmt = con.prepareStatement(sql);
            int row = table.getSelectedRow();
            pstmt.setString(1, (String)model.getValueAt(row, 0));
            
            int res = pstmt.executeUpdate();
            if(res > 0) {
                JOptionPane.showMessageDialog(null, "학생정보 삭제 성공!!");
            }else {
                JOptionPane.showMessageDialog(null, "학생정보 삭제 실패...");
            }
            
            model.removeRow(row);
            pstmt.close();
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
 
    public static void main(String[] args) {
        new STUDENT();
 
    }
 
}