다연이네

[days15] 구글차트와 외부차트 본문

JSP

[days15] 구글차트와 외부차트

　다연　 2021. 1. 15. 13:07
반응형

구글차트

- 원형차트

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
 <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">

      // Load the Visualization API and the corechart package.
      google.charts.load('current', {'packages':['corechart']});

      // Set a callback to run when the Google Visualization API is loaded.
      google.charts.setOnLoadCallback(drawChart);

      // Callback that creates and populates a data table,
      // instantiates the pie chart, passes in the data and
      // draws it.
      function drawChart() {

        // Create the data table.
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Topping');
        data.addColumn('number', 'Slices');
        data.addRows([
          ['Mushrooms', 3],
          ['Onions', 1],
          ['Olives', 1],
          ['Zucchini', 1],
          ['Pepperoni', 2]
        ]);

        // Set chart options
        var options = {'title':'How Much Pizza I Ate Last Night - TEST',
                       'width':500,
                       'height':400};

        // Instantiate and draw our chart, passing in some options.
        var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
</head>
<body>
<!-- 
   Google Chart
 -->   
 <!--Div that will hold the pie chart-->
 <div id="chart_div"></div>
</body>
</html>

-원형차트2

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

</head>
<body>
<div id="piechart"></div>

<script type="text/javascript">
// Load google charts
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);

// Draw the chart and set the chart values
function drawChart() {
  var data = google.visualization.arrayToDataTable(
		  [
			  ["DNAME","SUM_SAL"], //컬럼명
			  ["ACCOUNTING",8750], //부서명(10) 총급여합
			  ["OPERATIONS",0],  	 //20            총급여합 
			  ["RESEARCH",6775],
			  ["SALES",9400]
          ]);

  // Optional; add a title and set the width and height of the chart
  var options = {'title':'Total Sal by Department', 'width':550, 'height':400};

  // Display the chart inside the <div> element with id="piechart"
  var chart = new google.visualization.PieChart(document.getElementById('piechart'));
  chart.draw(data, options);
}
</script>
</body>
</html>

 

-가로막대형 차트

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="">
 <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

<style>
</style>
<script type="text/javascript">
    google.charts.load("current", {packages:["corechart"]});
    google.charts.setOnLoadCallback(drawChart);
    function drawChart() {
    	/* 
      var data = google.visualization.arrayToDataTable([
        ["Element", "Density", { role: "style" } ],
        ["Copper", 8.94, "#b87333"],
        ["Silver", 10.49, "silver"],
        ["Gold", 19.30, "gold"],
        ["Platinum", 21.45, "color: #e5e4e2"]
      ]);
 		*/
 		
 		
 		  var data = google.visualization.arrayToDataTable([
 		        ["Element", "Density" ],
 		        ["Copper", 8.94],
 		        ["Silver", 10.49],
 		        ["Gold", 19.30],
 		        ["Platinum", 21.45]
 		      ]);
 		
 		
      var view = new google.visualization.DataView(data);
      view.setColumns([0, 1]);

      var options = {
        title: "Density of Precious Metals, in g/cm^3",
        width: 600,
        height: 400,
        bar: {groupWidth: "95%"},
        legend: { position: "none" },
      };
      var chart = new google.visualization.BarChart(document.getElementById("barchart_values"));
      chart.draw(view, options);
  }
  </script>
</head>
<body>
<div id="barchart_values" style="width: 900px; height: 300px;"></div>
</body>
</html>

 

 

원형차트를 통해 db에서 데이터를 가져와 출력해보자

부서별 사원의 급여 합 출력하는 코딩

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
</head>
<body>
<div id="piechart"></div>

<script type="text/javascript">
// Load google charts
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);

// Draw the chart and set the chart values
function drawChart() {
	
	$.ajax({
			url:"ex04_json.jsp" //MVC패턴이라면 .do가 될 것  (C->H-> S->D)
			,dataType:"json"
			,type:"get"
			//,data:params 
			,cache:false 
			,success:function(data, textStatus, jqXHR){
				//alert(data);
				var data2 = google.visualization.arrayToDataTable( data  );
				  // Optional; add a title and set the width and height of the chart
				  var options = {'title':'Total Sal by Department', 'width':550, 'height':400};
				  // Display the chart inside the <div> element with id="piechart"
				  var chart = new google.visualization.PieChart(document.getElementById('piechart'));
				  chart.draw(data2, options);
			}
	        ,error:function(){
	        	alert("에러");
	        }
		}).responseText;//ajax
	//데이터를 가지고 온 후에 처리해야하기 떄문에 코딩을 success안에 적어야 한다. (중요)
	//가져오고 난 후에 지도를 그려라
	//밑에 있으면 아직 안 가져왔을때 지도 그리려 함 (비동기적, 스레드처럼 각자 실행되기 때문)
  
}
</script>
</body>
</html>

ex04_json.jsp

<%@page import="net.sf.json.JSONArray"%>
<%@page import="net.sf.json.JSONObject"%>
<%@page import="com.util.JdbcUtil"%>
<%@page import="com.util.ConnectionProvider"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@page trimDirectiveWhitespaces="true" %>
<%@ page pageEncoding="UTF-8" contentType="application/json; charset=UTF-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<%

   Connection con = null;
   PreparedStatement pstmt = null;
   ResultSet rs = null;
   
   String sql = "select d.deptno , d.dname , nvl( sum( sal ) , 0 ) sum_sal "
                + " from emp e right join dept d on e.deptno = d.deptno "
                + " group by d.deptno, d.dname "; 
   
   //JSONObject result = new JSONObject();
   
    JSONArray data = new JSONArray();
    
    JSONArray d = new JSONArray();
       d.add("DNAME");
       d.add("SUM_SAL");    
       //d.add("DEPTNO");
    data.add(d);
    //  [    ["DNAME","SUM_SAL"]  ]
    
   try{      
      con = ConnectionProvider.getConnection();
      pstmt = con.prepareStatement(sql);
      rs = pstmt.executeQuery(); 
      
      while( rs.next() ){
         //int deptno = rs.getInt("deptno"); 
         String dname = rs.getString("dname");
         double sum_sal  = rs.getDouble("sum_sal");
         
         d = new JSONArray();
         //d.add(deptno);
             d.add( dname ); 
             d.add(sum_sal); 
          data.add( d );
         
      } // while            
      
       //result.put("result", data); 
      
   }catch(Exception e){
      e.printStackTrace();
   }finally{
      JdbcUtil.close(rs);
      JdbcUtil.close(pstmt);
      JdbcUtil.close(con);
   } // try
%>
<%=  data %>

ex04_json.jsp를 바로 실행시키면 다음과 같이 출력된다.

 

 

버튼을 클릭하면 3초 후에 연봉이 높은 사원 10명을 출력하는 꺾은선 그래프를 나타내도록 해보자.

3초동안은 ajax를 사용해 로딩 gif를 표시해보자.

 

 

 

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
	
	<link rel="stylesheet" type="text/css" href="/jspPro/dhtmlxChart_v51_std/codebase/fonts/font_roboto/roboto.css"/>
	<link rel="stylesheet" type="text/css" href="/jspPro/dhtmlxChart_v51_std/codebase/dhtmlxchart.css"/>
	<script src="/jspPro/dhtmlxChart_v51_std/codebase/dhtmlxchart.js"></script>
	
	
<style>
  .container{
     position: relative;
     width:450px;
     height:350px;
     border:dotted 1px #555;
     padding:10px;
     marginn-top:10px;
  }
  
  .loader{
    position: absolute;
    top:0;
    left:0;
    width: 100%;
    height:100%;
    background: rgba(255,255,255,0) url('ajax-loader.gif') no-repeat 50% 50%;
  }
</style>

</head>
<body>
<!-- 
https://dhtmlx.com/  외부 차트 사이트
http://ajaxload.info/  ajax 로딩중임을 나타내는 이미지(gif) 생성 사이트
 -->
 <!-- 
 ex05_json.jsp
 emp테이블의 각 사원의 급여(sal)를 json [{},{},{}]
 ex05.jsp 차트 출력
  -->
  
  <a href="#" class="load">Ajax Chart</a>
<br>
<div id="chartbox" 
		class="container"
		style="width:600px;height:250px;border:1px solid #c0c0c0;">
		
</div>


<script>
   $(document).ready(function (){     
		var myLineChart;
	     	  
		   myLineChart =  new dhtmlXChart({
				view:"line",
				container:"chartbox",
				value:"#sal#",
				label:"#sal#",
				item:{
					borderColor: "#1293f8",
					color: "#ffffff"
				},
				line:{
					color:"pink",
					width:3
				},
				xAxis:{
					template:"'#ename#" //문자열일경우 앞에 홑따옴표
				},
				offset:0,
				yAxis:{
					start:0,
					end:10000,
					step:1000,
					template:function(obj){
						return (obj%20?"":obj)
					}
				}
			});
		   
		   var res = {
				   loader : $("<div />", {"class":"loader"})
				   , container: $(".container")
		   };
		   
		   $("a.load").on("click", function(event) {
			   	  //ajax - url : ex05_json.jsp
			   	  //차트 그리기
			   	  $.ajax({
	   			url:"ex05_json.jsp" 
	   			,dataType:"json"
	   			,type:"get"
	   			,cache:false 
				,beforeSend:function(xhr){ //일하러가기 직전
	   				// ajax-loader.gif 이미지 보이기
	   				res.container.empty().append(res.loader); //있는 내용 지우고 이미지 보이게
	   			}
	   			,success:function(data){ 
	   			   // ajax-loader.gif 이미지 삭제
	   				res.container.find(res.loader).remove();
	   			   
	   				myLineChart.parse(data.emp,"json");
	   			   //myBarChart1.attachEvent("onItemClick",function(empno){alert(empno)});
	   			}
	   			,error: function() {
	   			 // ajax-loader.gif 이미지 삭제
	   			 res.container.find(res.loader).remove();
	   				alert("error");
				}
	   		});//ajax
			   
	   }); //click
   });//ready
</script>
</body>
</html>
<%@page import="com.util.ConnectionProvider"%>
<%@page import="net.sf.json.JSONArray"%>
<%@page import="net.sf.json.JSONObject"%>
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@page import="java.util.Calendar"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="com.util.DBConn"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html; charset=UTF-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
  Thread.sleep(3000);   // 처리 시간 3초 걸렸다..

  Connection conn = ConnectionProvider.getConnection();
  Statement  stmt = conn.createStatement();
  
  String sql ="with temp as(select empno, ename, sal , rank() over(order by sal desc )   r from emp ) select * from temp where r <=10";
  
  ResultSet rs =  stmt.executeQuery(sql);
  
  Calendar cal =  Calendar.getInstance();  
  String now = String.format("%tT",cal  );
  
  JSONObject jobj = new JSONObject();
  JSONArray  jarr = new JSONArray();
  // jarr 
  while(rs.next()){
     int empno = rs.getInt(1);
     String ename = rs.getString(2);
     int sal = rs.getInt(3);
     int rank = rs.getInt(4);
     
     JSONObject empobj = new JSONObject();
        empobj.put("empno", empno);
        empobj.put("ename", ename);
        empobj.put("sal", sal);
        empobj.put("rank", rank);
     
     jarr.add(empobj);
     
  }
  
  jobj.put("emp", jarr);
  conn.close();
%>
<%= jobj %>

 

ex05_json.jsp를 컴파일시키면 다음과 같다.

반응형
저작자표시 비영리

'JSP' 카테고리의 다른 글

[days15] autocomplete  (0) 2021.01.15
[days15] 회원가입 ID 중복체크  (0) 2021.01.15
[days14] json 라이브러리 사용 (json-lib-2.4-jdk15.jar)  (0) 2021.01.14
[days14] JSON 개요  (0) 2021.01.14
[days13] AJAX  (0) 2021.01.13
'JSP' Related Articles more
Comments