Google Chart using JSP, JSON, JQuery, MySql

GoogleChart with JSP

Hello friends...

In this article I am going to write about how to display the google chart using JSP with MySql as data source. For this example you will have to download org.json.jar and basic knowledge of JSON,JQuery AJAX, java.
Here I have created two files. One html file googlechart.html where onload I am displaying the google chart
and second getdata.jsp . getdata.jsp file is as the source file where I am fetching the data from mysql and parsing into JSON. This file is getting called in googlechart.html file under
$.ajax({ url:""});
Change the chart type under
var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
to test for different charts...like BarChart,ColumnChart...etc..
For some of the chart it will needed more rows and columns so change the code as per the chart type.

But I will suggest you to visit google docs for any clarification....
https://developers.google.com/chart/interactive/docs/gallery
Now, Lets get into the code...

1. googlechart.html

<!DOCTYPE html>
<html>
<head>
<title>Google Chart with jsp Mysql Json</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">

var queryObject="";

var queryObjectLen="";

$.ajax({

type : 'POST',

url : 'getdata.jsp',

dataType:'json',

success : function(data) {

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

queryObjectLen = queryObject.empdetails.length;

error : function(xhr, type) {

alert('server error occoured')

}

});

google.load("visualization", "1", {packages:["corechart"]});

google.setOnLoadCallback(drawChart);

function drawChart() {

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

data.addColumn('string', 'name');

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

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

{

var name = queryObject.empdetails[i].name;

var empid = queryObject.empdetails[i].empid;

data.addRows([

[name,parseInt(empid)]

]);

}

var options = {

title: 'Employee Information',

};

var chart = new google.visualization.PieChart(document.getElementById('chart_div'));

chart.draw(data,options);

}

</script>
</head>
<body>
<div id="chart_div"></div>
</body>
</html>

2.getdata.jsp

<%@page import="java.sql.*" %>
<%@page import="java.util.*" %>
<%@page import="org.json.JSONObject" %>

<%
Connection con= null;
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb","root","password");

ResultSet rs = null;
List empdetails = new LinkedList();
JSONObject responseObj = new JSONObject();

String query = "SELECT id,name from employee";
PreparedStatement pstm= con.prepareStatement(query);

rs = pstm.executeQuery();
JSONObject empObj = null;

while (rs.next()) {
String name = rs.getString("name");
int empid = rs.getInt("id");
empObj = new JSONObject();
empObj.put("name", name);
empObj.put("empid", empid);
empdetails.add(empObj);
}
responseObj.put("empdetails", empdetails);
out.print(responseObj.toString());
}
catch(Exception e){
e.printStackTrace();
}finally{
if(con!= null){
try{
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
%>

Create table with name employee...

CREATE TABLE IF NOT EXISTS `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
`date` date DEFAULT NULL,
`working_days` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

Take reference from https://developers.google.com/chart/interactive/docs/gallery and play with JSP...
Change the code as per your needs....

Thank you :)

Wednesday 27 November 2013

1. googlechart.html

2.getdata.jsp

Create table with name employee...