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

chart.xml

    <!-- 가해자법규위반명, 법규 위반별 사망, 부상, 중상자수 통계 -->
    <select id="accidentDamageCount" resultType="com.carblre.repository.model.AccidentDamageCount">
        SELECT lt.aslt_vtr_nm AS 'asltVtrNm', SUM(ct.dth_dnv_cnt) AS 'dthDnvCnt', SUM(ct.injpsn_cnt) AS 'injpsnCnt', SUM(ct.se_dnv_cnt) AS 'seDnvCnt'
        FROM crushApi_tb AS ct
                 LEFT JOIN lawViolation_tb as lt
                           ON ct.aslt_vtr_cd = lt.aslt_vtr_cd
        GROUP BY ct.aslt_vtr_cd, lt.aslt_vtr_nm
        ORDER BY lt.aslt_vtr_nm ASC
    </select>

 

 

 

AccidentDamageCount

package com.carblre.repository.model;

import lombok.*;

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

    private String asltVtrNm; // 가해자법규위반명
    private Integer dthDnvCnt; // 법규 위반별 사망
    private Integer injpsnCnt; // 법규 위반별 부상
    private Integer seDnvCnt; // 법규 위반별 중상

}

 

 

 

ChartService

package com.carblre.service;

import com.carblre.repository.Interface.ChartRepository;
import com.carblre.repository.model.AccidentDamageCount;
import com.carblre.repository.model.Chart;
import com.carblre.repository.model.DeathToYearCount;
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 DeathToYearCount deathToYearCount;

    @Transactional
    public List<DeathToYearCount> deathToYearCount() {
        List<DeathToYearCount> result = chartRepository.deathToYearCount();
        System.out.println("년도별 사망 및 부상자 통계 : " + result); // 추가된 로그
        return result;
    }

    @Transactional
    public List<AccidentDamageCount> accidentDamageCount() {
        List<AccidentDamageCount> result = chartRepository.accidentDamageCount();
        System.out.println("가해자 법규 위반별 사망 및 부상자 통계 : " + result); // 추가된 로그
        return result;
    }

}

 

 

 

chart.jsp

<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 deathChartData = JSON.parse('${deathChartData}');
        const label = deathChartData.map(deathToYearCount => deathToYearCount.year);
        const allDeathCount = deathChartData.map(deathToYearCount => deathToYearCount.deathCount);
        const injuredCount = deathChartData.map(deathToYearCount => deathToYearCount.injuredCount);
        const seriousInjuriesCount = deathChartData.map(deathToYearCount => deathToYearCount.seriousInjuriesCount);
        const minorInjuriesCount = deathChartData.map(deathToYearCount => deathToYearCount.minorInjuriesCount);

        const data = {
            labels: label,
            datasets: [
                {
                    label: '사망자 수',
                    data: allDeathCount,
                    borderColor: 'rgba(255, 99, 132, 1)',
                    backgroundColor: 'rgba(255, 99, 132, 0.5)',
                    pointStyle: 'circle',
                    pointRadius: 10,
                    pointHoverRadius: 20
                },{
                    label: '부상자 수',
                    data: injuredCount,
                    borderColor: 'rgb(99,161,255)',
                    backgroundColor: 'rgba(99,161,255, 0.5)',
                    pointStyle: 'circle',
                    pointRadius: 10,
                    pointHoverRadius: 20
                },{
                    label: '중상자 수',
                    data: seriousInjuriesCount,
                    borderColor: 'rgb(128,255,99)',
                    backgroundColor: 'rgba(128,255,99, 0.5)',
                    pointStyle: 'circle',
                    pointRadius: 10,
                    pointHoverRadius: 20
                },{
                    label: '경상자 수',
                    data: minorInjuriesCount,
                    borderColor: 'rgb(255,229,99)',
                    backgroundColor: 'rgba(255,229,99, 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);
    });
    </script>