학원/Spring
4. 차트 그리기 ( 전체 조회하기 )
주먀
2023. 6. 7. 12:49
* 문화 빅데이터 플랫폼 : 데이터 가져오는 사이트 *
문화빅데이터 플랫폼
한국문화정보원이 운영하는 문화빅데이터 플랫폼은 공공기관 및 민간기업으로 구성된 데이터센터와 함께 도서, 체육, 예술, 숙박, 레저, 음식 등 고품질의 문화 분야 데이터를 개방하고 데이터
www.bigdata-culture.kr
1. mysql 다운로드
-> IDE( 통합개발환경 )
oracle DB --> sqldeveloper
mysql -> workbench
2. 로그인없이 다운로드 ( 다운로드 안되면 ) -> visual c++ 2019 다운로드하기
( X64 버전 다운로드하고 실행한 후 워크밴치 다운로드)
- https://learn.microsoft.com/ko-kr/cpp/windows/latest-supported-vc-redist?view=msvc-170
지원되는 최신 Visual C++ 재배포 가능 패키지 다운로드
이 문서에서는 최신 버전의 Visual C++ 재배포 가능 패키지 패키지에 대한 다운로드 링크를 나열합니다.
learn.microsoft.com
* 프로젝트 순서 ( 기능정의 ) *
- 데이터 전체 원본보기 -> 원본데이터로 기능 정의하기 -> 필요없는 컬럼 지우기
3. 실습
3-1. 프로젝트복사 -> jsp, controller ( kr 부터 ) 다 삭제하기
3-2. 템플릿 jsp views로 붙여넣기 -> resources 파일 전부 resources에 붙여넣기
4. TABLE 생성 -> COMMIT -> SELECT문 작성
4. 데이터 IMPORT 해주기 ( 전체를 보고 싶을때 : SELECT * FROM COM.BROADCAST; )
5. 패키지생성 -> 컨트롤러생성 -> servlet-context.xml 주소 변경하기
오류1. servers -> spring1 삭제
1-2. servers 더블클릭 -> Modules -> Path값 다르게 수정해주기
오류2. 404오류 ( 404오류는 실행이 안될때 뜨는거다. ) -> servlet-context 위치 잡아주기
2-1. 매핑 / 빼고 삭제하기
*에러*
- ill 오류 -> 볼 필요 없다.
- 검은색 글자 위주로 읽고, 오른쪽 끝까지 읽기
6. BroadCastController.java
6-1. 게시글 조회하기
6-2. mapper 파일명 xml 파일명 무조건 동일하게 하기
6-3. root-context 무조건 확인하기
6-4. 자바변수명은 소문자로 하기 " ; " 쓰기
6-5. result type이여도 행이 하나이면 ArrayList 안써도된다.
package kr.smhrd.controller;
import java.util.ArrayList;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import kr.smhrd.entity.BroadCast;
import kr.smhrd.mapper.BroadCastMapper;
@Controller // 1) 클래스파일이 Controller(POJO)임을 알려주기
public class BroadCastController {
// Mapper 인터페이스 사용할 수 잇게끔 연결
@Autowired
private BroadCastMapper mapper;
@RequestMapping("/") // 2) url mapping을 "/"로 들어왔을 떄 잡아주기
public String index(Model model) {
// db에서 데이터 조회하기
BroadCast vo = mapper.getRating();
model.addAttribute("vo", vo);
return "index";
// 3) /로 들어왔을 때 index.jsp 페이지를 forward방식으로 되돌려주기
}
}
7. BroadCastMapper.java
package kr.smhrd.mapper;
import kr.smhrd.entity.BroadCast;
// @Mapper ---> 생략가능
public interface BroadCastMapper {
// 남/여/전체 시청률 평균을 조회하는 기능
// 조회한 결과 행이 하나이기 때문에 BroadCast로 받아오기!
// session.selectOne();
public BroadCast getRating();
}
8. BroadCast.java
package kr.smhrd.entity;
import java.util.Date;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@NoArgsConstructor
@AllArgsConstructor
@Data
public class BroadCast {
// table에 존재하는 컬럼을 전부 필드로 만들기
private int brdcst_num;
private Date brdcst_de;
private String program_nm;
private float nyo_rt;
private float n10s_rt;
private float n20s_rt;
private float n30s_rt;
private float n40s_rt;
private float n50s_rt;
private float n60s_above_rt;
private float male_rt;
private float female_rt;
private float wtchng_rt;
private String fixing_cast_nm;
private String cast_nm;
}
9. BroadCastMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="kr.smhrd.mapper.BroadCastMapper">
<select id="getRating" resultType="kr.smhrd.entity.BroadCast">
SELECT
PROGRAM_NM,
ROUND( AVG (
MALE_RT ) , 3 ) AS MALE_RT,
ROUND ( AVG( FEMALE_RT ) , 3 ) AS
FEMALE_RT,
ROUND ( AVG( WTCHNG_RT ) , 3 ) AS WTCHNG_RT
FROM
COM.BROADCAST
WHERE PROGRAM_NM = '뮤직뱅크';
</select>
</mapper>
10. index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<meta name="description" content="">
<meta name="author" content="">
<title>Dashboard</title>
<!-- Custom fonts for this template-->
<link href="resources/vendor/fontawesome-free/css/all.min.css" rel="stylesheet" type="text/css">
<link href="https://fonts.googleapis.com/css?family=Nunito:200,200i,300,300i,400,400i,600,600i,700,700i,800,800i,900,900i"
rel="stylesheet">
<!-- Custom styles for this template-->
<link href="resources/css/sb-admin-2.min.css" rel="stylesheet">
</head>
<body id="page-top">
<!-- Page Wrapper -->
<div id="wrapper">
<!-- Sidebar -->
<ul class="navbar-nav bg-gradient-primary sidebar sidebar-dark accordion" id="accordionSidebar">
<!-- Sidebar - Brand -->
<a class="sidebar-brand d-flex align-items-center justify-content-center" href="index.html">
<div class="sidebar-brand-icon rotate-n-15">
<i class="fas fa-laugh-wink"></i>
</div>
<div class="sidebar-brand-text mx-3">SB Admin <sup>2</sup></div>
</a>
<!-- Divider -->
<hr class="sidebar-divider">
<!-- Heading -->
<div class="sidebar-heading">
Page
</div>
<!-- Nav Item - Charts -->
<li class="nav-item">
<a class="nav-link" href="register">
<i class="fas fa-fw fa-clipboard-list"></i>
<span>출연진 관리</span></a>
</li>
<!-- Divider -->
<hr class="sidebar-divider d-none d-md-block">
<!-- Sidebar Toggler (Sidebar) -->
<div class="text-center d-none d-md-inline">
<button class="rounded-circle border-0" id="sidebarToggle"></button>
</div>
</ul>
<!-- End of Sidebar -->
<!-- Content Wrapper -->
<div id="content-wrapper" class="d-flex flex-column">
<!-- Main Content -->
<div id="content">
<!-- Topbar -->
<nav class="navbar navbar-expand navbar-light bg-white topbar mb-4 static-top shadow">
<!-- Sidebar Toggle (Topbar) -->
<button id="sidebarToggleTop" class="btn btn-link d-md-none rounded-circle mr-3">
<i class="fa fa-bars"></i>
</button>
<!-- Topbar Search -->
<form
class="d-none d-sm-inline-block form-inline mr-auto ml-md-3 my-2 my-md-0 mw-100 navbar-search">
<div class="input-group">
<input type="text" class="form-control bg-light border-0 small" placeholder="Search for..."
aria-label="Search" aria-describedby="basic-addon2">
<div class="input-group-append">
<button class="btn btn-primary" type="button">
<i class="fas fa-search fa-sm"></i>
</button>
</div>
</div>
</form>
</nav>
<!-- End of Topbar -->
<!-- Begin Page Content -->
<div class="container-fluid">
<!-- Page Heading -->
<div class="d-sm-flex align-items-center justify-content-between mb-4">
<h1 class="h3 mb-0 text-gray-800">Dashboard</h1>
</div>
<!-- Content Row -->
<div class="row">
<!-- Earnings (Monthly) Card Example -->
<div class="col-xl-3 col-md-6 mb-4">
<div class="card border-left-primary shadow h-100 py-2">
<div class="card-body">
<div class="row no-gutters align-items-center">
<div class="col mr-2">
<div class="text-xs font-weight-bold text-primary text-uppercase mb-1">
${vo.program_nm} 남성 시청율 평균
</div>
<div class="row no-gutters align-items-center">
<div class="col-auto">
<div class="h5 mb-0 mr-3 font-weight-bold text-gray-800">${vo.male_rt}%</div>
</div>
<div class="col">
<div class="progress progress-sm mr-2">
<div class="progress-bar bg-primary" role="progressbar"
style="width:${vo.male_rt}%" aria-valuenow="${vo.male_rt}" aria-valuemin="0"
aria-valuemax="100"></div>
</div>
</div>
</div>
</div>
<div class="col-auto">
<i class="fas fa-clipboard-list fa-2x text-gray-300"></i>
</div>
</div>
</div>
</div>
</div>
<div class="col-xl-3 col-md-6 mb-4">
<div class="card border-left-danger shadow h-100 py-2">
<div class="card-body">
<div class="row no-gutters align-items-center">
<div class="col mr-2">
<div class="text-xs font-weight-bold text-danger text-uppercase mb-1">
${vo.program_nm} 여성 시청률 평균
</div>
<div class="row no-gutters align-items-center">
<div class="col-auto">
<div class="h5 mb-0 mr-3 font-weight-bold text-gray-800">${vo.female_rt}%</div>
</div>
<div class="col">
<div class="progress progress-sm mr-2">
<div class="progress-bar bg-danger" role="progressbar"
style="width: ${vo.female_rt}%" aria-valuenow="${vo.female_rt}" aria-valuemin="0"
aria-valuemax="100"></div>
</div>
</div>
</div>
</div>
<div class="col-auto">
<i class="fas fa-clipboard-list fa-2x text-gray-300"></i>
</div>
</div>
</div>
</div>
</div>
<div class="col-xl-3 col-md-6 mb-4">
<div class="card border-left-info shadow h-100 py-2">
<div class="card-body">
<div class="row no-gutters align-items-center">
<div class="col mr-2">
<div class="text-xs font-weight-bold text-info text-uppercase mb-1">
${vo.program_nm} 전체 시청률 평균
</div>
<div class="row no-gutters align-items-center">
<div class="col-auto">
<div class="h5 mb-0 mr-3 font-weight-bold text-gray-800">${vo.wtchng_rt}%</div>
</div>
<div class="col">
<div class="progress progress-sm mr-2">
<div class="progress-bar bg-info" role="progressbar"
style="width: ${vo.wtchng_rt}%" aria-valuenow="${vo.wtchng_rt}" aria-valuemin="0"
aria-valuemax="100"></div>
</div>
</div>
</div>
</div>
<div class="col-auto">
<i class="fas fa-clipboard-list fa-2x text-gray-300"></i>
</div>
</div>
</div>
</div>
</div>
</div>
<!-- Content Row -->
<div class="row">
<!-- Area Chart -->
<div class="col-xl-8 col-lg-7">
<div class="card shadow mb-4">
<!-- Card Header - Dropdown -->
<div
class="card-header py-3 d-flex flex-row align-items-center justify-content-between">
<h6 class="m-0 font-weight-bold text-primary">${vo.program_nm} 월별 시청률 </h6>
</div>
<!-- Card Body -->
<div class="card-body">
<div class="chart-area">
<canvas id="myAreaChart"></canvas>
</div>
</div>
</div>
</div>
<!-- Pie Chart -->
<div class="col-xl-4 col-lg-5">
<div class="card shadow mb-4">
<!-- Card Header - Dropdown -->
<div
class="card-header py-3 d-flex flex-row align-items-center justify-content-between">
<h6 class="m-0 font-weight-bold text-primary">${vo.program_nm} 연령대별 시청률 </h6>
</div>
<!-- Card Body -->
<div class="card-body">
<div class="chart-pie pt-4 pb-2">
<canvas id="myPieChart"></canvas>
</div>
<div class="mt-4 text-center small">
<span class="mr-2">
<i class="fas fa-circle text-primary"></i> 청소년
</span>
<span class="mr-2">
<i class="fas fa-circle text-success"></i> 성인
</span>
<span class="mr-2">
<i class="fas fa-circle text-info"></i> 60대 이상
</span>
</div>
</div>
</div>
</div>
</div>
<!-- Content Row -->
<div class="row">
<!-- Content Column -->
<div class="col-lg-6 mb-4">
<!-- Project Card Example -->
<div class="card shadow mb-4">
<div class="card-header py-3">
<h6 class="m-0 font-weight-bold text-primary">출연진 TOP5</h6>
</div>
<div class="card-body">
<h4 class="small font-weight-bold">
NMIXX <span class="float-right">20%</span>
</h4>
<div class="progress mb-4">
<div class="progress-bar bg-danger" role="progressbar" style="width: 20%"
aria-valuenow="20" aria-valuemin="0" aria-valuemax="100"></div>
</div>
<h4 class="small font-weight-bold">
스테이씨 <span class="float-right">40%</span>
</h4>
<div class="progress mb-4">
<div class="progress-bar bg-warning" role="progressbar" style="width: 40%"
aria-valuenow="40" aria-valuemin="0" aria-valuemax="100"></div>
</div>
<h4 class="small font-weight-bold">
르세라핌 <span class="float-right">60%</span>
</h4>
<div class="progress mb-4">
<div class="progress-bar" role="progressbar" style="width: 60%"
aria-valuenow="60" aria-valuemin="0" aria-valuemax="100"></div>
</div>
<h4 class="small font-weight-bold">
뉴진스<span class="float-right">80%</span>
</h4>
<div class="progress mb-4">
<div class="progress-bar bg-info" role="progressbar" style="width: 80%"
aria-valuenow="80" aria-valuemin="0" aria-valuemax="100"></div>
</div>
<h4 class="small font-weight-bold">
아이브<span class="float-right">100%</span>
</h4>
<div class="progress">
<div class="progress-bar bg-success" role="progressbar" style="width: 100%"
aria-valuenow="100" aria-valuemin="0" aria-valuemax="100"></div>
</div>
</div>
</div>
</div>
</div>
</div>
<!-- /.container-fluid -->
</div>
<!-- End of Main Content -->
<!-- Footer -->
<footer class="sticky-footer bg-white">
<div class="container my-auto">
<div class="copyright text-center my-auto">
<span>Copyright © Your Website 2023</span>
</div>
</div>
</footer>
<!-- End of Footer -->
</div>
<!-- End of Content Wrapper -->
</div>
<!-- End of Page Wrapper -->
<!-- Scroll to Top Button-->
<a class="scroll-to-top rounded" href="#page-top">
<i class="fas fa-angle-up"></i>
</a>
<!-- Bootstrap core JavaScript-->
<script src="resources/vendor/jquery/jquery.min.js"></script>
<script src="resources/vendor/bootstrap/js/bootstrap.bundle.min.js"></script>
<!-- Core plugin JavaScript-->
<script src="resources/vendor/jquery-easing/jquery.easing.min.js"></script>
<!-- Custom scripts for all pages-->
<script src="resources/js/sb-admin-2.min.js"></script>
<!-- Page level plugins -->
<script src="resources/vendor/chart.js/Chart.min.js"></script>
<!-- Page level custom scripts -->
<script src="resources/js/demo/chart-area-demo.js"></script>
<script src="resources/js/demo/chart-pie-demo.js"></script>
</body>
</html>