JDBC 맛보기

Music.java

package doodoo.vo;

import java.io.Serializable;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class Music implements Serializable{
	private int mno; // 고유번호(시퀀스)
	private int cno; // 카테고리 번호
	private String title; // 곡명
	private String poster; // 포스터
	private String singer; // 가수명
	private String album; // 앨범명
	private int idcrement; // 등폭
	private String state; // 상태
}

MusicSystem.java

package doodoo.music;

import java.util.*;
import java.io.*;
import doodoo.vo.*;
public class MusicSystem {
    private static ArrayList < Music > list = new ArrayList < Music > ();
    static {
        try {
            FileInputStream fis = new FileInputStream("c:\\javaDev\\music_data.txt");
            ObjectInputStream ois = new ObjectInputStream(fis);
            list = (ArrayList < Music > ) ois.readObject();
            ois.close();
            fis.close();
        } catch (Exception ex) {}
    }
    public int menu() {
        Scanner scan = new Scanner(System.in);
        System.out.println("-----Music MENU-----");
        System.out.println("1. Top100");
        System.out.println("2. 가요");
        System.out.println("3. POP");
        System.out.println("4. OST");
        System.out.println("5. 트로트");
        System.out.println("6. 재즈");
        System.out.println("7. 클래식");
        System.out.println("9. 종료");
        System.out.print("insert menu number:");
        return scan.nextInt();
    }

    public void print(int cno) {
        for (Music m: list) {
            if (m.getCno() == cno) {
                System.out.println(m.getTitle() + "[" + m.getSinger() + "]");
            }
        }
    }
    public void process() {
        while (true) {
            int m = menu();
            if (m == 9) {
                System.out.println("프로그램 종료");
                break;
            } else {
                print(m);
            }
        }
    }
}

MusicDAO.java

package doodoo.dao;

import java.sql.*;
import java.util.ArrayList;

import doodoo.vo.*;
public class MusicDAO {
    private Connection conn;
    private PreparedStatement ps;
    private final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
    private final String USERNAME = "hr";
    private final String PASSWORD = "happy";

    //1.드라이버 등록->한번만 수행할 거니까 생성자로 처리
    public void MusicDAO() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (Exception ex) {}
    }
    //2.데이터베이스 연결->
    public void getConnection() {
        try {
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (Exception ex) {}
    }
    //3.데이터베이스 해제
    public void disConnection() {
        try {
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        } catch (Exception ex) {}
    }
    //4.기능
    //4-1.Music 데이터 추가
    public void musicInsert(Music m) {
        try {
            //1.오라클 연결
            getConnection();
            //2.SQL문장
            String sql = "INSERT INTO music VALUES(?,?,?,?,?,?,?,?)";
            //3.오라클 전송
            ps = conn.prepareStatement(sql); //네트워크
            //4.실행.. SQL Plus에 값을 보내주기 전에 ?에 값을 채워야 한다.
            ps.setInt(1, m.getMno());
            ps.setInt(2, m.getCno());
            ps.setString(3, m.getTitle());
            ps.setString(4, m.getSinger());
            ps.setString(5, m.getAlbum());
            ps.setString(6, m.getPoster());
            ps.setInt(7, m.getIdcrement());
            ps.setString(8, m.getState());

            ps.executeUpdate();

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            disConnection();
        }
    }
    //4-2.데이터베이스에서 Music 읽어오기
    public ArrayList < Music > musicListData(int cno) {
        ArrayList < Music > list = new ArrayList < Music > ();
        try {
            getConnection();
            String sql = "select mno, poster, idcrement," +
                "state, title, singer, album" +
                "from music" +
                "where cno=" + cno;
            ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Music m = new Music();
                m.setMno(rs.getInt(1));
                m.setPoster(rs.getString(2));
                m.setIdcrement(rs.getInt(3));
                m.setState(rs.getString(4));
                m.setTitle(rs.getString(5));
                m.setSinger(rs.getString(6));
                m.setAlbum(rs.getString(7));
                list.add(m);
            }
        } catch (Exception e) {

        } finally {

        }
        return list;
    }

}

MusicData.java

package doodoo.data;

//한번 쓰고 버림
import java.util.*;

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

import java.io.*;
import doodoo.vo.*;
import doodoo.dao.*;

public class MusicData {
    public static void musicAllData() {
        MusicDAO dao = new MusicDAO();
        ArrayList < Music > list = new ArrayList < Music > ();
        String[] urls = {
            "https://www.genie.co.kr/chart/top200?ditc=D&ymd=20220615&hh=11&rtm=Y&pg=",
            "https://www.genie.co.kr/chart/genre?ditc=D&ymd=20220613&genrecode=M0100&pg=",
            "https://www.genie.co.kr/chart/genre?ditc=D&ymd=20220613&genrecode=M0200&pg=",
            "https://www.genie.co.kr/chart/genre?ditc=D&ymd=20220613&genrecode=M0300&pg=",
            "https://www.genie.co.kr/chart/genre?ditc=D&ymd=20220613&genrecode=M0400&pg=",
            "https://www.genie.co.kr/chart/genre?ditc=D&ymd=20220613&genrecode=M0500&pg=",
            "https://www.genie.co.kr/chart/genre?ditc=D&ymd=20220613&genrecode=M0600&pg="
        };

        try {
            int mno = 1;
            int cno = 1;
            for (String url: urls) {
                for (int i = 1; i <= 2; i++) {
                    Document doc = Jsoup.connect(url + i).get();
                    Elements title = doc.select("table.list-wrap td.info a.title");
                    Elements singer = doc.select("table.list-wrap td.info a.artist");
                    Elements album = doc.select("table.list-wrap td.info a.albumtitle");
                    Elements poster = doc.select("a.cover img");
                    Elements etc = null;
                    if (cno == 1) {
                        etc = doc.select("tr.list span.rank span.rank");
                    } else {
                        etc = doc.select("tr.list span.rank");
                    }
                    for (int j = 0; j < title.size(); j++) {
                        //etc
                        /*상승, 2하강, 유지, new 이렇게 가져옴*/
                        String state = "";
                        int id = 0;
                        String etc_data = etc.get(j).text();
                        state = etc_data.replaceAll("[0-9]", "");
                        //						System.out.println(state);
                        if (state.equals("유지") || state.equals("new"))
                            id = 0;
                        else
                            id = Integer.parseInt(etc_data.replaceAll("[가-힣]", ""));

                        try { //데이터가 없으면 스킵하도록!
                            System.out.println(mno + " " + title.get(j).text() + " " + singer.get(j).text() + " " +
                                album.get(j).text() + " " + poster.get(j).attr("src") + " " +
                                state + " " + id);
                            Music m = new Music();
                            m.setMno(mno);
                            m.setCno(cno);
                            m.setTitle(title.get(j).text());
                            m.setSinger(singer.get(j).text());
                            m.setAlbum(album.get(j).text());
                            m.setPoster(poster.get(j).attr("src"));
                            m.setState(state);
                            m.setIdcrement(id);

                            //							list.add(m); DAO가 추가되면서 필요없어짐
                            dao.musicInsert(m);
                        } catch (Exception ex) {}
                        mno++;
                    }
                    System.out.println("----------------------");
                }
                cno++;
                System.out.println("cno=" + cno);
                //				FileOutputStream fos = new FileOutputStream("c:\\javaDev\\music_data.txt");
                //				ObjectOutputStream oos = new ObjectOutputStream(fos);
                //				oos.writeObject(list);
                //				oos.close();
                //				fos.close();  DAO가 추가되면서 필요없어짐
            }
        } catch (Exception ex) {}
    }
    public static void main(String[] args) {
        musicAllData();
    }
}

MusicMain.java

package doodoo.user;

import doodoo.music.*;
public class MusicMain {

	public static void main(String[] args) {
		MusicSystem ms = new MusicSystem();
		ms.process();
	}

}