Spring/Spring 실습
spring / jsonparser 영화순위 api 배열 데이터 출력하기
java나유
2022. 9. 16. 14:27
1. movie 테이블 생성
create table apiMovie(
aidx int(4) not null auto_increment,
arank int(2) not null,
arankpart enum('OLD','NEW') not null default 'OLD',
aperson int(8) not null,
anm varchar(150) not null,
aindate datetime not null default '0001-00-00 01:00:00',
primary key(aidx)
);
2. 영화진흥위원회 / 일별 박스 오피스 API 서비스
2-1. jsonparser.java => 실행하면 데이터 베이스에 데이터 입력됨
@datainsert
package webpage;
import java.sql.*;
import org.apache.commons.dbcp.BasicDataSource;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
/*
최초 api데이터 로드할 때는 객체형 프로그램으로 실행
json 라이브러리 이용해서 해당 api 배열 데이터를 키값으로 분리
출력 확인 후 Controller로 변경
*/
@Controller
public class jsonparser {
@Autowired
BasicDataSource dataConnect;
//영화진흥위원회 오픈API 2022-09-07 일별 박스오피스
@RequestMapping("jsonparser.do")
public void parser() {
String nowtime=null;
try {
Connection con = dataConnect.getConnection();
System.out.println(con);
String todays ="select now() as today";
PreparedStatement ps1 =con.prepareStatement(todays);
ResultSet rs= ps1.executeQuery();
while(rs.next()) {
nowtime=rs.getString("today"); //sql에 현재 시간 출력하는 코드
}
System.out.println(nowtime);
String jsondata = "[{\"rnum\":\"1\",\"rank\":\"1\",\"rankInten\":\"4\",\"rankOldAndNew\":\"OLD\",\"movieCd\":\"20215601\",\"movieNm\":\"공조2: 인터내셔날\",\"openDt\":\"2022-09-07\",\"salesAmt\":\"2111720140\",\"salesShare\":\"74.0\",\"salesInten\":\"2082409140\",\"salesChange\":\"7104.5\",\"salesAcc\":\"2214244140\",\"audiCnt\":\"216311\",\"audiInten\":\"213060\",\"audiChange\":\"6553.7\",\"audiAcc\":\"227409\",\"scrnCnt\":\"2167\",\"showCnt\":\"10152\"},{\"rnum\":\"2\",\"rank\":\"2\",\"rankInten\":\"-1\",\"rankOldAndNew\":\"OLD\",\"movieCd\":\"20217826\",\"movieNm\":\"육사오(6/45)\",\"openDt\":\"2022-08-24\",\"salesAmt\":\"164228269\",\"salesShare\":\"5.8\",\"salesInten\":\"-275828897\",\"salesChange\":\"-62.7\",\"salesAcc\":\"12382654766\",\"audiCnt\":\"17032\",\"audiInten\":\"-27949\",\"audiChange\":\"-62.1\",\"audiAcc\":\"1233521\",\"scrnCnt\":\"743\",\"showCnt\":\"2000\"},{\"rnum\":\"3\",\"rank\":\"3\",\"rankInten\":\"0\",\"rankOldAndNew\":\"NEW\",\"movieCd\":\"20212709\",\"movieNm\":\"블랙폰\",\"openDt\":\"2022-09-07\",\"salesAmt\":\"105377437\",\"salesShare\":\"3.7\",\"salesInten\":\"105377437\",\"salesChange\":\"100\",\"salesAcc\":\"257667437\",\"audiCnt\":\"10678\",\"audiInten\":\"10678\",\"audiChange\":\"100\",\"audiAcc\":\"26618\",\"scrnCnt\":\"480\",\"showCnt\":\"1450\"},{\"rnum\":\"4\",\"rank\":\"4\",\"rankInten\":\"0\",\"rankOldAndNew\":\"NEW\",\"movieCd\":\"20183867\",\"movieNm\":\"알라딘\",\"openDt\":\"2019-05-23\",\"salesAmt\":\"74181000\",\"salesShare\":\"2.6\",\"salesInten\":\"74181000\",\"salesChange\":\"100\",\"salesAcc\":\"108234730039\",\"audiCnt\":\"6512\",\"audiInten\":\"6512\",\"audiChange\":\"100\",\"audiAcc\":\"12730289\",\"scrnCnt\":\"39\",\"showCnt\":\"238\"},{\"rnum\":\"5\",\"rank\":\"5\",\"rankInten\":\"-3\",\"rankOldAndNew\":\"OLD\",\"movieCd\":\"20211792\",\"movieNm\":\"헌트\",\"openDt\":\"2022-08-10\",\"salesAmt\":\"55319341\",\"salesShare\":\"1.9\",\"salesInten\":\"-153339477\",\"salesChange\":\"-73.5\",\"salesAcc\":\"42974976697\",\"audiCnt\":\"6404\",\"audiInten\":\"-14536\",\"audiChange\":\"-69.4\",\"audiAcc\":\"4166555\",\"scrnCnt\":\"585\",\"showCnt\":\"1143\"},{\"rnum\":\"6\",\"rank\":\"6\",\"rankInten\":\"9\",\"rankOldAndNew\":\"OLD\",\"movieCd\":\"20226879\",\"movieNm\":\"인생은 뷰티풀: 비타돌체\",\"openDt\":\"2022-09-07\",\"salesAmt\":\"181029000\",\"salesShare\":\"6.3\",\"salesInten\":\"158488000\",\"salesChange\":\"703.1\",\"salesAcc\":\"235784000\",\"audiCnt\":\"5231\",\"audiInten\":\"4653\",\"audiChange\":\"805\",\"audiAcc\":\"6635\",\"scrnCnt\":\"102\",\"showCnt\":\"145\"},{\"rnum\":\"7\",\"rank\":\"7\",\"rankInten\":\"-3\",\"rankOldAndNew\":\"OLD\",\"movieCd\":\"20194376\",\"movieNm\":\"탑건: 매버릭\",\"openDt\":\"2022-06-22\",\"salesAmt\":\"48117358\",\"salesShare\":\"1.7\",\"salesInten\":\"-36786323\",\"salesChange\":\"-43.3\",\"salesAcc\":\"86627341405\",\"audiCnt\":\"3433\",\"audiInten\":\"-3745\",\"audiChange\":\"-52.2\",\"audiAcc\":\"8082039\",\"scrnCnt\":\"104\",\"showCnt\":\"226\"},{\"rnum\":\"8\",\"rank\":\"8\",\"rankInten\":\"-5\",\"rankOldAndNew\":\"OLD\",\"movieCd\":\"20209343\",\"movieNm\":\"한산: 용의 출현\",\"openDt\":\"2022-07-27\",\"salesAmt\":\"18788800\",\"salesShare\":\"0.7\",\"salesInten\":\"-50840150\",\"salesChange\":\"-73\",\"salesAcc\":\"72960007186\",\"audiCnt\":\"2428\",\"audiInten\":\"-5543\",\"audiChange\":\"-69.5\",\"audiAcc\":\"7177401\",\"scrnCnt\":\"265\",\"showCnt\":\"405\"},{\"rnum\":\"9\",\"rank\":\"9\",\"rankInten\":\"2\",\"rankOldAndNew\":\"OLD\",\"movieCd\":\"20225892\",\"movieNm\":\"시맨틱 에러: 더 무비\",\"openDt\":\"2022-08-31\",\"salesAmt\":\"20544100\",\"salesShare\":\"0.7\",\"salesInten\":\"10634700\",\"salesChange\":\"107.3\",\"salesAcc\":\"393364400\",\"audiCnt\":\"2017\",\"audiInten\":\"1056\",\"audiChange\":\"109.9\",\"audiAcc\":\"33314\",\"scrnCnt\":\"32\",\"showCnt\":\"32\"},{\"rnum\":\"10\",\"rank\":\"10\",\"rankInten\":\"-3\",\"rankOldAndNew\":\"OLD\",\"movieCd\":\"20219345\",\"movieNm\":\"놉\",\"openDt\":\"2022-08-17\",\"salesAmt\":\"8489080\",\"salesShare\":\"0.3\",\"salesInten\":\"-17843310\",\"salesChange\":\"-67.8\",\"salesAcc\":\"4593492276\",\"audiCnt\":\"1255\",\"audiInten\":\"-1222\",\"audiChange\":\"-49.3\",\"audiAcc\":\"404563\",\"scrnCnt\":\"30\",\"showCnt\":\"39\"}]";
JSONParser parser = new JSONParser();
JSONArray as =(JSONArray)parser.parse(jsondata);
int w=0;
while(w<as.size()) {
JSONObject jobobject = (JSONObject)as.get(w);
System.out.println(jobobject.get("movieNm"));
String datain = "insert into apiMovie values('0',?,?,?,?,?)";
PreparedStatement ps2 = con.prepareStatement(datain);
ps2.setString(1,(String)jobobject.get("rank"));
ps2.setString(2,(String)jobobject.get("rankOldAndNew"));
ps2.setString(3,(String)jobobject.get("audiAcc"));
ps2.setString(4,(String)jobobject.get("movieNm"));
ps2.setString(5,nowtime);
ps2.executeUpdate();
w++;
}
}catch (Exception e) {
System.out.println(e);
}
}
}
3.movie-api.java
@RestContrller
package webpage;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.apache.commons.dbcp.BasicDataSource;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class movie_api {
private String apikey=null;
private String apidate=null;
private String result=null;
public String json_parsing = null;
@Autowired
BasicDataSource dataConnect;
//method=RequestMethod.GET : Front에서 전송된 데이터를 get으로 통신
//@GetMapping, @PostMapping, @PutMapping, @DeleteMapping : method 속성 하용하지 않음
@RequestMapping(value ="/movie", produces ="text/html; charset=utf-8",method=RequestMethod.GET)
public String apiserver(String apikey,String apidate) {
//(String apikey,String apidate) get으로 날리는 파라미터값
try {
this.apikey=apikey;
this.apidate=apidate;
//this.apidate=
//PrintWriter out =res.getWriter();
if(!this.apikey.equals("null") || !this.apikey.equals("")) {
if(this.apikey.equals("movie_youna") && !this.apidate.equals("")) {
//DB호출
Connection con = dataConnect.getConnection();
String sdate = this.apidate + " 00:00:00";
String edate = this.apidate + " 23:59:59";
String sql="select * from apiMovie where aindate >= ? and aindate <= ?";
PreparedStatement pr = con.prepareStatement(sql);
pr.setString(1, sdate);
pr.setString(2, edate);
System.out.println(pr);
ResultSet rs =pr.executeQuery();
JSONArray ja = new JSONArray();
while(rs.next()) {
JSONObject jb = new JSONObject();
jb.put("aidx", rs.getString("aidx"));
jb.put("arank", rs.getString("arank"));
jb.put("arankpart", rs.getString("arankpart"));
jb.put("aperson", rs.getString("aperson"));
jb.put("anm", rs.getString("anm"));
jb.put("aindate", rs.getString("aindate"));
//json_parsing = jb.toString();
ja.add(jb);
}
result=ja.toString();
System.out.println(result);
}
}
}
catch(Exception e) {
//System.out.println(e);
result="error:code[32]"; //이런식으로 주고받고
}
return result;
}
}
4.movieDAO.java
@getter, @setter
package webpage;
import lombok.Getter;
import lombok.Setter;
@Setter
@Getter
public class movieDAO {
private String aidx;
private String arank;
private String arankpart;
private String aperson;
private String anm;
private String aindate;
}
5.moviest.jsp
@View
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>일일 영화 순위 공작소 + movie_api.java</title>
</head>
<body>
<table border="1" cellpadding="0" cellspacing="0">
<thead>
<tr>
<th width="80">순위</th>
<th width="200">영화제목</th>
<th width="100">누적관객수</th>
</tr>
</thead>
<tbody id="listview">
</tbody>
</table>
</body>
<script>
/* front(ajax)는 apikey와 날짜를 파라미터에 적용하여 GET으로 전달*/
let today = "2022-09-13";
let ajax = new XMLHttpRequest();
ajax.open("GET", "http://localhost:8080/webpage/movie?apikey=movie_youna&apidate="+today,true);
ajax.send();
ajax.onreadystatechange = function(){
if(ajax.status == 200 && ajax.readyState == XMLHttpRequest.DONE){
var mdata = JSON.parse(ajax.response);
/* var mdata = ajax.response;
var mdata2 = mdata.relpace("\/",""); */
console.log(ajax.response);
html_print(mdata);
}
}
function html_print(mdata){
var pt=document.getElementById("listview");
var ea = mdata.length;
//console.log(ea);
var icon ="";//신규진입한 영화아이콘
var k=0;
var htmlcode ="";
do{
if(mdata[k]['arankpart']=="NEW"){
icon ="[NEW]";
}
else{
icon ="";
}
htmlcode +="<tr>";
htmlcode +="<td style='text-align:center;'>"+mdata[k]['arank']+"</td>";
htmlcode +="<td>"+icon+mdata[k]['anm']+"</td>";
htmlcode +="<td style='text-align:right;'>"+Number(mdata[k]['aperson']).toLocaleString()+'명'+"</td>";
htmlcode +="</tr>";
k++;
}while(k<ea);
pt.innerHTML = htmlcode;
}
</script>
</html>
최종 view 화면
728x90