본문 바로가기
기초 및 언어/▶ Java&JSP

bread jdbc

by 류딩이 2025. 9. 2.

main

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

public class BreadMain {
	Scanner sc = new Scanner(System.in);
	BreadDao bdao = new BreadDao(); 

	public BreadMain() {
		init();
	}

	public void init(){

		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. 빵 정보 삭제");
			System.out.println("6. 빵 정렬");
			System.out.println("7. 빵 그룹메서드 정의");
			System.out.println("8. 프로그램 종료");
			System.out.println();

			int menu = sc.nextInt();
			switch(menu) {
			case 1:
				// 1. 빵 정보 조회
				ArrayList<BreadBean>lists =  bdao.getBreadAll();
				display(lists);
				break;
			case 2:
				// 2. 빵 조건 조회
				BreadChoice();
				break;
			case 3:
				// 3. 빵 정보 추가
				insertBread();
				break;
			case 4:
				// 4. 빵 정보 수정
				updateBread();
				break;
			case 5:
				// 5. 빵 정보 삭제
				deleteBread();
				break;
			case 6:
				// 6. 빵 정렬
				orderByBread();
				break;
			case 7:
				// 7. 빵 그룹메서드 정의
				groupBread();
				break;
			case 8:
				System.out.println("프로그램 종료");
				System.exit(0);
				break;
			default:
				System.out.println("1-9사이 입력하세요");
			}
		}

	}


	// case 7 : 빵 그룹
	private void groupBread() {
		System.out.println("그룹화할 회사명 입력 : ");
		String company = sc.next();
		BreadBean bb = bdao.groupBread(company);
		
		if(bb == null) {
			System.out.println("없는 회사명 입니다");
		}else {
			System.out.println("회사명\t count");
			System.out.println(bb.getCompany() +"\t"+ bb.getPrice());
		}

	}
	

	// case 6 : 빵 정렬
	private void orderByBread() {
		String column;
		System.out.println("정렬 순서 선택");
		System.out.println("1. 번호순");
		System.out.println("2. 제품명순");
		System.out.println("3. 칼로리순");
		System.out.println("4. 회사명순");
		System.out.println("5. 가격순");

		int choice_num = sc.nextInt();
		switch(choice_num) {
		case 1:
			column = "num";
			break;
		case 2:
			column = "name";
			break;
		case 3:
			column = "calorie";
			break;
		case 4:
			column = "company";
			break;
		case 5:
			column = "price";
			break;
		default : System.out.println("1-5사이 입력");
		return;
		}
		System.out.println("1 : 오름차순 \t 2 : 내림차순");
		int choice_num2 = sc.nextInt();
		String column2 = null;
		switch(choice_num2) {
		case 1 : 
			column2 = "asc";
			break;
		case 2:
			column2 = "desc";
			break;
		}
		ArrayList<BreadBean>lists = bdao.orderByBread(column, column2);
		display(lists);
	}

	// case 5 : 빵 정보 삭제
	private void deleteBread() {
		System.out.println("삭제 할 번호 입력 :");
		int delete_num = sc.nextInt();

		bdao.deleteBread(delete_num);

	}

	// case 4 : 빵 정보 수정
	private void updateBread() {
		System.out.println("빵 정보 수정");
		System.out.println("변경할 목록 번호");
		System.out.println("1. 가격 변경");
		System.out.println("2. 제품명 변경");
		System.out.println("3. 칼로리 변경");
		System.out.println("4. 알레르기정보 변경");

		String column;
		int choice_num = sc.nextInt();
		switch(choice_num) {
		case 1: 
			System.out.println("1. 가격 변경");
			column = "price";
			break;
		case 2:
			System.out.println("2. 제품명 변경");
			column = "name";
			break;
		case 3:
			System.out.println("3. 칼로리 변경");
			column = "calorie";
			break;
		case 4:
			System.out.println("4. 알레르기정보 변경");
			column = "allergy_info";
			break;
		default : 
			System.out.println("1-5사이 번호만 입력");
			return;
		}
		System.out.print("수정할 번호 입력 : ");
		String update_num = sc.next();

		System.out.print("수정할 값 입력 : ");
		String update_bread = sc.next();

		bdao.updateBread(column, update_num, update_bread);


	}

	// case 3 : 빵 정보 추가
	private void insertBread() {
		System.out.println("빵 정보 추가");
		System.out.print("제품명 :");
		String name = sc.next();

		System.out.print("칼로리 :");
		int calorie = sc.nextInt();

		System.out.print("회사명 :");
		String company = sc.next();

		System.out.print("제품 가격 :");
		int price = sc.nextInt();

		System.out.print("알레르기 유발 성분 :");
		String allergy_info = sc.next();

		bdao.BreadBean(name, calorie, company, price, allergy_info);

	}

	// case 2 : 빵 조건 조회
	private void BreadChoice() {
		String column = null;
		int choice_menu;

		System.out.println("검색할 조건 선택");
		System.out.println("1. 제품명\t2.회사명");
		choice_menu = sc.nextInt();
		switch(choice_menu) {
		case 1:
			column = "name";
			break;
		case 2:
			column = "company";
			break;
		default : System.out.println("1,2 중에 선택하세요");
		}
		System.out.println("키워드 입력하세요");
		String search_word = sc.next();
		ArrayList<BreadBean> lists = bdao.BreadChoice(search_word, column);
		display(lists);

	}

	// 빵 display
	private void display(ArrayList<BreadBean> lists) {
		System.out.println("번호\t제품명\t칼로리\t회사명\t가격\t알려지정보");
		for(int i=0; i<lists.size(); i++) {
			BreadBean bb = lists.get(i);
			System.out.println(
					bb.getNum()
					+"\t" + bb.getName()
					+"\t" + bb.getCalorie()
					+"\t" + bb.getCompany()
					+"\t" + bb.getPrice()
					+"\t" + bb.getAllergy_info()
					);
		}
	}

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

	}

}

 

 

dao

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class BreadDao {
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:orcl";
	String id = "sqlid";
	String pw = "sqlpw";

	Connection conn = null;
	PreparedStatement  ps = null;
	ResultSet rs = null;

	public BreadDao() {	// 드라이
		try {
			Class.forName(driver);
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public void getConnect() { // 계정 접속
		try {
			conn = DriverManager.getConnection(url, id, pw);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}


	// case 1: 빵 정보 조회
	public ArrayList<BreadBean> getBreadAll() { 
		getConnect();
		ArrayList<BreadBean> lists = new ArrayList<BreadBean>();
		try {
			String sql = "select * from bread order by num";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while(rs.next()){

				BreadBean bb = new BreadBean();
				bb.setNum(rs.getInt("num"));
				bb.setName(rs.getString("name"));
				bb.setCalorie(rs.getInt("calorie"));
				bb.setCompany(rs.getString("company"));
				bb.setPrice( rs.getInt("price"));
				bb.setAllergy_info(rs.getString("allergy_info"));

				lists.add(bb);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs != null)
					rs.close();
				if(ps != null)
					ps.close();
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lists;

	}

	// case 2 : 빵 조건 조회
	public ArrayList<BreadBean> BreadChoice(String search_word, String column) {
		getConnect();
		ArrayList<BreadBean> lists = new ArrayList<BreadBean>();

		String sql = "select * from bread where LOWER("+ column +")like LOWER(?)" ;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, "%"+search_word+"%");


			rs = ps.executeQuery();

			while(rs.next()){
				BreadBean bb = new BreadBean();
				bb.setNum(rs.getInt("num"));
				bb.setName(rs.getString("name"));
				bb.setCalorie(rs.getInt("calorie"));
				bb.setCompany(rs.getString("company"));
				bb.setPrice( rs.getInt("price"));
				bb.setAllergy_info(rs.getString("allergy_info"));

				lists.add(bb);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs != null)
					rs.close();
				if(ps != null)
					ps.close();
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lists;

	}

	// case 3: 빵 정보 추가
	public void BreadBean(String name, int calorie, String company, int price, String allergy_info) {
		getConnect();

		String sql = "insert into Bread values(bread_seq.nextval, ?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);

			ps.setString(1, name);
			ps.setInt(2, calorie);
			ps.setString(3, company);
			ps.setInt(4, price);
			ps.setString(5, allergy_info);

			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs != null)
					rs.close();
				if(ps != null)
					ps.close();
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// case 4 : 빵 정보 수정
	public void updateBread(String column, String update_num, String update_bread) {
		getConnect();

		String sql = "update bread set " +column+"= ? where num = ?";
		try {
			ps = conn.prepareStatement(sql);

			ps.setString(1, update_bread);
			ps.setString(2, update_num);
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs != null)
					rs.close();
				if(ps != null)
					ps.close();
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	// case 5 : 빵 정보 삭제
	public void deleteBread(int delete_num) {
		getConnect();
		String sql = "delete from bread where num = ?";

		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, delete_num);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs != null)
					rs.close();
				if(ps != null)
					ps.close();
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	// case 6 : 빵 정렬
	public ArrayList<BreadBean> orderByBread(String column, String column2) {
		getConnect();
		ArrayList<BreadBean> lists = new ArrayList<BreadBean>();
		String sql = "select * from bread order by " + column + " " +column2;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while(rs.next()){

				BreadBean bb = new BreadBean();
				bb.setNum(rs.getInt("num"));
				bb.setName(rs.getString("name"));
				bb.setCalorie(rs.getInt("calorie"));
				bb.setCompany(rs.getString("company"));
				bb.setPrice( rs.getInt("price"));
				bb.setAllergy_info(rs.getString("allergy_info"));

				lists.add(bb);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs != null)
					rs.close();
				if(ps != null)
					ps.close();
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lists;
	}

	// 7. 빵 그룹
	public BreadBean groupBread(String company2) {
		getConnect();
		ArrayList<BreadBean> lists = new ArrayList<BreadBean>();
		String sql = "select company, count(*) as count "
				+ "from bread where company= ? group by company";

		BreadBean bb = null;
		int count;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, company2);
			rs = ps.executeQuery();
			if(rs.next()) {
				
				bb= new BreadBean();
				String company = rs.getString("company");
				count = rs.getInt("count");

				bb.setCompany(company);
				bb.setPrice(count);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs != null)
					rs.close();
				if(ps != null)
					ps.close();
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return bb;	

	}


}

 

 

bean

public class BreadBean {
	private int num;
	private String name;
	private int calorie;
	private String company;
	private int price;
	private String allergy_info;
	
	public BreadBean(int num, String name, int calorie, String company, int price, String allergy_info) {
		super();
		this.num = num;
		this.name = name;
		this.calorie = calorie;
		this.company = company;
		this.price = price;
		this.allergy_info = allergy_info;
	}

	public BreadBean() {
		
	}

	public int getNum() {
		return num;
	}

	public void setNum(int num) {
		this.num = num;
	}

	public String getName() {
		return name;
	}

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

	public int getCalorie() {
		return calorie;
	}

	public void setCalorie(int calorie) {
		this.calorie = calorie;
	}

	public String getCompany() {
		return company;
	}

	public void setCompany(String company) {
		this.company = company;
	}

	public int getPrice() {
		return price;
	}

	public void setPrice(int price) {
		this.price = price;
	}

	public String getAllergy_info() {
		return allergy_info;
	}

	public void setAllergy_info(String allergy_info) {
		this.allergy_info = allergy_info;
	}
	
	
}
저작자표시 비영리 변경금지 (새창열림)

'기초 및 언어 > ▶ Java&JSP' 카테고리의 다른 글

01. JSP에서 출력하기  (0) 2025.09.08
00.JSP_JSP와 언어 인코딩 설정  (0) 2025.09.08
BookJDBC  (0) 2025.09.02
38. SimpleJDBC (Select, Insert, Delete, Update)  (1) 2025.09.01
37. 문자 스트림 (FileReader / FileWriter / InputStreamReader)  (1) 2025.08.29

관련글

티스토리툴바