일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | 6 | 7 |
8 | 9 | 10 | 11 | 12 | 13 | 14 |
15 | 16 | 17 | 18 | 19 | 20 | 21 |
22 | 23 | 24 | 25 | 26 | 27 | 28 |
29 | 30 | 31 |
Tags
- Oracle기초
- 스프링 CRUD게시판
- 자바 정규표현식 예제
- spring crud
- 스프링게시판만들기
- 자바기초
- spring crud게시판
- 자바배열예제
- jsp게시판만들기
- 2020정보처리기사실기
- 오라클설치
- 스프링 crud
- 프로그래머스 SQL
- 프로그래머스 MYSQL
- crud게시판
- 정보처리기사실기정리
- 오버라이딩
- 날짜지정팝업
- html기초
- CRUD게시판만들기
- PLSQL
- 정처기실기정리
- 게시판만들기
- js datepicker
- jsp 날짜팝업
- jsp 팝업띄우기
- 2020정보처리기사실기요약
- 프로그래머스 쿼리문
- 2020정보처리기사실기정리
- 자바연산자
Archives
- Today
- Total
영보의 SystemOut.log
[Web] JSP 게시판만들기 #페이징/ 컨테이너/ 유형별 리스트 본문
반응형
# DB에서 테이블 생성 및 데이터 저장
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
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(260) 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
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, 'CCM');
INSERT INTO music_genre VALUES(10, '기타');
COMMIT;
--mno가 자동 증가
CREATE SEQUENCE music_mno_seq
START WITH 1
INCREMENT BY 1
NOCYCLE
NOCACHE;
|
cs |
- 먼저 SQL에서 테이블을 먼저 작성해야한다.
- 각 단락을 따로따로 실행해서 순서대로 값을 넣어준다.
# MusicVO.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
package com.sist.manager;
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 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;
}
public int getRank() {
return rank;
}
public void setRank(int rank) {
this.rank = rank;
}
}
|
cs |
- VO에서 getters/setters로 캡슐화를 해준다.
(아직 MyBatis를 사용하지 않고 자바로만 하는거라 비효율적으로 느껴질 수도 있다)
# MovieMnager.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
package com.sist.manager;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.select.Elements;
import com.sist.dao.MusicDAO;
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:10");
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();
}
}
|
cs |
- 사이트에서 값을 긁어오기위해 Manager가 필요하다.
- 각 카테고리에 cateno를 주었다.
- 테이블에서 만든 변수명과 꼭 다 통일해야하는 점 주의
# MusicDAO.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
|
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;
//sql 전송
private PreparedStatement ps;
//연결
private final String URL="jdbc:oracle:thin:@localhost:1521:XE";
public MusicDAO()
{
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
//드라이버를 이용해서 연결 = > thin 드라이버
}catch (Exception e) {}
}
//2.연결 메소드
public void getConnection()
{
try {
conn=DriverManager.getConnection(URL,"hr","happy");
}catch (Exception e) {}
}
//3.닫는 메소드
public void disConnection()
{
try {
if(ps!=null)ps.close();
if(conn!=null)conn.close();
}catch (Exception e) {}
}
// 기능
public void musicInsert(MusicVO vo) {
try {
// 연결
getConnection();
// sql문장
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;
}
}
|
cs |
- DAO : DB(Oracle) - JAVA - WEB - Server(Tomcat) - web 연동
# home.jsp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
<%@ 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>
|
cs |
# music_main.jsp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
<%@ 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>Bootstrap Example</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>
<%-- <jsp:include page="home.jsp"></jsp:include>--%>
</div>
</div>
</body>
</html>
|
cs |
# music.jsp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
<%@page import="com.sist.manager.MusicVO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import="com.sist.dao.*,java.util.*"%>
<%
String strPage = request.getParameter("page");
if (strPage == null)
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="danger">
<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 <= 6; i++) {
%> <!-- 두개를 넘길대는 반드시 &를 붙여야한다. -->
<li><a href="music_main.jsp?mode=<%=mode %>&page=<%=i%>"><%=i%></a></li>
<%
}
%>
</ul>
</td>
</tr>
</table>
</center>
</body>
</html>
|
cs |
https://github.com/BoyoungHyeon/music_BoardProject
자세한 코드는 Git에 올려놓았습니다 ㅎㅎ
많이 부족하지만 도움이 되셨으면 좋겠습니다.
cateno 번호 순서대로 카테고리에 출력됩니다
css는 밑에 사이트에서 참고했습니다~
https://www.w3schools.com/bootstrap/bootstrap_ver.asp
반응형
'Web' 카테고리의 다른 글
[Web] JSTL , CSS 이용하여 레시피 그리드 목록 출력-(2) (0) | 2020.10.07 |
---|---|
[Web] JSTL , CSS 이용하여 레시피 그리드 목록 출력-(1) (0) | 2020.10.07 |
[Web] JSTL 속성 / JSTL 태그 (0) | 2020.10.07 |
[WEB] GET방식과 POST방식 차이 (0) | 2020.09.30 |
[WEB] Java와 Oracle연동하여 HTML로 게시판 만들기 (2) | 2020.08.14 |