[프로젝트] 서양수박 - day01

개요

개발 목적

• 음악 사이트의 차트 조회 과정을 구현해본다.
• 음악 사이트의 플레이 리스트 기능을 구현해본다.
• JAVA, SQL 문법과 JDBC를 학습한다.

개발 환경

• 사용 언어: Java, SQL
• 사용 툴: Eclipse 2022-12, Oracle SQL Developer 19.2.1
• DB: Oracle 11g Express Edition

테이블 구조

테이블은 Music, Playlist, Userlist 3개로 이루어져 있다. Music은 음악 정보를 저장하고 Userlist는 회원 정보를 저장한다. Playlist는 회원별 플레이 리스트를 저장하며 Music의 music_no, Userlist의 user_no를 외래키로 가진다.
(테이블 그리기: AQueryTool)

프로젝트 구조

공통적으로 인기 차트, 노래 검색 기능을 사용할 수 있다.
인기 차트는 플레이 리스트에 추가된 수를 기준으로 정하며 전체 차트, 남성 인기 차트, 여성 인기 차트 3가지가 있다. 노래 검색은 특정 문자를 포함하는 가수의 노래 검색, 특정 문자를 포함하는 제목 검색, 특정 연도에 발매한 노래 검색, 제목+가수로 노래 검색이 가능하다.
비로그인 상황이라면 로그인, 회원가입이 가능하고, 로그인 상황이라면 플레이 리스트, 마이페이지가 추가된다. 플레이 리스트는 나의 플레이 리스트 조회, 노래 추가, 삭제가 가능하다. 마이페이지에서는 내 정보 조회, 수정, 로그아웃 및 탈퇴를 할 수 있다.

오류 및 문제점

1. 로그인 정보 저장

• 문제점: while 문 내부에 회원 번호, 회원 아이디를 변수로 저장해 로그인 상태를 유지한다. 하지만 코드를 간편하게 보기 위해 함수로 작성하며 변수를 변경할 수 없게 되었다.
• 해결 방안: 함수의 리턴 타입을 Map<Integer, String>으로 설정해 로그아웃, 탈퇴 시 값을 삭제하고 로그인 시 값을 추가한다.

private static Map<Integer, String> signInAndOut(Scanner sc, UserlistService uService) {}

public static void main(String[] args) {
	Map<Integer, String> user = mypage(sc, userNo, userId, uService);
	if (user.size() == 1) {
		for (Integer no : user.keySet()) {
			userNo = no;
			userId = user.get(no);
		}
	} else {
		userNo = 0;
		userId = null;
	}
}

2. ORA-00911: invalid character

• 문제점: 코드 실행 시 'ORA-00911: invalid character' 에러가 발생한다.
• 해결 방안: SQL 문장을 그대로 복사하며 많이 발생하는 오류이다. Java Project의 SQL 문장에서 세미콜론을 제외한다.

// 변경 전
String sql = """
		select *
		from music
		where music_name=? and singer=?;
			""";
// 변경 후
String sql = """
		select *
		from music
		where music_name=? and singer=?
			""";

3. 인기 차트 플레이 리스트 추가 개수 출력

• 문제점: MusicVO는 music_no, music_name, singer, relase_date로 이루어져 있는데 인기 차트에서 존재하지 않는 칼럼인 플레이 리스트에 추가된 횟수를 함께 출력하고자 한다. 따라서 List<MusicVO> 타입으로 데이터를 출력할 수 없다.
• 해결 방안: List<Map<MusicVO, Integer>> 타입으로 변경해 음악 정보, 추가된 횟수를 함께 리스트에 저장한다.

public List<Map<MusicVO, Integer>> selectAllChart(int ranking) {}

4. PreparedStatement: 부적합한 열 인덱스

• 문제점: PreparedStatement의 바인딩 변수와 문자열 비교 연산자 like를 함께 사용할 수 없다.
• 해결 방안: '%'와 변수를 ||로 연결한다.

// 변경 전
String sql = """
		select *
		from music
		where music_name like '%?%'
		order by music_name asc, singer asc
			""";
// 변경 후
String sql = """
		select *
		from music
		where music_name like '%'||?||'%'
		order by music_name asc, singer asc
			""";

5. 플레이 리스트 추가하면 null도 추가

• 문제점: 플레이 리스트에 노래 추가 시 존재하지 않는 노래가 null로 추가된다.
• 해결 방안: Playlist 테이블 생성 시 music_no, user_no를 not null로 제한한다.

create table playlist
(list_no number constraint playlist_no_pk primary key,
music_no number references music(music_no) on delete cascade not null,
user_no number references userlist(user_no) on delete cascade not null,
constraint playlist_uk unique (music_no, user_no));

진행 상황

1. DB 사용자 생성

2. 테이블 생성

-- MUSIC 테이블 생성
create table music
(music_no number constraint music_no_pk primary key, 
music_name varchar2(50) not null, 
singer varchar2(30) not null,
release_date date not null);

-- USERLIST 테이블 생성
create table userlist
(user_no number constraint user_no_pk primary key,
user_id varchar2(20) constraint user_id_uk unique not null,
user_pw varchar2(20) not null,
user_name varchar2(30) not null,
user_day date not null,
user_email varchar2(30) constraint user_email_uk unique not null,
user_gender char(1) constraint user_gender_check check(user_gender in ('M', 'W')) not null);

-- PLAYLIST 테이블 생성
create table playlist
(list_no number constraint playlist_no_pk primary key,
music_no number references music(music_no) on delete cascade not null,
user_no number references userlist(user_no) on delete cascade not null,
constraint playlist_uk unique (music_no, user_no));

3. JDBC 연결

4. 인기 차트

// MusicDAO.java
public List<Map<MusicVO, Integer>> selectAllChart(int ranking) {
	String sql = """
			select rownum, music_name, singer, release_date, sorted.개수
			from music, (select music_no, count(*) 개수
			    from music join playlist using (music_no)
			    group by music_no, music_name
			    order by 개수 desc, music_name asc) sorted
			where sorted.music_no=music.music_no
			and rownum <= ?
				""";
	List<Map<MusicVO, Integer>> musiclist = new ArrayList<>();
	conn = OracleUtil.getConnection();
	try {
		pst = conn.prepareStatement(sql);
		pst.setInt(1, ranking);
		rs = pst.executeQuery();

		while (rs.next()) {
			Map<MusicVO, Integer> music = makeChart(rs);
			musiclist.add(music);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		OracleUtil.dbDisconnect(rs, pst, conn);
	}
    
	return musiclist;
}
    
// MusicService.java
public List<Map<MusicVO, Integer>> selectAllChart(int num) {
	return musicDao.selectAllChart(num);
}

// MusicView.java
public static void print(List<Map<MusicVO, Integer>> musiclist, int idx) {
	for (Map<MusicVO, Integer> musics : musiclist) {
		for (MusicVO my : musics.keySet()) {
			System.out.printf("%3d. [♡%d]: ", idx, musics.get(my));
			System.out.println(my);
			idx++;
		}
	}
	System.out.println();
}

5. 노래 검색

// MusicDAO.java
public List<MusicVO> searchTitle(String title) {
	String sql = """
			select *
			from music
			where music_name like initcap('%'||?||'%')
			order by music_name asc, singer asc
				""";

	List<MusicVO> musiclist = new ArrayList<>();
	conn = OracleUtil.getConnection();
	try {
		pst = conn.prepareStatement(sql);
		pst.setString(1, title);
		rs = pst.executeQuery();

		while (rs.next()) {
			MusicVO music = makeMusic(rs);
			musiclist.add(music);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		OracleUtil.dbDisconnect(rs, pst, conn);
	}

	return musiclist;
}

// MusicService.java
public List<MusicVO> searchTitle(String title) {
	return musicDao.searchTitle(title);
}

// MusicView.java
public static void print(List<MusicVO> musiclist) {
	for (MusicVO music : musiclist) {
		System.out.println(music);
	}
	System.out.println();
}

6. 플레이 리스트 조회

// PlaylistDAO.java
public List<MusicVO> showPlaylist(int userno) {
	String sql = """
			select *
			from playlist join music using (music_no)
			join userlist using (user_no)
			where user_no=?
			""";
	List<MusicVO> playlist = new ArrayList<>();
	conn = OracleUtil.getConnection();
	try {
		pst = conn.prepareStatement(sql);
		pst.setInt(1, userno);
		rs = pst.executeQuery();

		while (rs.next()) {
			MusicVO music = makeMusic(rs);
			playlist.add(music);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		OracleUtil.dbDisconnect(rs, pst, conn);
	}

	return playlist;
}

// PlaylistService.java
public List<MusicVO> showPlaylist(int userno) {
	return playlistDao.showPlaylist(userno);
}