5일차 - DB 활용한 chart.js 차트 그리기(1)

▶ 밀어넣은 데이터를 활용해서 chart 그리기

 

 

ChartController

package com.carblre.controller;

import com.carblre.repository.model.DeathToYearCountDTO;
import com.carblre.service.ChartService;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import java.util.List;

@Controller
@RequestMapping("/chart")
public class ChartController {

    @Autowired
    private ChartService chartService;

    @Autowired
    private ObjectMapper objectMapper;

    @GetMapping("/chart")
    public String getChart(Model model) throws JsonProcessingException {
        List<DeathToYearCountDTO> chartData = chartService.deathToYearCount();
        String jsonChartData = objectMapper.writeValueAsString(chartData);
        System.out.println(chartData);
        System.out.println(jsonChartData);
        model.addAttribute("chartData", jsonChartData);
        return "chart/chart";
    }

}

 

 

 

DeathToYearCountDTO

package com.carblre.repository.model;

import lombok.*;

@Data
@ToString
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class DeathToYearCountDTO {

    private Integer year; // acc_year 에서 가져온 년도
    private Integer deathCount; // SUM(dth_dnv_cnt) 에서 가져온 사망자수
}

 

 

 

chart.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ include file="../layout/header.jsp"%>

<h1> 차트 JS </h1>

<canvas id="yearChart"></canvas>
<canvas id="yearCharts"></canvas>

<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/4.4.0/chart.umd.min.js"></script>
<script>
    <!-- 년도별 사망자수 통계 차트 START -->
    document.addEventListener('DOMContentLoaded', (event) => {
        const chartData = JSON.parse('${chartData}');
        const label = chartData.map(deathToYearCountDTO => deathToYearCountDTO.year);
        const allDeathCount = chartData.map(deathToYearCountDTO => deathToYearCountDTO.deathCount);

        const data = {
            labels: label,
            datasets: [
                {
                    label: [label],
                    data: allDeathCount,
                    borderColor: 'rgba(255, 99, 132, 1)',
                    backgroundColor: 'rgba(255, 99, 132, 0.5)',
                    pointStyle: 'circle',
                    pointRadius: 10,
                    pointHoverRadius: 20
                }
            ]
        };

        const config = {
            type: 'line',
            data: data,
            options: {
                responsive: true,
                plugins: {
                    title: {
                        display: true,
                        text: '년도별 사망자수 통계'
                    }
                }
            }
        };
        const ct = document.getElementById('yearCharts').getContext('2d');
        const yearCharts = new Chart(ct, config);
    });
    <!-- 년도별 사망자수 통계 차트 END -->


</script>

<%@ include file="../layout/footer.jsp"%>

 

 

 

ChartService

package com.carblre.service;

import com.carblre.repository.Interface.ChartRepository;
import com.carblre.repository.model.Chart;
import com.carblre.repository.model.DeathToYearCountDTO;
import lombok.Data;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Data
@Service
public class ChartService {

    @Autowired
    private ChartRepository chartRepository;

    private DeathToYearCountDTO deathToYearCountDTO;

    @Transactional
    public List<DeathToYearCountDTO> deathToYearCount() {
        List<DeathToYearCountDTO> result = chartRepository.deathToYearCount();
        System.out.println("레파지토리 값 : " + result); // 추가된 로그
        return result;
    }

}

 

 

 

chart.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="com.carblre.repository.Interface.ChartRepository">

    <!-- 년도별 사망자수 통계 -->
    <select id="deathToYearCount" resultType="com.carblre.repository.model.DeathToYearCountDTO">
        SELECT ct.acc_year AS 'year', SUM(ct.dth_dnv_cnt) AS 'deathCount'
        FROM crushApi_tb as ct
        GROUP BY ct.acc_year
        ORDER BY ct.acc_year ASC
    </select>

    <!-- 년도, 법규 위반별 사망, 부상, 중상자수 통계 -->
    <select id="yearDamageCount" resultType="com.carblre.repository.model.Chart">
        SELECT ct.acc_year AS '년도', ct.aslt_vtr_cd AS '가해자법규위반', SUM(ct.dth_dnv_cnt) AS '사망자수', SUM(ct.injpsn_cnt) AS '부상자수', SUM(ct.se_dnv_cnt) AS '중상자수'
        FROM crushApi_tb AS ct
        GROUP BY ct.acc_year, aslt_vtr_cd
        ORDER BY ct.acc_year DESC, aslt_vtr_cd
    </select>

    <!-- 차대차 유형 중분류 별로 사망, 경상, 중상자 수 통계 -->
    <select id="carTypeDamageCount" resultType="com.carblre.repository.model.Chart">
        SELECT ct.acc_ty_lclas_cd AS '사고유형대분류:차대차', ct.acc_ty_mlsfc_cd AS '사고유형중분류', ct.acc_ty_cd AS '사고유형명', SUM(ct.dth_dnv_cnt) AS '사망자수', SUM(ct.injpsn_cnt) AS '부상자수', SUM(ct.se_dnv_cnt) AS '중상자수'
        FROM crushApi_tb AS ct
        WHERE ct.acc_ty_lclas_cd = 02
        GROUP BY ct.acc_ty_mlsfc_cd
        ORDER BY ct.acc_ty_mlsfc_cd ASC
    </select>

    <!-- 도로형태별 통계 -->
    <select id="roadDamageCount" resultType="com.carblre.repository.model.Chart">
        SELECT ct.road_frm_cd AS '도로형태코드', COUNT(ct.road_frm_cd) AS '도로형태명'
        FROM crushApi_tb AS ct
        WHERE ct.road_frm_cd
        GROUP BY ct.road_frm_cd
        ORDER BY road_frm_cd ASC
    </select>

</mapper>

 

 

 

ChartRepository

package com.carblre.repository.Interface;

import com.carblre.repository.model.Chart;
import com.carblre.repository.model.DeathToYearCountDTO;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface ChartRepository {

    public List<DeathToYearCountDTO> deathToYearCount();

    public List<Chart> yearDamageCount();

    public List<Chart> carTypeDamageCount();

    public List<Chart> roadDamageCount();

}

 

---

오답노트

차트 그릴 때 콘솔창에 데이터는 잘 받아왔지만 차트는 그려지지 않았음

public List<DeathToYearCountDTO> deathToYearCount();

▶ 처음에 리스트를 ItemDTO로 받아왔는데 따로 DeathToYearCountDTO라는 이름으로 DTO를 생성 후 담아줌

 

    <!-- 년도별 사망자수 통계 -->
    <select id="deathToYearCount" resultType="com.carblre.repository.model.DeathToYearCountDTO">
        SELECT ct.acc_year AS 'year', SUM(ct.dth_dnv_cnt) AS 'deathCount'
        FROM crushApi_tb as ct
        GROUP BY ct.acc_year
        ORDER BY ct.acc_year ASC
    </select>

const chartData = JSON.parse('${chartData}');
const label = chartData.map(deathToYearCountDTO => deathToYearCountDTO.year);
const allDeathCount = chartData.map(deathToYearCountDTO => deathToYearCountDTO.deathCount);

▶ 처음 쿼리문 던질 때 AS '년도', AS '사망자수' 이렇게 던지고 jsp에서 deathToYearCountDTO.year, deathToYearCountDTO.deathCount 이렇게 가져오니 null 값이 떴음

▶ 이후 AS를 year, deathCount로 바꾸고 나니 잘 받아와졌다

 

---

 

년도별 사망자 차트인데 하나만 표출하니 빈약해 보여서 부상자, 중상자, 경상자도 추가해서 넣을 예정