영보의 SystemOut.log

[Web] JSP 게시판만들기 #페이징/ 컨테이너/ 유형별 리스트 본문

Web

[Web] JSP 게시판만들기 #페이징/ 컨테이너/ 유형별 리스트

영보로그 2020. 8. 27. 11:33
반응형

# 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

 

BoyoungHyeon/music_BoardProject

Contribute to BoyoungHyeon/music_BoardProject development by creating an account on GitHub.

github.com

자세한 코드는 Git에 올려놓았습니다 ㅎㅎ

많이 부족하지만 도움이 되셨으면 좋겠습니다.

 

 

 

 

 

 

 

cateno 번호 순서대로 카테고리에 출력됩니다

css는 밑에 사이트에서 참고했습니다~

 

https://www.w3schools.com/bootstrap/bootstrap_ver.asp

 

Bootstrap 3 or Bootstrap 4

 

www.w3schools.com

 

반응형
저작자표시

'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
'Web' Related Articles more