JDBC : 데이터베이스에서 우편번호 가져오기(테이블에 출력)

DAO Class

//zipcode DAO

public class zipDAO {

        private String seq;

        private String zipcode;

        private String sido;

        private String gugun;

        private String dong;

        private String ri;

        private String st_bunji;

        private String ed_bunji;

       

       

        public String getSeq() {

               return seq;

        }

        public void setSeq(String seq) {

               this.seq = seq;

        }

        public String getZipcode() {

               return zipcode;

        }

        public void setZipcode(String zipcode) {

               this.zipcode = zipcode;

        }

        public String getSido() {

               return sido;

        }

        public void setSido(String sido) {

               this.sido = sido;

        }

        public String getGugun() {

               return gugun;

        }

        public void setGugun(String gugun) {

               this.gugun = gugun;

        }

        public String getDong() {

               return dong;

        }

        public void setDong(String dong) {

               this.dong = dong;

        }

        public String getRi() {

               return ri;

        }

        public void setRi(String ri) {

               this.ri = ri;

        }

        public String getSt_bunji() {

               return st_bunji;

        }

        public void setSt_bunji(String st_bunji) {

               this.st_bunji = st_bunji;

        }

        public String getEd_bunji() {

               return ed_bunji;

        }

        public void setEd_bunji(String ed_bunji) {

               this.ed_bunji = ed_bunji;

        }

       

       

}

 

 

 

 

 

 

 

Table 입력 Class

import javax.swing.table.AbstractTableModel;

 

public class zipTable extends AbstractTableModel {

        //컬럼의 이름

        String[] columNames =

{"일련번호","우편번호","시.도","구.군","동","리","시작번지","마지막번지"};

        //데이터

        Object[][] data = {{" ", " "," "," "," "," "," "," "}};

       

       

       

        public zipTable(){

              

        }

 

        public zipTable(Object[][] data) {

               this.data = data;

        }

 

        @Override

        public int getColumnCount() {

               // TODO Auto-generated method stub

               return columNames.length;

        }

 

        @Override

        public int getRowCount() {

               // TODO Auto-generated method stub

               return data.length;           //2차 배열의 길이

        }

 

        @Override

        public Object getValueAt(int arg0, int arg1) {

               // TODO Auto-generated method stub

               return data[arg0][arg1];

        }

 

        @Override

        public String getColumnName(int arg0) {

               // TODO Auto-generated method stub

               return columNames[arg0];

        }

}

 

 

 

 

Control Class

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

 

public class zipControl {

         Connection conn;

         PreparedStatement pstmt;

         ResultSet rs;

        

        

         // 데이터베이스 연결

         public void connection() {

                  try {

                           Class.forName("oracle.jdbc.driver.OracleDriver");

                           conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr");

                          

                  } catch (ClassNotFoundException e) {

                  } catch (SQLException e) {

                  }

         }

        

         // 데이터베이스 연결종료

         public void disconnection() {

                  try {

                           if(pstmt != null) pstmt.close();

                           if(rs != null) rs.close();

                           if(conn != null) conn.close();

                  } catch (SQLException e) {

                  }

         }

        

         // 시도데이터=============================================

         public ArrayList<zipDAO> searchSido() {

                  ArrayList<zipDAO> sidoList = new ArrayList<>();

                  try {

                           String query = "select distinct(sido) from zipcode";

                           pstmt = conn.prepareStatement(query);

                           rs = pstmt.executeQuery();

                           while(rs.next()){

                                   zipDAO zipcode = new zipDAO();

                                   zipcode.setSido(rs.getString("SIDO"));

                                   sidoList.add(zipcode);

                           }

                  } catch (SQLException e) {

                  }

 

                  return sidoList;

                 

         }

        

         // 구군데이터=============================================

         public ArrayList<zipDAO> searchGugun(String sido) {

                  ArrayList<zipDAO> gugunList = new ArrayList<>();

                 

                  try {

                           String query = "select distinct(gugun) from zipcode where sido = \'" + sido + "\' ";

                           pstmt = conn.prepareStatement(query);

                           rs = pstmt.executeQuery();

                           while(rs.next()){

                                   zipDAO zipcode = new zipDAO();

                                   zipcode.setGugun(rs.getString("GUGUN"));

                                   gugunList.add(zipcode);

                           }

                  } catch (SQLException e) {

                  }

                                  

                  return gugunList;         

         }

        

         // 동데이터=============================================

         public ArrayList<zipDAO> searchDong(String sido, String gugun) {

                  ArrayList<zipDAO> dongList = new ArrayList<>();

                 

                  try {

                           String query = "select distinct(dong) from zipcode where sido = \'" + sido + "\'  and gugun = \'" + gugun + "\'";

                           pstmt = conn.prepareStatement(query);

                           rs = pstmt.executeQuery();

                           while(rs.next()){

                                   zipDAO zipcode = new zipDAO();

                                   zipcode.setDong(rs.getString("DONG"));

                                   dongList.add(zipcode);

                           }

                  } catch (SQLException e) {

                  }

                 

                 

                  return dongList;          

         }

        

         // 전부주소 데이터 =============================================

         public ArrayList<zipDAO> searchAddress(String sido, String gugun, String dong) {

                  ArrayList<zipDAO> addressList = new ArrayList<>();

                 

                 

                  try {

                           String query = "select * from zipcode where sido = \'" + sido + "\'  and gugun = \'" + gugun + "\' and dong = \'" + dong +"\'";

                           pstmt = conn.prepareStatement(query);

                           rs = pstmt.executeQuery();

                           while(rs.next()){

                                   zipDAO zipcode = new zipDAO();

                                   zipcode.setSeq(rs.getString("seq"));

                                   zipcode.setZipcode(rs.getString("zipcode"));

                                   zipcode.setSido(rs.getString("sido"));

                                   zipcode.setGugun(rs.getString("gugun"));

                                   zipcode.setDong(rs.getString("dong"));

                                   zipcode.setRi(rs.getString("ri"));

                                   zipcode.setSt_bunji(rs.getString("st_bunji"));

                                   zipcode.setEd_bunji(rs.getString("ed_bunji"));

                                   addressList.add(zipcode);

                           }

                  } catch (SQLException e) {

                  }

                 

                 

                  return addressList;               

         }

}

 

 

 

 

 

 

실행 Class

import java.awt.BorderLayout;

import java.awt.EventQueue;

import javax.swing.JFrame;

import javax.swing.JPanel;

import javax.swing.border.EmptyBorder;

import javax.swing.JList;

import javax.swing.JComboBox;

import javax.swing.JLabel;

import javax.swing.SwingConstants;

import javax.swing.JTable;

import javax.swing.DefaultComboBoxModel;

import java.awt.event.MouseAdapter;

import java.awt.event.MouseEvent;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.awt.event.ActionListener;

import java.awt.event.ActionEvent;

import java.awt.event.ItemListener;

import java.awt.event.ItemEvent;

import javax.swing.JScrollPane;

import javax.swing.table.DefaultTableModel;

import javax.swing.border.TitledBorder;

import oracle.net.jdbc.TNSAddress.AddressList;

 

public class zipSearch extends JFrame {

 

        private JPanel contentPane;

        private JTable table;

        private JComboBox comboBox;

        private JComboBox comboBox_1;

        private JComboBox comboBox_2;

       

        private Connection conn = null;

        private PreparedStatement pstmt = null;      

        private ResultSet rs = null;         

        private JScrollPane scrollPane;

        private JPanel panel;

       

        /**

         * Launch the application.

         */

        public static void main(String[] args) {    

               EventQueue.invokeLater(new Runnable() {

                       public void run() {

                              try {

                                      zipSearch frame = new zipSearch();

                                      frame.setVisible(true);

                              } catch (Exception e) {

                                      e.printStackTrace();

                              }

                       }

               });

        }

 

        /**

         * Create the frame.

         */

        public zipSearch() {

              

               setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

               setBounds(100, 100, 628, 515);

               contentPane = new JPanel();

               contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));

               setContentPane(contentPane);

               contentPane.setLayout(null);

              

               panel = new JPanel();

               panel.setBorder(new TitledBorder(null, "우편번호 검색", TitledBorder.LEADING, TitledBorder.TOP, null, null));

               panel.setBounds(6, 22, 594, 70);

               contentPane.add(panel);

               panel.setLayout(null);

              

               scrollPane = new JScrollPane();

               scrollPane.setBounds(12, 111, 588, 356);

               contentPane.add(scrollPane);

              

               table = new JTable();

               table.setModel(new DefaultTableModel(

                       new Object[][] {

                              {" ", " ", " ", " ", " ", " ", " ", " "},

                       },

                       new String[] {

                              "\uC77C\uB828\uBC88\uD638", "\uC6B0\uD3B8\uBC88\uD638", "\uC2DC.\uB3C4", "\uAD6C.\uAD70", "\uB3D9", "\uB9AC", "\uC2DC\uC791\uBC88\uC9C0", "\uB9C8\uC9C0\uB9C9\uBC88\uC9C0"

                       }

               ) {

                       boolean[] columnEditables = new boolean[] {

                              false, false, false, false, false, false, false, false

                       };

                       public boolean isCellEditable(int row, int column) {

                              return columnEditables[column];

                       }

               });

              

               scrollPane.setViewportView(table);

              

              

               //첫번째 combobox 생성

               comboBox = new JComboBox();  

               comboBox.setBounds(146, 40, 100, 20);

               panel.add(comboBox);

               comboBox.addItem("시.도 선택");

              

               displaySido();

               //시.도 콤보박스=============================================

               comboBox.addItemListener(new ItemListener() {

                       public void itemStateChanged(ItemEvent e) {

               if(e.getStateChange()==ItemEvent.SELECTED)

                       selectSido(comboBox.getSelectedItem().toString());

                             

                       }

               });

               comboBox.setToolTipText("");

              

              

               JLabel label = new JLabel("시.도");

               label.setBounds(146, 14, 100, 20);

               panel.add(label);

               label.setHorizontalAlignment(SwingConstants.CENTER);

              

               //구.군 ComboBox=============================================

               comboBox_1 = new JComboBox();

               comboBox_1.setBounds(258, 40, 100, 20);

               panel.add(comboBox_1);

              

               JLabel label_1 = new JLabel("구.군");

               label_1.setBounds(258, 14, 100, 20);

               panel.add(label_1);

               label_1.setHorizontalAlignment(SwingConstants.CENTER);

              

               comboBox_1.addItemListener(new ItemListener() {

                       public void itemStateChanged(ItemEvent e) {

                              if(e.getStateChange()==ItemEvent.SELECTED)

                                      selectGugun(comboBox.getSelectedItem().toString() ,comboBox_1.getSelectedItem().toString());

                       }

               });

              

               //동 ComboBox=============================================

               comboBox_2 = new JComboBox();

               comboBox_2.setBounds(370, 40, 100, 20);

               panel.add(comboBox_2);

              

               JLabel label_2 = new JLabel("동");

               label_2.setBounds(370, 14, 100, 20);

               panel.add(label_2);

               label_2.setHorizontalAlignment(SwingConstants.CENTER);

               comboBox_2.addItemListener(new ItemListener() {

                       public void itemStateChanged(ItemEvent e) {

                              if(e.getStateChange()==ItemEvent.SELECTED)

                             

                              //table에 집어넣기 실행=====================================

                              selectDong(comboBox.getSelectedItem().toString(), comboBox_1.getSelectedItem().toString(), comboBox_2.getSelectedItem().toString());

                       }             

               });

        }

        //프로그램 시작시 시.도 보여주기====================================================================

        public void displaySido(){

               //선언

               zipControl controller = new zipControl();

               //DB연결

               controller.connection();             

               //

               ArrayList<zipDAO> sidoList = controller.searchSido();

               for(int i = 0 ; i < sidoList.size() ; i++){

                       zipDAO zipcode = sidoList.get(i);

                       comboBox.addItem(zipcode.getSido());

               }             

               //DB연결 해제

               controller.disconnection();

        }

        //sido 선택(gugun 출력)====================================================================

        public void selectSido(String sido){

               System.out.println(sido);

               zipControl controller = new zipControl();

               //DB연결

               controller.connection();             

               //

               ArrayList<zipDAO> gugunList = controller.searchGugun(sido);

               comboBox_1.removeAllItems();

               comboBox_2.removeAllItems();

               comboBox_1.addItem("구.군 선택");

               for(int i = 0 ; i < gugunList.size() ; i++){

                       zipDAO zipcode = gugunList.get(i);

                       comboBox_1.insertItemAt(zipcode.getGugun(), i);

               }

               table.setModel(new zipTable());

               //DB연결 해제

               controller.disconnection();

        }      

        //gugun 선택(dong 출력)====================================================================

        public void selectGugun(String sido, String gugun){

               System.out.println(gugun);

               zipControl controller = new zipControl();

               //DB연결

               controller.connection();             

               //

               ArrayList<zipDAO> dongList = controller.searchDong(sido, gugun);

               comboBox_2.removeAllItems();

               comboBox_2.addItem("동 선택");

               for(int i = 0 ; i < dongList.size() ; i++){

                       zipDAO zipcode = dongList.get(i);

                       comboBox_2.insertItemAt(zipcode.getDong(),i);

               }

               table.setModel(new zipTable());

               //DB연결 해제

               controller.disconnection();                 

        }

       

        //마지막 Dong 선택(테이블에 출력)====================================================================

        public void selectDong(String sido, String gugun, String dong){

              

               zipControl controller = new zipControl();

               //DB연결

               controller.connection();             

               //

               ArrayList<zipDAO> addressList = controller.searchAddress(sido, gugun, dong);

              

               Object[][] arrAdd = new Object[addressList.size()][8];

              

               for(int i = 0 ; i < addressList.size() ; i++){

                       zipDAO zipcode = addressList.get(i);

                       //출력!

                       System.out.println(zipcode.getSeq() + " " + zipcode.getZipcode()+ " " +zipcode.getSido()+ " " +zipcode.getGugun()+ " " +zipcode.getDong() + " " + zipcode.getRi() + " " + zipcode.getSt_bunji() + " " + zipcode.getEd_bunji());                      

                       //테이블에 넣기!

                       arrAdd[i][0] = zipcode.getSeq();

                       arrAdd[i][1] = zipcode.getZipcode();

                       arrAdd[i][2] = zipcode.getSido();

                       arrAdd[i][3] = zipcode.getGugun();

                       arrAdd[i][4] = zipcode.getDong();

                       arrAdd[i][5] = zipcode.getRi();

                       arrAdd[i][6] = zipcode.getSt_bunji();

                       arrAdd[i][7] = zipcode.getEd_bunji();

                      

                       table.setModel(new zipTable(arrAdd));

               }

               //DB연결 해제

               controller.disconnection();

              

        }

       

}

 

 

 

 

 

 

 

출력물