BookRepository

package dao;

import java.sql.*;
import java.util.ArrayList;
import dto.Book;

public class BookRepository {
    private static BookRepository instance = new BookRepository();

    public static BookRepository getInstance() {
        return instance;
    }

    private String jdbcUrl = "jdbc:mysql://localhost:3306/BookmarketDB?useSSL=false&serverTimezone=UTC";
    private String jdbcUser = "root";
    private String jdbcPassword = "1234";

    public ArrayList<Book> getAllBooks() {
        ArrayList<Book> listOfBooks = new ArrayList<>();

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
            String sql = "SELECT * FROM book";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Book book = new Book(rs.getString("b_id"), rs.getString("b_name"), rs.getInt("b_unitPrice"));
                book.setAuthor(rs.getString("b_author"));
                book.setDescription(rs.getString("b_description"));
                book.setPublisher(rs.getString("b_publisher"));
                book.setCategory(rs.getString("b_category"));
                book.setUnitsInStock(rs.getLong("b_unitsInStock"));
                book.setReleaseDate(rs.getString("b_releaseDate"));
                book.setCondition(rs.getString("b_condition"));
                book.setFileName(rs.getString("b_fileName"));

                listOfBooks.add(book);
            }

            rs.close();
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return listOfBooks;
    }

    public Book getBookById(String bookId) {
        Book book = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
            String sql = "SELECT * FROM book WHERE b_id = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, bookId);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                book = new Book(rs.getString("b_id"), rs.getString("b_name"), rs.getInt("b_unitPrice"));
                book.setAuthor(rs.getString("b_author"));
                book.setDescription(rs.getString("b_description"));
                book.setPublisher(rs.getString("b_publisher"));
                book.setCategory(rs.getString("b_category"));
                book.setUnitsInStock(rs.getLong("b_unitsInStock"));
                book.setReleaseDate(rs.getString("b_releaseDate"));
                book.setCondition(rs.getString("b_condition"));
                book.setFileName(rs.getString("b_fileName"));
            }

            rs.close();
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return book;
    }

    public void addBook(Book book) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
            String sql = "INSERT INTO book VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, book.getBookId());
            pstmt.setString(2, book.getName());
            pstmt.setInt(3, book.getUnitPrice());
            pstmt.setString(4, book.getAuthor());
            pstmt.setString(5, book.getDescription());
            pstmt.setString(6, book.getPublisher());
            pstmt.setString(7, book.getCategory());
            pstmt.setLong(8, book.getUnitsInStock());
            pstmt.setString(9, book.getReleaseDate());
            pstmt.setString(10, book.getCondition());
            pstmt.setString(11, book.getFileName());

            pstmt.executeUpdate();

            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import dto.Book;

// Book 클래스가 돈이면 돈을 주고 받고 
public class BookRepository {
	//                                            ex) 빵<치즈>
	// 변수 : 전역변수, 참조변수 <북>                arrayList안에<Book>
	private ArrayList<Book> listOfBooks = new ArrayList<Book>();
	private static BookRepository instance = new BookRepository();
	
	public static BookRepository getInstance() {
	    return instance;
	}
	
	// 함수 : 생성자(클래스 이름과 같다) : 객체 생성시 전역변수 초기화 
	public BookRepository() {
		Book book1 = new Book("ISBN1234","C# 프로그래밍",27000);
		book1.setAuthor("우재님");
		book1.setDescription("C#을 처음 접하는 독자를 대상으로 일대일 수업처럼 자세히 설명한 책이다. 꼭 알아야 할 핵심 개념은 기본 예제로 최대한 쉽게 설명했으며, 중요한 내용은 응용 예제, 퀴즈, 셀프 스터디, 예제 모음으로 한번 더 복습할 수 있다.");
		book1.setPublisher("한빛아카데미");
		book1.setCategory("IT모바일");
		book1.setUnitsInStock(1000);
		book1.setReleaseDate("2022/10/06");
		book1.setFileName("ISBN1234.jpg");
		
		Book book2 = new Book("ISBN1235","자바마스터",30000);
		book2.setAuthor("송미영");
		book2.setDescription("자바를 처음 배우는 학생을 위해 자바의 기본 개념과 실습 에제를 그림을 이용하여 쉽게 설명합니다. 자바의 이론적 개념→기본 예제→프로젝트 순으로 단계별 학습이 가능하며, 각 챕터의 프로젝트를 실습하면서 온라인 서점을 완성할 수 있도록 구성하였습니다.");
		book2.setPublisher("한빛아카데미");
		book2.setCategory("IT모바일");
		book2.setUnitsInStock(1000);
		book2.setReleaseDate("2023/10/06");
		book2.setFileName("ISBN1235.jpg");
		
		Book book3 = new Book("ISBN1236","파이썬 프로그래밍",30000);
		book3.setAuthor("최성철");
		book3.setDescription("파이썬으로 프로그래밍을 시작하는 입문자가 쉽게 이해할 수 있도록 기본 개념을 상세하게 설명하며, 다양한 예제를 제시합니다. 또한 프로그래밍의 기초 원리를 이해하면서 파이썬으로 데이터를 처리하는 기법도 배웁니다.");
		book3.setPublisher("한빛아카데미");
		book3.setCategory("IT모바일");
		book3.setUnitsInStock(1000);
		book3.setReleaseDate("2024/10/06");
		book3.setFileName("ISBN1236.jpg");
		
		listOfBooks.add(book1);
		listOfBooks.add(book2);
		listOfBooks.add(book3);
		
	}
	
	// 함수 : 참조변수를 리턴              뱓는게 없음
	public ArrayList<Book> getAllBooks() {
		return listOfBooks;
	}
	
	// 함수 : 참조변수를 리턴              문자열하나줘
	public Book getBookById(String bookId) {
		
		 Connection conn = null;
		 PreparedStatement pstmt = null;
		 ResultSet rs = null;
		 Book book = null;

		 try {
		     Class.forName("com.mysql.cj.jdbc.Driver");
		     conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/BookmarketDB", "root", "1234");
		     String sql = "SELECT * FROM book WHERE b_id = ?";
		     pstmt = conn.prepareStatement(sql);
		     pstmt.setString(1, bookId);
		     rs = pstmt.executeQuery();

		     if (rs.next()) {
		         book = new Book(rs.getString("b_id"), rs.getString("b_name"), rs.getInt("b_unitPrice"));
		         book.setAuthor(rs.getString("b_author"));
		         book.setDescription(rs.getString("b_description"));
		         book.setPublisher(rs.getString("b_publisher"));
		         book.setCategory(rs.getString("b_category"));
		         book.setUnitsInStock(rs.getLong("b_unitsInStock"));
		         book.setReleaseDate(rs.getString("b_releaseDate"));
		         book.setCondition(rs.getString("b_condition"));
		         book.setFileName(rs.getString("b_fileName"));
		     }

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

		 return book;
	}
	
	public void addBook(Book book) {
		listOfBooks.add(book);
	}
	
}