[Java] 웹 페이지의 JSON을 가져와 DB 테이블에 삽입하기

album 테이블

CREATE DATABASE albumdb;
USE albumdb;
 
CREATE TABLE album (
    userId INT,
    id INT PRIMARY KEY,
    title VARCHAR(200)
);
 
SELECT * FROM album;

 

DBHelper

public class DBHelper {
 
    private static final String DB_HOST = "localhost";
    private static final String DB_PORT = "3306";
    private static final String DB_DATABASE_NAME = "albumdb";
    private static final String DB_CHARSET = "UTF-8";
    private static final String DB_USER_NAME = "root";
    private static final String DB_PASSWORD = "1234";
 
    private Connection conn;
    private static DBHelper dbHelper;
 
    private DBHelper() {
    }
 
    public static DBHelper getInstance() {
        if (dbHelper == null) {
            dbHelper = new DBHelper();
        }
        return dbHelper;
    }
 
    public Connection getConnection() {
        if (conn == null) {
            String urlFormat = "jdbc:mysql://%s:%s/%s?serverTimezone=Asia/Seoul&characterEncoding=%s";
            String url = String.format(urlFormat, DB_HOST, DB_PORT, DB_DATABASE_NAME, DB_CHARSET);
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection(url, DB_USER_NAME, DB_PASSWORD);
                System.out.println(">> DB 연결 완료 <<");
            } catch (Exception e) {
                System.out.println(">> DBHelper 에서 오류가 발생 했어! <<");
                e.printStackTrace();
            }
        }
        return conn;
    }
 
    public void closeConnection() {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        conn = null;
    }
 
}

 

AlbumDao

public class AlbumDao implements IAlbumDao {
 
    private Connection conn;
    private PreparedStatement pstmt;
    private ResultSet rs;
 
    public AlbumDao() {
        conn = DBHelper.getInstance().getConnection();
    }
 
    @Override
    public int insert(AlbumDto dto) {
        int result = 0;
        String query = " INSERT INTO album VALUES (?, ?, ?) ";
 
        try {
            pstmt = conn.prepareStatement(query);
            pstmt.setInt(1, dto.getUserId());
            pstmt.setInt(2, dto.getId());
            pstmt.setString(3, dto.getTitle());
            result = pstmt.executeUpdate(); // 성공 시 1
 
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
 
}

 

AlbumService

public class AlbumService {
 
    private AlbumDao albumDao;
    
    public AlbumService() {
        albumDao = new AlbumDao();
    }
    
    // 앨범 추가하는 로직
    public int addAlbum(AlbumDto dto) {
        int result = 0;
        
        result = albumDao.insert(dto);
        return result;
    }
    
}

 

AlbumController

public class AlbumController {
 
    private AlbumService albumService;
 
    public AlbumController() {
        albumService = new AlbumService();
    }
 
    // 앨범 추가 요청
    public int requestAddAlbum(AlbumDto dto) {
        int responseInt = 0;
 
        // 성공했다면 1 반환
        responseInt = albumService.addAlbum(dto);
 
        // 실패했을 때만 콘솔로 알려줌
        if (responseInt == 0) {
            System.out.println("추가 요청이 실패했습니다.");
        }
 
        return responseInt;
    }
 
}

 

Main

public class MainTest {
 
    public static void main(String[] args) {
 
        try {
            URL url = new URL("https://jsonplaceholder.typicode.com/albums");
            HttpURLConnection conn = (HttpURLConnection) url.openConnection();
            conn.setRequestMethod("GET");
            conn.connect();
 
            int statusCode = conn.getResponseCode();
 
            if (statusCode == 200) {
                BufferedReader reader = new BufferedReader(new InputStreamReader(conn.getInputStream()));
 
                String line = null;
                StringBuffer sb = new StringBuffer();
 
                while ((line = reader.readLine()) != null) {
                    sb.append(line);
                }
 
                String resultStr = sb.toString();
                Gson gson = new Gson();
 
                Type listType = new TypeToken<List<AlbumDto>>() {
                }.getType();
                ArrayList<AlbumDto> albumList = gson.fromJson(resultStr, listType);
 
                // 리스트에 있는 객체들을 DB에 담기
                AlbumController albumController = new AlbumController();
 
                for (AlbumDto albumDto : albumList) {
                    albumController.requestAddAlbum(albumDto);
                    System.out.println(albumDto);
                }
 
            } // end of if
 
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 

 

---

실행 후 SQL 테이블 확인