Tech Story/IoT

구글차트 DB 연동하기

슈퍼맨짱 2020. 12. 19. 20:16

구글차트 MySQL 연동하기 - 멀티데이터, 멀티차트, 애니메이션 효과


[ DB 데이터 불러오기 - data_read4.jsp ]


<%@page import="java.text.DecimalFormat"%>

<%@page import="java.sql.*"%>

<%@page import="java.util.*"%>

<%@page import="org.json.simple.JSONObject"%>

<%

    //커넥션 선언

    Connection con = null;


    try {

        //드라이버 호출, 커넥션 연결

        Class.forName("com.mysql.jdbc.Driver").newInstance();

        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?autoReconnect=true&useSSL=false&characterEncoding=utf8&serverTimezone=UTC", "mgt", "mgt");


    ResultSet rs = null;

        //DB에서 뽑아온 데이터(JSON) 을 담을 객체. 후에 responseObj에 담기는 값

        List dustlist = new LinkedList();

 

//전체 데이터 

        //String query = "select datecreated as mdatecreated, pm10Value, pm25Value from dust_airkorea where gps_id='JongRo-Gu'";

        //String query = "select a.datecreated as mdatecreated, b.pm25Value as Drnpm25Value, a.pm10Value as pm10Value, a.pm25Value as pm25Value from dust_airkorea a, dust_drone b where b.gps_id='JongRo-Gu'";


//시간대별  평균값을 산출

String query = "select DATE_FORMAT(a.datecreated, '%Y-%m-%d %H:00:00') mdatecreated, avg(a.pm10Value) as Drnpm10Value, avg(a.pm25Value) as Drnpm25Value , avg(b.pm10Value) as pm10Value, avg(b.pm25Value) as pm25Value from dust_drone a inner join dust_airkorea b where a.gps_id=b.gps_id and DATE_FORMAT(a.datecreated,'%Y-%m-%d %H:00')=DATE_FORMAT(b.datecreated,'%Y-%m-%d %H:00') group by mdatecreated";



PreparedStatement pstm = con.prepareStatement(query);

        rs = pstm.executeQuery(query);

        

        //ajax에 반환할 JSON 생성

        JSONObject responseObj = new JSONObject();

        JSONObject lineObj = null;

        

DecimalFormat f1 = new DecimalFormat("");


    while (rs.next()) {

            String mdatecreated = rs.getString("mdatecreated");

            float Drnpm10Value = rs.getFloat("Drnpm10Value");

            float Drnpm25Value = rs.getFloat("Drnpm25Value");

            float pm10Value = rs.getFloat("pm10Value");

            float pm25Value = rs.getFloat("pm25Value");

      lineObj = new JSONObject();

            lineObj.put("mdatecreated", mdatecreated);

            lineObj.put("Drnpm10Value", (int)Drnpm10Value);

            lineObj.put("Drnpm25Value", (int)Drnpm25Value);

            lineObj.put("pm10Value", (int)pm10Value);

            lineObj.put("pm25Value", (int)pm25Value);

            dustlist.add(lineObj);

        } 


    responseObj.put("dustlist", dustlist);

        out.print(responseObj.toString());

 

    } catch (Exception e) {

        e.printStackTrace();

    } finally {

        if (con != null) {

            try {

                con.close();

            } catch (Exception e) {

                e.printStackTrace();

            }

        }

 

    }

%>






[ 구글차트 그리기 - view6.html ]


<!DOCTYPE html>

<html>

  <head>

    <meta charset="utf-8">

    <title>Line_Controls_Chart</title>


    <meta http-equiv="refresh" content="300">

    <!-- jQuery -->

    <script src="https://code.jquery.com/jquery.min.js"></script>

    <!-- google charts -->

    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

    <script src="https://www.google.com/jsapi"></script>

  <body>

  <font size=5><center>서울시 종로구 미세먼지 농도 측정 그래프</center></font><br>

 

   <div style="text-align:right; width: 1100px;">

    <font size=1>

    <font color=blue>Drnpm10Value</font>, <font color=red>Drnpm25Value</font>: 쿼드콥터 미세먼지 센서로 측정된 수치, <font color=orange>pm10Value</font>, <font color=green>pm25Value</font> : AirKorea에서 제공된 미세먼지 수치

    </font>

    </div>


    <div style="text-align:right; width: 908px;">

        <font size=2>update : <span id="clock"></span></font><br>

    </div>


    <div id="Line_Controls_Chart">

        <!-- 라인 차트 생성할 영역 -->

        <div id="lineChartArea" style="padding:0px 20px 0px 0px;"></div>

        <!-- 컨트롤바를 생성할 영역 -->

  <div id="controlsArea" style="padding:0px 20px 0px 0px;"></div>

    </div>


  </body>


  <script>

  function printTime() {

    var clock = document.getElementById("clock"); // 출력할 장소 선택

    var now = new Date();                         // 현재시간

    var nowTime = now.getFullYear() + "." + (now.getMonth()+1) + "." + now.getDate() + " " + now.getHours() + ":" + now.getMinutes() + ":" + now.getSeconds();

    clock.innerHTML = nowTime;      // 현재시간을 출력

  }

  window.onload = function() {         // 페이지가 로딩되면 실행

    printTime();

  }

  </script>

  </head>


  <script>

  var chartDrowFun = {


    chartDrow : function(){

var queryObject = "";

    var queryObjectLen = "";

    $.ajax({

           type : 'POST',

           url : 'data_read4.jsp',

           dataType : 'json',

           success : function(data) {

               queryObject = eval('(' + JSON.stringify(data,null, 2) + ')');

               queryObjectLen = queryObject.dustlist.length;

               //alert('Total lines : ' + queryObjectLen + 'EA');

           },

           error : function(xhr, type) {

       //alert('server error occoured')

      alert('server msg : ' + xhr.status)

           }

    });


        //날짜형식 변경하고 싶으시면 이 부분 수정하세요.

        //var chartDateformat = 'yyyy년MM월dd일';

        var chartDateformat = 'M.dd. HH:MM';

        //라인차트의 라인 수

        var chartLineCount    = 10;

        //컨트롤러 바 차트의 라인 수

        var controlLineCount = 10;



        function drawDashboard() {


          var data = new google.visualization.DataTable();

          data.addColumn('datetime', 'mdatecreated');

          data.addColumn('number', 'Drnpm10Value');

          data.addColumn('number', 'Drnpm25Value');

          data.addColumn('number', 'pm10Value');

          data.addColumn('number', 'pm25Value');

 

          //alert('data생성-------------------' + queryObjectLen);

          for (var i = 0; i < queryObjectLen; i++) {

              var mdatecreated = queryObject.dustlist[i].mdatecreated;

              var Drnpm10Value = queryObject.dustlist[i].Drnpm10Value;

              var Drnpm25Value = queryObject.dustlist[i].Drnpm25Value;

              var pm10Value = queryObject.dustlist[i].pm10Value;

              var pm25Value = queryObject.dustlist[i].pm25Value;

      //alert(mdatecreated + ' ' + Drnpm10Value + ' ' + Drnpm25Value + ' ' + pm10Value + ' ' + pm25Value);

              data.addRows([ [ new Date(mdatecreated), Drnpm10Value, Drnpm25Value, pm10Value, pm25Value ] ]);

          }


          var chart = new google.visualization.ChartWrapper({

              chartType   : 'LineChart',

              containerId : 'lineChartArea', //라인 차트 생성할 영역

              options     : {

                  isStacked   : 'percent',

                  focusTarget : 'category',

                  height      : 500,

                  width       : '100%',

                  legend      : { position: "top", textStyle: {fontSize: 13}},

                  pointSize   : 5,

                  tooltip     : {textStyle : {fontSize:12}, showColorCode : true,trigger: 'both'},

                  hAxis : {format: chartDateformat, gridlines:{count:chartLineCount,units: {

                      years : {format: ['yyyy년']},

                      months: {format: ['MM월']},

                      days  : {format: ['dd일']},

                      hours : {format: ['HH시']}}

                      },textStyle: {fontSize:12}},

                 vAxis : {minValue: 50,viewWindow:{min:0},gridlines:{count:-1},textStyle:{fontSize:12}},

                 animation  : {startup: true,duration: 1000,easing: 'in' },

                 annotations: {pattern: chartDateformat,

                      textStyle: {

                      fontSize: 15,

                      bold: true,

                      italic: true,

                      color: '#871b47',

                      auraColor: '#d799ae',

                      opacity: 0.8,

                      pattern: chartDateformat

                      }

                  }

              }

          });


          var control = new google.visualization.ControlWrapper({

              controlType: 'ChartRangeFilter',

              containerId: 'controlsArea',  //control bar를 생성할 영역

              options: {

                  ui:{

                        chartType: 'LineChart',

                        chartOptions: {

                        chartArea: {'width': '60%','height' : 80},

                          hAxis: {'baselineColor': 'none', format: chartDateformat, textStyle: {fontSize:12},

                        gridlines:{count:controlLineCount,units: {

                             years : {format: ['yyyy년']},

                             months: {format: ['MM월']},

                             days  : {format: ['dd일']},

                             hours : {format: ['HH시']}}

                        }}

                      }

                  },

                  filterColumnIndex: 0

              }

          });


          var date_formatter = new google.visualization.DateFormat({ pattern: chartDateformat});

          date_formatter.format(data, 0);


          var dashboard = new google.visualization.Dashboard(document.getElementById('Line_Controls_Chart'));

          window.addEventListener('resize', function() { dashboard.draw(data); }, false); //화면 크기에 따라 그래프 크기 변경

          dashboard.bind([control], [chart]);

          dashboard.draw(data);


        }

        google.charts.setOnLoadCallback(drawDashboard);


      }

    }


$(document).ready(function(){

    google.charts.load('current', {

       'packages':['line','controls']

    });

    chartDrowFun.chartDrow(); //chartDrow() 실행

});

  </script>

  <font size=1>

  *공공데이터 참조사이트 : http://tech.leotek.co.kr/2017/04/11/공공데이터포털-한국환경공단-대기오염-api/<br>

  *AirKorea API : http://openapi.airkorea.or.kr/openapi/services/rest/ArpltnInforInqireSvc/getMsrstnAcctoRltmMesureDnsty?stationName=종로구&dataTerm=month&pageNo=1&numOfRows=10&ServiceKey=u4Q%2FF%2BzFS1PHRIhVj2cJcxGP8J%2B5vOxCbaO039frcCGDEuD2km6rhbR2wZrwBrZtlLu2Z%2FbsqMHDVVGHwkq8ow%3D%3D&ver=1.3%22

  </font>

</html>







[ 결과화면 ]





[해당파일]

https://github.com/e3jake/DustProject


[참조:http://private.tistory.com/66]