Loading
Creating Charts in Java using Database
FusionCharts JSP Wrapper can be downloaded from here.
In addition to specifying the chart data (or the URL for the file in which the chart data is stored) directly in the JSON/XML code, you can also fetch data for the chart from a database.
This section showcases how you can do this using the FusionCharts JSP wrapper.
In this section, you will be shown how you can:
-
Create a simple column 2D chart by fetching data from a database
-
Create a drill-down chart by fetching data from a database
Although you can create all kinds of charts using this method, in this section we will focus on only these two examples.
Creating a Column 2D Chart
Assume that you have a fusioncharts_jspsample database with the Country table that stores the population of all countries in the world. Using the data in this database, let’s say you want to plot a column 2D chart showing the top ten most populous countries in the world.
You can download this database from the MYSQL website or refer to the sample database available here.
The resultant column 2D chart looks like this:
FusionCharts will load here..
The data structure to create this chart, by fetching chart data from a database, is given below:
<%
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %>
<%@page import="java.util.*" %>
<%@page import="com.google.gson.*" %>
<%
/*
The following 4 code lines contain the database connection information.
Alternatively, you can move these code lines to a separate file and
include the file here. You can also modify this code based on your
database connection.
*/
String hostdb = "localhost:3306"; // MySQl host
String userdb = "root"; // MySQL username
String passdb = ""; // MySQL password
String namedb = "fusioncharts_jspsample"; // MySQL database name
// Establish a connection to the database
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Connection con = DriverManager.getConnection("jdbc:mysql://" + hostdb + "/" + namedb , userdb , passdb);
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Creating Charts with Data from a Database - fusioncharts.com</title>
<!-- Step 1: Include the `fusioncharts.js` file. This file is needed to
render the chart. Ensure that the path to this JS file is correct.
Otherwise, it may lead to JavaScript errors.
-->
<script src="../scripts/fusioncharts.js"></script>
</head>
<body>
<div id="chart"></div>
<!-- Step 2: Include the `FusionCharts.java` file as a package in your
project.
Step 3:Include the package in the file where you want to show
FusionCharts.
-->
<%@page import="fusioncharts.FusionCharts" %>
<!--
Step 4: Create a chart object using the FusionCharts JAVA class
constructor. Syntax for the constructor:
`FusionCharts("type of chart", "unique chart id", "width of chart",
"height of chart", "div id to render the chart",
"data format", "data source")`
-->
<%
/*
google-gson
Gson is a Java library facilitating conversion of Java objects
into their JSON representation and
JSON strings into their equivalant Java objects.
Gson can also work with arbitrary Java objects
including the pre-existing ones that
you may not have the source-code for.
Read the note below this code for more details on the google-gson library.
*/
Gson gson = new Gson();
// Form the SQL query that returns the top 10 most populous countries
String sql="SELECT Name, Population FROM Country ORDER BY Population DESC LIMIT 10";
// Execute the query
PreparedStatement pt=con.prepareStatement(sql);
ResultSet rs=pt.executeQuery();
// The 'chartobj' map object holds the chart attributes and data.
Map<String, String> chartobj = new HashMap<String, String>();
chartobj.put("caption", "Top 10 most populous countries");
chartobj.put("paletteColors", "#0075c2");
chartobj.put("bgColor", "#ffffff");
chartobj.put("borderAlpha", "20");
chartobj.put("canvasBorderAlpha", "0");
chartobj.put("usePlotGradientColor", "0");
chartobj.put("plotBorderAlpha", "10");
chartobj.put("showXAxisLine", "1");
chartobj.put("xAxisLineColor", "#999999");
chartobj.put("showValues", "0");
chartobj.put("divlineColor", "#999999");
chartobj.put("divLineIsDashed", "1");
chartobj.put("showAlternateHGridColor", "0");
// Push the data into the array using map object.
ArrayList arrData = new ArrayList();
while(rs.next()) {
Map<String, String> lv = new HashMap<String, String>();
lv.put("label", rs.getString("Name"));
lv.put("value", rs.getString("Population"));
arrData.add(lv);
}
//close the connection.
rs.close();
//create 'dataMap' map object to make a complete FC datasource.
Map<String, String> dataMap = new LinkedHashMap<String, String>();
/*
gson.toJson() the data to retrieve the string containing the
JSON representation of the data in the array.
*/
dataMap.put("chart", gson.toJson(chartobj));
dataMap.put("data", gson.toJson(arrData));
FusionCharts columnChart= new FusionCharts(
"column2d", //type of chart
"chart1", //unique chart ID
"500","300", //width and height of the chart
"chart", //div ID of the chart container
"json", //data format
gson.toJson(dataMap) //data source
);
%>
<!-- Step 5: Render the chart -->
<%=columnChart.render()%>
</body>
</html>The code above uses the google-gson library.
Gson is a Java library that can be used to convert:
- Java objects into their corresponding JSON representation
- JSON strings into their corresponding Java objects.
Gson allows working with arbitrary Java objects that include pre-existing objects for which you may not have the source code.
The google-gsn library :
- Provides the toJson() and fromJson() methods for converting Java objects to JSON and the other way round
- Allows conversion of the already existing unmodifiable objects to and from JSON
- Supports Java Generics extensively
- Allows custom representations of objects
- Supports arbitrarily complex objects (with deep inheritance hierarchies and extensive use of generic types)
The code above imports the com.google.gson package that enables the conversion from JSON to Java and Java to JSON.
Click here for more information on google-gson.
Creating a Drill-down Chart
Let’s say that you now need to plot a chart that shows the top 10 most populous countries and the top 10 most populous cities in these countries. Assume that the fusioncharts_jspsample database also includes the City table that stores the population of all cities in all countries.
Drill-down charts are used to show data at different levels of a hierarchy.
Therefore, the resultant drill-down chart, according to the use-case, looks like this:
FusionCharts will load here..
To render the above chart, we need a JSP files —country.jsp. The country.jsp file creates a drill-down column 2D chart showing the most populous countries and the most populous cities for countries that will be selected in the parent chart.
The data structure that should go into the country.jsp file is given below:
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %>
<%@page import="java.util.*" %>
<%@page import="com.google.gson.*" %>
<%
/*
The following four code lines contain the database connection information.
Alternatively, you can move these code lines to a separate file and
include the file here. You can also modify this code based on your
database connection.
*/
String hostdb = "localhost:3306"; // MySQl host
String userdb = "root"; // MySQL username
String passdb = ""; // MySQL password
String namedb = "fusioncharts_jspsample"; // MySQL database name
// Establish a connection to the database
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Connection con = DriverManager.getConnection("jdbc:mysql://" + hostdb + "/" + namedb , userdb , passdb);
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Creating Charts with Data from a Database - fusioncharts.com</title>
<!-- Step 1: Include the `fusioncharts.js` file. This file is needed to
render the chart. Ensure that the path to this JS file is correct.
Otherwise, it may lead to JavaScript errors.
-->
<script src="../scripts/fusioncharts.js"></script>
</head>
<body>
<div id="chart"></div>
<!-- Step 2: Include the `FusionCharts.java` file as a package in your
project.
Step 3:Include the package in the file where you want to show
FusionCharts.
-->
<%@page import="fusioncharts.FusionCharts" %>
<!--
Step 4: Create a chart object using the FusionCharts JAVA class
constructor. Syntax for the constructor:
`FusionCharts("type of chart", "unique chart id", "width of chart",
"height of chart", "div id to render the chart",
"data format", "data source")`
-->
<%
/*
google-gson
Gson is a Java library facilitating conversion of Java objects
into their JSON representation and
JSON strings into their equivalant Java objects.
Gson can also work with arbitrary Java objects
including the pre-existing ones that
you may not have the source-code for.
Read the note below this code for more details on the google-gson library.
*/
Gson gson = new Gson();
// Form the SQL query that returns the top 10 most populous countries
String sqlQueryCountry="SELECT * FROM Country ORDER BY Population DESC LIMIT 10";
// Prepare the query statement
PreparedStatement ptCountry=con.prepareStatement(sqlQueryCountry);
// Prepare the query statement
ResultSet rsCountry=ptCountry.executeQuery();
// The 'chart' map object holds the chart attributes and data.
Map<String, String> chart = new HashMap<String, String>();
chart.put("caption", "Top 10 Most Populous Countries");
chart.put("paletteColors", "#0075c2");
chart.put("bgColor", "#ffffff");
chart.put("usePlotGradientColor", "0");
chart.put("plotBorderAlpha", "10");
chart.put("showXAxisLine", "1");
chart.put("xAxisLineColor", "#999999");
chart.put("showValues", "0");
chart.put("divlineColor", "#999999");
chart.put("divLineIsDashed", "1");
chart.put("showAlternateHGridColor", "0");
chart.put("showBorder", "0");
chart.put("baseFont", "Helvetica Neue,Arial");
chart.put("captionFontSize", "14");
// Push the data into the array using map object.
ArrayList data = new ArrayList();
/*
`linkeddata` array: It contains data for individual linked
items. The links should be defined in the format
`newchart-dataformat-datasource`.
*/
ArrayList linkeddata = new ArrayList();
while(rsCountry.next()) {
/*
The `link` attribute: It defines the unique id of the linked
data inside the `linkeddata` array. Using this attribute
FusionCharts finds out the corresponding data for each linked
item.
For the data string method, dataformat takes JSON and
datasource takes the value of the unique identifier that
refers to the data embedded inside the linkeddata array in
the parent data source. So the Country drill-down links will
get generated as newchart-json-IND, newchart-json-CHN ...
*/
Map<String, String> lv = new HashMap<String, String>();
lv.put("label", rsCountry.getString("Name"));
lv.put("value", rsCountry.getString("Population"));
lv.put("link", "newchart-json-" + rsCountry.getString("Code"));
data.add(lv);
// Create the linkedDataObj for cities drilldown
Map<String, String> linkedDataObj = new HashMap<String, String>();
// Inititate the linkedDataObj for cities drilldown
linkedDataObj.put("id", rsCountry.getString("Code"));
// The 'linkedChartAttribute' map object holds the chart attributes .
Map<String, String> linkedChartAttribute = new HashMap<String, String>();
linkedChartAttribute.put("caption" , "Top 10 Most Populous Cities - " + rsCountry.getString("Name") );
linkedChartAttribute.put("paletteColors" , "#0075c2");
linkedChartAttribute.put("bgColor" , "#ffffff");
linkedChartAttribute.put("borderAlpha", "20");
linkedChartAttribute.put("canvasBorderAlpha", "0");
linkedChartAttribute.put("usePlotGradientColor", "0");
linkedChartAttribute.put("plotBorderAlpha", "10");
linkedChartAttribute.put("showXAxisLine", "1");
linkedChartAttribute.put("xAxisLineColor" , "#999999");
linkedChartAttribute.put("showValues", "0");
linkedChartAttribute.put("divlineColor" , "#999999");
linkedChartAttribute.put("divLineIsDashed" , "1");
linkedChartAttribute.put("showAlternateHGridColor" , "0");
// Convert the data in the `City` model into a format that can be consumed by FusionCharts.
ArrayList linkedChartData = new ArrayList();
// Filtering the data base on the Country Code
// Form the SQL query that returns the top 10 most populous countries
String sqlQueryCity="SELECT Name, Population FROM City WHERE CountryCode = ? ORDER BY Population DESC LIMIT 10";
// Prepare the query statement.
PreparedStatement ptCity=con.prepareStatement(sqlQueryCity);
ptCity.setString(1, rsCountry.getString("Code"));
// Execute the query.
ResultSet rsCity=ptCity.executeQuery();
while(rsCity.next()) {
Map<String, String> arrDara = new HashMap<String, String>();
arrDara.put("label", rsCity.getString("Name"));
arrDara.put("value", rsCity.getString("Population"));
linkedChartData.add(arrDara);
}
//closing the connection.
rsCity.close();
/* create a 'linkedchart' map object to make a FC's
linkedchart structure.
*/
Map<String, String> linkedchart = new HashMap<String, String>();
/*
gson.toJson() the data to retrieve the string containing the
JSON representation of the data in the array.
*/
linkedchart.put("chart", gson.toJson(linkedChartAttribute));
linkedchart.put("data", gson.toJson(linkedChartData));
linkedDataObj.put("linkedchart", gson.toJson(linkedchart));
linkeddata.add(linkedDataObj);
} //end of while loop
//closing the connection.
rsCountry.close();
//create 'dataMap' map object to make a complete FC datasource.
Map<String, String> dataMap = new LinkedHashMap<String, String>();
dataMap.put("chart", gson.toJson(chart));
dataMap.put("data", gson.toJson(data));
dataMap.put("linkeddata", gson.toJson(linkeddata));
FusionCharts columnChart= new FusionCharts(
"column2d", //type of chart
"chart1", //unique chart ID
"500","300", //width and height of the chart
"chart", //div ID of the chart container
"json", //data format
gson.toJson(dataMap) //data source
);
%>
<!-- Step 5: Render the chart -->
<%=columnChart.render()%>
</body>
</html>