0623 JDBC 응용 2일차

SQL:

drop sequence bseq ;
create sequence bseq 
   increment by 1
   start with 1
   minvalue 1
   nocache ; -- 미리 생성하고 있지 말아라.
   
drop table book  ;
create table book(
   no number primary key,
   title varchar2(20) not null,  
   author varchar2(20) not null, 
   publisher varchar2(20),
   price number not null,
   pub_day date default sysdate
);

insert into book values(bseq.nextval,'봉순이 언니','공지영','한겨레출판',5000,'2013-10-15');
insert into book values(bseq.nextval,'7년의 밤','정유정','은행나무',8000,'2011-03-23');
insert into book values(bseq.nextval,'해리포터','조앤롤링','문학수첩',3000,'2007-11-15');
insert into book values(bseq.nextval,'국화옆에서','서정주','민음사',4000,'2001-08-16');
insert into book values(bseq.nextval,'아리랑','조정래','해냄출판사',9000,'2007-01-30');

commit ;
select * from book ;

 

Main

 

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

public class BookMain {

	BookDao bdao = new BookDao();
	Scanner sc=new Scanner(System.in);
	BookMain(){
		init();
	}
	public void init() {
		while(true) {
			System.out.println("\n===메뉴 선택하기===");
			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.print(">> 메뉴 번호 입력 : ");
			int menu = sc.nextInt();

			switch(menu) {
			case 1: 
				ArrayList<BookBean> lists = bdao.getAllBook();
				showBook(lists);
				break;
			case 2: 
				getBookBySearch();
				break;
			case 3: 
				insertBook();
				break;
			case 4: 
				updateBook();
				break;
			case 5: 
				deleteBook();
				break;
			case 6: 
				System.out.println("프로그램을 종료합니다.");
				System.exit(0);				
			default : 
				System.out.println("1~6만 입력 가능");
				break;
			}


		}//while		
	}//init
	public void deleteBook() {
		System.out.print("삭제할 번호 입력 : ");
		int no = sc.nextInt();
		int result = bdao.deleteBook(no);
		if(result>0) {
			System.out.println("삭제 성공");
		} else if(result==-1){
			System.out.println("삭제 실패");
		} 
	}
	public void updateBook() {
		System.out.print("수정할 번호 입력 : ");
		int no =sc.nextInt();	
		System.out.print("제목 입력 : ");
		String title = sc.next();
		System.out.print("저자 입력 : ");
		String author = sc.next();
		System.out.print("출판사 입력 : ");
		String publisher=sc.next();
		int price=0;
		do{
			try{
				System.out.print("가격 입력 : ");
				price = sc.nextInt();
				break;
			}catch(InputMismatchException e) {
				System.out.println("숫자만 입력 가능합니다.");
				sc.next();
			}
		}while(true);
		System.out.print("출간일자 입력 : ");
		String pub_day=sc.next();
		BookBean bb= new BookBean(no,title,author,publisher,price,pub_day);
		int result = bdao.updateBook(bb);
		if(result>0) {
			System.out.println("수정 성공");
		} else if(result==-1){
			System.out.println("수정 실패");
		}
	}
	public void insertBook() {
		System.out.println("번호는 시퀀스로 자동생성 됩니다.");
		System.out.print("제목을 입력하세요 : ");
		String title =sc.next();
		System.out.print("저자를 입력하세요 : ");
		String author=sc.next();
		System.out.print("출판사를 입력하세요 : ");
		String publisher=sc.next();
		System.out.print("가격을 입력하세요 : ");
		int price=sc.nextInt();
		System.out.print("출간일자를 입력하세요 : ");
		String pub_day=sc.next();
		BookBean bb= new BookBean(0,title,author,publisher,price,pub_day);
		int result=bdao.insertBook(bb);
		if(result>0) {
			System.out.println("추가 성공");
		} else if(result==-1){
			System.out.println("추가 실패");
		}
	}
	public void getBookBySearch() {
		System.out.print("제목:1 저자:2 출판사:3 번호입력>>");
		int search_num=sc.nextInt();
		String column = null;
		switch(search_num) {
		case 1 : 
			System.out.print("조회할 제목 : ");
			column = "title";
			break;
		case 2 : 
			System.out.print("조회할 저자 : ");
			column = "author";
			break;
		case 3 : 
			System.out.print("조회할 출판사 : ");
			column = "publisher";
			break;	
		default:
			System.out.println("1~3만 입력 가능함");
			return;
		}//switch

		String search_word=sc.next();
		ArrayList<BookBean> lists = bdao.getBookBySearch(column,search_word);
		showBook(lists);

	}
	public void showBook(ArrayList<BookBean> lists) {
		for(int i=0;i<lists.size();i++) {
			BookBean pb= lists.get(i);
			System.out.println(pb.toString());
		}

	}
	public static void main(String[] args) {
		BookMain bm= new BookMain();

	}

}//main

Bean

 

public class BookBean {
	private int no;
	private	String title;
	private String author;
	private	String publisher;
	private int price;
	private String pub_day;
	public BookBean() {
		
	}
	public BookBean
	(int no, String title, String author, String publisher, int price, String pub_day) {
		super();
		this.no = no;
		this.title = title;
		this.author = author;
		this.publisher = publisher;
		this.price = price;
		this.pub_day = 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;
	}

	@Override
	public String toString() {
		return  no + "," + title + "," + author + "," + publisher
				+ "," + price + "," + pub_day;
	}
	
	
}

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 BookDao {
	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;
	
	BookDao(){
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			System.out.println("드라이버 로드 실패");
		}
	}
	public void connect() {
		try {
			conn=DriverManager.getConnection(url,id,pw);
		} catch (SQLException e) {
			System.out.println("접속 실패");
		}
	}
	public ArrayList<BookBean> getAllBook() {
		connect();
		ArrayList<BookBean> lists= new  ArrayList<BookBean>();
		String sql = "select * from book";
		
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				BookBean bb = new BookBean(rs.getInt("no"),rs.getString("title")
						,rs.getString("author"),rs.getString("publisher")
						,rs.getInt("price"),String.valueOf(rs.getDate("pub_day")));
				lists.add(bb);
			}
		} catch (SQLException e) {
			System.out.println("에러 1");
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return lists;
	}
	public ArrayList<BookBean> getBookBySearch(String column, String search_word) {
		connect();
		ArrayList<BookBean> lists= new  ArrayList<BookBean>();
		String sql ="select * from book where "+column+" like ?";
		
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,"%"+search_word+"%");
			rs=ps.executeQuery();
			while(rs.next()) {
				BookBean bb = new BookBean(rs.getInt("no"),rs.getString("title")
						,rs.getString("author"),rs.getString("publisher")
						,rs.getInt("price"),String.valueOf(rs.getDate("pub_day")));				
				lists.add(bb);
			}
		} catch (SQLException e) {
			System.out.println("에러 1");
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		System.out.println("검색한 항목은 "+lists.size()+"권입니다.");
		return lists;
	}
	public int insertBook(BookBean bb) {
		connect();
		String sql = "insert into book values(bseq.nextval,?,?,?,?,?)";
		int result=-1;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, bb.getTitle());
			ps.setString(2, bb.getAuthor());
			ps.setString(3, bb.getPublisher());
			ps.setInt(4, bb.getPrice());
			ps.setString(5, bb.getPub_day());
			result=ps.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
	public int updateBook(BookBean bb) {
		connect();
		String sql = "update book set "
				+ "title=?,author=?,publisher=?,price=?,pub_day=?"
				+ " where no=?";
		
		int result=-1;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, bb.getTitle());
			ps.setString(2, bb.getAuthor());
			ps.setString(3, bb.getPublisher());
			ps.setInt(4, bb.getPrice());
			ps.setString(5, bb.getPub_day());
			ps.setInt(6, bb.getNo());
			result=ps.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
		
	}
	public int deleteBook(int no) {
		connect();
		String sql = "delete book where no=?";
		int result=-1;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, no);
			result=ps.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
}