🖥WatchIt-DB-Project🖥

This is a project to deal with big data by using appropriate indexes in the database & dynamic queries.
It is a console project implementing functions in OTT(over-the-top) services.
It uses large amount of dummy data created instead of real data used in current OTT services.

대용량 데이터 처리를 위한 DB 및 Transaction을 설계 및 구현한 프로젝트입니다.
콘솔 프로그램이며, 기본적인 OTT 서비스를 위한 기능들을 포함하고 있습니다.
서비스 기능 구현을 위해 대용량 더미 데이터를 만들어 사용하였습니다.

🌱Project Introduction🌱

Goal

• To deal with big data (ex. over 1000000 data) we need to think of a way to effectively process them in the database
• This could be done by using appropriate indexs in relational databases

Service

• Login/ Register (로그인/회원가입)
• Buy tickets (이용권 결제)
• Multiple profiles per user (한 사용자에게 여러개의 프로필 할당)
• Show all contents to user (모든 컨텐츠 보여주기)
• Show contents by genre (장르별 컨텐츠 보여주기)
• Search contents / See user search history (컨탠츠 검색 및 사용자 검색기록 보기)
• View content specific information (컨텐츠 상세 정보 보기)
• Rate content (컨텐츠 별점 주기)
• Add content to my favorites (내 찜 목록에 컨텐츠 추가)

💻Installation and Setup💻

To begin using this template, choose one of the following options to get started:

• Fork, Clone, or Download on GitHub
• Open the project through Intellij IDEA
• Run "Main.java" to start using the program

To set up the database with pre-defined SQL statements, please refer to the file below:

• See SQL

📝Requirements📝

• Java Version 11
• MySQL Version 8.0.23
• MySQLWorkBench Version 8.0.20

🗂Project Directories 🗂

├── dao
│   ├── ContentCommentDao.java
│   ├── ContentDao.java
│   ├── MyContentDao.java
│   ├── OrdersDao.java
│   ├── RatingDao.java
│   ├── SearchHistoryDao.java
│   ├── TicketDao.java
│   ├── UserDao.java
│   └── UserProfileDao.java
├── entity
│   ├── ContentComment.java
│   ├── Content.java
│   ├── LoggedInUser.java
│   ├── MyContent.java
│   ├── Orders.java
│   ├── Rating.java
│   ├── SearchHistory.java
│   ├── Ticket.java
│   ├── User.java
│   └── UserProfile.java
├── enums
│   ├── ContentGenre.java
│   ├── ContentType.java
│   ├── RateStatus.java
│   ├── TicketType.java
│   └── UserStatus.java
└── Main.java

📙Project Functions📙

Image	Description
	Welcome page
	Register
	Login
	Choose User Profile
	Buy Ticket
	Main Page
	See Content by Type Choose content type
	See Content by Type List of all contents
	See Content by Genre Choose content genre
	See Content by Genre List of all contents
	See My Content List
	See Content Detail
	See Search History
	See My Comment List & Change My Comment

📌ERD📌

✨Connecting to Database✨

• Database is connected by using jdbc
• To use this code and connect to your local database, change the "USERNAME" & "PASSWORD"
• You may need to change the URL if your schema name is not "WatchIt"

        Connection conn = null;

        final String USERNAME = "ENTER_YOUR_MYSQL_USERNAME";
        final String PASSWORD = "ENTER_YOUR_MYSQL_PASSWORD";
        final String URL = "jdbc:mysql://localhost:3306/WatchIt?characterEncoding=latin1&useConfigs=maxPerformance";
        try{
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        }catch (ClassNotFoundException e){
            e.printStackTrace();
            System.out.println("Class not found!!");
        }catch (SQLException e){
            e.printStackTrace();
            System.out.println("Connection failed!!");
        }

📊Creating Big Data Example📊

1. Create a method making dynamic SQL statements
➡️ Use PreparedStatement to create dynamic queries & use set methods to insert data for each property

  public static void insertComments(ContentComment contentComment, Connection conn) throws SQLException {
        String sqlStmt = "insert into content_comment values(?,?,?,?);";
        PreparedStatement pStmt = null;
        try{
            pStmt = conn.prepareStatement(sqlStmt);
            pStmt.setInt(1,contentComment.getId());
            pStmt.setString(2,contentComment.getComment());
            pStmt.setInt(3,contentComment.getUserProfileId());
            pStmt.setInt(4,contentComment.getContentId());
            pStmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            pStmt.close();
        }
    }

2. Create method for making Entities
3. Call the method made in (1) and provide entity made in (2) & connection instance parameters

  private static void makeRandomContents() throws SQLException {
        for(int i =1;i<=5;i++){
            ContentDao contentDao = new ContentDao();
            Content content = new Content();
            content.setId(i);
            content.setContentType(getContentType(getRandomIndex(0,4)));
            content.setContentGenre(getContentGenre(getRandomIndex(0,12)));
            content.setTitle(getRandomString(5,10));
            content.setYear(getRandomIndex(1980,2022));
            content.setDescription(getRandomString(30,300));
            content.setPoster("https://watchIt.com/"+getRandomString(5,20)+".jpg");
            content.setVideo("https://watchIt.com/"+getRandomString(5,20)+".mp4");
            content.setTotalRateScore(getRandomDouble(0.0,5.0));
            content.setAgeLimit(getRandomIndex(0,20));
            contentDao.insertContent(content);
        }
    }

Result
➡️ 100000 data created in database

Creating Indexs

• Function: Showing all contents by type
  • Index needed in Content table
  • SQL statement for creating index:

    CREATE INDEX idx_type_content ON WatchIt.content(type);

• Function: Showing contents in user's my content list that the user didn't rate
  • Index needed in MyContent table
  • SQL statement for creating index:

    CREATE INDEX idx_profile_id_rating_status ON WatchIt.my_content(user_profile_id,rating_status);

• Function: Change rate status of a certain content in my content list
  • Index needed in MyContent table
  • SQL statement for creating index:

    CREATE INDEX dx_profile_content ON WatchIt.my_content(user_profile_id,content_id);

Transaction Example

• Function:
  • Update user ticket payment & status when "INACTIVE" user logged in
  • 사용자 로그인 시 사용자가 비활성화 된 사용자의 이용권 구매 및 status 업데이트
• SQL statements in transaction:

  • S1-1 :

          SELECT * FROM user WHERE username = ? and password = ? ;

  • S1-1 Explanation:

    • Check if user with username & password exists
    • 회원가입 된 사용자 중, 사용자 입력으로 들어온 username & password를 갖는 사용자가 있는지 확인 후 있다면 해당 사용자 조회

  • S1-2:

      SELECT * FROM ticket;  ( 정적 SQL ) 

  • S1-2 Explanation:

    • If user status is INACTIVE, redirect to payment page & show all ticket types
    • 사용자가 비활성화 된 사용자인 경우 이용원 구매 페이지로 이동하여 이용권을 모두 보여줌

  • S1-3:

      SELECT MAX( id ) as total FROM orders;

  • S1-3 Explanation:

    • If user bought a new ticket, get max id from order table
    • 사용자가 이용권을 구매하면 orders 테이블에 레코드를 하나 생성해야 하는데 이때 id값 설정을 위해 현재 orders 테이블에 레코드 중 id 칼럼의 최대값을 조회하여 이보다 1만큼 큰 수를 새 레코드의 id 값으로 설정한다.

  • S1-4:

      INSERT INTO orders values(?,?,?,?,?);

  • S1-4 Explanation:

    • If user bought a new ticket, add a record in order table
    • 사용자가 새 이용권을 구매했다면 order 테이블에 레코드 하나 생성

  • S1-5:

      UPDATE user SET status = ? WHERE id = ?;

  • S1-5 Explanation:

    • Update user status to ACTIVE
    • 사용자가의 새 이용원 구매가 성공적이며 사용자의 상태를 비활성화에서 활성화로 변경한다. 이때 동적 매개변수 id의 값은 S1-1에서 반은 결과 셋의 데이터 값의 일부이다.