[초급예제 - Java&DB 연동 예제] 재고관리시스템

[문제 01] 재고관리시스템을 만들어주세요.

[조건]

1>상품 등록
- 상품 고유번호/ 상품명/ 상품설명/ 상품 등록 날짜
2>상품조회
고유번호      상품명      상품설명      등록날짜
10001      키보드      기계식키보드   2020/03/26
10002      마우스      쥐아님      2020/03/26
10003      키보드 케이스   가죽 케이스   2020/03/26

3> 상품삭제 - 목록을 보여준 후 고유번호로 삭제
4> 상품검색 - 상품명으로 검색 -> ex) 키보드 검색하면 키보드와 키보드 케이스 둘다 나와야함.
5> 종료

Main

import java.util.HashMap;
import java.util.List;
import java.util.Scanner;
 
public class Main {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        IM im = new IM();
 
        while (true) {
            System.out.println("==== 재고관리시스템  ====");
            System.out.println("1>  상품 등록");
            System.out.println("2>  상품조회");
            System.out.println("3>  상품검색");
            System.out.println("4>  상품삭제");
            System.out.println("5>  종료");
 
            int menu = Integer.parseInt(sc.nextLine());
            if (menu == 1) {
                System.out.println("=== 상품등록 ===");
                System.out.print("> 상품명 : ");
                String imP = sc.nextLine();
                System.out.print("> 상품설명 : ");
                String imPInfo = sc.nextLine();
                try {
                    int result = im.insert(imP, imPInfo);
                    if (result>0) {
                        System.out.println("성공");
                    }else {
                        System.out.println("실패");
                    }
                }catch(Exception e) {
                    e.printStackTrace();
                }            
            }else if (menu == 2) {
                System.out.println("=== 상품조회 ===");
                try {
                    List <HashMap<String, Object>> hachList = im.inquiry();
                    for (HashMap<String,Object> hashm : hachList) {
                        System.out.println(
                                hashm.get("imNo") + "\t"+ hashm.get("imP") + "\t"+ hashm.get("imPInfo") + "\t"+    hashm.get("imDate")
                        );
                    }
                }catch(Exception e) {
                    e.printStackTrace();
                }
            }else if (menu == 3) {
                System.out.println("=== 상품검색 ===");
                System.out.println("> 상품이름을 입력해주세요 : ");
                String Idck = sc.nextLine();
                try {
                    List <Product> isckList = im.isIdExist(Idck);
                    System.out.println("번호 \t 제품명 \t 상세내용 \t 입력날짜");
                    for(Product isckname : isckList) {
                        System.out.println(
                            isckname.getNo()+ "\t" + isckname.getImP()+ "\t" + isckname.getImPInfo()+ "\t" + isckname.getDate()
                        );
                    }
                    
                } catch(Exception e) {
                    e.printStackTrace();
                }
            }else if (menu == 4) {
                System.out.println("=== 상품삭제 ===");
                System.out.println("> 상품id를 입력해주세요 : ");
                int imNo = Integer.parseInt(sc.nextLine());
                try {
                    int result = im.delete(imNo);
                    if (result>0) {
                        System.out.println("성공");
                    }else {
                        System.out.println("실패");
                    }
                }catch(Exception e) {
                    e.printStackTrace();
                }
 
            }else if (menu == 5) {
                System.out.println("=== 프로그램이 종료됩니다. ===");
                System.exit(0);
            }else {
                System.out.println("1~4까지의 숫자를 입력해주세요.");
            }
        }        
    }
}
 

 

Product

 
public class Product {
    private int no;
    private String imP;
    private String imPInfo;
    private Object date;
    
    public Product(int no, String imP, String imPInfo) {
        super();
        this.no = no;
        this.imP = imP;
        this.imPInfo = imPInfo;
    }
    
    public Product(int no, String imP, String imPInfo, Object date) {
        super();
        this.no = no;
        this.imP = imP;
        this.imPInfo = imPInfo;
        this.date = date;
    }
 
    public int getNo() {
        return no;
    }
 
    public void setNo(int no) {
        this.no = no;
    }
 
    public Object getDate() {
        return date;
    }
 
    public void setDate(Object date) {
        this.date = date;
    }
    
    public String getImP() {
        return imP;
    }
    public void setImP(String imP) {
        this.imP = imP;
    }
    public String getImPInfo() {
        return imPInfo;
    }
    public void setImPInfo(String imPInfo) {
        this.imPInfo = imPInfo;
    }
}
 

 

IM

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
 
public class IM {
    
    public Connection makeConnection () throws Exception{
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String loginId = "kh";
        String loginPw = "kh";
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection con = DriverManager.getConnection(url, loginId, loginPw);
        
        return con;
    }
    
    /* 1 상품등록 */
    public int insert (String imP, String imPInfo) throws Exception{        
        Connection con = makeConnection();
        String sql = "insert into InventoryM values(InventoryM_seq.nextval,?,?,sysdate)";
        PreparedStatement pstat = con.prepareStatement(sql);
        pstat.setString(1, imP);
        pstat.setString(2, imPInfo);
        int result = pstat.executeUpdate();
        con.commit();
        pstat.close();
        con.close();
        return result;        
    }
    
    /* 2 상품조회*/
    public List <HashMap<String, Object>> inquiry() throws Exception{
        String sql = "select * from InventoryM";
        try(    Connection con = makeConnection();        
                PreparedStatement pstat = con.prepareStatement(sql);
            ){
            ResultSet rs = pstat.executeQuery();
            List <HashMap<String, Object>> hasmapList = new ArrayList<HashMap<String, Object>> ();            
            while(rs.next()) {
                HashMap<String, Object> hashput = new HashMap<String, Object>();
                hashput.put("imNo",rs.getInt(1));
                hashput.put("imP",rs.getString(2));
                hashput.put("imPInfo",rs.getString(3));
                hashput.put("imDate",rs.getDate(4));
                hasmapList.add(hashput);
            }
            return hasmapList;
        }
        
    }
    
    /* 3 상품검색 - 이름으로 검색*/
    public List <Product> isIdExist(String Idck) throws Exception{
        String sql = "Select * from InventoryM where imp = ?";
        try(    Connection con = makeConnection();
                PreparedStatement pstat = con.prepareStatement(sql);
                ){
            pstat.setString(1, Idck);
            ResultSet rs = pstat.executeQuery();
            List <Product> isIdckList = new ArrayList <Product> ();
            while (rs.next()) {
                int no = rs.getInt(1);
                String p =rs.getString(2);
                String info = rs.getString(3);
                Date date = rs.getDate(4);
                isIdckList.add(new Product(no,p,info,date));
            }
            return isIdckList;
        }    
    }
    
    /* 4 상품삭제 */
    public int delete(int imNo) throws Exception{
        String sql = "delete from InventoryM where imNo=?";        
        try(    Connection con = makeConnection();
                PreparedStatement pstat = con.prepareStatement(sql);
            ){
            pstat.setInt(1, imNo);
            int result = pstat.executeUpdate();
            con.commit();
            return result;            
        }
    }
    
    
}