상세 컨텐츠

본문 제목

JAVA - SQL 테이블 연결 예제

DATA/SQL-Transform

by 에스프리터 2018. 1. 23. 17:23

본문

728x90
package pack.db2;

import java.awt.GridLayout;
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.PreparedStatement;
import java.sql.ResultSet;

import javax.swing.JButton;
import javax.swing.JDialog;
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.table.DefaultTableModel;

public class DbTest13CRD extends JFrame implements ActionListener {
    private JButton btnInsert, btnDelte, btnExit;
    private String[][] datas = new String[0][5];
    private String[] title = { "코드", "상품명", "수량", "단가", "금액" };
    private DefaultTableModel model = new DefaultTableModel(datas, title);
    private JTable table = new JTable(model);
    private JLabel lblCount = new JLabel("개수: + 0");

    private Connection conn;
    private PreparedStatement pstmt;
    private ResultSet rs;

    public DbTest13CRD() {

        initLayout();

        accDb();

        setTitle("상품처리");
        setResizable(false); // 사이즈 조절 못하게
        setBounds(200, 200, 300, 250);
        setVisible(true);

        addWindowListener(new WindowAdapter() {
            @Override
            public void windowClosing(WindowEvent arg0) {
                int re = JOptionPane.showConfirmDialog(DbTest13CRD.this, "정말 종료할까요?", "종료",
                        JOptionPane.OK_CANCEL_OPTION);

                if (re == JOptionPane.OK_OPTION) {
                    System.exit(0);
                } else {
                    setDefaultCloseOperation(DO_NOTHING_ON_CLOSE);
                }
            }
        });
    }

    private void initLayout() {
        btnInsert = new JButton("추가");
        btnDelte = new JButton("삭제");
        btnExit = new JButton("종료");
        btnInsert.addActionListener(this);
        btnDelte.addActionListener(this);
        btnExit.addActionListener(this);
        JPanel panel = new JPanel();
        panel.add(btnInsert);
        panel.add(btnDelte);
        panel.add(btnExit);
        add("North", panel);

        // 테이블의 열폭 조절
        table.getColumnModel().getColumn(0).setPreferredWidth(30);
        table.getColumnModel().getColumn(1).setPreferredWidth(100);
        JScrollPane scrollPane = new JScrollPane(table);
        add("Center", scrollPane);
        add("South", lblCount);
    }

    private void accDb() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }catch (Exception e) {
            System.out.println("accDb : " + e);
            return;
        }
        
        displayData();
    }

    private void displayData() {
        model.setNumRows(0);
        try {
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
            String sql = "select * from sangdata order by code desc";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            int count = 0;

            while (rs.next()) {
                String[] imsi = { rs.getString("code"), rs.getString("sang"), rs.getString("su"), rs.getString("dan"),
                        Integer.toString(rs.getInt("su") * rs.getInt("dan")) };
                model.addRow(imsi);
                count++;
            }
            lblCount.setText("개수 :" + count);
        } catch (Exception e) {
            System.out.println("에러 발생 : " + e);
        } finally {
            try {

                if (rs != null)
                    rs.close();
                if (pstmt != null)
                    pstmt.close();
                if (conn != null)
                    conn.close();
            } catch (Exception e2) {

            }
        }
    }

    @Override
    public void actionPerformed(ActionEvent arg0) {
        if(arg0.getSource() == btnInsert) { // 상품추가
            InsertForm insertForm = new InsertForm(this);
            displayData(); // 추가 후 자료 refresh
            
    }else if(arg0.getSource() == btnDelte) { // 상품삭제
        String delNo = JOptionPane.showInputDialog(this,"삭제할 코드 입력");
        System.out.println(delNo);
        int re = JOptionPane.showConfirmDialog(this, delNo + "삭제하시겠습니까?", "삭제", JOptionPane.OK_CANCEL_OPTION);
        if(re == JOptionPane.CANCEL_OPTION) return;// 삭제 취소
        
        try {
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
            String sql = "delete from sangdata where code=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, delNo);
            if(pstmt.executeUpdate() == 0) {
                JOptionPane.showConfirmDialog(this, delNo + "번은 등록된 자료가 아닙니다"    );
            }
            displayData(); // 삭제 후 자료 보기
            
        } catch (Exception e) {
            System.out.println("삭제 오류 :" + e);
        }    finally {
            try {
                if (rs != null)
                    rs.close();
                if (conn != null)
                    conn.close();
            } catch (Exception e2) {
                // TODO: handle exception
            }
        }        
        
    }else if(arg0.getSource() == btnExit) { // 종료
        int re = JOptionPane.showConfirmDialog(DbTest13CRD.this, "정말 종료할까요?", "종료",
                JOptionPane.OK_CANCEL_OPTION);

        if (re == JOptionPane.OK_OPTION) {
            System.exit(0);
        } else {
            setDefaultCloseOperation(DO_NOTHING_ON_CLOSE);
        }
    }
    }

    // 상품 추가를 위한 내부 클래스 생성
    class InsertForm extends JDialog implements ActionListener{
        JTextField txtSang = new JTextField();
        JTextField txtSu = new JTextField();
        JTextField txtDan = new JTextField();
        JButton btnOk = new JButton("등록");
        JButton btnCancel = new JButton("취소");
        
        public InsertForm(JFrame frame) {
            super(frame, "상품추가");
            setModal(true);
        
            // 추가 화면 디자인
            JPanel pn1 = new JPanel(new GridLayout(4,2));
            pn1.add(new JLabel("품명"));
            pn1.add(txtSang); // 여기까지가 1행
            pn1.add(new JLabel("수량"));
            pn1.add(txtSu);
            pn1.add(new JLabel("단가"));
            pn1.add(txtDan);
            pn1.add(btnOk);
            btnOk.addActionListener(this);
            btnCancel.addActionListener(this);
            
            add("North", new JLabel("--자료 입력 --"));
            add("Center", pn1);
            
            setBounds(210,210,150,150);
            setVisible(true);
            
            addWindowListener(new WindowAdapter() {
                @Override
                public void WindowClosing(WindowEvent arg0) {
                    dispose();
                }
            });
            
        }

        @Override
        public void actionPerformed(ActionEvent arg0) {
            if(arg0.getSource() == btnOk) { // 신상품 추가
            // 입력자료 검사
            if(txtSang.getText().equals("")) {
                JOptionPane.showMessageDialog(this, "상품명 입력!");
                txtSang.requestFocus();
                return;
            }
            if(txtSu.getText().equals("")) {
                JOptionPane.showMessageDialog(this, "수량 입력!");
                txtSang.requestFocus();
                return;
            }
            if(txtDan.getText().equals("")) {
                JOptionPane.showMessageDialog(this, "단가 입력!");
                txtSang.requestFocus();
                return;
            }    
            
            //수량과 단가는 숫자만 허용. try~ , ascii code, 정규 표현식...
            int su = 0;
            try {
                su = Integer.parseInt(txtSu.getText());
            } catch (Exception e) {
                JOptionPane.showMessageDialog(this, "수량은 숫자만 가능");
                txtSu.requestFocus();
                return;
            }
            
            int dan = 0;
            try {
                su = Integer.parseInt(txtSu.getText());
            } catch (Exception e) {
                JOptionPane.showMessageDialog(this, "수량은 숫자만 가능");
                txtSu.requestFocus();
                return;
            }

                // 등록이 가능한 상태----
            try {
                conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
                
                // 새상품 코드 구하기
                int new_code = 0;
                String sql = "select max(code) from sangdata";
                pstmt = conn.prepareStatement(sql);
                rs = pstmt.executeQuery();
                if(rs.next()) {
                    new_code = rs.getInt(1);
                }
                //System.out.println(new_code +1);
                sql ="insert into sangdata values(?,?,?,?)";
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, new_code + 1);
                pstmt.setString(2, txtSang.getText().trim()); // " 홍 길동 "
                pstmt.setInt(3, su);
                pstmt.setInt(4, dan);
                if(pstmt.executeUpdate() > 0) {
                    JOptionPane.showMessageDialog(this, "등록성공");
                }else {
                    JOptionPane.showConfirmDialog(this, "헐, 실패");
                }                
                
                
                // 입력자료 초기화
                txtSang.setText(null);
                txtSu.setText("");
                txtDan.setText("");
                txtSang.setText("");
                txtSang.requestFocus();
                                
            } catch (Exception e) {
                System.out.println("신상품 등록 실패 : " + e);
                }finally {
                    try {
                        if (rs != null)
                            rs.close();
                        if (conn != null)
                            conn.close();
                    } catch (Exception e2) {
                        // TODO: handle exception
                    }
                }
            
            
            
            }else if(arg0.getSource() == btnCancel)    { //작업 취소
                dispose();
                        
        }
        }
    }
    
    public static void main(String[] args) {
        new DbTest13CRD();
    }
}





본 포스팅은 IT 교육기관인 KIC 캠퍼스의 지원을 받은 리포트입니다.
혹시 잘못되거나 문제 소지시 댓글 남겨주시면 조치하겠습니다.



태그

관련글 더보기

댓글 영역