kosta 클라우드 네이티브 애플리케이션 개발 과정 day 24

kosta 클라우드 네이티브 애플리케이션 개발 과정 day 24

 

JDBC: Java Database Connectivity 

- 자바를 이용한 데이터베이스 접속과 SQL 문장의 실행, 그리고 실행 결과로 얻어진 데이터 핸들링을 제공하는 방법과 절차에 관한 규약 

- 자바 프로그램 내에서 SQL 문을 실행하기 위한 자바 API 

 

 

 

JDBC를 이용한 데이터 베이스 연결방법 

 

0. import java.sql.*;

1. JDBC 드라이버 로딩 

2. Connection 얻어오기 

3. SQL문 준비 / 바인딩 / 실행 

4. 결과처리 

5. 자원정리 

 

 

 

 

package kosta.basic.day024;

import java.sql.*;

public class Main {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url = "jdbc:oracle:thin:@localhost:1521:XE";
            String id = "webdb";
            String pw = "oracle";
            connection = DriverManager.getConnection(url, id, pw);

            System.out.println("접속 성공");

            String query = "select author_id, author_name name, author_desc from author";
            preparedStatement = connection.prepareStatement(query);

            resultSet = preparedStatement.executeQuery();

            while(resultSet.next()){
                int authorId = resultSet.getInt("author_id");
                String authorName = resultSet.getString("name");
                String authorDesc = resultSet.getString("author_desc");
                System.out.printf("""
                        authorId: %s
                        authorName: %s
                        authorDesc: %s
                        """, authorId,authorName,authorDesc);
            }
        } catch (ClassNotFoundException | SQLException e){
            e.printStackTrace();
        }


    }
}

 

 

인텔리제이에서 외부 라이브러리에 oracle 설치를 해주기 

 

에러가 나는 경우 problem에서 build를 해주기 

 

 

 

 

1. connection 객체와 template 객체를 분리 -> db에 직접 닿는 액션 객체들을 별도 구현
2. setter interface를 구현하고 이를 람다로 필요한 곳에서 주입해주는 방식으로 확장성 구현
3. mapper interface를 통해 find 라는 특수 기능을 전담하는 방식을 구현하고 마찬가지로 람다로 사용
4. 복수 객체를 다루는 방식으로 books를 표현(크게 의미가 있는지는…)

 

 

package kosta.basic.day024.jdbc;

import java.sql.*;

public class ConnectionManager {

    private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:XE";
    private static final String DB_USERNAME = "webdb";
    private static final String DB_PW = "oracle";

    public static Connection getConnection() {
        try {
            Class.forName(DB_DRIVER);
            System.out.println("DB 접속 성공");
            return DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PW);
        } catch (SQLException | RuntimeException | ClassNotFoundException e) {
            e.printStackTrace();
            throw new IllegalStateException("error");
        }
    }
}

 

 

package kosta.basic.day024.jdbc;

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

public class JdbcTemplate {

    private Connection connection = ConnectionManager.getConnection();

    public int executeInsert(String sqlQuery, PreparedStatementSetter preparedStatementSetter) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sqlQuery);
        preparedStatementSetter.setPreparedStatement(preparedStatement);

        return preparedStatement.executeUpdate();
    }

    public int executeUpdate(String sqlQuery, PreparedStatementSetter preparedStatementSetter) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sqlQuery);
        preparedStatementSetter.setPreparedStatement(preparedStatement);

        return preparedStatement.executeUpdate();
    }

    public int executeDelete(String sqlQuery, PreparedStatementSetter preparedStatementSetter) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sqlQuery);
        preparedStatementSetter.setPreparedStatement(preparedStatement);

        return preparedStatement.executeUpdate();
    }

    public ResultSet executeQuery(String sqlQuery) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sqlQuery);
        return preparedStatement.executeQuery();
    }

    public ResultSet executeQuery(String sqlQuery, PreparedStatementSetter preparedStatementSetter) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sqlQuery);
        preparedStatementSetter.setPreparedStatement(preparedStatement);

        return preparedStatement.executeQuery();
    }
}

 

 

 

package kosta.basic.day024.jdbc;

import java.sql.PreparedStatement;
import java.sql.SQLException;

@FunctionalInterface
public interface PreparedStatementSetter {
    void setPreparedStatement(PreparedStatement preparedStatement) throws SQLException;
}

 

 

package kosta.basic.day024.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;

@FunctionalInterface
public interface RowMapper {
    Object mapRow(ResultSet resultSet) throws SQLException;
}

 

 

 

package kosta.basic.day024.book;

import kosta.basic.day024.jdbc.JdbcTemplate;

import java.sql.SQLException;

public class BookMain {
    public static void main(String[] args) throws SQLException {
        BookDao bookDao = new BookDao();
    }

    private static void bookActions(BookDao bookDao) throws SQLException {
        bookDao.insert(new Book(29, "dd", "출판사3", "1995", 26));
        bookDao.selectById();

        Book book2 = bookDao.findBookByTitle("삼국지", resultSet -> new Book(
                resultSet.getInt("book_id"),
                resultSet.getString("title"),
                resultSet.getString("publisher"),
                resultSet.getString("pub_date"),
                resultSet.getInt("pub_date")));

        System.out.println(book2);

        Books books = bookDao.findBooksByContaining("삼");
        books.show();
    }
}

 

 

 

package kosta.basic.day024.book;

import kosta.basic.day024.jdbc.JdbcTemplate;
import kosta.basic.day024.jdbc.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

public class BookDao {
    private static final JdbcTemplate JDBC_TEMPLATE = new JdbcTemplate();

    private enum queries {
        INSERT("INSERT INTO BOOK VALUES (?, ?, ?, ?, ?)"),
        DELETE("DELETE FROM BOOK WHERE book_id = ?"),
        UPDATE("""
                UPDATE AUTHOR author
                SET author.AUTHOR_NAME = ?,
                    author.AUTHOR_DESC = ?
                WHERE author.AUTHOR_ID = ?
                """),
        SELECT_BY_ID("SELECT book_id FROM BOOK"),
        SELECT_ALL("""
                SELECT *
                FROM book
                """),
        FIND_BY_TITLE("SELECT book_id, title, publisher, pub_date, author_id FROM book WHERE title = ?");

        private final String sql;

        queries(String sql) {
            this.sql = sql;
        }
        }

    public int insert(Book book) throws SQLException {
        return JDBC_TEMPLATE.executeUpdate(queries.INSERT.sql, preparedStatement -> {
            preparedStatement.setInt(1, book.getBookId());
            preparedStatement.setString(2, book.getTitle());
            preparedStatement.setString(3, book.getPublisher());
            preparedStatement.setString(4, book.getPublishDate());
            preparedStatement.setInt(5, book.getAuthorId());
        });
    }

    public int delete(Book Book, String deleteIndex) throws SQLException {
        return JDBC_TEMPLATE.executeDelete(queries.DELETE.sql, preparedStatement ->
                preparedStatement.setString(1, deleteIndex));
    }

    public int update(Book book, String updateIndexAt, String updateName, String updateDescription) throws SQLException {
        return JDBC_TEMPLATE.executeUpdate(queries.UPDATE.sql, preparedStatement -> {
            preparedStatement.setString(1, updateName);
            preparedStatement.setString(2, updateDescription);
            preparedStatement.setString(3, updateIndexAt);
        });
    }

    private ResultSet selectAll() throws SQLException {
        return JDBC_TEMPLATE.executeQuery(queries.SELECT_ALL.sql);
    }

    public void selectById() throws SQLException {
        ResultSet resultSet = JDBC_TEMPLATE.executeQuery(queries.SELECT_BY_ID.sql);
        printById(resultSet);
    }

    public Book findBookByTitle(String title, RowMapper rowMapper) throws SQLException {
        ResultSet resultSet = JDBC_TEMPLATE.executeQuery(queries.FIND_BY_TITLE.sql,
                preparedStatement -> preparedStatement.setString(1, title));

        Object object = null;
        if (resultSet.next()) {
            return (Book) rowMapper.mapRow(resultSet);
        }
        return (Book) object;
    }

    public Books findBooksByContaining(String value) throws SQLException {
        ResultSet resultSet = selectAll();

        List<Book> books = new ArrayList<>();
        while (resultSet.next()) {
            books.add(new Book(
                    resultSet.getInt("book_id"),
                    resultSet.getString("title"),
                    resultSet.getString("publisher"),
                    resultSet.getString("pub_date"),
                    resultSet.getInt("author_id")));
        }

        return new Books(books.stream()
                .filter(book -> book.contains(value))
                .collect(Collectors.toList()));
    }

    private static void printById(ResultSet resultSet) throws SQLException {
        while (resultSet.next()) {
            System.out.printf("""
                    bookId: %s
                    """, resultSet.getInt("book_id"));
        }
    }
}

 

 

 

package kosta.basic.day024.book;

public class Book {

    private int bookId;
    private String title;
    private String publisher;
    private String publishDate;
    private int authorId;


    public Book(int bookId, String title, String publisher, String publishDate, int authorId) {
        this.bookId = bookId;
        this.title = title;
        this.publisher = publisher;
        this.publishDate = publishDate;
        this.authorId = authorId;
    }


    public int getBookId() {
        return bookId;
    }

    public String getTitle() {
        return title;
    }

    public String getPublisher() {
        return publisher;
    }

    public String getPublishDate() {
        return publishDate;
    }

    public int getAuthorId() {
        return authorId;
    }

    public boolean contains(String value) {
        return title.contains(value) || publisher.contains(value) || publishDate.contains(value);
    }

    public void show() {
        System.out.printf(
                """     
                        <book %s>
                        title: %s
                        publisher: %s
                        publishDate: %s
                        authorId: %s
                                                
                        """, bookId, title, publisher, publishDate, authorId
        );
    }
}