|
In this section, we'll show you how to use FusionCharts and JSP to plot
charts from data contained in a database. We'll create a pie chart to
show "Production by Factory" using:
- dataXML method first.
- Thereafter, we'll convert this chart to use dataURL
method.
For the sake of ease, we'll use MySQL Database. The database is present
in Download Package > Code > JSP > DB
folder. You can, however, use any database with FusionCharts including
MySQL, MS SQL, Oracle. Database creation script for MySQL is also present
in the same folder.
Before you go further with this page, we recommend you to please
see the previous section "Basic Examples" as we start off from
concepts explained in that page.
The code examples contained in this page are present
in Download Package > Code > JSP > DBExample
folder.
The database scripts are present in Download
Package > Code > JSP > DB. |
|
Before we code the JSP pages to retrieve data,
let's quickly have a look at the database structure. |
 |
The database contains just 2 tables:
- Factory_Master: To store the name
and id of each factory
- Factory_Output: To store the number
of units produced by each factory for a given date.
For demonstration, we've fed some dummy data in the database. Let's now
shift our attention to the JSP page that will interact with the database,
fetch data and then render a chart.
- The database configuration is present in the file Download Package > Code > JSP > META-INF > context.xml file. Here we need to specify the database name, user name and password to access the database. We have used the MySQL database for our examples. We assume that you have created the database with the name factorydb, username root with no password.
- Once this is done, we need to create the required tables. The sql script "FactoryDBCreation.sql" present in the folder Download Package > Code > JSP > DB will create the database with two tables and sample data. Note that these scripts will not create foreign key relationships. You would have to manually alter the table to create the relationship, if you think necessary.
- Once this is done, we need to create the tables required for the UTF8 examples. The required sql script "UTFExampleTablesCreation.sql" is present in the Download Package > Code > JSP > DB folder. You could run this script in your mysql - this will alter the database to use UTF8 as default character set, create the Japanese_Factory_Master and French_Factory_Master tables and insert sample data into them.
|
|
The JSP page for dataXML
method example is named as BasicDBExample.jsp
(in DBExample folder). It contains the following
code: |
<%@ include file="../Includes/DBConn.jsp"%><%@ page import="java.sql.Statement"%>
<%@ page import="java.sql.ResultSet"%>
<%@ page import="java.sql.Date"%>
<HTML>
<HEAD>
<TITLE>FusionCharts - Database Example</TITLE>
<%
%>
<SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT>
<style type="text/css">
<!--
body {
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.text{
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
-->
</style>
</HEAD>
<BODY>
<CENTER>
<h2>FusionCharts Database Example</h2>
<%
Statement st1, st2;
ResultSet rs1, rs2;
String strQuery = "";
String strXML = "";
strXML = "<chart caption='Factory Output report' subCaption='By Quantity'
pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units'>";
strQuery = "select * from Factory_Master";
st1 = oConn.createStatement();
rs1 = st1.executeQuery(strQuery);
String factoryId = null;
String factoryName = null;
String totalOutput = "";
while (rs1.next()) {
factoryId = rs1.getString("FactoryId");
factoryName = rs1.getString("FactoryName");
strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" + factoryId;
st2 = oConn.createStatement();
rs2 = st2.executeQuery(strQuery);
if (rs2.next()) {
totalOutput = rs2.getString("TotOutput");
}
strXML += "<set label='" + factoryName + "' value='" + totalOutput + "'/>";
try {
if (null != rs2) {
rs2.close();
rs2 = null;
}
} catch (java.sql.SQLException e) {
System.out.println("Could not close the resultset");
}
try {
if (null != st2) {
st2.close();
st2 = null;
}
} catch (java.sql.SQLException e) {
System.out.println("Could not close the statement");
}
}
strXML += "</chart>";
try {
if (null != rs1) {
rs1.close();
rs1 = null;
}
} catch (java.sql.SQLException e) {
System.out.println("Could not close the resultset");
}
try {
if (null != st1) {
st1.close();
st1 = null;
}
} catch (java.sql.SQLException e) {
System.out.println("Could not close the statement");
}
try {
if (null != oConn) {
oConn.close();
oConn = null;
}
} catch (java.sql.SQLException e) {
System.out.println("Could not close the connection");
}
%>
<jsp:include page="../Includes/FusionChartsRenderer.jsp" flush="true">
<jsp:param name="chartSWF" value="../../FusionCharts/Pie3D.swf" />
<jsp:param name="strURL" value="" />
<jsp:param name="strXML" value="<%=strXML%>" />
<jsp:param name="chartId" value="FactorySum" />
<jsp:param name="chartWidth" value="600" />
<jsp:param name="chartHeight" value="300" />
<jsp:param name="debugMode" value="false" />
<jsp:param name="registerWithJS" value="false" />
</jsp:include>
<BR>
<BR>
<a href='../NoChart.html' target="_blank">Unable to see the chart above?</a></CENTER>
</BODY>
</HTML> |
The following actions are taking place in this code:
- We first include FusionCharts.js JavaScript class to enable easy embedding of FusionCharts.
- We then include DBConn.jsp, which connects to the database and stores the connection in oConn Connection object.
- Thereafter, we generate the XML data document by iterating through the resultset and store it in strXML variable.
- Finally, we render the chart by including FusionChartsRenderer.jsp and passing strXML and other parameters to it .
When you now run the code, you'll get an output as under: |
 |
|
Let's not convert this example to use dataURL method. As previously explained,
in dataURL mode, you need two pages:
- Chart Container Page - The page which embeds the
HTML code to render the chart. This page also tells the chart where
to load the data from. We'll name this page as Default.jsp.
- Data Provider Page - This page provides the XML data
to the chart. We'll name this page as PieData.jsp
The pages in this example are contained in
Download Package > Code > JSP > DB_dataURL folder. |
|
Default.jsp
contains the following code to render the chart: |
<%@ page import ="com.fusioncharts.FusionChartsHelper"%>
<%
%>
<HTML>
<HEAD>
<TITLE>FusionCharts - dataURL and Database Example</TITLE>
<%
%>
<SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT>
<style type="text/css">
<!--
body {
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.text{
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
-->
</style>
</HEAD>
<BODY>
<CENTER>
<h2>FusionCharts dataURL and Database</h2>
<h4>Click on any pie slice to slice it out.Or, right click to enable
rotation mode.</h4>
<%
String strDataURL = "";
strDataURL = FusionChartsHelper.encodeDataURL("PieData.jsp?animate=1", "false", response);
%>
<jsp:include page="../Includes/FusionChartsRenderer.jsp" flush="true">
<jsp:param name="chartSWF" value="../../FusionCharts/Pie3D.swf" />
<jsp:param name="strURL" value="<%=strDataURL%>" />
<jsp:param name="strXML" value="" />
<jsp:param name="chartId" value="FactorySum" />
<jsp:param name="chartWidth" value="600" />
<jsp:param name="chartHeight" value="400" />
<jsp:param name="debugMode" value="false" />
<jsp:param name="registerWithJS" value="false" />
</jsp:include>
<BR>
<BR>
<a href='../NoChart.html' target="_blank">Unable to see the chart above?</a></CENTER>
</BODY>
</HTML>
|
In this page:
- FusionCharts.js JavaScript file is included.
- The dataURL string with value "PieData.jsp" is stored in strDataURL variable.
- Finally, we render the chart using FusionChartsRenderer.jsp and pass strURL parameter with value present in strDataURL.
|
PieData.jsp contains the following code to output
XML Data: |
<%
%>
<%@ include file="../Includes/DBConn.jsp" %> <%@ page import="java.sql.Statement"%>
<%@ page import="java.sql.ResultSet"%>
<%
Statement st1 = null, st2 = null;
ResultSet rs1 = null, rs2 = null;
String strQuery = "";
String strXML = "";
String animateChart;
animateChart = request.getParameter("animate");
if (null == animateChart || animateChart.equals("")) {
animateChart = "1";
}
strXML = "<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30'
showBorder='1' formatNumberScale='0' numberSuffix=' Units'
animation=' " + animateChart + "'>";
strQuery = "select * from Factory_Master";
st1 = oConn.createStatement();
rs1 = st1.executeQuery(strQuery);
String factoryId = null;
String factoryName = null;
String totalOutput = "";
while (rs1.next()) {
factoryId = rs1.getString("FactoryId");
factoryName = rs1.getString("FactoryName");
strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" + factoryId;
st2 = oConn.createStatement();
rs2 = st2.executeQuery(strQuery);
if (rs2.next()) {
totalOutput = rs2.getString("TotOutput");
}
strXML += "<set label='" + factoryName + "' value='" + totalOutput + "' />";
try {
if (null != rs2) {
rs2.close();
rs2 = null;
}
} catch (java.sql.SQLException e) {
System.out.println("Could not close the resultset");
}
try {
if (null != st2) {
st2.close();
st2 = null;
}
} catch (java.sql.SQLException e) {
System.out.println("Could not close the statement");
}
}
strXML += "</chart>";
try {
if (null != rs1) {
rs1.close();
rs1 = null;
}
} catch (java.sql.SQLException e) {
System.out.println("Could not close the resultset");
}
try {
if (null != st1) {
st1.close();
st1 = null;
}
} catch (java.sql.SQLException e) {
System.out.println("Could not close the statement");
}
try {
if (null != oConn) {
oConn.close();
oConn = null;
}
} catch (java.sql.SQLException e) {
System.out.println("Could not close the connection");
}
response.setContentType("text/xml");
%>
<%=strXML%> |
In the above page:
- We first request the animate property which has been passed to it
(from dataURL)
- We query the database to get the total output of each factory.
- We generate the data and store it in strXML
variable
- Finally, we write this data to output stream without any HTML tags.
When you view this page, you'll get the same output as before.
Database connection can be achieved in 2 ways:
- By including DBConn.jsp in the page as shown in the above example
- By using the DBConnection class as a bean in the application scope
We have seen how to use DBConn.jsp to get a Connection. Now let us see how
to achieve
the same result using DBConnection bean. |
|
The JSP page using DBConnection class is named as BasicDBExampleUsingConnectionClass.jsp (in DBExample folder).
In order to get a connection using the Java
class DBConnection, first you need to use the DBConnection class as a bean
in application scope and import java.sql.Connection class.
|
<jsp:useBean id="dbConn" class="com.fusioncharts.database.DBConnection"
scope="application" />
<%@ page import="java.sql.Connection"%>
|
Wherever a Connection to the database is required,
call the getConnection method in the DBConnection class as follows. |
Connection oConn=dbConn.getConnection(); |
Once the connection is achieved, use it to create
statements, ResultSet objects and finally close it. This can be done in a
try-catch block in the following manner. |
try {
if(null!=oConn) {
oConn.close();
oConn=null;
}
}catch(java.sql.SQLException e){
System.out.println("Could not close
the connection");
} |
So, this is how we use the DBConnection class to get a Connection. After getting the connection, you can perform the queries to get the data. All the work of configuring the database name, MySQL db Datasource name (i.e, all the database related configuration) is done in the configuration file - context.xml.
|