DB- 실습02- 연습편

2021-12-26

시간이 모자라서 완벽하게 하지는 못했다.

해야할것
1. 매출등록 날짜 중복가능하게 처리
2. 매출삭제 날짜와 번호두개를 받아서 삭제하기.
3. delete 부분 null 이게 맞는건지..

Product

public class Product {
	private String pno;
	private String pname;
	private int price;
	public String getPno() {
		return pno;
	}
	public void setPno(String pno) {
		this.pno = pno;
	}
	public String getPname() {
		return pname;
	}
	public void setPname(String pname) {
		this.pname = pname;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
	@Override
	public String toString() {
		return "Product [pno=" + pno + ", pname=" + pname + ", price=" + price + "]";
	}
}

메뉴 1~ 4번에 관련이있다.

Sale

public class Sale {
	private String pno;
	private String date;
	private int price;
	private int quantity;
	private int amount;
	
	
	public String getPno() {
		return pno;
	}
	public void setPno(String pno) {
		this.pno = pno;
	}
	public String getDate() {
		return date;
	}
	public void setDate(String date) {
		this.date = date;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
	public int getQuantity() {
		return quantity;
	}
	public void setQuantity(int quantity) {
		this.quantity = quantity;
	}
	public int getAmount() {
		return amount;
	}
	public void setAmount(int amount) {
		this.amount = amount;
	}
	@Override
	public String toString() {
		return "Sale [pno=" + pno + ", date=" + date + ", price=" + price + ", quantity=" + quantity + ", amount="
				+ amount + "]";
	}
}

메뉴 5~7 관련이있다.

Main

public class Main {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
//		Connection CON=Database.CON;
		ProductConsole console = new ProductConsole();
		Product pvo =new  Product();
		Sale sale = new Sale();
		

		EXIT:
			while(true) {
				int menu = console.inputProductMenu();
				
				switch(menu) {
				case 1: ///상품등록
					console.insert(pvo);
					break; 
				case 2: //상품목록
					console.list();
					break;
				case 3: //상품검색
					console.seachProduct();
					break;
				case 4: //상품수정
					console.updateProduct();
					break;
				case 5:  //매출등록
					console.Sale(sale);
					break;
				case 6: //매출현황
					console.saleslistProduct(sale);
					break;
				case 7: //매출삭제
					console.salesdeleteProduct(null);
					break;
				case 0 : //종료
					System.out.println("프로그램종료!");
					break EXIT;
				default:
					System.out.println("0~4번메뉴를 선택하세요!");
		
				}
		
	}

	}
}

ProductConsole

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Scanner;

public class ProductConsole {
	
	private ProductService service;
	private Product pvo;
	private Sale sale;
	
	public ProductConsole() {
		service = new ProductService();
		pvo = new Product();
		sale = new Sale();
	}

	//메뉴
	public int inputProductMenu() {
		Scanner scan = new Scanner(System.in);
		System.out.println("");
		System.out.println("┌─────────────────────────────────────┐");
		System.out.print("│1.상품등록  ");
		System.out.print("2.상품목록  ");
		System.out.print("3.상품검색  ");
		System.out.print("4.상품수정  ");
		System.out.print("5.매출등록  ");
		System.out.print("6.매출현황  ");
		System.out.print("7.매출삭제  ");
		System.out.print("0.종료    │\n");
		System.out.println("└─────────────────────────────────────┘");
		System.out.print("선택>");
		int menu = scan.nextInt();
		
		return menu;
	}
	
	//상품등록
	public void insert(Product pvo) throws ClassNotFoundException, SQLException {
		Scanner scan = new Scanner(System.in);
		System.out.print("상품명>"); pvo.setPname(scan.nextLine());
		System.out.print("상품가격>"); pvo.setPrice(Integer.parseInt(scan.nextLine()));
		service.insert(pvo);
	}

	//상품목록
	public void list() throws ClassNotFoundException, SQLException {
		List<Product> array = service.list();
		for(Product p:array) {
			System.out.printf("%s\t%s\t%d\n",p.getPno(),p.getPname(),p.getPrice());
		}
		
	}

	//상품검색
	public void seachProduct() throws ClassNotFoundException, SQLException {
		Scanner scan = new Scanner(System.in);
		System.out.print("상품번호>"); String pno = scan.nextLine();
		System.out.println("──────────");
		pvo = service.read(pno);
		if(pvo.getPname()==null) {
			System.out.println("해당상품이 없습니다!");
		}else {
			System.out.println("상품명:" + pvo.getPname());
			System.out.println("상품가격:" +pvo.getPrice());
		}
	}

	public void updateProduct() throws ClassNotFoundException, SQLException {
		Scanner scan = new Scanner(System.in);
		System.out.print("수정할 번호 >"); String pno = scan.nextLine();
		pvo= service.read(pno);
		if(pvo.getPname()==null) {
			System.out.println("해당상품이 없습니다!");
		}else {
			System.out.print("상품명:" + pvo.getPname()+">");
			String pname = scan.nextLine();
			if(!pname.equals("")) pvo.setPname(pname);
			System.out.print("상품가격:" +pvo.getPrice()+">");
			String price = scan.nextLine();
			if(!price.equals("")) pvo.setPrice(Integer.parseInt(price));
			System.out.print("수정하실래요 ? (y/ㅛ)");
			String sel = scan.nextLine();
			if(sel.equals("y")||sel.equals("ㅛ")) {
				service.update(pvo);
			}
		}
	}
	//매출등록
	public void Sale(Sale sale) throws ClassNotFoundException, SQLException {
		Scanner scan = new Scanner(System.in);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		System.out.print("상품번호>"); String pno = scan.nextLine();
		sale.setPno(pno);
		pvo = service.read(pno);
		if(pvo.getPname()==null) {
			System.out.println("해당상품이없습니다!");
		}else {
			System.out.print("상품명:" + pvo.getPname());
			System.out.print("판매가격:" +pvo.getPrice()+">");
			String price = scan.nextLine();
			if(!price.equals("")) {
				sale.setPrice(Integer.parseInt(price));
			}else {
				sale.setPrice(pvo.getPrice());
			}
			String today =sdf.format(new Date());
			System.out.print("판매일:"+today+">"+"\n");
			String date =scan.nextLine();
			if(date.equals("")) {
				System.out.println(today);
			}else {
				System.out.println(date);
			}
			System.out.print("판매수량>");
			String quantity=scan.nextLine();
			sale.setQuantity(Integer.parseInt(quantity));
			sale.setAmount(sale.getPrice()*sale.getQuantity());
			service.saleinsert(sale);
		}
	}
	public void saleslistProduct(Sale sale ) throws ClassNotFoundException, SQLException {
		Scanner scan = new Scanner(System.in);
		System.out.print("상품번호>"); String pno = scan.nextLine();
		pvo=service.read(pno);
		if(pvo.getPname()==null) {
			System.out.println("해당상품이없습니다!");
		}else {
			System.out.println("상품명:" + pvo.getPname());
			System.out.println("상품가격:" +pvo.getPrice());
			List<Sale> array = service.salelist(pno);
			if(array.size() == 0) {
				System.out.println("매출정보가없습니다.");
			}else {
				for(Sale s:array) {
					System.out.printf("%s\t%s\t%s\t%s\n",
							s.getPno(),s.getDate(),s.getPrice(),s.getQuantity());
				}
			}
		}
	}
	public void salesdeleteProduct(String pno) throws ClassNotFoundException, SQLException {
		Scanner scan = new Scanner(System.in);
		System.out.println("삭제할 번호>"); pno = scan.nextLine();
		pvo=service.read(pno);
		if(pvo.getPname()==null) {
			System.out.println("해당상품이없습니다!");
		}else {
			System.out.println("상품명:" + pvo.getPname());
			System.out.println("상품가격:" +pvo.getPrice());
			System.out.println("삭제하시겠습니까? (y/ㅛ)");
			String sel = scan.nextLine();
			if(sel.equals("y")|| sel.equals("ㅛ")) {
				service.saledelete(pno);
			}	
	    }
	}
}

상품등록

	public int insert(Product pvo) throws SQLException, ClassNotFoundException {
		String sql = "insert into tbl_product(pno,pname,price)" + 
				"values(seq_product.nextval, ?,?)";
		Class.forName(driver);
		Connection con = DriverManager.getConnection(url,uid,pwd);
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, pvo.getPname());
		ps.setInt(2, pvo.getPrice());
		int result = ps.executeUpdate();
		System.out.println("상품등록완료!");
		ps.close();
		con.close();
		return result;
		
	}

상품목록

public List<Product> list() throws SQLException, ClassNotFoundException {
		String sql = "select * from tbl_product";
		Class.forName(driver);
		Connection con = DriverManager.getConnection(url,uid,pwd);
		PreparedStatement ps = con.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		
		List<Product> list = new ArrayList<Product>();

		while(rs.next()) {
			Product pvo = new Product();
			pvo.setPno(rs.getString("pno"));
			pvo.setPname(rs.getString("pname"));
			pvo.setPrice(rs.getInt("price"));
			list.add(pvo);
		}
		rs.close();
		ps.close();
		con.close();
		return list;
		
	}

상품검색

public Product read(String pno) throws SQLException, ClassNotFoundException {
		Product pvo = new Product();
		String sql = "select * from tbl_product where pno=?";
		Class.forName(driver);
		Connection con = DriverManager.getConnection(url,uid,pwd);
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, pno);
		ResultSet rs = ps.executeQuery();
		if(rs.next()) {
			pvo.setPno(rs.getString("pno"));
			pvo.setPname(rs.getString("pname"));
			pvo.setPrice(rs.getInt("price"));
		}
		return pvo;
		
	}

상품수정

public int update(Product vo) throws SQLException, ClassNotFoundException {
		String sql = "update tbl_product set pname=?,price=? where pno=?";
		Class.forName(driver);
		Connection con = DriverManager.getConnection(url,uid,pwd);
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, vo.getPname());
		ps.setInt(2, vo.getPrice());
		ps.setString(3, vo.getPno());
		int result = ps.executeUpdate();
		System.out.println("상품등록완료!");
		ps.close();
		con.close();
		return result;
	}

조건에 맞는 이름과 가격을 수정한다.

매출등록

public void saleinsert(Sale sale)  {
		try {
		String sql = "insert into tbl_sale(pno,sdate,price,quantity) values(?,?,?,?)";
		Class.forName(driver);
		Connection con = DriverManager.getConnection(url,uid,pwd);
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1,sale.getPno());
		ps.setString(2,sale.getDate());
		ps.setInt(3,sale.getPrice());
		ps.setInt(4, sale.getQuantity());
		ps.execute();
		System.out.println("매출등록완료!");
		}catch(Exception e) {
			System.out.println("saleinsert 오류 :" +e.toString());
		}
	
	}

위에 상품등록과 매우 비슷하다.

매출현황

public List<Sale> salelist(String pno)  {
		List<Sale> list = new ArrayList<Sale>();
		try {
			String sql = "select * from tbl_sale";
			Class.forName(driver);
			Connection con = DriverManager.getConnection(url,uid,pwd);
			PreparedStatement ps = con.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();

			while(rs.next()) {
				Sale s = new Sale();
				s.setPno(rs.getString("pno"));
				s.setDate(rs.getString("sdate"));
				s.setPrice(rs.getInt("price"));
				s.setQuantity(rs.getInt("quantity"));
				list.add(s);
			}
		}catch(Exception e) {
			System.out.println("salelist 오류: "+e.toString());
		}
		return list;
	}

매출현황도 역시 상품목록과 비슷하다.

매출삭제

	public void saledelete(String pno) {
		try {
			String sql = "delete from tbl_sale where pno=?";
			Class.forName(driver);
			Connection con = DriverManager.getConnection(url,uid,pwd);
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setString(1, pno);
			ps.execute();
			System.out.println("삭제완료!");
		}catch(Exception e) {
			System.out.println("saledelete 오류 :" +e.toString());
		}
		
	}

삭제를할때 번호와 날짜 두개를 받아서 삭제해야하는데 나는 번호만 일단 받아서 삭제했다. 추후에 추가할 예정이다.

Twitter Facebook LinkedIn

KangKkaem

DB- 실습02- 연습편

Product

Sale

Main

ProductConsole

상품등록

상품목록

상품검색

상품수정

매출등록

매출현황

매출삭제

공유하기

댓글남기기

참고

CSS - Grid

CSS - Flex

[오류]Several ports (8005, 8080, 8009) required by Tomcat v7.0 Server at localhost are already in use.

git - github에 파일올리고 내려받기