DAO / DTO

• DAO (Date Access Object)
  • 데이터베이스 작업을 전담하는 객체
  • 데이터베이스와 연계하여 처리할 프로그램을 정규화해 둔 클래스
  • 입력, 수정, 삭제, 검색등의 작업을 미리 캡슐화
  • CRUD
    • C : create, insert
    • R : read, select
    • U : update
    • D : delete
• DTO (Data Tranfer Object)
  • 객체를 표현한 한 단위
  • 데이터를 전달하는 단위
  • 데이터를 하나의 객체로 관리할 목적으로 만들어 둔 클래스의 객체
  • 데이터베이스의 table과 거의 동일한 필드를 갖는다

예시1 (book)

• DBManager
  • 데이터베이스 로딩, 접속, 접속해제 모듈화
• BookDTO
  • book테이블 표현, get,set
• BookDAO
  • 데이터베이스 작업 클래스
  • insert, delete, 프로시저 등 실행
• BookManager
  • 자바 내 클래스 모듈화
• BookConsole
  • 메인 클래스

DBManager

• 매번 반복 사용되는 클래스
• static 설정
• 연결 정보 Connection 리턴
• dbclose 오버로딩메서드
  • rs,ps,con,stmt 등 사용 정보에 따라 상황별로 오버로딩

package book;

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

public class DBManager {
    static {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            System.out.println("로딩성공");
        }catch(ClassNotFoundException e) {
            System.out.println("로딩실패");
            e.printStackTrace();

        } 
    }//DB매니저 호출 시 자동 드라이버 로딩

    public static Connection getConnection() throws SQLException{
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "hr";
        String pwd = "hr123";

        Connection con = DriverManager.getConnection(url, user, pwd);
        System.out.println("연결 성공");
        return con;

    }// Connection 리턴

    public static void dbClose(ResultSet rs, PreparedStatement ps, Connection con) throws SQLException {
        if(rs!=null) rs.close();
        if(ps!=null) ps.close();
        if(con!=null) con.close();
    }
    public static void dbClose(PreparedStatement ps, Connection con) throws SQLException {
        if(ps!=null) ps.close();
        if(con!=null) con.close();
    }
}

BookDTO

• book 테이블 표현
• get,set 메서드

package book;

import java.sql.Timestamp;

public class BookDTO {
    private int no;
    private String title;
    private int price;
    private String publisher;
    private Timestamp joindate;

    public BookDTO() {
        super();
    }
    public BookDTO(int no, String title, int price, String publisher, Timestamp joindate) {
        super();
        this.no = no;
        this.title = title;
        this.price = price;
        this.publisher = publisher;
        this.joindate = joindate;
    }

    public int getNo() {
        return no;
    }
    public String getTitle() {
        return title;
    }
    public int getPrice() {
        return price;
    }
    public String getPublisher() {
        return publisher;
    }
    public Timestamp getJoindate() {
        return joindate;
    }

    public void setNo(int no) {
        this.no = no;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public void setPrice(int price) {
        this.price = price;
    }
    public void setPublisher(String publisher) {
        this.publisher = publisher;
    }
    public void setJoindate(Timestamp joindate) {
        this.joindate = joindate;
    }

    public String toString() {
        return "BookDTO [no="+no+", title="+title+", price="+price
                +", publisher="+publisher+", joindate="+joindate+"]";
    }

}

BookDAO

• db작업 클래스
• insert, delete, 프로시저 등

package book;

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

public class BookDAO {
    public int insertBook(BookDTO dto) throws SQLException{
        Connection con = null;
        PreparedStatement ps = null;
        int cnt=0;

        try {
            con = DBManager.getConnection();

            String sql = "insert into book values"
                    +"(book_seq.nextval,?,?,?,sysdate)";
            ps = con.prepareStatement(sql);
            ps.setString(1, dto.getTitle());
            ps.setInt(2, dto.getPrice());
            ps.setString(3, dto.getPublisher());

            cnt = ps.executeUpdate();
            System.out.println(cnt+"행 입력");
        } finally {
            DBManager.dbClose(ps, con);
        }
        return cnt;
    }//insert

    public int updateBook(BookDTO dto) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        int cnt=0;

        try {
            con = DBManager.getConnection();

            String sql = "update book\r\n"
                    + "set title = ?, price = ?, publisher = ?\r\n"
                    + "where no = ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, dto.getTitle());
            ps.setInt(2, dto.getPrice());
            ps.setString(3,dto.getPublisher());
            ps.setInt(4,dto.getNo());

            cnt = ps.executeUpdate();
            System.out.println(cnt+"행 수정");
        } finally {
            DBManager.dbClose(ps, con);
        }
        return cnt;
    }//update

    public int deleteBook(int no) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        int cnt=0;

        try {
            con = DBManager.getConnection();

            String sql = "delete from book where no = ?";
            ps = con.prepareStatement(sql);
            ps.setInt(1, no);

            cnt = ps.executeUpdate();
            System.out.println(cnt+"행 수정");
        } finally {
            DBManager.dbClose(ps, con);
        }
        return cnt;
    }//delete

    public ArrayList<BookDTO> selectBook() throws SQLException{
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs=null;
        ArrayList<BookDTO> list = new ArrayList<BookDTO>();

        try {
            con = DBManager.getConnection();

            String sql = "select * from book order by no";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();

            while(rs.next()) {
                int no = rs.getInt(1);
                String title = rs.getString(2);
                int price = rs.getInt(3);
                String publisher = rs.getString(4);
                Timestamp joindate = rs.getTimestamp(5);

                BookDTO dto = new BookDTO(no,title,price,publisher,joindate);
                list.add(dto);
            }
            System.out.println("전체 데이터 수 = "+list.size());
        } finally {
            DBManager.dbClose(rs, ps, con);
        }
        return list;
    }//select

    public ArrayList<BookDTO> searchBook(int no) throws SQLException{
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs=null;
        ArrayList<BookDTO> list = new ArrayList<BookDTO>();

        try {
            con = DBManager.getConnection();

            String sql = "select * from book where no = ?";
            ps = con.prepareStatement(sql);
            ps.setInt(1, no);
            rs = ps.executeQuery();

            while(rs.next()) {
                int wno = rs.getInt(1);
                String title = rs.getString(2);
                int price = rs.getInt(3);
                String publisher = rs.getString(4);
                Timestamp joindate = rs.getTimestamp(5);

                BookDTO dto = new BookDTO(wno,title,price,publisher,joindate);
                list.add(dto);
            }
            System.out.println("검색 데이터 수 = "+list.size());
        } finally {
            DBManager.dbClose(rs, ps, con);
        }
        return list;
    }
}

BookManager

• 메인 클래스에서 사용할 메서드들의 모듈화
• 메뉴불러오기, 테이블 행별로 받아올 리스트등 정의

package book;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Scanner;

public class BookManager {
    public static final int FIND=1;
    public static final int ALL=2;
    private BookDAO bookDao;
    private Scanner sc = new Scanner(System.in);

    public BookManager() {
        bookDao = new BookDAO();
    }

    public void mainMenu() {
        System.out.println("\n--------main menu-----------");
        System.out.println("1.등록, 2.수정, 3.삭제, 4.검색, 5.전체조회, 6.종료");
        System.out.println("------------------------------");
        System.out.println("번호 입력");
    }

    public void insert() {
        System.out.println("책 제목 입력");
        String title = sc.nextLine();
        System.out.println("책 가격 입력");
        int price = sc.nextInt();
        sc.nextLine();
        System.out.println("출판사 입력");
        String publisher = sc.nextLine();
        BookDTO dto = new BookDTO();
        dto.setTitle(title);
        dto.setPrice(price);
        dto.setPublisher(publisher);
        try {
            int cnt = bookDao.insertBook(dto);
            if(cnt>0) System.out.println("입력성공");
            else System.out.println("입력실패");
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }//insert

    public void delete() {
        System.out.println("삭제할 번호 입력");
        int no = sc.nextInt();
        sc.nextLine();
        try {
            int cnt = bookDao.deleteBook(no);
            if(cnt>0) System.out.println("삭제성공");
            else System.out.println("삭제실패");
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }//delete

    public void update() {
        System.out.println("수정할 번호 입력");
        int no = sc.nextInt();
        sc.nextLine();
        System.out.println("수정 제목 입력");
        String title = sc.nextLine();
        System.out.println("수정 가격 입력");
        int price = sc.nextInt();
        sc.nextLine();
        System.out.println("수정 출판사 입력");
        String publisher = sc.nextLine();

        BookDTO dto = new BookDTO();
        dto.setNo(no);
        dto.setTitle(title);
        dto.setPrice(price);
        dto.setPublisher(publisher);
        try {
            int cnt = bookDao.updateBook(dto);
            if(cnt>0) System.out.println("입력성공");
            else System.out.println("입력실패");
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }//update

    public void select(int n) {
        ArrayList<BookDTO> list = null;
        if(n == this.ALL) {
            try {
                list = bookDao.selectBook();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }else if(n == this.FIND) {
            try {
                System.out.println("검색 번호 입력");
                int no = sc.nextInt();
                sc.nextLine();
                list = bookDao.searchBook(no);
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
        if (list == null || list.isEmpty()) {
            System.out.println("정보 없음");
            return;
        }
        System.out.println("----------책 목록----------");
        System.out.println("번호\t제목\t가격\t출판사\t등록일");
        for(int i=0 ; i<list.size();i++) {
            BookDTO dto = list.get(i);
            int no = dto.getNo();
            String title = dto.getTitle();
            int price = dto.getPrice();
            String publisher = dto.getPublisher();
            Timestamp joindate = dto.getJoindate();

            System.out.println(no+"\t"+title+"\t"+price+"\t"+publisher+"\t"+joindate);
        }
    }
}

BookConsole

• 프로그램을 실행할 메인 클래스

package book;

import java.util.Scanner;

public class BookConsole {

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

        Scanner sc = new Scanner(System.in);

        while(true) {
            bm.mainMenu();
            String no = sc.nextLine();

            switch(no) {
            case "1" :
                bm.insert();
                break;
            case "2" :
                bm.update();
                break;
            case "3" :
                bm.delete();
                break;
            case "4" :
                bm.select(BookManager.FIND);
                break;
            case "5" :
                bm.select(BookManager.ALL);
                break;
            case "6" :
                System.out.println("종료합니다");
                System.exit(0);
            default :
                System.out.println("잘못선택");
                continue;
            }
        }

    }

}