[JDBC/JAVA/MySQL] DTO, DAO, Main / 도서 입력 테이블 추가,조회

 

DTO는 값을 설정하는 곳이라면 

DAO는 그 값을 불러오고, 추가하고, 변경하고, 삭제하는 기능들을 나열하는 공간이다. 

그리고 실행은 Main에서 처리 

 

package dto;

public class BookDTO {
	private String bookNo;
	private String bookTitle;
	private String bookAuthor;
	private int bookYear;
	private int bookPrice;
	private String bookPublisher;
  

	public BookDTO() {
	}

	public BookDTO(String bookNo, String bookTitle, String bookAuthor, int bookYear, int bookPrice,
			String bookPublisher) {
		super();
		this.bookNo = bookNo;
		this.bookTitle = bookTitle;
		this.bookAuthor = bookAuthor;
		this.bookYear = bookYear;
		this.bookPrice = bookPrice;
		this.bookPublisher = bookPublisher;
	}

	
	public String getBookNo() {
		return bookNo;
	}

	public void setBookNo(String bookNo) {
		this.bookNo = bookNo;
	}

	public String getBookTitle() {
		return bookTitle;
	}

	public void setBookTitle(String bookTitle) {
		this.bookTitle = bookTitle;
	}

	public String getBookAuthor() {
		return bookAuthor;
	}

	public void setBookAuthor(String bookAuthor) {
		this.bookAuthor = bookAuthor;
	}

	public int getBookYear() {
		return bookYear;
	}

	public void setBookYear(int bookYear) {
		this.bookYear = bookYear;
	}

	public int getBookPrice() {
		return bookPrice;
	}

	public void setBookPrice(int bookPrice) {
		this.bookPrice = bookPrice;
	}

	public String getBookPublisher() {
		return bookPublisher;
	}

	public void setBookPublisher(String bookPublisher) {
		this.bookPublisher = bookPublisher;
	}
}

 

package dao;

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

import dto.BookDTO;

public class BookDAO {
	
	public void insertBook(BookDTO bookDTO) {
	
		int insertbk = 0 ;
		Connection conn = null;
		PreparedStatement pt = null;
			
		try {
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bookdb", "root", "1234");
				 
		String insertSQL = "insert into book(bookNo, bookTitle, bookAuthor, bookYear, bookPrice, bookPublisher)"
								+ "values (?, ?, ?, ?, ?, ? )";
			
		pt = conn.prepareStatement(insertSQL);
	
		pt.setString(1, bookDTO.getBookNo());
		pt.setString(2, bookDTO.getBookTitle());
		pt.setString(3, bookDTO.getBookAuthor());
		pt.setInt(4, bookDTO.getBookYear());
		pt.setInt(5, bookDTO.getBookPrice());
		pt.setString(6, bookDTO.getBookPublisher());
		
		insertbk = pt.executeUpdate();
		
		pt.close();
		conn.close();
		}
		catch(ClassNotFoundException e) {
			System.out.println("mysql driver 미설치 또는 드라이버이름 오류");
			e.printStackTrace();
		}
		catch(SQLException e) {
			System.out.println("db접속오류이거나 sql문장오류");
			e.printStackTrace();
		}
		finally {
			try {
			pt.close();
			conn.close();
			}catch(SQLException e) {}
		}
	}  

	
public void selectBook() {
	
		BookDTO selectBook = new BookDTO();
		Connection conn = null;
		PreparedStatement pt	 = null;
		ResultSet rs = null;
			
		String selectSQL = "select bookNo,"
							+ "bookTitle, "
							+ "bookAuthor, "
							+ "bookYear, "
							+ "bookPrice, "
							+ "bookPublisher "
							+ "from book";
				
		try {			
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bookdb", "root", "1234");
		pt = conn.prepareStatement(selectSQL);
		rs = pt.executeQuery();
		
		while(rs.next()) {
		String bookNo = rs.getString("BookNo");
		String bookTitle = rs.getString("BookTitle");
		String bookAuthor = rs.getString("BookAuthor");
		int bookYear = rs.getInt("BookYear");
		int bookPrice = rs.getInt("BookPrice");
		String bookPublisher = rs.getString("BookPublisher");
				
		System.out.println(bookNo + "\t" + bookTitle + "\t" + bookAuthor + "\t" + bookYear + "\t" + bookPrice + "\t" + bookPublisher);
									
		}rs.close();
		 pt.close();
		 conn.close();
		 
		}catch(ClassNotFoundException e) {
			System.out.println("mysql driver 미설치 또는 드라이버이름 오류");
		    e.printStackTrace();
		}catch(SQLException e) {
			System.out.println("db접속오류이거나 sql문장오류");
			e.printStackTrace();
		}finally {
			try {
			conn.close();
			}catch(SQLException e) {}
		}
	}
}

 

package main;

import dao.BookDAO;
import dto.BookDTO;

public class BookTest {
	
	public static void main(String[] args) {
	 
		BookDTO bookDTO = new BookDTO();
		BookDAO dao = new BookDAO();
	
		bookDTO.setBookNo("04");
		bookDTO.setBookTitle("숨");
		bookDTO.setBookAuthor("테드창");
		bookDTO.setBookYear(2019);
		bookDTO.setBookPrice(16500);
		bookDTO.setBookPublisher("엘리");
		
		dao.insertBook(bookDTO);
		dao.selectBook();
	}	
}

 

import 는 ctrl+shift+O 키로 해결가능

변수값이 미정일때는 '?'을 써준다는 것 : 이에 관한 정확한 내용이 생각안나니 찾아볼것 ! 

 

확실히 자바 뒷부분의 복습이 제대로 이루어지지 않아 뒷편이 완전히 막힌다. 

인터페이스, 상속, 추상클래스 개념도 다시 짚어보자. 

 

 

[더 찾아봐야 할 것]

 

-DTO와 DAO 의 확실한 개념

-prepareStatement / Statement 구분하기 

-[java] try-catch 문 확실하게 작성하는 방법 정리하기 

-유튜브에 나와있는 DTO와 VO에 관한 개념들 정독해서 보기 

-[java] 메서드를 호출하고 전달하는 과정에 대한 분명한 이해.