구글차트 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]
'Tech Story > IoT' 카테고리의 다른 글
| 라즈베리파이를 이용한 구글어시스턴트스피커 (16) | 2020.12.19 |
|---|---|
| 파이썬으로 공공데이터 API XML 파싱후 DB저장하기 (0) | 2018.06.18 |
| 웹으로 DB에 데이터 저장하기 (0) | 2018.06.18 |
| MySQL 테이블 생성-기초 (0) | 2018.06.18 |
| MySQL 접속테스트 (1) | 2018.06.18 |