본문 바로가기
✨ Club/이대솔브닥웹사이트 - 뀨엘 | Frontend(React,Next.js)

💽 이대백준랭작프로그램 [뀨업] - MySQL-데이터베이스

by 정람지 2024. 5. 27.

히언이경이 파트 SQL문들 체크하기~

체고

요구조건 잘 보고 체크하기

📊 < 초기 SQL문 > 확인하기

초기 세팅용이다

create database DB2024Team01;
use DB2024Team01;


create table DB2024_Students(
handle VARCHAR(50) NOT NULL PRIMARY KEY,
userlink VARCHAR(50)  NOT NULL ,
solvednum INT NOT NULL ,
tier varchar(30) NOT NULL, 
rank_ingroup INT  NOT NULL 
);

-- Organizations Table 
CREATE TABLE DB2024_Organizations (
    groupName VARCHAR(70) NOT NULL,
    solvedNum INT  NOT NULL ,
    ranking INT  NOT NULL PRIMARY KEY 
);

-- Problems 
CREATE TABLE DB2024_Problems (
    pid VARCHAR(40) NOT NULL PRIMARY KEY,
    pTitle VARCHAR(100),
    tier int NOT NULL,
    solvednum INT  NOT NULL ,
    link VARCHAR(200)  NOT NULL 
);

-- TodayPS
CREATE TABLE DB2024_TodayPS (
    pid VARCHAR(40) NOT NULL PRIMARY KEY,
    picked BOOLEAN,
    handle VARCHAR(50),
    FOREIGN KEY (pid) REFERENCES DB2024_Problems(pid),
    FOREIGN KEY (handle) REFERENCES DB2024_Students(handle)
);

-- PStogether 
CREATE TABLE DB2024_PStogether (
    togetherid VARCHAR(40) NOT NULL PRIMARY KEY,
    pid VARCHAR(40) NOT NULL,
    link VARCHAR(100)  NOT NULL ,
    handle VARCHAR(50) ,
    FOREIGN KEY (pid) REFERENCES DB2024_Problems(pid),
    FOREIGN KEY (handle) REFERENCES DB2024_Students(handle)
);

-- Algorithms
CREATE TABLE DB2024_Algorithms(
	  pid VARCHAR(40) NOT NULL,
    tag varchar(40) NOT NULL,
    PRIMARY KEY (pid, tag),
    FOREIGN KEY (pid) REFERENCES DB2024_Problems(pid)
);

-- 스키마 확인
select * from DB2024_Students order by rank_ingroup; 
select * from DB2024_Organizations ; 
select * from DB2024_Problems; 
select * from DB2024_TodayPS ;
select * from DB2024_PStogether;
select * from DB2024_Algorithms;

기본 구조 잡고 시작한다

 

그리고 무지막지한 십만 insert문들 생략..

교수님이 초기 데이터ㄷ들을 초기 SQL파일에 다 넣어놓기를 바라셔서 일단 이렇게 하고

실시간 변동 데이터사용 +크롤링 은 나중에 진짜로 배포할 때 수정하기로

 

INSERT INTO DB2024_Students(handle, userlink, solvednum, tier, rank_ingroup) VALUES
('celina324', 'https://solved.ac/profile/celina324', 1046, 'Platinum 5', 1),

핫핫 첫줄에 나다

 

📊 <자바 + 내부 SQL문> 확인하기

순위 기능

  • 현재 순위
  • 전체 순위(단체 순위)
  • 직전 등수 단체의 이름과 문제 차이 수
// 메서드1 현재 순위 구하기: groupname을 인자로 받아야 함 (이화여자대학교)
PreparedStatement pstmt = conn.prepareStatement(
						"select ranking, solvednum "
						+ "from DB2024_Organizations "
						+ "where groupname = ?");
						
pstmt.setString(1, groupname);
ResultSet rs = pstmt.executeQuery();
rs.next();
return new Object[] {rs.getInt(1), rs.getInt(2)}; 
// Object[] 타입으로 반환, 순서: 현재순위, 푼 문제수
		
			
// 메서드2 직전 등수 단체 구하기: groupname을 인자로 받아야 함 (이화여자대학교)
PreparedStatement pstmt = conn.prepareStatement(
						"select ranking, groupname, solvednum "
						+ "from DB2024_Organizations "
						+ "where ranking = (select ranking from GroupRanking where groupname = ?) - 1");
						
pstmt.setString(1, groupname);
ResultSet rs = pstmt.executeQuery();
rs.next();
return new Object[] {rs.getInt(1), rs.getString(2), rs.getInt(3)};
// Object[] 타입으로 반환, 순서: 순위, 단체명, 푼 문제수
			
// 메서드1,2 각각 Rank[], NextRank[]에 반환값 저장한다고 했을 때 
// 문제 차이 수: (int)nextRank[2]-(int)Rank[1] 

// 메서드3 전체 단체 순위:
PreparedStatement pstmt = conn.prepareStatement(
						"select ranking, groupname, solvednum from DB2024_Organizations Order by ranking");
						
ResultSet rs = pstmt.executeQuery();
ArrayList<Object[]> result = new ArrayList<Object[]>();

while(rs.next())
		result.add(new Object[] {rs.getInt(1), rs.getString(2), rs.getInt(3)});
			
return result;  
// ArrayList<Object[]> 타입으로 반환, Object 배열 내 순서: 순위, 단체명, 푼 문제수
  • 그룹 내(이화여자대학교 내) 자기 등수
    public ArrayList<Object[]> getStudentsOrderedByRank() throws SQLException {
        String query = "SELECT * FROM DB2024_Students ORDER BY rank_ingroup ASC";
        PreparedStatement pstmt = conn.prepareStatement(query);

        // Result
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Object[]> result = new ArrayList<>();

        while (rs.next()) {
            int rank_ingroup = rs.getInt("rank_ingroup");
            String handle = rs.getString("handle");
            String userlink = rs.getString("userlink");
            String tier = rs.getString("tier");
            int solvednum = rs.getInt("solvednum");

            // Add the row data to the result list
            result.add(new Object[]{rank_ingroup, handle, userlink, tier, solvednum});
        }

        rs.close();
        pstmt.close();

        return result;
    }

오늘의 문제 기능

  • 오늘의 추천 문제 ( 안 푼 문제 중 많이 푼 순 정렬 - 5/5/5 문제 )
    • 골 5
    • 실 5
    • 브 5
String query = ''
  • 찜하기 기능 ( 풀고 싶은 사람이 문제를 클릭한 뒤 아이디를 입력하면 오늘의 문제에서 삭제됨. 대신 하루 지나서도 안 풀리면 재등록 )
  • 옆에 찜한 사용자 띄우기

(푼 사용자 알기)

알고리즘 별 안 푼 문제 보기 기능

  • 각 알고리즘 태그(분류) 별로 안 푼 문제 보기 기능( 많이 푼 순 정렬 )
// whichTag=사용자 입력 받아서 아래 parameter로 넘겨줌.     
public ArrayList<Object[]> getProblemsByTag(String whichTag) throws SQLException {
        String query = "SELECT * FROM DB2024_VIEW_tag_"+wichTag+" ORDER BY solvednum DESC";
        PreparedStatement pstmt = conn.prepareStatement(query);

        ResultSet rs = pstmt.executeQuery();
        ArrayList<Object[]> result = new ArrayList<>();

        while (rs.next()) {
            // Process each row and add it to the result list
            String pid = rs.getString("pid");
            String pTitle = rs.getString("pTitle");
            String link = rs.getString("link");
            int solvednum = rs.getInt("solvednum");
            int tier = rs.getInt("tier");
            String tag = rs.getString("tag");

            result.add(new Object[]{pid, pTitle, link, solvednum, tier});
        }
               
        return result;
    }

티어 별 안 푼 문제 보기 기능

  • 각 티어(문제 티어) 별로 안 푼 문제 보기 기능( 많이 푼 순 정렬 )
public ArrayList<ArrayList<Object[]>> getProblemsByTier() throws SQLException {
        String query = "SELECT * FROM DB2024_Problems natural join DB2024_Algorithms WHERE tier = ?";
        PreparedStatement pstmt = conn.prepareStatement(query);

        ArrayList<ArrayList<Object[]>> result = new ArrayList<>();

        // Iterate over tiers (adjust range as needed)
        for (int whichTier = 0; whichTier <= 36; whichTier++) {
            // Create a list for each tier
            ArrayList<Object[]> tierProblems = new ArrayList<>();

            // Set the tier parameter
            pstmt.setInt(1, whichTier);

            // Execute the query
            ResultSet rs = pstmt.executeQuery();

            // Process each row and add it to the tierProblems list
            while (rs.next()) {
                String pid = rs.getString("pid");
                String pTitle = rs.getString("pTitle");
                String link = rs.getString("link");
                int solvednum = rs.getInt("solvednum");
                int tier = rs.getInt("tier");
                String tag = rs.getString("tag");

                tierProblems.add(new Object[]{pid, pTitle, link, solvednum, tier, tag});
            }

            // Add the tierProblems list to the result
            result.add(tierProblems);
        }
        return result;
    }
}

같이 풀어요 기능 (2가지 버전)

  • 사용자가 문제 정보(문제 번호, 제목, 티어, 알고리즘) + 깃허브 링크 게시글로 등록
int togetherid=0 //초기화

// 사용자 입력
togetherid+=1;
System.out.print("문제 번호 입력: ");
String pid = scanner.nextLine();
System.out.print("문제 링크 입력: ");
String link = scanner.nextLine();
System.out.print("게시자 핸들 입력 (생략 가능): ");
String handle = scanner.nextLine();
 
 //DB에 INSERT    
String sql = "INSERT INTO DB2024_PStogether (togetherid, pid, link, handle) VALUES (?, ?, ?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, togetherid);
pstmt.setString(2, pid);
pstmt.setString(3, link);
pstmt.setString(4, handle);
pStmt.executeUpdate()

ver.2 (은채 의견)

-- PStogether 
CREATE TABLE DB2024_PStogether (
    togetherid VARCHAR(40) NOT NULL PRIMARY KEY,
    togethertitle VARCHAR(100)  NOT NULL**,**
    pid VARCHAR(40) NOT NULL,
    gitlink VARCHAR(100)  NOT NULL ,
    handle VARCHAR(50) ,
    FOREIGN KEY (pid) REFERENCES DB2024_Problems(pid),
    FOREIGN KEY (handle) REFERENCES DB2024_Students(handle)
);


int togetherid=0 //초기화

// 사용자 입력
togetherid+=1;
System.out.print("게시글 제목 입력: "); // 테이블 스키마 수정 필요
String togethertitle= scanner.nextLine();
System.out.print("문제 번호 입력: ");
String pid = scanner.nextLine();
System.out.print("깃허브 pull request 링크 입력: "); // 테이블 스키마 수정 필요
String gitlink = scanner.nextLine();
System.out.print("게시자 핸들 입력 (생략을 원하면 그냥 엔터): ");  //빈 문자열일시 null로 입력
String handle = scanner.nextLine();

if (handle.isEmpty()) {
		handle = null;
}

 //DB에 INSERT    
String sql = "INSERT INTO DB2024_PStogether (togetherid, togethertitle, pid, gitlink, handle) VALUES (?, ?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, togetherid);
pstmt.setString(2, togethertitle);
pstmt.setString(3, pid);
pstmt.setString(4, gitlink);
pstmt.setString(5, handle);
pStmt.executeUpdate()
  • 문제 번호로 문제 검색
//사용자 입력
System.out.print("문제 번호 입력: ");
String pid = scanner.nextLine();

//DB 검색
String query= "SELECT * FROM PStogether WHERE pid = ?";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, pid); 

//결과
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
    String togetherId = rs.getString("togetherid");
    String pid = rs.getString("pid");
    String link = rs.getString("link");
    String handle = rs.getString("handle");    
    
    // 데이터 간에 컴마 구분, 튜플 간에 줄바꿈 구분
    result.append(togetherId )
          .append(",")
          .append(pid )
          .append(",")
          .append(link )
          .append(",")
          .append(handle)
          .append("\\n")
 }
// result.toString를 반환

ver. 2 (은채 의견)

-- PStogether 
CREATE TABLE DB2024_PStogether (
    togetherid VARCHAR(40) NOT NULL PRIMARY KEY,
    togethertitle VARCHAR(100)  NOT NULL**,**
    pid VARCHAR(40) NOT NULL,
    gitlink VARCHAR(100)  NOT NULL ,
    handle VARCHAR(50) ,
    FOREIGN KEY (pid) REFERENCES DB2024_Problems(pid),
    FOREIGN KEY (handle) REFERENCES DB2024_Students(handle)
);


//사용자 입력
System.out.print("문제 번호 입력: ");
String pid = scanner.nextLine();

//DB 검색
String query= "SELECT togetherid,togethertitle, handle  FROM PStogether WHERE pid = ?";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, pid); 

//결과
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
    String togetherid = rs.getString("togetherid");
    String togethertitle = rs.getString("togethertitle");
    String handle = rs.getString("handle");    
    
    // 데이터 간에 컴마 구분, 튜플 간에 줄바꿈 구분
    result.append(pid)
          .append(",")
          .append(togethertitle)
          .append(",")
          .append(handle)
          .append("\\n"); 
 }
 
// result.toString를 반환
저작자표시

'✨ Club > 이대솔브닥웹사이트 - 뀨엘 | Frontend(React,Next.js)' 카테고리의 다른 글

📊 이대백준랭작프로그램 [뀨업] - 프론트 : API 연결 1  (1) 2024.05.27
💽 이대백준랭작프로그램 [뀨업] - SpringBoot 백엔드  (0) 2024.05.27
📊 이대백준랭작프로그램 [뀨업] - 프론트 : 리액트/next.js 3  (0) 2024.05.21
📊 이대백준랭작프로그램 [뀨업] - 프론트 : 리액트/next.js 2  (0) 2024.05.20
📊 이대백준랭작프로그램 [뀨업] - 프론트 : 리액트/next.js 1  (0) 2024.05.16

관련글

티스토리툴바