DB- 실습02

2021-12-21

먼저 java3 이름으로 만들었다. 여기서 이클립스에서 연동할때 이름을 java3로 안둬서 자꾸 테이블을 만들어라고 오류가떠서 고생했다.. 첨에는 프로그램뭘 잘못건드렸는지 실행이안되서 피봤고 ..

여튼 주의사항 ! 이름과 패스워드 확인을 잘하자.

create table tbl_product(
  pno char(3),
  pname varchar(100),
  price int,
  primary key(pno)
);

desc tbl_product;
drop table tbl_product;
drop table tbl_sale;

insert into tbl_product(pno, price, pname) 
values('100', 300, '냉장고');
insert into tbl_product(pno, price, pname) 
values('101', 150, '세탁기');
insert into tbl_product(pno, price, pname) 
values('102', 150, '건조기');
insert into tbl_product(pno, price, pname) 
values('103', 200, 'TV');
insert into tbl_product(pno, price, pname) 
values('104', 350, 'LG 냉장고');

select * from tbl_product;

create table tbl_sale(
  pno char(3),
  sdate date,
  price int,
  quantity int,
  primary key(pno, sdate),
  foreign key(pno) references tbl_product(pno)
);

insert into tbl_sale(pno, sdate, price, quantity)
values('100', '2021-12-01', 280, 10);
insert into tbl_sale(pno, sdate, price, quantity)
values('101', '2021-12-01', 140, 5);
insert into tbl_sale(pno, sdate, price, quantity)
values('100', '2021-12-02', 290, 6);
insert into tbl_sale(pno, sdate, price, quantity)
values('103', '2021-12-03', 190, 7);


select s.*, price*quantity amount from tbl_sale s;
select max(price), min(price), sum(quantity) from tbl_sale;

select s.*, pname, p.price
from tbl_product p, tbl_sale s
where s.pno=p.pno and sdate between '2021-12-02' and '2021-12-03'
order by s.price desc;

select * 
from tbl_product;

update tbl_product
set pname='LG 오브제 냉장고', price=310
where pno='100';

delete from tbl_product
where pno='104';


create sequence seq_product increment by 1 start with 104;
drop sequence seq_product;

insert into tbl_product(pno, pname, price)
values(seq_product.nextval, '전자레인지', 10);


select * 
from tbl_sale;

select * from tbl_sale where pno='100' and sdate = '2021-12-30';

tbl_product 테이블과 tbl_sale테이블을 생성했다.

select 컬럼명 from 테이블명 where 검색조건 order by 정렬조건;

insert into 테이블명(컬럼명) values(컬럼값);

update 테이블명 set 컬럼명=컬럼값 where 검색조건;

delete from 테이블명 where 검색조건; —– 외워야 할것..

Main

import java.sql.Connection;
import java.text.*;
import java.util.*;

public class Main {

	public static void main(String[] args) {
		SaleDAO sdao=new SaleDAO();
		Connection CON=Database.CON;
		Scanner s = new Scanner(System.in);
		boolean run =true;
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		DecimalFormat df = new DecimalFormat("#,###");
		ProductDAO pdao = new ProductDAO();
			while(run) {
				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("선택>");
				String menu = s.nextLine();
				switch(menu) {
				case "1": 
					Product vo = new Product();
					System.out.print("상품명>"); vo.setPname(s.nextLine());
					System.out.print("상품가격>"); String price = s.nextLine();
					vo.setPrice(Integer.parseInt(price));
					pdao.insert(vo);
					break;
				case "2": 
					ArrayList<Product> array =pdao.list();
					for(Product p:array) {
						System.out.printf("%s\t%-30s\t%-10d\n", p.getPno(),p.getPname(),p.getPrice());
					}
					break;
				case "3": //상품검색
						System.out.print("상품번호 >"); String pno = s.nextLine();
						vo = pdao.read(pno);
						if(vo.getPname()==null) {
							System.out.println("해당상품이없습니다!");
						
						}else {
							System.out.println("상품명:" +vo.getPname());
							System.out.println("상품가격:" +vo.getPrice());
						}
					break;
				case "4":
					System.out.print("상품번호 >"); pno = s.nextLine();
					vo = pdao.read(pno);
					if(vo.getPname()==null) {
						System.out.println("해당상품이없습니다!");
					
					}else {
						System.out.print("상품명:" +vo.getPname()+">");
						String pname = s.nextLine();
						if(!pname.equals("")) vo.setPname(pname);//입력한값이 있다면
						System.out.print("상품가격:" +vo.getPrice()+">");
						price = s.nextLine();
						if(!price.equals("")) vo.setPrice(Integer.parseInt(price));
						System.out.print("수정하실래요?(y)");
						String sel= s.nextLine();
						if(sel.equals("y")|| sel.equals("ㅛ")) {
							pdao.update(vo);
						}
					}
					
					break;
				case "5":
						Sale svo =new Sale();
						System.out.print("상품번호 >"); pno = s.nextLine();
						svo.setPno(pno);
						vo = pdao.read(pno);
						if(vo.getPname()==null) {
							System.out.println("해당상품이없습니다!");
						}else {
							System.out.println("상품명:" +vo.getPname());
							System.out.print("판매가격:" +vo.getPrice()+">");
							price = s.nextLine();
							if(!price.equals("")) {
								svo.setPrice(Integer.parseInt(price));
							}else {
								svo.setPrice(vo.getPrice());
							}
							String today = sdf.format(new Date());
							System.out.print("판매일:"+today+">");
							String date =s.nextLine();
							if(date.equals("")) {
								svo.setDate(today);
							}else {
								svo.setDate(date);
							}
							System.out.print("판매수량>");
							String quantity =s.nextLine();
							svo.setQuantity(Integer.parseInt(quantity));
							svo.setAmount(svo.getPrice()*svo.getQuantity());
							sdao.insert(svo);
						}
					break;
				case "6":
					System.out.print("상품번호 >"); pno = s.nextLine();
					vo = pdao.read(pno);
					if(vo.getPname()==null) {
						System.out.println("해당상품이없습니다!");
					
					}else {
						System.out.println("상품명:" +vo.getPname());
						System.out.println("상품가격:" +vo.getPrice());
						ArrayList<Sale> sarray = sdao.list(pno);
						if(sarray.size()==0) {
							System.out.println("매출 정보가 없습니다.");
						} else {
							for(Sale sale:sarray) {
								System.out.printf("%s\t%s\t%s\t%s\n",
										sale.getDate().substring(0,10), 
										df.format(sale.getPrice()),
										df.format(sale.getQuantity()),
										df.format(sale.getAmount()));
							}
						}
					}
					break;
				case "7":
					System.out.print("상품번호 >"); pno = s.nextLine();
					vo = pdao.read(pno);
					if(vo.getPname()==null) {
						System.out.println("해당상품이없습니다!");
					
					}else {
						System.out.println("상품명:" +vo.getPname());
						System.out.println("상품가격:" +vo.getPrice());
						System.out.println("───────────────────────────────────────");
						ArrayList<Sale> sarray = sdao.list(pno);
						if(sarray.size()==0) {
							System.out.println("매출 정보가 없습니다.");
						} else {
							for(Sale sale:sarray) {
								System.out.printf("%s\t%s\t%s\t%s\n",
										sale.getDate().substring(0,10), 
										df.format(sale.getPrice()),
										df.format(sale.getQuantity()),
										df.format(sale.getAmount()));
							}
							System.out.println("───────────────────────────────────────");
							System.out.print("삭제할날짜>");
							String date = s.nextLine();
							System.out.print("삭제하실래요?(y)");
							String sel= s.nextLine();
							if(sel.equals("y")|| sel.equals("ㅛ")) {
								boolean result=sdao.delete(pno, date);
								if(result==false) {
									System.out.println("해당일에 매출데이터가 없습니다.");
								}else {
									System.out.println("매출데이터 삭제!");
								}
						    }
						}
					}
					break;
					
				case "0":
					run=false;
					break;
				default:
					System.out.println("0~4번 메뉴를 선택하세요!");
	            }

            }
			System.out.println("프로그램종료!");
    }   
}

상품 등록, 목록,검색,수정 과 매출등록,현황,삭제 이렇게 묶어서 클래스를 만들어볼것이다. 일단 상품등록부분 먼저 하겠다.

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 + ", pprice=" + price + "]";
	}
}

getter setter로 값을 만들어주고 투스트링으로 값을 가져온다.

ProductDAO

public class ProductDAO {
	
	public void insert(Product vo) {
		try {
			String sql ="insert into tbl_product(pno, pname,price) values(seq_product.nextval,?,?)";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, vo.getPname());
			ps.setInt(2, vo.getPrice());
			ps.execute();
			System.out.println("상품등록완료!");
		}catch(Exception e) {
			System.out.println("insert 오류: "+e.toString());
		}
		
	}
		
	public ArrayList<Product> list() {
		ArrayList<Product> array = new ArrayList<Product>();
		try {
			String sql = "select * from tbl_product";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ResultSet rs =ps.executeQuery();
			while(rs.next()) {
				Product vo = new Product();
				vo.setPno(rs.getString("pno"));
				vo.setPname(rs.getString("pname"));
				vo.setPrice(rs.getInt("price"));
				array.add(vo);
			}
			
		}catch(Exception e) {
			System.out.println("list 오류: "+e.toString());
		}
		return array;
	}
	
	//상품검색
	public Product read (String pno) {
		Product vo = new Product();
		try {
			String sql = "select * from tbl_product where pno=?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, pno);
			ResultSet rs =ps.executeQuery();
			if(rs.next()) {
				vo.setPno(rs.getString("pno"));
				vo.setPname(rs.getString("pname"));
				vo.setPrice(rs.getInt("price"));
			}
		}catch(Exception e) {
			System.out.println("read 오류: "+e.toString());
		}
		return vo;
	}
	
	//상품수정
	public void update(Product vo) {
		try {
			String sql ="update tbl_product set pname=?,price=? where pno=?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, vo.getPname());
			ps.setInt(2, vo.getPrice());
			ps.setString(3, vo.getPno());
			ps.execute();
			System.out.println("상품수정완료!");
		}catch(Exception e) {
			System.out.println("update 오류: "+e.toString());
		}
	}
}

다음은 매출부분이다.

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 + "]";
    }
}

SaleDAO

import java.sql.*;
import java.util.ArrayList;

public class SaleDAO {

	//매출등록
	public void insert (Sale vo) {
		try {
			String sql ="insert into tbl_sale(pno,sdate,price,quantity) values(?,?,?,?)";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, vo.getPno());
			ps.setString(2, vo.getDate());
			ps.setInt(3, vo.getPrice());
			ps.setInt(4, vo.getQuantity());
			ps.execute();
			System.out.println("매출등록완료!");
		}catch(Exception e) {
			System.out.println("Sale insert 오류: "+e.toString());
		}
	}
	
	//매출현황
	public ArrayList<Sale> list(String pno) {
		ArrayList<Sale> array = new ArrayList<>();
		try {
			String sql= "select * from tbl_sale where pno=? order by sdate desc";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, pno);
			ResultSet rs= ps.executeQuery();
			while(rs.next()) {
				Sale vo = new Sale();
				vo.setPno(rs.getString("pno"));
				vo.setDate(rs.getString("sdate"));
				vo.setPrice(rs.getInt("price"));
				vo.setQuantity(rs.getInt("quantity"));
				vo.setAmount(vo.getPrice()*vo.getQuantity());
				array.add(vo);
			}
		}catch (Exception e) {
			System.out.println("sale list:" +e.toString());
		}
		return array;
	}
	
	//매출삭제
	public boolean delete(String pno, String date) {
		boolean success = false;
		try {
			String sql = "select * from tbl_sale where pno =? and sdate =?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, pno);
			ps.setString(2, date);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				sql = "delete from tbl_sale where pno=? and sdate=?";
				ps= Database.CON.prepareStatement(sql);
				ps.setString(1, pno);
				ps.setString(2, date);
				ps.execute();
				success =true;
			}
		}catch (Exception e) {
			System.out.println("sale delete:" +e.toString());
		}
		return success;
	}
}

출력값1

출력값2

Twitter Facebook LinkedIn

KangKkaem

DB- 실습02

공유하기

댓글남기기

참고

CSS - Grid

CSS - Flex

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

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