Main

package Main;

public class Main {

	public static void main(String[] args) {
		new MainManager();
	}

}

package Main;

import java.util.Scanner;

import Manager.BasketManager;
import Manager.ClientManager;
import Manager.GoodsManager;
import Manager.ShoppingManager;

public class MainManager {
	private ClientManager CM = new ClientManager();
	private GoodsManager GM = new GoodsManager();
	private BasketManager BM= new BasketManager();
	private ShoppingManager SM=new ShoppingManager();
	private Scanner in=new Scanner(System.in);

	MainManager() {
		manu();
	}

	private void manu() {
		while (true) {
			set1();
			String sel=in.nextLine();
			if(sel.equals("1")) {
				CM.menu();
			}else if(sel.equals("2")) {
				GM.menu();
			}else if(sel.equals("3")) {
				SM.Start();
			}else if(sel.equals("4")) {
				System.out.println("시스템을 종료합니다");
				break;
			}else {
				System.out.println("번호 입력이 잘못됬습니다"+"\n"+"다시 입력해 주세요");
			}
		}
	}

	private void set1() {
		System.out.println("1.  고객 관리 ");
		System.out.println("2.  물건 관리 ");
		System.out.println("3.  쇼핑 ");
		System.out.println("4.  시스템 종료");
		System.out.println("====메뉴를 선택하세요====");

	}
}

Manager

package Manager;

import java.util.ArrayList;
import java.util.Scanner;

import DAO.BasketDAO;
import DTO.BasketDTO;
import DTO.GoodsDTO;

public class BasketManager {
	private Scanner in = new Scanner(System.in);
	private BasketDAO b= new BasketDAO();
	private BasketDTO bi=null;
	

	public void BasketStart(String id) {
		menu(id);
	}

	private void menu(String id) {
		while(true) {
			set1();
			String sel = in.nextLine();
			if(sel.equals("1")) {
				listGoods();
			}else if(sel.equals("2")) {
				chackList(id);
			}else if(sel.equals("3")) {
				buyList(id);
			}else if(sel.equals("4")) {
				System.out.println("====== 다시 찾아주세요 ======");
				break;
			}
		}

	}

	private void chackList(String id) {
		ArrayList<BasketDTO> BasketList = b.chackList(id);
		for (BasketDTO b : BasketList) {
			b.prt();
			System.out.println("========");
		}
		
		
	}

	private void buyList(String id) {
		bi=new BasketDTO();
		System.out.println("구입할 물건의 ID을 입력하세요");
		String gid=in.nextLine();
		System.out.println("구입할 수량을 입력하세요");
		int cnt=in.nextInt();
		in.nextLine();
		bi.setC_Id(id);
		bi.setG_Id(gid);
		bi.setB_cnt(cnt);
		b.buyGoods(bi);
	}

	private void listGoods() {
		ArrayList<GoodsDTO> GoodsList = b.ListGoodsDTO();
		for (GoodsDTO g : GoodsList) {
			g.prt();
			System.out.println("========");
		}
	}

	private void set1() {
		System.out.println(" 1.  물품 전체보기");
		System.out.println(" 2.  구매 물품확인");
		System.out.println(" 3.  물품 구매하기");
		System.out.println(" 4.  쇼핑 종료하기");
		System.out.println("==== 메뉴 선택 ====");

	}
}

package Manager;

import java.util.ArrayList;
import java.util.Scanner;

import DAO.ClientDAO;
import DTO.ClientInfoDTO;

public class ClientManager {
	private Scanner in = new Scanner(System.in);
	private ClientInfoDTO ci = null;
	private ClientDAO c = new ClientDAO();

	

	public void menu() {
		for (;;) {
			set1();
			int sel = in.nextInt();
			in.nextLine();
			if (sel == 1) {
				addClientInfo();
			} else if (sel == 2) {
				listClientInfo();
			} else if (sel == 3) {
				modClientInfo();
			} else if (sel == 4) {
				delDlientInfo();
			} else if (sel == 5) {
				selClientInfo();
			} else if (sel == 6) {
				System.out.println("프로그램을 종료합니다");
				break;
			} else {
				System.out.println("잘못된 번호입니다" + "\n" + "다시 입력해주세요");
			}

		}
	}

	private void selClientInfo() {
		System.out.println("[검색] 고객 ID을 입력하세요");
		String id = in.nextLine();
		c.selClientInfo(id);
		ArrayList<ClientInfoDTO> infoList = c.selClientInfo(id);
		for (ClientInfoDTO c : infoList) {
			c.prt();
			System.out.println("========");
		}

	}

	private void delDlientInfo() {
		System.out.println("[삭제] 고객 ID을 입력하세요");
		String id = in.nextLine();
		c.delClientInfo(id);

	}

	private void modClientInfo() {
		ci = new ClientInfoDTO();
		System.out.println("[수정] 고객 ID를 입력하세요");
		String id = in.nextLine();
		if (c.chackId(id) > 0) {
			System.out.println("[수정] 고객 이름를 입력하세요");
			String name = in.nextLine();
			System.out.println("[수정] 고객 비밀번호를 입력하세요");
			String pass = in.nextLine();
			System.out.println("[수정] 고객 주소를 입력하세요");
			String addr = in.nextLine();
			System.out.println("[수정] 고객 포인트를 입력하세요");
			int point = in.nextInt();
			in.nextLine();
			ci.setId(id);
			ci.setName(name);
			ci.setPass(pass);
			ci.setAddr(addr);
			ci.setPoint(point);
			c.modClientInfo(ci);
		} else {
			System.out.println("없는 ID입니다");
		}

	}

	private void listClientInfo() {
		ArrayList<ClientInfoDTO> infoList = c.ListClientInfo();
		for (ClientInfoDTO c : infoList) {
			c.prt();
			System.out.println("========");
		}
	}

	private void addClientInfo() {
		ci = new ClientInfoDTO();
		System.out.println("고객 ID를 입력하세요");
		String id = in.nextLine();
		if (c.chackId(id) == 0) {
			System.out.println("고객 이름를 입력하세요");
			String name = in.nextLine();
			System.out.println("고객 비밀번호를 입력하세요");
			String pass = in.nextLine();
			System.out.println("고객 주소를 입력하세요");
			String addr = in.nextLine();
			System.out.println("고객 포인트를 입력하세요");
			int point = in.nextInt();
			in.nextLine();
			ci.setId(id);
			ci.setName(name);
			ci.setPass(pass);
			ci.setAddr(addr);
			ci.setPoint(point);
			c.addClientinfo(ci);

		} else {
			System.out.println("이미 존재하는 ID입니다");
		}
	}

	private void set1() {
		System.out.println("1.  고객 정보 입력");
		System.out.println("2.  고객 정보 목록보기");
		System.out.println("3.  고객 정보 수정");
		System.out.println("4.  고객 정보 삭제");
		System.out.println("5.  고객 정보 검색");
		System.out.println("6.  프로그램 종료");
		System.out.println("====메뉴를 선택해주세요====");

	}
}

package Manager;

import java.util.ArrayList;
import java.util.Scanner;

import DAO.GoodsDAO;
import DTO.ClientInfoDTO;
import DTO.GoodsDTO;

public class GoodsManager {
	private Scanner in = new Scanner(System.in);
	private GoodsDTO gi = null;
	private GoodsDAO g = new GoodsDAO();

	public void menu() {
		while (true) {
			set1();
			String sel = in.nextLine();
			if (sel.equals("1")) {
				addGoods();
			} else if (sel.equals("2")) {
				listGoods();
			} else if (sel.equals("3")) {
				modGoods();
			} else if (sel.equals("4")) {
				selGoods();
			} else if (sel.equals("5")) {
				delGoods();
			} else if (sel.equals("6")) {
				stockGoods();
			} else if (sel.equals("7")) {
				System.out.println("프로그램을 종료합니다");
				break;
			} else {
				System.out.println("번호 입력이 잘못됬습니다" + "\n" + "다시 입력해 주세요");
			}

		}

	}

	private void stockGoods() {
		ArrayList<GoodsDTO> GoodsList = g.stockGoods();;
		for (GoodsDTO g : GoodsList) {
			g.prt();
			System.out.println("========");
		}

	}

	private void delGoods() {
		System.out.println("[삭제] 물품 ID을 입력하세요");
		String id = in.nextLine();
		g.selGoodsDTO(id);

	}

	private void selGoods() {
		System.out.println("[검색] 물품 ID 혹은 물품 이름을 입력하세요");
		String id = in.nextLine();
		g.selGoodsDTO(id);
		ArrayList<GoodsDTO> GoodsList = g.selGoodsDTO(id);;
		for (GoodsDTO g : GoodsList) {
			g.prt();
			System.out.println("========");
		}

	}

	private void modGoods() {
		gi = new GoodsDTO();
		System.out.println("[수정] 물품 ID를 입력하세요");
		String id = in.nextLine();
		System.out.println("[수정] 물품 이름를 입력하세요");
		String name = in.nextLine();
		System.out.println("[수정] 물품 수량를 입력하세요");
		int cnt = in.nextInt();
		in.nextLine();
		System.out.println("[수정] 물품 가격를 입력하세요");
		int price = in.nextInt();
		in.nextLine();
		System.out.println("[수정] 판매자 이름를 입력하세요");
		String seller = in.nextLine();

		gi.setG_id(id);
		gi.setG_name(name);
		gi.setG_cnt(cnt);
		gi.setG_price(price);
		gi.setG_seller(seller);
		g.modGoodsDTO(gi);

	}

	private void listGoods() {
		ArrayList<GoodsDTO> GoodsList = g.ListGoodsDTO();
		for (GoodsDTO g : GoodsList) {
			g.prt();
			System.out.println("========");
		}
	}

	private void addGoods() {
		gi = new GoodsDTO();
		System.out.println("물품 ID를 입력하세요");
		String id = in.nextLine();
		System.out.println("물품 이름를 입력하세요");
		String name = in.nextLine();
		System.out.println("물품 수량를 입력하세요");
		int cnt = in.nextInt();
		in.nextLine();
		System.out.println("물품 가격를 입력하세요");
		int price = in.nextInt();
		in.nextLine();
		System.out.println("판매자 이름를 입력하세요");
		String seller = in.nextLine();

		gi.setG_id(id);
		gi.setG_name(name);
		gi.setG_cnt(cnt);
		gi.setG_price(price);
		gi.setG_seller(seller);
		g.addGoodsDTO(gi);

	}

	private void set1() {
		System.out.println(" 1.  물건 등록 ");
		System.out.println(" 2.  물건 전체 보기 ");
		System.out.println(" 3.  물건 수정 ");
		System.out.println(" 4.  물건 검색 ");
		System.out.println(" 5.  물건 삭제 ");
		System.out.println(" 6.  재고량 분석 ");
		System.out.println(" 7.  프로그램 종료");
		System.out.println("==== 메뉴 선택 ==== ");

	}

}

package Manager;

import java.util.Scanner;

import DAO.ClientDAO;

public class ShoppingManager {
	private Scanner in = new Scanner(System.in);
	private ClientDAO c = new ClientDAO();
	private BasketManager BM = new BasketManager();

	public void Start() {
		System.out.println("로그인 해주세요");
		System.out.println("고객 ID 입력");
		String id = in.nextLine();
		System.out.println("고객 비밀 번호 입력");
		String pass = in.nextLine();
		if (c.logChack(id, pass)) {
			System.out.println("==== 좋은 쇼핑 되세요 ====");
			BM.BasketStart(id);
		} else {
			System.out.println("잘못된 ID와 비밀번호입니다" + "\n" + "다시 입력하세요");
			System.out.println("===================");
		}

	}

}

DTO

package DTO;

import java.sql.Date;

public class BasketDTO {
	private int b_no;
	private Date b_day;
	private int b_cnt;
	private String c_Id;
	private String g_Id;
	private String c_Addr;
	private String g_Name;
	private String g_Seller;
	private int g_Price;

	public void prt() {
		System.out.println(" 고객 ID : " + c_Id);
		System.out.println(" 물건 ID : " + g_Id);
		System.out.println(" 물건 이름 : " + g_Name);
		System.out.println(" 물건 판매자 : " + g_Seller);
		System.out.println(" 물건 가격 : " + g_Price);
		System.out.println(" 구매 일 : " + b_day);
		System.out.println(" 구매 갯수 : " + b_cnt);
		System.out.println(" 고객주소 : " + c_Addr);

	}

	public int getB_no() {
		return b_no;
	}

	public void setB_no(int b_no) {
		this.b_no = b_no;
	}

	public Date getB_day() {
		return b_day;
	}

	public void setB_day(Date b_day) {
		this.b_day = b_day;
	}

	public int getB_cnt() {
		return b_cnt;
	}

	public void setB_cnt(int b_cnt) {
		this.b_cnt = b_cnt;
	}

	public String getC_Id() {
		return c_Id;
	}

	public void setC_Id(String c_Id) {
		this.c_Id = c_Id;
	}

	public String getG_Id() {
		return g_Id;
	}

	public void setG_Id(String g_Id) {
		this.g_Id = g_Id;
	}

	public String getC_Addr() {
		return c_Addr;
	}

	public void setC_Addr(String c_Addr) {
		this.c_Addr = c_Addr;
	}

	public String getG_Name() {
		return g_Name;
	}

	public void setG_Name(String g_Name) {
		this.g_Name = g_Name;
	}

	public String getG_Seller() {
		return g_Seller;
	}

	public void setG_Seller(String g_Seller) {
		this.g_Seller = g_Seller;
	}

	public int getG_Price() {
		return g_Price;
	}

	public void setG_Price(int g_Price) {
		this.g_Price = g_Price;
	}

}

package DTO;

public class ClientInfoDTO {
	private String id;
	private String name;
	private String pass;
	private String addr;
	private int point;

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPass() {
		return pass;
	}

	public void setPass(String pass) {
		this.pass = pass;
	}

	public String getAddr() {
		return addr;
	}

	public void setAddr(String addr) {
		this.addr = addr;
	}

	public int getPoint() {
		return point;
	}

	public void setPoint(int point) {
		this.point = point;
	}
	public void prt() {
		System.out.println("고객 ID : "+id);
		System.out.println("고객 이름 : "+name);
		System.out.println("고객 비밀번호 : "+pass);
		System.out.println("고객 주소 : "+addr);
		System.out.println("고객 포인트 : "+point);
	}
}

package DTO;

import java.sql.Date;

public class GoodsDTO {
	private String g_id;
	private String g_name;
	private int g_cnt;
	private int g_price;
	private String g_seller;
	private Date g_day;

	
	public void prt() {
		System.out.println(" 물건 ID : " + g_id);
		System.out.println(" 물건 이름 : " + g_name);
		System.out.println(" 물건 수량 : " + g_cnt);
		System.out.println(" 물건 가격 : " + g_price);
		System.out.println(" 물건 판메자 : " + g_seller);
		System.out.println(" 입고 날짜 : " + g_day);
		
	}


	public String getG_id() {
		return g_id;
	}


	public void setG_id(String g_id) {
		this.g_id = g_id;
	}


	public String getG_name() {
		return g_name;
	}


	public void setG_name(String g_name) {
		this.g_name = g_name;
	}


	public int getG_cnt() {
		return g_cnt;
	}


	public void setG_cnt(int g_cnt) {
		this.g_cnt = g_cnt;
	}


	public int getG_price() {
		return g_price;
	}


	public void setG_price(int g_price) {
		this.g_price = g_price;
	}


	public String getG_seller() {
		return g_seller;
	}


	public void setG_seller(String g_seller) {
		this.g_seller = g_seller;
	}


	public Date getG_day() {
		return g_day;
	}


	public void setG_day(Date g_day) {
		this.g_day = g_day;
	}

}

DAO

package DAO;

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 DTO.BasketDTO;
import DTO.GoodsDTO;

public class BasketDAO {
	private Connection conn = null;
	private GoodsDTO gi = null;
	private BasketDTO bi = null;
	private ResultSet rs = null;
	private ArrayList<GoodsDTO> goodsList = null;
	private ArrayList<BasketDTO> basketList = null;

	public BasketDAO() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			System.out.println("로드 실패");
		}
	}

	public boolean connect() {
		try {
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system", "11111111");
			return true;
		} catch (SQLException e) {
			System.out.println("연결 실패");
			return false;
		}
	}

	public ArrayList<BasketDTO> chackList(String id) {
		basketList = new ArrayList<>();
		if (connect()) {
			String sql = "select * from b_list where c_id = ?";
			try {
				PreparedStatement psmt = conn.prepareStatement(sql);
				psmt.setString(1, id);
				rs = psmt.executeQuery();
				while (rs.next()) {
					bi = new BasketDTO();
					bi.setC_Id(rs.getString("c_id"));
					bi.setG_Id(rs.getString("g_id"));
					bi.setG_Name(rs.getString("g_name"));
					bi.setG_Seller(rs.getString("g_seller"));
					bi.setG_Price(rs.getInt("g_price"));
					bi.setB_day(rs.getDate("b_day"));
					bi.setB_cnt(rs.getInt("b_cnt"));
					bi.setC_Addr(rs.getString("c_addr"));
					basketList.add(bi);
				}
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return basketList;
		} else {
			System.out.println("연결 할 수 없어 입력할수 없습니다");
		}
		return null;

	}

	public void buyGoods(BasketDTO bi) {
		if (connect()) {
			String sql1 = "update goods set g_cnt = g_cnt - ? where g_id = ?";
			String sql2 = "insert into basket values (seq_no.nextval, ?, default, ?, ?) ";
			try {
				PreparedStatement pstm = conn.prepareStatement(sql2);
				pstm.setInt(1, bi.getB_cnt());
				pstm.setString(2, bi.getC_Id());
				pstm.setString(3, bi.getG_Id());
				pstm.execute();
				pstm = conn.prepareStatement(sql1);
				pstm.setInt(1, bi.getB_cnt());
				pstm.setString(2, bi.getG_Id());
				int r = pstm.executeUpdate();
				System.out.println(r + "건이 구입되었습니다");
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();

			}

		}
	}

	public ArrayList<GoodsDTO> ListGoodsDTO() {
		goodsList = new ArrayList<>();
		if (connect()) {
			String sql = "select * from goods";
			try {
				Statement psmt = conn.createStatement();
				rs = psmt.executeQuery(sql);
				while (rs.next()) {
					gi = new GoodsDTO();
					gi.setG_id(rs.getString("g_id"));
					gi.setG_name(rs.getString("g_name"));
					gi.setG_cnt(rs.getInt("g_cnt"));
					gi.setG_price(rs.getInt("g_price"));
					gi.setG_seller(rs.getString("g_seller"));
					gi.setG_day(rs.getDate("g_day"));
					goodsList.add(gi);
				}
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return goodsList;
		} else {
			System.out.println("연결 할 수 없어 입력할수 없습니다");
		}
		return null;
	}
}

package DAO;

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 DTO.ClientInfoDTO;

public class ClientDAO {
	private Connection conn = null;
	private ClientInfoDTO ci = null;
	private ResultSet rs = null;
	private ArrayList<ClientInfoDTO> infoList = null;

	public ClientDAO() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			System.out.println("로드 실패");
		}
	}

	public boolean connect() {
		try {
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system", "11111111");
			return true;
		} catch (SQLException e) {
			System.out.println("연결 실패");
			return false;
		}
	}

	public void delClientInfo(String id) {
		if (connect()) {
			String sql = "delete from client where c_id=?";
			try {
				PreparedStatement psmt = conn.prepareStatement(sql);
				psmt.setString(1, id);
				psmt.executeUpdate();
				conn.close();
				System.out.println("["+id+"] 삭제 완료");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} else {
			System.out.println("연결 할 수 없어 입력할수 없습니다");
		}

	}

	public ArrayList<ClientInfoDTO> selClientInfo(String word) {
		infoList = new ArrayList<>();
		if (connect()) {
			String sql = "select * from client where c_id like ? or c_name like ? ";
			try {
				PreparedStatement psmt = conn.prepareStatement(sql);
				psmt.setString(1, "%"+word+"%");
				psmt.setString(2, "%"+word+"%");
				rs = psmt.executeQuery();
				while (rs.next()) {
					ci = new ClientInfoDTO();
					ci.setId(rs.getString("c_id"));
					ci.setName(rs.getString("c_name"));
					ci.setPass(rs.getString("c_pass"));
					ci.setAddr(rs.getString("c_addr"));
					ci.setPoint(rs.getInt("c_point"));
					infoList.add(ci);
				}
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return infoList;
		} else {
			System.out.println("연결 할 수 없어 입력할수 없습니다");
		}
		return null;
	}

	public void modClientInfo(ClientInfoDTO ci) {
		if (connect()) {
			String sql = "update client set c_name=?, c_pass=?, c_addr=?, c_point=? where c_id=?";
			try {
				PreparedStatement psmt = conn.prepareStatement(sql);
				psmt.setString(1, ci.getName());
				psmt.setString(2, ci.getPass());
				psmt.setString(3, ci.getAddr());
				psmt.setInt(4, ci.getPoint());
				psmt.setString(5, ci.getId());

				int e = psmt.executeUpdate();
				System.out.println(e + "건이 입력되었습니다");
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} else {
			System.out.println("연결 할 수 없어 입력할수 없습니다");
		}

	}

	public ArrayList<ClientInfoDTO> ListClientInfo() {
		infoList = new ArrayList<>();
		if (connect()) {
			String sql = "select * from client";
			try {
				Statement psmt = conn.createStatement();
				rs = psmt.executeQuery(sql);
				while (rs.next()) {
					ci = new ClientInfoDTO();
					ci.setId(rs.getString("c_id"));
					ci.setName(rs.getString("c_name"));
					ci.setPass(rs.getString("c_pass"));
					ci.setAddr(rs.getString("c_addr"));
					ci.setPoint(rs.getInt("c_point"));
					infoList.add(ci);
				}
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return infoList;
		} else {
			System.out.println("연결 할 수 없어 입력할수 없습니다");
		}
		return null;
	}

	public int chackId(String id) {
		if (connect()) {
			String sql = "select count(*) cnt from client where c_id=?"; 
			try {
				PreparedStatement psmt = conn.prepareStatement(sql);
				psmt.setString(1, id);
				rs = psmt.executeQuery();
				if (rs.next()) {
					int cnt = rs.getInt("cnt");
					return cnt;
				}
				conn.close();
			} catch (SQLException e) {

			}
		}
		return 0;

	}

	public void addClientinfo(ClientInfoDTO ci) {
		if (connect()) {
			String sql = "insert into client values (?,?,?,?,?)";
			try {
				PreparedStatement psmt = conn.prepareStatement(sql);
				psmt.setString(1, ci.getId());
				psmt.setString(2, ci.getName());
				psmt.setString(3, ci.getPass());
				psmt.setString(4, ci.getAddr());
				psmt.setInt(5, ci.getPoint());
				int e = psmt.executeUpdate();
				System.out.println(e + "건이 입력되었습니다");
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} else {
			System.out.println("연결 할 수 없어 입력할수 없습니다");
		}
	}
	public boolean logChack(String id, String pass) {
		if (connect()) {
			String sql = "select count(*) cnt from client where c_id=? and c_pass =? "; 
			try {
				PreparedStatement psmt = conn.prepareStatement(sql);
				psmt.setString(1, id);
				psmt.setString(2, pass);
				rs = psmt.executeQuery();
				if (rs.next()) {
					int cnt = rs.getInt("cnt");
					if(cnt>0) {
						return true;
					}
				}
				conn.close();
			} catch (SQLException e) {

			}
		}
		return false;

	}
}

package DAO;

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 DTO.ClientInfoDTO;
import DTO.GoodsDTO;

public class GoodsDAO {
	private Connection conn = null;
	private GoodsDTO gi = null;
	private ResultSet rs = null;
	private ArrayList<GoodsDTO> GoodsList = null;

	public GoodsDAO() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			System.out.println("로드 실패");
		}
	}

	public boolean connect() {
		try {
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system", "11111111");
			return true;
		} catch (SQLException e) {
			System.out.println("연결 실패");
			return false;
		}
	}

	public void delGoodsDTO(String id) {
		if (connect()) {
			String sql = "delete from goods where g_id=?";
			try {
				PreparedStatement psmt = conn.prepareStatement(sql);
				psmt.setString(1, id);
				psmt.executeUpdate();
				conn.close();
				System.out.println("[" + id + "] 삭제 완료");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} else {
			System.out.println("연결 할 수 없어 입력할수 없습니다");
		}

	}

	public ArrayList<GoodsDTO> stockGoods() {
		GoodsList = new ArrayList<>();
		if (connect()) {
			String sql1 = "select avg(g_cnt) avg from goods";
			String sql2 = "select * from goods where g_cnt > (select avg(g_cnt) from goods) ";
			try {
				Statement psmt = conn.createStatement();
				rs = psmt.executeQuery(sql1);
				if (rs.next()) {
					System.out.println("평균 수량 :" + rs.getInt("avg") + "\n");
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}

			try {
				Statement psmt = conn.createStatement();
				rs = psmt.executeQuery(sql2);
				while (rs.next()) {
					gi = new GoodsDTO();
					gi.setG_id(rs.getString("g_id"));
					gi.setG_name(rs.getString("g_name"));
					gi.setG_cnt(rs.getInt("g_cnt"));
					gi.setG_price(rs.getInt("g_price"));
					gi.setG_seller(rs.getString("g_seller"));
					gi.setG_day(rs.getDate("g_day"));
					GoodsList.add(gi);
				}
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return GoodsList;

	}

	public ArrayList<GoodsDTO> selGoodsDTO(String word) {
		GoodsList = new ArrayList<>();
		if (connect()) {
			String sql = "select * from goods where g_id like ? or g_name like ? ";
			try {
				PreparedStatement psmt = conn.prepareStatement(sql);
				psmt.setString(1, "%" + word + "%");
				psmt.setString(2, "%" + word + "%");
				rs = psmt.executeQuery();
				while (rs.next()) {
					gi = new GoodsDTO();
					gi.setG_id(rs.getString("g_id"));
					gi.setG_name(rs.getString("g_name"));
					gi.setG_cnt(rs.getInt("g_cnt"));
					gi.setG_price(rs.getInt("g_price"));
					gi.setG_seller(rs.getString("g_seller"));
					gi.setG_day(rs.getDate("g_day"));
					GoodsList.add(gi);
				}
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return GoodsList;
		} else {
			System.out.println("연결 할 수 없어 입력할수 없습니다");
		}
		return null;
	}

	public void modGoodsDTO(GoodsDTO gi) {
		if (connect()) {
			String sql = "update goods set g_name = ?, g_cnt = ?, g_price = ?, g_seller = ?, g_day = default where g_id = ?";
			try {
				PreparedStatement psmt = conn.prepareStatement(sql);
				psmt.setString(5, gi.getG_id());
				psmt.setString(1, gi.getG_name());
				psmt.setInt(2, gi.getG_cnt());
				psmt.setInt(3, gi.getG_price());
				psmt.setString(4, gi.getG_seller());

				int e = psmt.executeUpdate();
				System.out.println(e + "건이 입력되었습니다");
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} else {
			System.out.println("연결 할 수 없어 입력할수 없습니다");
		}

	}

	public ArrayList<GoodsDTO> ListGoodsDTO() {
		GoodsList = new ArrayList<>();
		if (connect()) {
			String sql = "select * from goods";
			try {
				Statement psmt = conn.createStatement();
				rs = psmt.executeQuery(sql);
				while (rs.next()) {
					gi = new GoodsDTO();
					gi.setG_id(rs.getString("g_id"));
					gi.setG_name(rs.getString("g_name"));
					gi.setG_cnt(rs.getInt("g_cnt"));
					gi.setG_price(rs.getInt("g_price"));
					gi.setG_seller(rs.getString("g_seller"));
					gi.setG_day(rs.getDate("g_day"));
					GoodsList.add(gi);
				}
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return GoodsList;
		} else {
			System.out.println("연결 할 수 없어 입력할수 없습니다");
		}
		return null;
	}

	public int chackId(String id) {
		if (connect()) {
			String sql = "select count(*) cnt from goods where g_id = ?";
			try {
				PreparedStatement psmt = conn.prepareStatement(sql);
				psmt.setString(1, id);
				rs = psmt.executeQuery();
				if (rs.next()) {
					int cnt = rs.getInt("cnt");
					return cnt;
				}
				conn.close();
			} catch (SQLException e) {

			}
		}
		return 0;

	}

	public void addGoodsDTO(GoodsDTO gi) {
		if (connect()) {
			String sql = "insert into goods values (?, ?, ?, ?, ?, default)";
			try {
				PreparedStatement psmt = conn.prepareStatement(sql);
				psmt.setString(1, gi.getG_id());
				psmt.setString(2, gi.getG_name());
				psmt.setInt(3, gi.getG_cnt());
				psmt.setInt(4, gi.getG_price());
				psmt.setString(5, gi.getG_seller());
				int e = psmt.executeUpdate();
				System.out.println(e + "건이 입력되었습니다");
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} else {
			System.out.println("연결 할 수 없어 입력할수 없습니다");
		}
	}
}

==============================================

SQL

create table client(
c_id varchar2(20) primary key,
c_name varchar2(20),
c_pass varchar2(20),
c_addr varchar2(20),
c_point number
);

create table goods(
g_id varchar2(20) primary key,
g_name varchar2(20),
g_cnt number,
g_price number,
g_seller varchar2(20),
g_day date default sysdate
);

create sequence seq_no
increment by 1
start with 1;

create table basket(
b_no number primary key,
b_cnt number,
b_day date default sysdate,
c_id varchar2(20),
g_id varchar2(20),
foreign key (c_id) references client(c_id) on delete cascade,
foreign key (g_id) references goods (g_id) on delete cascade
);

create view b_list as
select c.c_id, g.g_id, g.g_name, g.g_seller, g.g_price, b.b_day, b.b_cnt, c.c_addr
from basket b
inner join client c on c.c_id = b.c_id
inner join goods g on g.g_id=b.g_id
;