package stockmanagiment; import java.sql.*; public class Connect { public static Connection makeConnection() { String url = "jdbc:mysql://localhost/convenience"; // 데이터베이스 주소 String id = "root"; String password = "1234"; Connection con = null; try { Class.forName("com.mysql.cj.jdbc.Driver"); System.out.println("드라이버 적재 성공"); con = DriverManager.getConnection(url, id, password); System.out.println("데이터베이스 연결 성공"); } catch (ClassNotFoundException e) { System.out.println("드라이버를 찾을 수 없습니다."); }catch (SQLException e) { System.out.println("연결에 실패하였습니다."); } return con; } }
db연결 클래스
package stockmanagiment; import java.awt.BorderLayout; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.sql.Connection; 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.JTextField; public class Stockmanage implements ActionListener { private JPanel idPanel; private JLabel idlabel; private JTextField idInput; private JLabel passlabel; private JTextField passInput; private JPanel passPanel; private JButton loginButton; private JFrame jframe; private Connection con; public Stockmanage() { idlabel = new JLabel("ID"); idInput = new JTextField(15); idPanel = new JPanel(); idPanel.setLayout(new BorderLayout()); idPanel.add(idlabel,BorderLayout.WEST); idPanel.add(idInput,BorderLayout.CENTER); passlabel = new JLabel("비밀번호"); passInput = new JTextField(15); passPanel = new JPanel(); passPanel.setLayout(new BorderLayout()); passPanel.add(passlabel,BorderLayout.WEST); passPanel.add(passInput,BorderLayout.CENTER); loginButton = new JButton("로그인"); loginButton.addActionListener(this); jframe = new JFrame("재고관리 프로그램"); jframe.setLayout(new BorderLayout()); jframe.add(idPanel,BorderLayout.NORTH); jframe.add(passPanel,BorderLayout.CENTER); jframe.add(loginButton,BorderLayout.SOUTH); jframe.pack(); jframe.setLocationRelativeTo(null); jframe.setVisible(true); } @Override public void actionPerformed(ActionEvent e) { Object obj = e.getSource(); if(obj == loginButton) { String id = idInput.getText(); String password = passInput.getText(); if(id.equals("head") && password.equals( "0000")) { jframe.setVisible(false); try { con = Connect.makeConnection(); new Head(con); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } else if((id.equals("knbranch")|| (id.equals("mrbranch")))&& password.equals("0011")) { jframe.setVisible(false); con = Connect.makeConnection(); new Branch(id,con); } else { JOptionPane.showMessageDialog(null, "일치하는 계정정보가 없습니다. " + "다시 확인하세요","알림", JOptionPane.ERROR_MESSAGE); } } } public static void main(String[] args) { new Stockmanage(); } } package stockmanagiment; import java.awt.BorderLayout; import java.awt.CardLayout; import java.awt.Container; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.awt.event.ItemEvent; import java.awt.event.ItemListener; import java.sql.Connection; import java.sql.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.HashMap; import javax.swing.JButton; import javax.swing.JComboBox; import javax.swing.JFrame; import javax.swing.JLabel; import javax.swing.JPanel; import javax.swing.JScrollPane; import javax.swing.JTextArea; public class Head implements ActionListener, ItemListener{ private JLabel label; private JTextArea dateshow; private JTextArea dataOut; private JLabel sumlabel; private JTextArea sumOut; private JComboBox<String> branchc; private JButton showOrder; private JButton aprOrder; private Container tab; private CardLayout clayout; private JPanel headPanel; private JPanel buttonPanel1; private JPanel buttonPanel2; private JPanel functionPanel; private JPanel sumPanel; private JFrame jframe; private Connection con; private PreparedStatement pstmt; private HashMap<String, String> brtablelist; public Head() {} public Head(Connection con) throws SQLException { this.con = con; label = new JLabel("본사"); brtablelist = new HashMap<String,String>(); // 보완필요 if문 반복을 피하기 위해서 hashmap으로 코드를 짰는데 프로그램 실행할 때마다 반복해서 put메서드를 실행해줘야 하는게... brtablelist.put("강남점", "kangnam"); brtablelist.put("모란점", "moran"); Date now = new Date(System.currentTimeMillis()); SimpleDateFormat simple= new SimpleDateFormat("YY-MM-dd"); dateshow = new JTextArea(); dateshow.setText(simple.format(now)); // textArea에 날짜 표시 dateshow.setEditable(false); headPanel = new JPanel(); headPanel.setLayout(new BorderLayout()); headPanel.add(label,BorderLayout.WEST); headPanel.add(dateshow,BorderLayout.EAST); dataOut= new JTextArea(40,30); JScrollPane jsp = new JScrollPane(dataOut, JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED, JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED); String[]list = {"지점선택","본사","강남점","모란점"}; branchc = new JComboBox<String>(list); branchc.addItemListener(this); showOrder = new JButton("주문조회"); showOrder.addActionListener(this); buttonPanel1 = new JPanel(); buttonPanel1.setLayout(new BorderLayout()); buttonPanel1.add(showOrder,BorderLayout.CENTER); aprOrder = new JButton("승인"); aprOrder.addActionListener(this); buttonPanel2 = new JPanel(); buttonPanel2.setLayout(new BorderLayout()); buttonPanel2.add(aprOrder,BorderLayout.CENTER); tab = new JPanel(); clayout = new CardLayout(); tab.setLayout(clayout); tab.add(buttonPanel1, "show"); tab.add(buttonPanel2, "approve"); sumlabel = new JLabel("매출합계"); sumOut = new JTextArea(); sumPanel = new JPanel(); sumPanel.setLayout(new BorderLayout()); sumPanel.add(sumlabel,BorderLayout.WEST); sumPanel.add(sumOut,BorderLayout.CENTER); functionPanel = new JPanel(); functionPanel.setLayout(new BorderLayout()); functionPanel.add(branchc,BorderLayout.NORTH); functionPanel.add(tab,BorderLayout.CENTER); functionPanel.add(sumPanel,BorderLayout.SOUTH); clayout.show(tab, "show"); jframe = new JFrame("재고관리 프로그램"); jframe.setLayout(new BorderLayout()); jframe.add(headPanel,BorderLayout.NORTH); jframe.add(jsp,BorderLayout.CENTER); jframe.add(functionPanel,BorderLayout.EAST); jframe.pack(); jframe.setLocationRelativeTo(null); jframe.setVisible(true); } @Override public void actionPerformed(ActionEvent e) { Object obj = e.getSource(); if( obj== showOrder) { String branch,code,name=""; Date aprdate,orderdate; int idx,unitprice,quantity,totalprice=0; try { dataOut.setText(""); //데이터창 초기화 dataOut.append("주문번호 주문지점 주문일 코드 품명 단가 수량 금액 처리일"); //컬럼명 출력 dataOut.append("\n"); pstmt = con.prepareStatement("select * from convenience.order order by aprdate;"); ResultSet rs = pstmt.executeQuery(); while(rs.next()) { idx = rs.getInt("idx"); //서로다른 지점에서 같은 날 같은제품을 주문했을 경우에 구분이 필요해서 PK로 지정할 일련번호? 주문번호가 필요함 branch = rs.getString("branch"); orderdate = rs.getDate("orderdate"); code = rs.getString("code"); name = rs.getString("name"); unitprice = rs.getInt("unitprice"); quantity = rs.getInt("quantity"); totalprice = rs.getInt("totalprice"); aprdate = rs.getDate("aprdate"); //값을 하나씩 읽어와서 변수에 대입 dataOut.append( idx +"\t"+branch+"\t"+orderdate+"\t"+code+"\t"+ name +"\t" + unitprice+"\t"+quantity +"\t"+ totalprice+"\t"+aprdate); //데이터 창에 출력 dataOut.append("\n"); } clayout.show(tab, "approve"); //버튼 바꾸기 } catch (Exception ex) { // TODO: handle exception } } else if(obj==aprOrder) { try { pstmt = con.prepareStatement("select idx, aprdate from convenience.order where aprdate is null;"); //승인일란이 공란인 데이터를 가져옴 ResultSet rs = pstmt.executeQuery(); //쿼리문 실행 ArrayList<Integer>list = new ArrayList<Integer>(); //idx를 넣을 arraylist 변수 선언 while(rs.next()) { list.add(rs.getInt("idx")); //각 데이터의 idx(주문번호)를 가져와서 list에 넣음 } int size = list.size(); for(int i=0;i<size;i++) { pstmt = con.prepareStatement("update convenience.order set aprdate = now() where idx = ?;"); // 내부함수를 써서 오늘 날짜로 승인일 변경 pstmt.setInt(1, list.get(i)); //list에서 값을 하나씩 읽어서 order테이블에서 일치하는 행의 데이터를 가져와서 오늘 날짜로 승인일 입력 pstmt.executeUpdate(); //본사 재고에서 빼서 지점 재고로 넣는 코드 필요 } clayout.show(tab, "show"); } catch (Exception e2) { // TODO: handle exception } } } @Override public void itemStateChanged(ItemEvent ie) { String code,name,shpoint=""; Date shipping,receiving; int quantity,unitprice,totalprice=0; Timestamp expiry; String choice = branchc.getSelectedItem().toString(); try { if(choice.equals("지점선택")){} //지점선택은 아무런 행동 x else if(choice.equals("본사")) { //본사 테이블과 지점 테이블 컬럼이 달라서 본사만 따로 분리 pstmt = (PreparedStatement)con.prepareStatement("select * from convenience.head order by expiry;"); ResultSet rs = pstmt.executeQuery(); dataOut.setText(""); dataOut.append("코드 품명 수량 단가 금액 입고날짜 출고날짜 유통기한 출고지점"); dataOut.append("\n"); while(rs.next()) { code = rs.getString("code"); name = rs.getString("name"); quantity = rs.getInt("quantity"); unitprice = rs.getInt("unitprice"); totalprice = rs.getInt("totalprice"); receiving = rs.getDate("receiving"); shipping = rs.getDate("shipping"); expiry = rs.getTimestamp("expiry"); shpoint = rs.getString("shpoint"); dataOut.append(code+"\t"+ name + "\t"+quantity + "\t"+unitprice + "\t"+totalprice +"\t"+receiving +"\t"+ shipping + "\t"+expiry + "\t"+shpoint); dataOut.append("\n"); } } else { Date saledate; dataOut.setText(""); dataOut.append("코드 품명 수량 단가 금액 입고날짜 판매일 유통기한"); dataOut.append("\n"); String branch=brtablelist.get(choice); //한글지점명을 key로 value인 영어 테이블 값을 가져와서 branch에 대입 if(branch!=null) { PreparedStatement pstmt = con.prepareStatement("select * from convenience."+branch+ " order by expiry;"); ResultSet rs = pstmt.executeQuery(); while(rs.next()) { code = rs.getString("code"); name = rs.getString("name"); quantity = rs.getInt("quantity"); unitprice = rs.getInt("unitprice"); totalprice = rs.getInt("totalprice"); receiving = rs.getDate("receiving"); saledate = rs.getDate("saledate"); expiry = rs.getTimestamp("expiry"); dataOut.append(code+ "\t"+name + "\t"+quantity +"\t"+ unitprice + "\t"+totalprice +"\t"+receiving +"\t"+ saledate +"\t"+ expiry); dataOut.append("\n"); } } } } catch (Exception e) { // TODO: handle exception } } } package stockmanagiment; import java.awt.BorderLayout; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.sql.Connection; import java.sql.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Timestamp; import java.text.SimpleDateFormat; import javax.swing.JButton; import javax.swing.JFrame; import javax.swing.JLabel; import javax.swing.JPanel; import javax.swing.JTextArea; public class Branch implements ActionListener{ private JLabel label; private JTextArea dateshow; private JTextArea dataOut; private JLabel sumlabel; private JTextArea sumOut; private JButton showButton; private JButton orderButton; private JPanel headPanel; private JPanel functionPanel; private JPanel sumPanel; private JPanel buttonPanel; private JFrame jframe; private Connection con; private String id; public Branch() {}; public Branch(String id,Connection con) { this.id = id; this.con = con; label = new JLabel("지점"); Date now = new Date(System.currentTimeMillis()); SimpleDateFormat simple= new SimpleDateFormat("YY/MM-dd"); dateshow = new JTextArea(); dateshow.setText(simple.format(now)); dateshow.setEditable(false); headPanel = new JPanel(); headPanel.setLayout(new BorderLayout()); headPanel.add(label,BorderLayout.WEST); headPanel.add(dateshow,BorderLayout.EAST); dataOut= new JTextArea(30,30); sumlabel = new JLabel("매출합계"); sumOut = new JTextArea(); sumPanel = new JPanel(); sumPanel.setLayout(new BorderLayout()); sumPanel.add(sumlabel,BorderLayout.WEST); sumPanel.add(sumOut,BorderLayout.CENTER); showButton = new JButton("조회"); showButton.addActionListener(this); orderButton = new JButton("주문"); orderButton.addActionListener(this); buttonPanel = new JPanel(); buttonPanel.setLayout(new BorderLayout()); buttonPanel.add(showButton,BorderLayout.CENTER); buttonPanel.add(orderButton,BorderLayout.AFTER_LAST_LINE); functionPanel = new JPanel(); functionPanel.setLayout(new BorderLayout()); functionPanel.add(sumPanel,BorderLayout.NORTH); functionPanel.add(buttonPanel,BorderLayout.CENTER); jframe = new JFrame("재고관리 프로그램"); jframe.setLayout(new BorderLayout()); jframe.add(headPanel,BorderLayout.NORTH); jframe.add(dataOut,BorderLayout.CENTER); jframe.add(functionPanel,BorderLayout.EAST); jframe.pack(); jframe.setLocationRelativeTo(null); jframe.setVisible(true); } public static void main(String[] args) { } @Override public void actionPerformed(ActionEvent e) { Object obj = e.getSource(); if(obj == showButton) { String code,name=""; Date receiving,saledate; int quantity,unitprice,totalprice; Timestamp expiry; String sql=""; if(id.startsWith("kn")) sql = "select * from convenience.kangnam order by expiry;"; else if(id.startsWith("mr")) sql = "select * convenience.moran order by expiry;"; try { PreparedStatement psmt = (PreparedStatement)con.prepareStatement(sql); ResultSet rs = psmt.executeQuery(); dataOut.setText(""); dataOut.append("코드 품명 수량 단가 금액 입고날짜 판매일 유통기한"); while(rs.next()) { code = rs.getString("code"); name = rs.getString("name"); quantity = rs.getInt("quantity"); unitprice = rs.getInt("unitprice"); totalprice = rs.getInt("totalprice"); receiving = rs.getDate("receiving"); saledate = rs.getDate("saledate"); expiry = rs.getTimestamp("expiry"); // 값을 한 줄씩 읽어와서 변수에 대입하고 dataOut에 출력 dataOut.append(code+ "\t"+name + "\t"+quantity + "\t"+unitprice + "\t"+totalprice +"\t"+receiving + "\t"+saledate + "\t"+expiry+"\n"); } } catch (Exception e2) { // TODO: handle exception } } else if(obj == orderButton) { new Order(con,id); } } } package stockmanagiment; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; public class Info { //상품코드를 기반으로 상품리스트에서 정보를 가져오는 클래스 public String name; public int unitprice; Connection con; java.sql.Statement stmt; public Info serch(Connection con, String code) throws SQLException { this.con = con; Info info = new Info(); stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("select name, unitprice from product where code='"+code+"'"); //code와 일치하는 행을 찾아서 rs.next(); info.name = rs.getString("name"); //제품이름과 info.unitprice = rs.getInt("unitprice"); //단가를 멤버변수에 대입해서 return info; //info객체 반환 } } package stockmanagiment; import java.awt.BorderLayout; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.sql.Connection; import java.sql.Date; import java.sql.PreparedStatement; import java.sql.SQLException; import javax.swing.JButton; import javax.swing.JFrame; import javax.swing.JLabel; import javax.swing.JPanel; import javax.swing.JTextField; public class Order implements ActionListener{ private JLabel codeLabel; private JTextField codeinput; private JPanel codePanel; private JLabel quanLabel; private JTextField quaninput; private JPanel quanPanel; private JButton insertButton; private JFrame order; private Connection con; private String id; private Info info; private PreparedStatement pstmt; public Order(){} public Order(Connection con, String id){ this.con = con; this.id = id; codeLabel = new JLabel("코드"); codeinput = new JTextField(5); codePanel = new JPanel(); codePanel.setLayout(new BorderLayout()); codePanel.add(codeLabel,BorderLayout.WEST); codePanel.add(codeinput,BorderLayout.EAST); quanLabel = new JLabel("주문량"); quaninput = new JTextField(5); quanPanel = new JPanel(); quanPanel.setLayout(new BorderLayout()); quanPanel.add(quanLabel,BorderLayout.WEST); quanPanel.add(quaninput,BorderLayout.EAST); insertButton = new JButton("입력"); insertButton.addActionListener(this); order = new JFrame("주문창"); order.setLayout(new BorderLayout()); order.add(codePanel, BorderLayout.NORTH); order.add(quanPanel, BorderLayout.CENTER); order.add(insertButton,BorderLayout.SOUTH); order.pack(); order.setResizable(false); order.setVisible(true); } @Override public void actionPerformed(ActionEvent e) { Object obj = e.getSource(); if(obj == insertButton ) { try { String branch=""; if(id.startsWith("kn")) branch = "강남점"; else if(id.startsWith("mr")) branch = "모란점"; String code = codeinput.getText(); info = new Info().serch(con, code); String name = info.name; int unitprice = info.unitprice; int quantity = Integer.parseInt(quaninput.getText()); int totalprice = unitprice*quantity; pstmt = con.prepareStatement("insert into convenience.order " + "( branch, orderdate, code, name, unitprice, quantity, totalprice) values (?,now(),?,?,?,?,?);"); pstmt.setString(1, branch); pstmt.setString(2, code); pstmt.setString(3,name); pstmt.setInt(4,unitprice); pstmt.setInt(5,quantity); pstmt.setInt(6,totalprice); pstmt.executeUpdate(); // 각 물음표에 넣을 값을 설정해주고 쿼리문 실행 } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } } }