BookJDBC

main

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

public class BookMain2{
	Scanner sc = new Scanner(System.in);
	BookDao2 bdao = new BookDao2();
	
	public BookMain2() {
		
		init();
	}

	public void init() {
		while(true) {
			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(">> 메뉴번호 선택");
			int menu = sc.nextInt();

			switch(menu) {
			case 1:
				ArrayList<BookBean2> lists = bdao.selectAll(); //?
				selectAll(lists);
				break;
			case 2:
				selectChoice();
				break;
			case 3:break;
			case 4:break;
			case 5:break;
			case 6:
				System.out.println("프로그램 종료");
				System.exit(0);
				break;
			default:
				System.out.println("1-6사이 선택");
				return;

			}
		}
	}
	// case 2:
	private void selectChoice() {
		System.out.println("제목 선택 : ");
		String title = sc.next();
		
		ArrayList<BookBean2> lists = bdao.selectChoice(title);
	
		display(lists);
	}
	public void display(ArrayList<BookBean2> lists) {
		System.out.println("순번/제목/저자/출판사/가격/출시일");
		for(int i=0; i< lists.size(); i++) {
			BookBean2 bb = lists.get(i);
			System.out.println(
					bb.getNo()
					+"\t"+bb.getTitle()
					+"\t"+bb.getAuthor()
					+"\t"+bb.getPrice()
					+"\t"+bb.getPublisher()
					+"\t"+bb.getPub_Day()	
					);
			
			
		}
		
	}

	// case 1:
	private void selectAll(ArrayList<BookBean2> lists) { // DB에서 가져온 데이터(lists)는 BookDao2 → BookMain2 쪽으로 넘어옵
		System.out.println("순번/제목/저자/출판사/가격/출시일"); // 따라서 display()가 출력하려면, 외부에서 그 데이터를 넘겨받아야 해요. 
														//👉 그 방법이 바로 매개변수(ArrayList<BookBean2> lists)입니다.
					
		for(int i=0; i<lists.size(); i++) {
			BookBean2 bb = lists.get(i);
			System.out.println(
					bb.getNo()
					+"\t"+bb.getTitle()
					+"\t"+bb.getAuthor()
					+"\t"+bb.getPrice()
					+"\t"+bb.getPublisher()
					+"\t"+bb.getPub_Day()	
					);
		}
		
	}

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


}

 

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 BookDao2 {
	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 BookDao2() {
		// 드라이버 호출
		try {
			Class.forName(driver);
			System.out.println("드라이버 호출 성공");
		} catch (ClassNotFoundException e) {
			System.out.println("드라이버 호출 실패");
			e.printStackTrace();
		}
	}

	public void getConnect() {
		try {
			conn = DriverManager.getConnection(url, id, pw);
		} catch (SQLException e) {
			System.out.println("계정 연결 실패");
			e.printStackTrace();
		}
	}

	// 1. 모든 정보 조회
	public ArrayList<BookBean2> selectAll() {
		getConnect();
		ArrayList<BookBean2> lists = new ArrayList<BookBean2>();
		try {
			String sql = "select * from book order by no";
			ps = conn.prepareStatement(sql); // 조회 분석
			rs = ps.executeQuery();

			while(rs.next()) {
				int no = rs.getInt("no");
				String title = rs.getString("title");
				String author = rs.getString("author");
				String publisher = rs.getString("publisher");
				int price = rs.getInt("price");
				String pub_day = String.valueOf(rs.getDate("pub_day"));

				BookBean2 bb = new BookBean2();
				bb.setNo(no);
				bb.setTitle(title);
				bb.setAuthor(author);
				bb.setPublisher(publisher);
				bb.setPrice(price);
				bb.setPub_Day(pub_day);

				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;
	}


	// 2. 조건 선택
	public ArrayList<BookBean2> selectChoice(String title) {
		getConnect();
		ArrayList<BookBean2>lists = new ArrayList<BookBean2>();

		try {
			String sql = "select * from book where title = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, title);

			rs = ps.executeQuery();

			while(rs.next()) {
				int no = rs.getInt("no");
				String title2 = rs.getString("title");
				String author = rs.getString("author");
				String publisher = rs.getString("publisher");
				int price = rs.getInt("price");
				String pub_day = String.valueOf(rs.getDate("pub_day"));

				BookBean2 bb = new BookBean2();
				bb.setNo(no);
				bb.setTitle(title2);
				bb.setAuthor(author);
				bb.setPublisher(publisher);
				bb.setPrice(price);
				bb.setPub_Day(pub_day);

				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;


	}


}

 

bean

public class BookBean2 { // 한줄의 레코드를 담을 수 있는 클래스
	private int no;
	private String title;
	private String author;
	private String publisher;
	private int price;
	private String pub_Day;
	public int getNo() {
		return no;
	}
	public void setNo(int no) {
		this.no = no;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public String getPublisher() {
		return publisher;
	}
	public void setPublisher(String publisher) {
		this.publisher = publisher;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
	public String getPub_Day() {
		return pub_Day;
	}
	public void setPub_Day(String pub_Day) {
		this.pub_Day = pub_Day;
	}
	
	
}