Oracle 16일차 - 지니뮤직, 멜론뮤직 출력

2020.08.26(수)

 

테이블 생성 = 데이터 수집 = 화면

 

테이블 생성

 번호 : 음악번호 (Rank)

 제목

 포스터

 가수명

 앨범

 CATE

 

Jsoup WebContent-WEB-INF-lib에 복붙 jsoup-1.13.1.jar

try~catch(Exception ex){} => 에러가 나도 점프해라 

SELECT avg(sal) FROM emp;
SELECT ename, sal FROM emp WHERE sal<2073;
-- 서브쿼리 : 여러개를 묶어서 한번에 전송 => 속도 빨라짐
SELECT ename, sal FROM emp WHERE sal<(SELECT AVG(sal) FROM emp);

GENIE music

 

CREATE TABLE music_genre(
    no NUMBER,
    genre VARCHAR2(20) CONSTRAINT mg_genre_nn NOT NULL,
    CONSTRAINT mg_no_pk PRIMARY KEY(no)
);
CREATE TABLE music(
    mno NUMBER,
    cateno NUMBER,
    title VARCHAR2(300) CONSTRAINT music_title_nn NOT NULL,
    poster VARCHAR2(260) CONSTRAINT music_poster_nn NOT NULL,
    singer VARCHAR2(100) CONSTRAINT music_singer_nn NOT NULL,
    album VARCHAR2(100) CONSTRAINT music_album_nn NOT NULL,
    CONSTRAINT music_mno_pk PRIMARY KEY(mno),
    CONSTRAINT music_cateno_fk FOREIGN KEY(cateno)
    REFERENCES music_genre(no)
);
-- 가요 POP OST 트롯 EDM JAZZ CLASSIC JPOP CMM 기타

INSERT INTO music_genre VALUES(1, '가요');
INSERT INTO music_genre VALUES(2, 'POP');
INSERT INTO music_genre VALUES(3, 'OST');
INSERT INTO music_genre VALUES(4, '트롯');
INSERT INTO music_genre VALUES(5, 'EDM');
INSERT INTO music_genre VALUES(6, 'JAZZ');
INSERT INTO music_genre VALUES(7, 'CLASSIC');
INSERT INTO music_genre VALUES(8, 'JPOP');
INSERT INTO music_genre VALUES(9, 'CMM');
INSERT INTO music_genre VALUES(10, '기타');
COMMIT;

-- mno가 자동 증가
CREATE SEQUENCE music_mno_seq 
    START WITH 1
    INCREMENT BY 1 
    NOCYCLE 
    NOCACHE;
    
DESC music;

SELECT COUNT(*) FROM music;
DELETE FROM music WHERE cateno=3;
DROP TABLE music;

SELECT avg(sal) FROM emp;
SELECT ename, sal FROM emp WHERE sal<2073;
-- 서브쿼리 : 여러개를 묶어서 한번에 전송 => 속도 빨라짐
SELECT ename, sal FROM emp WHERE sal<(SELECT AVG(sal) FROM emp);

 

MELON music2

CREATE TABLE music_genre2(
    no NUMBER,
    genre VARCHAR2(20) CONSTRAINT mg2_genre_nn NOT NULL,
    CONSTRAINT mg2_no_pk PRIMARY KEY(no)
);

CREATE TABLE music2(
    mno NUMBER,
    cateno NUMBER,
    title VARCHAR2(300) CONSTRAINT music2_title_nn NOT NULL,
    poster VARCHAR2(260) CONSTRAINT music2_poster_nn NOT NULL,
    singer VARCHAR2(100) CONSTRAINT music2_singer_nn NOT NULL,
    album VARCHAR2(100) CONSTRAINT music2_album_nn NOT NULL,
    CONSTRAINT music2_mno_pk PRIMARY KEY(mno),
    CONSTRAINT music2_cateno_fk FOREIGN KEY(cateno)
    REFERENCES music_genre2(no)
);

INSERT INTO music_genre2 VALUES(1, '발라드');
INSERT INTO music_genre2 VALUES(2, '댄스');
INSERT INTO music_genre2 VALUES(3, '랩/힙합');
INSERT INTO music_genre2 VALUES(4, 'RNB/Soul');
INSERT INTO music_genre2 VALUES(5, '인디음악');
INSERT INTO music_genre2 VALUES(6, '록/메탈');
INSERT INTO music_genre2 VALUES(7, '트로트');
INSERT INTO music_genre2 VALUES(8, '포크/블루스');
COMMIT;
DELETE FROM music_genre2 WHERE no BETWEEN 1 and 8;

SELECT * FROM music_genre2;
SELECT * FROM music2;
DELETE FROM music2 WHERE cateno=2;

-- mno가 자동 증가
CREATE SEQUENCE music_mno_seq2 
    START WITH 1
    INCREMENT BY 1 
    NOCYCLE 
    NOCACHE;

 

GENIE

- JAVA Class

MusicVO

package com.sist.manager;
/*
 *  MNO    NOT NULL NUMBER        
	CATENO          NUMBER        
	TITLE  NOT NULL VARCHAR2(300) 
	POSTER NOT NULL VARCHAR2(260) 
	SINGER NOT NULL VARCHAR2(100) 
	ALBUM  NOT NULL VARCHAR2(100) 
 */
public class MusicVO {
	private int mno;
	private int cateno;
	private String title;
	private String poster;
	private String singer;
	private String album;
	private int rank;
	
	public int getRank() {
		return rank;
	}
	public void setRank(int rank) {
		this.rank = rank;
	}
	public int getMno() {
		return mno;
	}
	public void setMno(int mno) {
		this.mno = mno;
	}
	public int getCateno() {
		return cateno;
	}
	public void setCateno(int cateno) {
		this.cateno = cateno;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getPoster() {
		return poster;
	}
	public void setPoster(String poster) {
		this.poster = poster;
	}
	public String getSinger() {
		return singer;
	}
	public void setSinger(String singer) {
		this.singer = singer;
	}
	public String getAlbum() {
		return album;
	}
	public void setAlbum(String album) {
		this.album = album;
	}
	
}

MusicManager

package com.sist.manager;

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.select.Elements;

import com.sist.dao.MusicDAO;

/* 
      <tr class="list" songId="90726458">
    <td class="check"><input type="checkbox" class="select-check" title=" 내가 아는 그대 "/>
  <td class="number" list-role="번호">1
    <td><a href="#" class="cover"  onclick="fnViewAlbumLayer(81549860); return false;" ontouchend="fnViewAlbumLayer(81549860); return false;" ><span class="mask"></span><img onerror="this.src='//image.genie.co.kr/imageg/web/common/blank_68.gif';" src="//image.genie.co.kr/Y/IMAGE/IMG_ALBUM/081/549/860/81549860_1598338597705_1_140x140.JPG" alt="내가 아는 그대" /></a></td>
    <td class="link"><a href="#" class="btn-basic btn-info" onclick="fnViewSongInfo(90726458); return false;" ontouchend="fnViewSongInfo(90726458); return false;" >곡 제목 정보 페이지</a></td>
      <td class="info">
           <a href="#" class="title ellipsis" title="내가 아는 그대" onclick="fnPlaySong('90726458;','1'); return false;" ontouchend="fnPlaySong('90726458;','1'); return false;">
내가 아는 그대</a>
            <a href="#" class="artist ellipsis"onclick="fnViewArtist(80635263); return false;" ontouchend="fnViewArtist(80635263); return false;" >이사배</a>           <div class="toggle-button-box" id="hide-button">
               <button type="button" class="btn artist-etc"onclick="fnRelationArtistList('90726458'); artist_etc_layer._show(this);return false;" ontouchend="fnRelationArtistList('90726458'); artist_etc_layer._show(this);return false;" >외</button>
               <dl class="list" id="RelationArtist_90726458">
               </dl>
           </div>
            <i class="bar">|</i>
            <a href="#" class="albumtitle ellipsis" onclick="fnViewAlbumLayer(81549860); return false;" ontouchend="fnViewAlbumLayer(81549860); return false;" >내가 아는 그대 (X-MAS Project Special)</a>
        </td>
 */
public class MusicManager {
	public void musicAllData() {
		MusicDAO dao = new MusicDAO();
		try {
			int k=1;
			for(int i=1; i<=5; i++) {
				Document doc=Jsoup.connect("https://www.genie.co.kr/genre/L0207?genreCode=L0207&pg="+i).get();
				Elements title = doc.select("td.info a.title");
				Elements singer = doc.select("td.info a.artist");
				Elements album = doc.select("td.info a.albumtitle");
				Elements poster = doc.select("a.cover img");
			
			for(int j=0; j<title.size(); j++) {
				try {
					MusicVO vo = new MusicVO();
					System.out.println("번호"+k++);
					System.out.println("cateno:1");
					System.out.println("제목:"+title.get(j).text());
					System.out.println("가수명:"+singer.get(j).text());
					System.out.println("앨범:"+album.get(j).text());
					System.out.println("포스터:"+poster.get(j).attr("src"));
					System.out.println("============================");
					// vo에 값을 채운다 => DAO
					vo.setCateno(10);
					vo.setTitle(title.get(j).text());
					vo.setSinger(singer.get(j).text());
					vo.setAlbum(album.get(j).text());
					vo.setPoster(poster.get(j).attr("src"));
					// DAO로 전송
					dao.musicInsert(vo);
					Thread.sleep(100);
				}catch(Exception ex) {}
			}
			System.out.println("End...");
		}
			
		}catch(Exception ex) {}
	}
	public static void main(String[] args) {
		MusicManager m = new MusicManager();
		m.musicAllData();
	}

}

MusicDAO

package com.sist.dao;

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

import com.sist.manager.MusicVO;

public class MusicDAO {
	private Connection conn;
	private PreparedStatement ps;
	private final String URL="jdbc:oracle:thin:@localhost:1521:XE";
	public MusicDAO() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}catch(Exception ex) {}
	}
	public void getConnection() {
		try {
			conn=DriverManager.getConnection(URL,"hr","happy");
		}catch(Exception ex) {}
	}
	public void disConnection() {
		try {
			if(ps!=null) ps.close();
			if(conn!=null) conn.close();
		}catch(Exception ex) {}
	}
	
	//기능
	public void musicInsert(MusicVO vo) {
		try {
			getConnection();
			String sql="INSERT INTO music VALUES("
					+"music_mno_seq.nextval,?,?,?,?,?)";
								//하나씩 증가
			ps=conn.prepareStatement(sql);
			// ?에 값을 채운다
			ps.setInt(1, vo.getCateno());
			ps.setString(2, vo.getTitle());
			ps.setString(3, vo.getPoster());
			ps.setString(4, vo.getSinger());
			ps.setString(5, vo.getAlbum());
			// 실행 명령
			ps.executeUpdate(); // INSERT문장을 실행 => COMMIT 자동으로 됨
		}catch(Exception ex) {
			System.out.println(ex.getMessage());
		}finally {
			disConnection();
		}
	}
		//장르
		public ArrayList<String> musicGenreAllData(){
			ArrayList<String> list = new ArrayList<String>();
			try {
				getConnection();
				String sql="SELECT genre FROM music_genre "
						+"ORDER BY no";
				ps=conn.prepareStatement(sql);
				ResultSet rs=ps.executeQuery();
				while(rs.next()) {
					String genre=rs.getString(1);
					list.add(genre);
				}
				rs.close();
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}finally {
				disConnection();
			}
			return list;
		}
		// Music 출력
		public ArrayList<MusicVO> musicAllData(int cateno, int page){
			ArrayList<MusicVO> list = new ArrayList<MusicVO>();
			try {
				// subquery
				/*
				 * 	SELECT ename, (SELECT~~) => (컬럼대신) 스칼라 서브쿼리
				 *  FROM (SELECT~~) => 인라인뷰
				 *  WHERE sal=(SELECT~) => 단일행 서브쿼리, 다중행 서브쿼리
				 *   ===> 다중컬럼 서브쿼리
				 *   INSERT INTO table_name VALUES((SELECT NVL(MAX(no)+1,1) ,?,?,?)
				 *	 JOIN => SELECT만 사용이 가능
				 *	 SUBQUERY => DML전체 
				 */
				getConnection();
				String sql="SELECT mno, title, poster, singer, album, RANK() OVER(ORDER BY mno ASC), num "
						+"FROM (SELECT mno, title, poster, singer, album, rownum as num "
						+"FROM (SELECT mno, title, poster, singer, album "
						+"FROM music WHERE cateno=? ORDER BY mno)) "
						+"WHERE num BETWEEN ? AND ?"; // 페이징기법
				int rowSize=30;
				int start=(rowSize*page)-(rowSize-1);
				// rownum ==> 시작번호 (1)
				int end = rowSize*page;
				
				ps=conn.prepareStatement(sql);
				ps.setInt(1, cateno);
				ps.setInt(2, start);
				ps.setInt(3, end);
				
				// 실행
				ResultSet rs = ps.executeQuery();
				while(rs.next()) {
					MusicVO vo = new MusicVO();
					vo.setMno(rs.getInt(1));
					vo.setTitle(rs.getString(2));
					vo.setPoster(rs.getString(3));
					vo.setSinger(rs.getString(4));
					vo.setAlbum(rs.getString(5));
					vo.setRank(rs.getInt(6));
					list.add(vo);
				}
				rs.close();
				
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}finally {
				disConnection();
			}
			return list;
		}
		public String musicGetGenre(int cateno) {
			String genre = "";
			try {
				getConnection();
				String sql="SELECT genre FROM music_genre "
						+"WHERE no=?";
				ps=conn.prepareStatement(sql);
				ps.setInt(1, cateno);
				ResultSet rs = ps.executeQuery();
				rs.next();
				genre=rs.getString(1);
				rs.close();
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}finally {
				disConnection();
			}
			return genre;
		}
}

 

-JSP

music_main

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="java.util.*, com.sist.dao.*" %>
<%
	MusicDAO dao = new MusicDAO();
	ArrayList<String> mList = dao.musicGenreAllData();
	
	String mode=request.getParameter("mode");
	String jsp="";
	if(mode==null)
		jsp="home.jsp";
	else
		jsp="music.jsp";
%>
<!DOCTYPE html>
<html>
<head>
  <title>지니뮤직</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>

<nav class="navbar navbar-inverse">
  <div class="container-fluid">
    <div class="navbar-header">
      <a class="navbar-brand" href="#">SIST Music</a>
    </div>
    <ul class="nav navbar-nav">
      <li class="active"><a href="music_main.jsp">Home</a></li>
	  <%
	  	int i = 1;
	  	for(String genre:mList){
	  %>
	  		<li><a href="music_main.jsp?mode=<%=i%>"><%=genre %></a></li>
	  <%
	  	  i++;
	  	}
	  %>
    </ul>
  </div>
</nav>
  
<div class="container">
  	<div class="row">
  	 <jsp:include page="<%=jsp %>"></jsp:include>
  	</div>
</div>

</body>
</html>

home

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<center>
		<img src="main.png" width=100%>
	</center>
</body>
</html>

music

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="com.sist.dao.*, java.util.*, com.sist.manager.MusicVO" %>
<%
	String strPage=request.getParameter("page");
	if(strPage==null){ // page뒤 숫자 없으면 1페이지
		strPage="1";
	}
	// 이전 다음
	int curpage=Integer.parseInt(strPage);
	String mode = request.getParameter("mode");
	MusicDAO dao = new MusicDAO();
	String genre = dao.musicGetGenre(Integer.parseInt(mode));
	ArrayList<MusicVO> list = dao.musicAllData(Integer.parseInt(mode), curpage);
	
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<center>
		<h1>장르별 음악(<%=genre %>)</h1>
		<table class="table table-hover">
			<tr class="info">
				<th class="text-center">순위</th>
				<th class="text-center"></th>
				<th class="text-center">곡명</th>
				<th class="text-center">가수명</th>
				<th class="text-center">앨범</th>
			</tr>
			<%
				for(MusicVO vo:list){
			%>
			<tr>
				<td class="text-center"><%=vo.getRank()+((curpage*30)-30) %></td>
				<td class="text-center"><img src=<%=vo.getPoster() %> width=35 height=35 class="img-circle"></td>
				<td><%=vo.getTitle() %></td>
				<td><%=vo.getSinger() %></td>
				<td><%=vo.getAlbum() %></td>
			</tr>
			<%
				}
			%>
		</table>
		<table class="table">
			<tr>
				<td class="text-center">
				  <ul class="pagination">
				  <%
				  for(int i=1; i<=5; i++){
				  %>
					  <li><a href="music_main.jsp?mode=<%=mode%>&page=<%=i %>"><%=i %></a></li>
					  
			      <%
				  	}
			      %>
				  </ul>
				</td>
			</tr>
		</table>
	</center>
</body>
</html>

 

MELON

- JAVA Class

MusicVO2

package com.sist.manager;

public class MusicVO2 {
	private int mno;
	private int cateno;
	private String title;
	private String poster;
	private String singer;
	private String album;
	private int rank;
	
	public int getRank() {
		return rank;
	}
	public void setRank(int rank) {
		this.rank = rank;
	}
	public int getMno() {
		return mno;
	}
	public void setMno(int mno) {
		this.mno = mno;
	}
	public int getCateno() {
		return cateno;
	}
	public void setCateno(int cateno) {
		this.cateno = cateno;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getPoster() {
		return poster;
	}
	public void setPoster(String poster) {
		this.poster = poster;
	}
	public String getSinger() {
		return singer;
	}
	public void setSinger(String singer) {
		this.singer = singer;
	}
	public String getAlbum() {
		return album;
	}
	public void setAlbum(String album) {
		this.album = album;
	}
	
}

MusicManager2

package com.sist.manager;

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.select.Elements;

import com.sist.dao.MusicDAO2;

public class MusicManager2 {
	public void musicAllData2() {
		MusicDAO2 dao = new MusicDAO2();
		try {
			int k=1;
			for(int i=1; i<=1; i++) {
				Document doc=Jsoup.connect("https://www.melon.com/genre/song_list.htm?gnrCode=GN0200").get();
				Elements title = doc.select("div.rank01");
				Elements singer = doc.select("div.rank02 span.checkEllipsis");
				Elements album = doc.select("div.rank03");
				Elements poster = doc.select("div.wrap img");
			
			for(int j=0; j<50; j++) {
				try {
					MusicVO2 vo = new MusicVO2();
					System.out.println("번호"+k++);
					System.out.println("cateno:1");
					System.out.println("제목:"+title.get(j).text());
					System.out.println("가수명:"+singer.get(j).text());
					System.out.println("앨범:"+album.get(j).text());
					System.out.println("포스터:"+poster.get(j).attr("src"));
					System.out.println("============================");
					// vo에 값을 채운다 => DAO
					vo.setCateno(2);
					vo.setTitle(title.get(j).text());
					vo.setSinger(singer.get(j).text());
					vo.setAlbum(album.get(j).text());
					vo.setPoster(poster.get(j).attr("src"));
					// DAO로 전송
					dao.musicInsert2(vo);
					Thread.sleep(100);
				}catch(Exception ex) {}
			}
			System.out.println("End...");
		}
			
		}catch(Exception ex) {}
	}
	public static void main(String[] args) {
		MusicManager2 m = new MusicManager2();
		m.musicAllData2();
	}

}

MusicDAO2

package com.sist.dao;

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

import com.sist.manager.MusicVO;
import com.sist.manager.MusicVO2;

public class MusicDAO2 {
	private Connection conn;
	private PreparedStatement ps;
	private final String URL="jdbc:oracle:thin:@localhost:1521:XE";
	public MusicDAO2() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}catch(Exception ex) {}
	}
	public void getConnection() {
		try {
			conn=DriverManager.getConnection(URL,"hr","happy");
		}catch(Exception ex) {}
	}
	public void disConnection() {
		try {
			if(ps!=null) ps.close();
			if(conn!=null) conn.close();
		}catch(Exception ex) {}
	}
	
	//기능
	public void musicInsert2(MusicVO2 vo) {
		try {
			getConnection();
			String sql="INSERT INTO music2 VALUES("
					+"music_mno_seq2.nextval,?,?,?,?,?)";
								//하나씩 증가
			ps=conn.prepareStatement(sql);
			// ?에 값을 채운다
			ps.setInt(1, vo.getCateno());
			ps.setString(2, vo.getTitle());
			ps.setString(3, vo.getPoster());
			ps.setString(4, vo.getSinger());
			ps.setString(5, vo.getAlbum());
			// 실행 명령
			ps.executeUpdate(); // INSERT문장을 실행 => COMMIT 자동으로 됨
		}catch(Exception ex) {
			System.out.println(ex.getMessage());
		}finally {
			disConnection();
		}
	}
		//장르
		public ArrayList<String> musicGenreAllData2(){
			ArrayList<String> list = new ArrayList<String>();
			try {
				getConnection();
				String sql="SELECT genre FROM music_genre2 "
						+"ORDER BY no";
				ps=conn.prepareStatement(sql);
				ResultSet rs=ps.executeQuery();
				while(rs.next()) {
					String genre=rs.getString(1);
					list.add(genre);
				}
				rs.close();
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}finally {
				disConnection();
			}
			return list;
		}
		// Music 출력
		public ArrayList<MusicVO2> musicAllData2(int cateno, int page){
			ArrayList<MusicVO2> list = new ArrayList<MusicVO2>();
			try {
				getConnection();
				String sql="SELECT mno, title, poster, singer, album, RANK() OVER(ORDER BY mno ASC), num "
						+"FROM (SELECT mno, title, poster, singer, album, rownum as num "
						+"FROM (SELECT mno, title, poster, singer, album "
						+"FROM music2 WHERE cateno=? ORDER BY mno)) "
						+"WHERE num BETWEEN ? AND ?"; // 페이징기법
				int rowSize=25;
				int start=(rowSize*page)-(rowSize-1);
				// rownum ==> 시작번호 (1)
				int end = rowSize*page;
				
				ps=conn.prepareStatement(sql);
				ps.setInt(1, cateno);
				ps.setInt(2, start);
				ps.setInt(3, end);
				
				// 실행
				ResultSet rs = ps.executeQuery();
				while(rs.next()) {
					MusicVO2 vo = new MusicVO2();
					vo.setMno(rs.getInt(1));
					vo.setTitle(rs.getString(2));
					vo.setPoster(rs.getString(3));
					vo.setSinger(rs.getString(4));
					vo.setAlbum(rs.getString(5));
					vo.setRank(rs.getInt(6));
					list.add(vo);
				}
				rs.close();
				
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}finally {
				disConnection();
			}
			return list;
		}
		public String musicGetGenre2(int cateno) {
			String genre = "";
			try {
				getConnection();
				String sql="SELECT genre FROM music_genre2 "
						+"WHERE no=?";
				ps=conn.prepareStatement(sql);
				ps.setInt(1, cateno);
				ResultSet rs = ps.executeQuery();
				rs.next();
				genre=rs.getString(1);
				rs.close();
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}finally {
				disConnection();
			}
			return genre;
		}
}

 

-JSP

music_main2

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="java.util.*, com.sist.dao.MusicDAO2" %>
<%
	MusicDAO2 dao = new MusicDAO2();
	ArrayList<String> mList = dao.musicGenreAllData2();
	
	String mode=request.getParameter("mode");
	String jsp="";
	if(mode==null)
		jsp="home2.jsp";
	else
		jsp="music2.jsp";
%>
<!DOCTYPE html>
<html>
<head>
  <title>멜론뮤직</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>

<nav class="navbar navbar-inverse">
  <div class="container-fluid">
    <div class="navbar-header">
      <a class="navbar-brand" href="#">SIST Music</a>
    </div>
    <ul class="nav navbar-nav">
      <li class="active"><a href="music_main2.jsp">Home</a></li>
	  <%
	  	int i = 1;
	  	for(String genre:mList){
	  %>
	  		<li><a href="music_main2.jsp?mode=<%=i%>"><%=genre %></a></li>
	  <%
	  	  i++;
	  	}
	  %>
    </ul>
  </div>
</nav>
  
<div class="container">
  	<div class="row">
  	 <jsp:include page="<%=jsp %>"></jsp:include>
  	</div>
</div>

</body>
</html>

home2

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<center>
		<img src="main2.PNG" width=75%>
	</center>
</body>
</html>

music2

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="com.sist.dao.MusicDAO2, java.util.*, com.sist.manager.MusicVO2" %>
<%
	String strPage=request.getParameter("page");
	if(strPage==null){ // page뒤 숫자 없으면 1페이지
		strPage="1";
	}
	// 이전 다음
	int curpage=Integer.parseInt(strPage);
	String mode = request.getParameter("mode");
	MusicDAO2 dao = new MusicDAO2();
	String genre = dao.musicGetGenre2(Integer.parseInt(mode));
	ArrayList<MusicVO2> list = dao.musicAllData2(Integer.parseInt(mode), curpage);
	
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<center>
		<h1>장르별 음악(<%=genre %>)</h1>
		<table class="table table-hover">
			<tr class="info">
				<th class="text-center">순위</th>
				<th class="text-center"></th>
				<th class="text-center">곡명</th>
				<th class="text-center">가수명</th>
				<th class="text-center">앨범</th>
			</tr>
			<%
				for(MusicVO2 vo:list){
			%>
			<tr>
				<td class="text-center"><%=vo.getRank()+((curpage*25)-25) %></td>
				<td class="text-center"><img src=<%=vo.getPoster() %> width=35 height=35 class="img-circle"></td>
				<td><%=vo.getTitle() %></td>
				<td><%=vo.getSinger() %></td>
				<td><%=vo.getAlbum() %></td>
			</tr>
			<%
				}
			%>
		</table>
		<table class="table">
			<tr>
				<td class="text-center">
				  <ul class="pagination">
				  <%
				  for(int i=1; i<=2; i++){
				  %>
					  <li><a href="music_main2.jsp?mode=<%=mode%>&page=<%=i %>"><%=i %></a></li>
					  
			      <%
				  	}
			      %>
				  </ul>
				</td>
			</tr>
		</table>
	</center>
</body>
</html>