본문 바로가기
JAVA

Jsoup을 이용한 웹 크롤링 (2) - 지니 차트

코딩 수달 2022. 7. 17. 23:14
반응형

지니 사이트의 데이터를 크롤링하여 MVC 로 구현하기 

SQL

1
2
3
4
5
6
7
8
9
10
SELECT * FROM USER_TABLES;
 
CREATE TABLE GENIE(
    NUM INT PRIMARY KEY,
    NAME VARCHAR(200) NOT NULL,
    ARTIST VARCHAR(200) NOT NULL,
    ALBUM VARCHAR(200) NOT NULL
);
 
SELECT * FROM GENIE;
 cs

Crawling (크롤링한 데이터를 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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
package controller;
 
import java.io.IOException;
import java.util.Iterator;
 
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
 
import model.GenieDAO;
import model.GenieVO;
 
public class Crawling {
    public static void sample() {
        Document doc = null;
        final String url = "https://www.genie.co.kr/chart/top200";
        try {
            doc = Jsoup.connect(url).get();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    
        
        Elements eles=doc.select("div.music-list-wrap");
        
        Iterator<Element> itr=eles.select("a.title").iterator();
        Iterator<Element> itr2=eles.select("a.artist").iterator();
        Iterator<Element> itr3=eles.select("a.albumtitle").iterator();
        GenieDAO DAO = new GenieDAO();
        
        while(itr.hasNext()) {
            GenieVO vo = new GenieVO();
            
            String str=itr.next().text();
            String strt=itr2.next().text();
            String strm=itr3.next().text();
            vo.setName(str);
            vo.setArtist(strt);
            vo.setAlbum(strm);
            
            DAO.insert(vo);
            System.out.println(vo);
        }
        System.out.println("로그: 샘플데이터 저장완료!");
    }
 
}
 
 
 cs

VO

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
package model;
 
public class GenieVO {
    private int num; // PK값
    private String name; // 곡명
    private String artist; // 가수 
    private String album; // 앨범명 
    public int getNum() {
        return num;
    }
    public void setNum(int num) {
        this.num = num;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getArtist() {
        return artist;
    }
    public void setArtist(String artist) {
        this.artist = artist;
    }
    public String getAlbum() {
        return album;
    }
    public void setAlbum(String album) {
        this.album = album;
    }
    @Override
    public String toString() {
        return "MusicVO [num=" + num + ", name=" + name + ", artist=" + artist + ", album=" + album + "]";
    }
}
 
 cs

DAO

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
package model;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
 
import model.JDBCUtil;
 
public class GenieDAO {
    Connection conn;
    PreparedStatement pstmt;
    final String sql_sample="SELECT COUNT(*) AS CNT FROM GENIE";
    final String sql_insert="INSERT INTO GENIE VALUES((SELECT NVL(MAX(NUM),0)+1 FROM GENIE),?,?,?)";
    final String sql_selectAll="SELECT * FROM GENIE ORDER BY NUM ASC";
    final String sql_selectOne="SELECT * FROM GENIE WHERE NUM=?"; 
    final String sql_delete="DELETE FROM GENIE WHERE NUM=?";
    final String sql_update="UPDATE GENIE SET NAME=?, ARTIST=?, ALBUM=? WHERE NUM=?"; 
 
    public boolean insert(GenieVO vo) {
        conn=JDBCUtil.connect();
 
        try {
            pstmt=conn.prepareStatement(sql_insert);
            pstmt.setString(1, vo.getName());
            pstmt.setString(2, vo.getArtist());
            pstmt.setString(3, vo.getAlbum());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return false;
 
        }finally {
            JDBCUtil.disconnect(pstmt, conn);
        }
 
        return true;
    }
    public ArrayList<GenieVO> selectAll(GenieVO vo){
        ArrayList<GenieVO> datas = new ArrayList<GenieVO>();
        conn=JDBCUtil.connect();
        try {
            pstmt=conn.prepareStatement(sql_selectAll);
            ResultSet rs=pstmt.executeQuery();
            while(rs.next()) {
                GenieVO data =new GenieVO();
                data.setNum(rs.getInt("num"));
                data.setName(rs.getString("name"));
                data.setArtist(rs.getNString("artist"));
                data.setAlbum(rs.getString("album"));
                datas.add(data);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            JDBCUtil.disconnect(pstmt, conn);
        }
        return datas;
    }
 
    public boolean delete(GenieVO vo) {
        conn=JDBCUtil.connect();
        try {
            pstmt=conn.prepareStatement(sql_delete);
            pstmt.setInt(1,vo.getNum());
            int res = pstmt.executeUpdate();
            if (res==0) {
                return false;
            }
 
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            JDBCUtil.disconnect(pstmt, conn);
        }
        return true;
    }
 
    public GenieVO selectOne(GenieVO vo) {
        conn=JDBCUtil.connect();
        try {
            pstmt=conn.prepareStatement(sql_selectOne);
            pstmt.setInt(1, vo.getNum());
            ResultSet rs=pstmt.executeQuery();
            if(rs.next()) {
                GenieVO data=new GenieVO();
                data.setNum(rs.getInt("num"));
                data.setName(rs.getString("name"));
                data.setArtist(rs.getNString("artist"));
                data.setAlbum(rs.getString("album"));
                return data;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JDBCUtil.disconnect(pstmt, conn);
        }
        return null;
    }
 
    public boolean update(GenieVO vo) {
        conn=JDBCUtil.connect();
        try {
            pstmt=conn.prepareStatement(sql_update);
            pstmt.setString(1, vo.getName());
            pstmt.setString(2, vo.getArtist());
            pstmt.setString(3, vo.getAlbum());
            pstmt.setInt(4, vo.getNum());
            int res=pstmt.executeUpdate();
            if(res<=0) {
                System.out.println("로그: 변경실패...");
                return false;
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            return false;
        }finally {
            JDBCUtil.disconnect(pstmt, conn);
        }
        return true;
    }
 
        public boolean hasSample(GenieVO vo) {
            conn=JDBCUtil.connect();
            try {
                pstmt=conn.prepareStatement(sql_sample);
                ResultSet rs=pstmt.executeQuery();
                rs.next();
                int cnt=rs.getInt("CNT");
                if(cnt>=5) {
                    return true;
                }
                return false;
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                return false;
            } finally {
                JDBCUtil.disconnect(pstmt, conn);
            }
        }
}
 
 
 cs

View

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
package view;
 
import java.util.ArrayList;
import java.util.Scanner;
 
import model.GenieVO;
 
public class View {
    public int action;
    Scanner sc = new Scanner(System.in);
    public void startView() {
        while(true) {
        System.out.println();
        System.out.println("==== GENIE  MUSIC ====");
        System.out.println("1. 1 - 50 차트목록");
        System.out.println("2. 곡 추가 ");
        System.out.println("3. 곡 삭제 ");
        System.out.println("4. 곡 검색 ");
        System.out.println("5. 곡 변경 ");
        System.out.println("6. 종료");
        try {
            action=sc.nextInt();
                    if (1<=action&&action<=6) {
                        break;
                    }
                    System.out.println("범위 외 입력 1~6번만 눌러주세요");
        } catch (Exception e) {
            System.out.println("정수만 입력하세요!");
            // TODO: handle exception
        }
    }
    }
    public int inputInt() { 
        System.out.print("정수입력) ");
        int i=sc.nextInt();
        return i;
    }
    
    public void input() {
        System.out.println("추가하실 곡에 대한 정보를 입력해주세요.");
    }
    
    public String inputName() {
        System.out.println("가수를 입력해주세요");
        String str=sc.next();
        return str;
    }
    public String inputArtist() {
        System.out.println("아티스트 입력해주세요");
        String str=sc.next();
        return str;
    }
    public String inputAlbum() {
        System.out.println("앨범을 입력해주세요");
        String str=sc.next();
        return str;
    }
    
    public void func2() {
        System.out.println("음악목록 메뉴입니다.");
    }
    
    
    public int song(ArrayList<GenieVO>datas) {
        System.out.println();
          System.out.println("음악목록");
            for(GenieVO g:datas) {
                System.out.println(g);
            }
            System.out.println("음악번호 입력 : ");
            int num=sc.nextInt();
            return num;
        }
    
    
    public void printData(ArrayList<GenieVO>datas) {
 
        System.out.println();
        if (datas.size()==0) {
            System.out.println("저장된 정보가 없습니다.");
            return;
        }
        System.out.println("== TOP 50 노래 차트 ==");
        for(GenieVO v : datas) {
            System.out.println();
            System.out.println(v.getNum()+"위 ");
            System.out.println("가수명 : " + v.getArtist());
            System.out.println("제목 :  " + v.getName());
            System.out.println(" 앨범명 :  "+v.getAlbum());
            
        }
        }
    
        
    public void success() {
        System.out.println("수행 성공");
    }
    public void fail() {
        System.out.println("수행 실패");
    }
    
    public void serch() {
        System.out.println("검색하실 곡을 입력하세요");
    }
    
    public void changeint() {
        System.out.println("변경하실 곡 번호를 입력하세요");
    }
    
    public void end() {
        System.out.println("프로그램을 종료합니다...");
    }
}
 
 
 
 
 
 
 cs

 

Controller 

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
package controller;
 
import java.util.ArrayList;
 
import model.GenieDAO;
import model.GenieVO;
import view.View;
 
public class Controller {
    private GenieDAO model;
    private View view;
 
    public Controller() {
        model=new GenieDAO();
        view=new View();
        if(!model.hasSample(null)) { // SELECT COUNT(*) FROM MEMBER
            Crawling.sample();
        }
    }
    public void startApp() {
        while(true) {
            view.startView();
            if (view.action==1) { // 전체 목록 출력
                view.func2();
                GenieVO vo = new GenieVO();
                view.printData(model.selectAll(vo));
            }
            else if (view.action==2) { // 추가
                view.input();
                String name=view.inputName();//곡명입력
                String Artist=view.inputArtist();//가수명입력
                String Album=view.inputAlbum();//앨범명입력
 
                GenieVO vo = new GenieVO();
                vo.setName(name);
                vo.setArtist(Artist);
                vo.setAlbum(Album);
                boolean flag =model.insert(vo);
                if (flag) {
                    view.success();
                }else {
                    view.fail();
                }
            }
            else if (view.action==3) { // 곡 삭제 
                int num=view.inputInt();
 
                GenieVO vo = new GenieVO();
                vo.setNum(num);
                vo=model.selectOne(vo);
                if (vo==null) {
                    System.out.println("로그 : 해당"+num+"번은 존재하지않습니다!");
                    continue;
                }
                boolean flag = model.delete(vo);
                if (flag) {
                    view.success();
                }else {
                    view.fail();
                }
            }
            else if (view.action==4) { // 곡 검색
                view.serch();
                GenieVO vo = new GenieVO();
                ArrayList<GenieVO> datas = model.selectAll(vo);
                int num=view.song(model.selectAll(vo));
                vo.setNum(model.selectAll(vo).get(num-1).getNum());
                 vo = model.selectOne(vo);
                    if(vo==null) {
                        continue;
                    }
                System.out.println(vo);
                
            }
            else if (view.action==5) { // 곡 변경 
                view.changeint();
                int num = view.inputInt();
                String name = view.inputName();
                String artist = view.inputArtist();
                String album = view.inputAlbum();
 
                GenieVO vo = new GenieVO();
                vo.setNum(num);
                vo.setName(name);
                vo.setArtist(artist);
                vo.setAlbum(album);
                boolean flag = model.update(vo);
                if (flag) {
                    view.success();
                }else {
                    view.fail();
                }
            }else if (view.action==6) { // 프로그램 종료
                view.end();
                break;
            }
        }
    }
 
 
}
 
 cs

Client

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package Client;
 
import controller.Controller;
 
public class Client {
    public static void main(String[] args) {
        Controller app = new Controller();
        app.startApp();
    }
 
}
 
 
 
 
 
 
 cs

JDBCUtil 

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
package model;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
public class JDBCUtil {
    static final String driverName="oracle.jdbc.driver.OracleDriver";
    static final String url="jdbc:oracle:thin:@localhost:1521:xe";
    static final String user="kim";
    static final String passwd="1234";
    public static Connection connect() {
        Connection conn=null;
        try {
            Class.forName(driverName);
 
            conn=DriverManager.getConnection(url, user, passwd);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }
    public static void disconnect(PreparedStatement pstmt,Connection conn) {
        try {
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}
 
 
 cs

 

 

 

반응형

'JAVA' 카테고리의 다른 글

[JAVA] 배열의 최대값 최소값 구하기  (0) 2022.11.07
Jsoup을 이용한 웹 크롤링 (1)  (0) 2022.07.13
[JAVA] MVC 학생부 프로그램 실습  (0) 2022.07.04
[JAVA] MVC 자판기 프로그램 실습 (CRUD 개념)  (0) 2022.06.29
[JAVA] 시험 오답풀이 (For-each문)  (0) 2022.06.27

'JAVA' Related Articles

티스토리툴바