|
Server : Apache/2.2.17 (Unix) mod_ssl/2.2.17 OpenSSL/0.9.8e-fips-rhel5 DAV/2 PHP/5.2.17 System : Linux localhost 2.6.18-419.el5 #1 SMP Fri Feb 24 22:47:42 UTC 2017 x86_64 User : nobody ( 99) PHP Version : 5.2.17 Disable Function : NONE Directory : /home/queenjbs/www/FusionChart/Contents/Code/J2EE/ |
Upload File : |
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Using FusionCharts XT with JSP - Plotting data from a database</title>
<link rel="stylesheet" href="../../assets/ui/css/style.css" type="text/css" />
<script type="text/javascript" src="../../assets/prettify/prettify.js"></script>
<link rel="stylesheet" type="text/css" href="../../assets/prettify/prettify.css" />
<script type="text/javascript" src="../../assets/ui/js/jquery.min.js" ></script>
<style type="text/css">
<!--
div.WebHelpPopupMenu { position:absolute;
left:0px;
top:0px;
z-index:4;
visibility:hidden; }
a.whtbtnhide, a.whtbtnshow, a.whtbtnhidenav , a.whtbtnshownav { border-bottom:none !important; }
-->
</style>
<script type="text/javascript" language="javascript1.2" src="../../assets/ui/js/whmsg.js"></script>
<script type="text/javascript" language="javascript" src="../../assets/ui/js/whver.js"></script>
<script type="text/javascript" language="javascript1.2" src="../../assets/ui/js/whproxy.js"></script>
<script type="text/javascript" language="javascript1.2" src="../../assets/ui/js/whutils.js"></script>
<script type="text/javascript" language="javascript1.2" src="../../assets/ui/js/whlang.js"></script>
<script type="text/javascript" language="javascript1.2" src="../../assets/ui/js/whtopic.js"></script>
<script type="text/javascript" src="../../assets/ui/js/lib.js"></script>
</head>
<body>
<!-- breadcrumb starts here -->
<div id="breadcrumb"></div>
<script type="text/javascript">
document.write( addFCBreadcrumb( [ "Home|../../Introduction/Overview.html", "Guide for web developers", "Using with J2EE|JSP_GettingStarted.html", "Plotting from database" ] ) );
</script>
<!-- breadcrumb ends here -->
<table width="98%" border="0" cellspacing="0" cellpadding="3" align="center">
<tr>
<td class="pageHeader">Plotting data from a database</td>
</tr>
<tr>
<td valign="top" class="text">
<p>In this section, we will show you how to use FusionCharts XT and JSP to plot
charts from data contained in a database. Here, we will do the following: </p>
<ul>
<li><a href="#datastr">Create a pie chart to show "Production by Factory" by initially using the<span class="codeInline"> Data String </span>method</a></li>
<li><a href="#dataurl">Convert the chart to use the <span class="codeInline">Data URL</span> method</a></li>
<li><a href="#msline">Create a multi-series line chart from database</a></li>
</ul>
<p>For the sake of ease, we will use MySQL Database. The database is present
in <span class="codeInline">Download Package > Code > J2EE > DB
</span>folder. You can, however, use any database with FusionCharts XT including
MS Access, MySQL, MS SQL, Oracle. Database creation script for MySQL is also present
in the same folder.</p>
<p><strong>Before you go further with this page, we recommend you to please
see the previous section <a href="JSP_BasicExample.html">Basic Examples</a> as we start off from
concepts explained in that page. </strong></p>
<p class="highlightBlock">The code examples contained in this page are present
in <span class="codeInline">Download Package > Code > J2EE > DBExample
</span> folder. <br>
The database scripts are present in <span class="codeInline">Download
Package > Code > J2EE ></span> <span class="codeInline">DB</span>. </p> </td>
</tr>
<tr>
<td valign="top" class="header">Database Structure </td>
</tr>
<tr>
<td valign="top" class="text">
<p>Before we code the JSP pages to retrieve data,
let's quickly have a look at the database structure. </p>
</td>
</tr>
<tr>
<td valign="top" class="text"><img src="../../guide-for-web-developers/Images/Code_DB.gif" width="372" height="124" /></td>
</tr>
<tr>
<td valign="top" class="text">
<p>The database contains just two tables:</p>
<ol>
<li><span class="codeInline">Factory_Master</span>: To store the name
and id of each factory</li>
<li><span class="codeInline">Factory_Output</span>: To store the number
of units produced by each factory for a given date.</li>
</ol>
<p>For demonstration, we have 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. </p></td></tr>
<tr>
<td class="header">Database Configuration</td></tr>
<tr>
<tr>
<td valign="top" class="text"> </td>
</tr>
<td valign="top" class="text">
<ol>
<li>The database configuration is present in the file <span class="codeInline">Download Package > Code > JSP > META-INF > context.xml file</span>. 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 <span class="codeInline">factorydb</span>, username <span class="codeInline">root</span> with no password. </li>
<li>Once this is done, we need to create the required tables. The SQL script "<span class="codeInline">FactoryDBCreation.sql</span>" present in the folder <span class="codeInline">Download Package > Code > JSP > DB </span>will create the database with two tables and sample data. Note that these scripts will not create foreign key relationships. You will have to manually alter the table to create the relationship, if you think necessary. </li>
<li> 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 <span class="codeInline">Download Package > Code > JSP > DB</span> folder. You could run this script in your MySQL - this will alter the database to use UTF8 as the default character set, create the <span class="codeInline">Japanese_Factory_Master</span> table and insert sample data into them.</li>
</ol> </td>
</tr>
<tr>
<td valign="top" class="text"> </td>
</tr>
<tr>
<td valign="top" class="header"><a name="datastr" id="datastr"></a>Building the JSP Page for the Data String Method </td>
</tr>
<tr>
<td valign="top" class="text">
<p>The JSP page for <span class="codeInline">Data String</span>
method example is named as <span class="codeInline">BasicDBExample.jsp</span>
(in <span class="codeInline">DBExample</span> folder). It contains the following
code: </p>
</td>
</tr>
<tr>
<td valign="top" class="text">
<pre class="code_container prettyprint"><%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="tags" tagdir="/WEB-INF/tags" %>
<%@ taglib uri="http://www.fusioncharts.com/jsp/core" prefix="fc" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<jsp:useBean id="factoriesBean" class="com.fusioncharts.sampledata.FactoriesBean" />
<c:set var="folderPath" value="../../FusionCharts/" />
<c:set var="title" value="FusionCharts - Database Example" scope="request" />
<c:set var="header1" value="FusionCharts Database Example" scope="request" />
<c:set var="jsPath" value="${folderPath}" scope="request"/>6
<sql:setDataSource dataSource="jdbc/FactoryDB"/>
<c:set var="strXML" value="<chart caption='Factory Output report' subCaption='By Quantity'
pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' animation='${animateChart}'>"/>
<c:set var="strQuery" value="select * from Factory_Master"/>
<sql:query var="entries" sql="${strQuery}">
</sql:query>
<c:forEach var="row" items="${entries.rows}">
<c:set var="factoryId" value="${row.FactoryId}"/>
<c:set var="factoryName" value="${row.FactoryName}"/>
<c:set var="strQuery2" value="select sum(Quantity) as TotOutput from Factory_Output where FactoryId=${factoryId}"/>
<sql:query var="factoryDetailEntries" sql="${strQuery2}">
</sql:query>
<c:forEach var="detailRow" items="${factoryDetailEntries.rows}">
<c:set var="totalOutput" value="${detailRow.TotOutput}"/>
<c:set var="setElem" value="<set label='${factoryName}' value='${totalOutput}' />"/>
<c:set var="strXML" value="${strXML}${setElem} "/>
</c:forEach>
</c:forEach>
<c:set var="strXML" value="${strXML}</chart> "/>
<tags:template2>
<span class="codeComment"><!-- Create the chart - Pie 3D Chart with data contained in bean --></span>
<fc:render chartId="${factoriesBean.chartId}" swfFilename="${folderPath}${factoriesBean.filename}"
width="${factoriesBean.width}" height="${factoriesBean.height}" debugMode="false"
registerWithJS="false" xmlData="${strXML}" />
</tags:template2>
</pre></td>
</tr>
<tr>
<td valign="top" class="text">
<p>The following actions are taking place in this code:</p>
<ol>
<li>We first include the JavaScript class to enable easy embedding of FusionCharts. This is achieved by setting the value for the variable jsPath, so that the template can include the script.<br /><br />
<pre class="code_container prettyprint">
<c:set var="folderPath" value="../../FusionCharts/"/>
<c:set var="jsPath" value="${folderPath}" scope="request"/>
</pre><br />
</li>
<li>Next, we declare the datasource for the database connection. For this, we have used <span class="codeInline">sql</span> tags from <span class="codeInline">jstl</span>. Here the data source is jdbc/FactoryDB.
<br /><br />
<pre class="code_container prettyprint"><sql:setDataSource dataSource="jdbc/FactoryDB"/>
</pre>
<p>This data source has been defined in context.xml in the application.</p>
</li>
<li>We then execute the query to get the list of all the factories and their total output from the database, using sql:query tag.
<p>Then, we execute the queries to get list of factories:</p>
<pre class="code_container prettyprint"><c:set var="strQuery" value="select * from Factory_Master"/>
<sql:query var="entries" sql="${strQuery}"> </sql:query></pre>
<p>We loop through the resultset of this query (list of factories) and within the loop, we execute another query to get the total output for the particular factory:</p>
<pre class="code_container prettyprint"><c:set var="strQuery2" value="select sum(Quantity) as TotOutput from Factory_Output
where FactoryId=${factoryId}"/>
<sql:query var="factoryDetailEntries" sql="${strQuery2}"> </sql:query></pre>
<p>Thus, we have seen how to use <span class="codeInline">jstl sql tags </span> to get a Connection in a jsp and run queries.</p>
</li>
<li>Thereafter, we generate the XML data document by iterating through the resultset and store it in <span class="codeInline">strXML</span> variable. </li><br />
<li>Finally, we render the chart by using the <span class="codeInline">fc:render</span> tag and passing <span class="codeInline">strXML </span>and other attributes to it . </li>
</ol>
<p>Note that in this jsp, we have written the complete code for retrieving data in the jsp itself. Ideally, the database connection and data retrieval code should be present in the bean class. </p>
<p>In our other database examples, we have used this approach. </p>
<p>When you now run the code, you will get an output as under: </p>
<p><img src="../../guide-for-web-developers/Images/Code_DBOut.jpg" width="572" height="273" class="imageBorder" /></p></td>
</tr>
<tr>
<td valign="top" class="header"><a name="dataurl" id="dataurl"></a>Converting the example to use the Data URL method </td>
</tr>
<tr>
<td valign="top" class="text">
<p>Let's now convert this example to use Data URL method. As previously explained,
in Data URL mode, you need two pages:</p>
<ol>
<li><strong>Chart Container Page</strong> - The page which embeds the
HTML code to render the chart. This page also tells the chart where
to load the data from. We will name this page as <span class="codeInline">Default.jsp</span>. </li>
<li><strong>Data Provider Page</strong> - This page provides the XML data
to the chart. We will name this page as <span class="codeInline">PieData.jsp</span></li>
</ol>
<p class="highlightBlock">The pages in this example are contained in<span class="codeInline">
Download Package > Code > JSP > DB_dataURL</span> folder. </p> </td>
</tr>
<tr>
<td valign="top" class="header">Chart Container Page - <span class="codeInline">Default.jsp
</span></td>
</tr>
<tr>
<td valign="top" class="text">
<p><span class="codeInline">Default.jsp</span>
contains the following code to render the chart: </p>
</td>
</tr>
<tr>
<td valign="top" class="text">
<pre class="code_container prettyprint"> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="tags" tagdir="/WEB-INF/tags" %>
<%@ taglib uri="http://www.fusioncharts.com/jsp/core" prefix="fc" %>
<strong><%@ taglib uri="http://www.fusioncharts.com/jsp/functions" prefix="fchelper" %> </strong>
<%@page import="com.fusioncharts.FusionChartsHelper" %>
<jsp:useBean id="chartData" class="com.fusioncharts.sampledata.DynamicRenderData"/>
<strong><c:set var="animateChart" value="${param.animate}"/>
<c:if test="${empty animateChart}">
<c:set var="animateChart" value="1"/>
</c:if></strong>
<c:set var="folderPath" value="../../FusionCharts/"/>
<c:set var="title" value="FusionCharts - dataURL and Database Example" scope="request"/>
<c:set var="header1" value="FusionCharts - Database dataURL Example" scope="request"/>
<c:set var="header2" value="Click on any pie slice to slice it out.Or, right click to enable
rotation mode." scope="request"/>
<strong><c:set var="jsPath" value="${folderPath}" scope="request"/>
<c:set var="urlWithParams" value="${chartData.url}?animate=${animateChart}"/>
</strong><tags:template2>
<span class="codeComment"><!-- Create the chart - Pie 3D Chart with data got from another jsp --></span>
<strong><fc:render chartId="${chartData.chartId}" swfFilename="${folderPath}${chartData.filename}"
width="${chartData.width}" height="${chartData.height}" debugMode="false"
registerWithJS="false" xmlUrl="${<strong>urlWithParams</strong>}" /></strong>
</tags:template2></pre> </td>
</tr>
<tr>
<td valign="top" class="text">
<p>In this page,<span class="codeInline"></span></p>
<ol>
<li>The path (jsPath) to the folder containing the JavaScript files is specified, so that the template tag file can include the required file.<br /><br />
</li>
<li>The value of <span class="codeInline">animateChart</span> is obtained from the request and used to set the animation to true or false.<br><br>
<pre class="code_container prettyprint"><c:set var="animateChart" value="${param.animate}"/>
<c:if test="${empty animateChart}">
<c:set var="animateChart" value="1"/>
</c:if></pre><br />
</li>
<li>The initial URL to the jsp file which will provide the data is obtained from the bean <span class="codeInline">com.fusioncharts.sampledata.DynamicRenderDat</span>a. The value for URL is declared as follows: <br>
<br>
<pre class="code_container prettyprint">protected String url="PieData.jsp";</pre>
<p>The URL to the xml data is constructed with parameters and then encoded.</p>
<pre class="code_container prettyprint"><c:set var="urlWithParams" value="${chartData.url}?animate=${animateChart}"/>
</pre>
<p>Since we are using the <span class="codeInline">fc:render</span> tag, we did not encode the URL with parameters. In case we use the <span class="codeInline">fc:renderHTML</span> tag, then we have to encode the URL before providing it to the tag. This can be done as shown below:</p>
<pre class="code_container prettyprint"><c:set var="encodedURL" value="<strong>${fchelper:encodeDataURL(urlWithParams,true)}</strong>"/></pre>
<p>Here, we have used another tag library prefixed as <span class="codeInline">fchelper</span>. <span class="codeInline">encodeDataURL</span>. This is a function defined by the tag library which can be used to encode the URL before providing it to the <span class="codeInline">fc:renderHTML</span> tag.</p>
</li>
<li>The <span class="codeInline">xmlUrl</span> string with value <span class="codeInline">PieData.jsp?animate=1</span> is then provided to the <span class="codeInline">fc:render</span> tag. <br><br>
</li>
<li>Finally, we render the chart using the<span class="codeInline"> fc:render</span> tag.</li>
</ol>
<p>When you view this page, you'll get the same output as before.</p></td>
</tr>
<tr>
<td valign="top" class="header">Creating the data provider page <span class="codeInline">PieData.jsp
</span></td>
</tr>
<tr>
<td valign="top" class="text">
<p><span class="codeInline">PieData.jsp</span> contains the following code to output
XML Data: </p>
</td>
</tr>
<tr>
<td valign="top" class="text">
<pre class="code_container prettyprint"><%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<sql:setDataSource dataSource="jdbc/FactoryDB"/>
<strong><c:set var="animateChart" value="${param.animate}"/></strong>
<c:set var="strXML" value="<chart caption='Factory Output report' subCaption='By Quantity'
pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' animation='${animateChart}'>"/>
<c:set var="strQuery" value="select * from Factory_Master"/>
<sql:query var="entries" sql="${strQuery}">
</sql:query>
<c:forEach var="row" items="${entries.rows}">
<c:set var="factoryId" value="${row.FactoryId}"/>
<c:set var="factoryName" value="${row.FactoryName}"/>
<strong> <c:set var="strQuery2" value="select sum(Quantity) as TotOutput from
Factory_Output where FactoryId=${factoryId}"/>
<sql:query var="factoryDetailEntries" sql="${strQuery2}">
</sql:query>
<c:forEach var="detailRow" items="${factoryDetailEntries.rows}">
<c:set var="totalOutput" value="${detailRow.TotOutput}"/>
<c:set var="setElem" value="<set label='${factoryName}' value='${totalOutput}' />"/>
<c:set var="strXML" value="${strXML}${setElem} "/>
</c:forEach></strong>
</c:forEach>
<c:set var="strXML" value="${strXML}</chart> "/>
<strong><c:set target="${pageContext.response}" property="contentType" value="text/xml"/>
<c:out value="${strXML}" escapeXml="false"/></strong>
</pre></td>
</tr>
<tr>
<td valign="top" class="text">
<p>In the above page,</p>
<ol>
<li>We first request the <span class="codeInline">animate</span> property which has been passed to it
(as part of request parameter in <span class="codeInline">xmlUrl</span> provided to the chart )</li>
<li>We query the database to get the total output of each factory. </li>
<li>We generate the xml data and store it in <span class="codeInline">strXML</span>
variable</li>
<li>Finally, we write this data to output stream without any HTML tags. The <span class="codeInline">contentType</span> for this page is set to <span class="codeInline">"text/xml"</span>. </li>
</ol></td></tr>
<tr>
<td valign="top" class="text"> </td>
</tr>
<tr>
<td class="header">About the database connection</td></tr>
<tr>
<td valign="top" class="text">
<p>Database connection can be achieved in two ways: </p>
<ol>
<li>By using the <span class="codeInline">jstl sql tags</span> in the jsp as shown in the above example.</li>
<li>By connecting to database from a java bean using the DBConnection class.<br>
</li>
</ol>
<p>Using jstl tags, we first define the data source as shown:</p>
<pre class="code_container prettyprint"><sql:setDataSource dataSource="jdbc/FactoryDB"/> </pre>
<p> We have seen how to use the <span class="codeInline">jstl sql tags </span> to get a Connection in a jsp and run queries. Now, let us see how
to achieve
the same result using the <span class="codeInline">DBConnection</span> class in a bean. </p> </td>
</tr>
<tr>
<td valign="top" class="header">Connecting to the database from bean </td>
</tr>
<tr>
<td valign="top" class="text"><p>The JSP page using bean with <span class="codeInline">DBConnection</span> class is named as <span class="codeInline">BasicDBExampleUsingBean.jsp</span> (in the <span class="codeInline">DBExample</span> folder). </p>
<p>The code in the jsp is kept minimum and all the work of connecting to the database and generating the XML is done in the bean class <span class="codeInline">com.fusioncharts.sampledata.FactoriesBeanWithoutLink.<br>
</span>Hence, the jsp mainly has the JavaScript path set and the <span class="codeInline">fc:render</span> tag with attribute values obtained from the bean. </p>
<p>Now, let us take a look at the relevant code in the bean:</p>
<pre class="code_container prettyprint">private String getFactoryXML(){
<span class="codeComment">//strXML will be used to store the entire XML document generated
</span>String strXML="";
try{
DBConnection dbConn = new DBConnection();
Connection oConn= dbConn.getConnection();
<span class="codeComment"> //Database Objects - Initialization</span>
Statement st1=null,st2=null;
ResultSet rs1=null,rs2=null;
String strQuery="";
Map<String,String> chartAttributes=new HashMap<String, String>();;
chartAttributes.put("caption", "Factory Output report");
chartAttributes.put("subCaption", "By Quantity");
chartAttributes.put("pieSliceDepth", "30");
chartAttributes.put("showBorder", "1");
chartAttributes.put("formatNumberScale", "0");
chartAttributes.put("numberSuffix", " Units");
DOMHelper domHelper = new DOMHelper();
Document chartDoc = domHelper.getDocument();
Element rootElement = chartDoc.createElement("chart");
domHelper.addAttributesToElement(rootElement, chartAttributes);
<span class="codeComment">//Iterate through each factory</span>
strQuery = "select * from Factory_Master";
st1=oConn.createStatement();
rs1=st1.executeQuery(strQuery);
String factoryId=null;
String factoryName=null;
String totalOutput="";
while(rs1.next()) {
totalOutput="";
factoryId=rs1.getString("FactoryId");
factoryName=rs1.getString("FactoryName");
<span class="codeComment">//Now create second recordset to get details for this factory</span>
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");
}
<span class="codeComment">//Generate <set label='..' value='..'/> </span>
Element setElem = chartDoc.createElement("set");
setElem.setAttribute("label", factoryName);
setElem.setAttribute("value", totalOutput);
rootElement.appendChild(setElem);
<span class="codeComment"> //close the resultset,statement
//enclose them in try catch block</span>
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");
}
<span class="codeComment">//Finally, close <chart> element</span>
chartDoc.appendChild(rootElement);
strXML=domHelper.getXMLString(chartDoc);
<span class="codeComment">//close the resultset,statement,connection
//enclose them in try catch block</span>
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");
}
}catch(java.sql.SQLException e){
System.out.println("Could not close the statement");
}
return strXML;
}</pre>
<p class="text">The above method <span class="codeInline">getFactoryXML()</span> constructs the XML and it is called within the <span class="codeInline">getXml()</span> method. </p> <p>In order to get a connection using the Java
class <span class="codeInline">DBConnection</span>, first you need to import the <span class="codeInline">DBConnection</span> class.</p>
<pre class="prettyprint code_container">import com.fusioncharts.database.DBConnection;</pre>
<p>Wherever a connection to the database is required,
call the <span class="codeInline">getConnection</span> method in the <span class="codeInline">DBConnection</span> class as follows.</p>
<pre class="prettyprint code_container">Connection oConn=dbConn.getConnection();</pre> </td>
</tr>
<tr>
<td valign="top" class="text">
<p>Once the connection is obtained, use it to create the
<span class="codeInline">Statements</span>, <span class="codeInline">ResultSet</span> objects, and finally close it. This can be done in a
try-catch block in the following manner.</p>
</td>
</tr>
<tr>
<td valign="top"><pre class="prettyprint code_container">try {
if(null!=oConn) {
oConn.close();
oConn=null;
}
}catch(java.sql.SQLException e){
<span class="codeComment">//do some exception handling</span>
System.out.println("Could not close
the connection");
}</pre></td>
</tr>
<tr>
<td valign="top" class="text">
<p>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 database Datasource name (that is, the complete database related configuration) is done in the configuration file - <span class="codeInline">context.xml</span>. </p>
<p>The logic for iterating through the factories to obtain the total output for each factory is similar to our earlier examples. The only difference now is the way the XML is constructed. Here, we have used DOM to create the XML instead of string concatenation.</p>
<p>For this purpose, we have used the <span class="codeInline">com.fusioncharts.sampledata.DOMHelper</span> class. This is a simple class which has DOM related functions to get the document, output the document and get the XML document as a string. </p>
<p>The code snippets involving the usage of DOM to construct the XML are shown below:</p>
<pre class="prettyprint code_container">Map<String,String> chartAttributes=new HashMap<String, String>();;
chartAttributes.put("caption", "Factory Output report");
chartAttributes.put("subCaption", "By Quantity");
chartAttributes.put("pieSliceDepth", "30");
chartAttributes.put("showBorder", "1");
chartAttributes.put("formatNumberScale", "0");
chartAttributes.put("numberSuffix", " Units");
DOMHelper domHelper = new DOMHelper();
Document chartDoc = domHelper.getDocument();
Element rootElement = chartDoc.createElement("chart");
domHelper.addAttributesToElement(rootElement, chartAttributes); </pre>
<p>The above code creates the root element <span class="codeInline"><chart></span> and adds the caption, subCaption and other attributes to it. Next, the children to the <span class="codeInline"><chart></span> element are added. In the single-series chart, we will have <span class="codeInline"><set></span> elements as the children to the <span class="codeInline"><chart></span> tag. This is done for each factory in a loop.</p>
<pre class="prettyprint code_container">// "<set label="+factoryName+" value="+totalOutput"/>"
Element setElem = chartDoc.createElement("set");
setElem.setAttribute("label", factoryName);
setElem.setAttribute("value", totalOutput);
rootElement.appendChild(setElem);</pre>
<p>Finally, add the chart element to the document and obtain the document as an xml string to be provided to <span class="codeInline">fc:render</span> tag:</p>
<pre class="prettyprint code_container">chartDoc.appendChild(rootElement);<br>
strXML=domHelper.getXMLString(chartDoc);</pre></td></tr>
<tr>
<td valign="top" class="text"> </td></tr>
<tr>
<td class="header"><a name="msline" id="msline"></a>Create multi-series line chart</td></tr>
<tr>
<td valign="top" class="text">
<p>Let's now see how we can create a multi-series chart from database. This is no different from creating the Pie chart we saw above. </p>
<p>All you need to do is to build proper XML from the database that you query from your database. <a href="../../DataFormats/XML/MultiSeries.html">Multi-series charts have a different data format</a> from the format of an XML for a single-series chart like pie. </p>
<p>The jsp code will be similar to all the examples we have seen before. We will use the bean <span class="codeInline">MSFactoriesData</span> as shown:</p>
<pre class="code_container prettyprint"><strong>JSP: MSDBExample.jsp </strong>
<jsp:useBean id="factoriesBean" class="com.fusioncharts.sampledata.MSFactoriesData" /></pre> <p>And then, rendering the chart is also similar:</p>
<pre class="code_container prettyprint"><fc:render chartId="${factoriesBean.chartId}" swfFilename="${folderPath}${factoriesBean.filename}"
width="${factoriesBean.width}" height="${factoriesBean.height}"
debugMode="false" registerWithJS="false" xmlData="${factoriesBean.xml}" /></pre><p>The fields in the bean and the code in the bean to construct datasetXML and categoriesXML are as shown: </p>
<pre class="code_container prettyprint"> protected String chartId = "FactorySum";<br> protected String width = "700";<br> protected String height = "400";p<br> protected String filename = ChartType.MSLINE.getFileName();
protected String xml = null;
<span class="codeComment">/**
* Build the xml for the categories
* @param chartDoc
* @param rootElement
* @param categoriesResultSet
* @throws SQLException
*/</span>
private void buildCategoriesXML(Document chartDoc, Element rootElement,
ResultSet categoriesResultSet) throws SQLException {
Element categoriesElem = chartDoc.createElement("categories");
if (categoriesResultSet.next()) {
while (categoriesResultSet.next()) {
String datePro = categoriesResultSet.getString("DatePro");
Element categoryElem = chartDoc.createElement("category");
categoryElem.setAttribute("label", datePro);
categoriesElem.appendChild(categoryElem);
}
rootElement.appendChild(categoriesElem);
}
}
<span class="codeComment">/**
* Build the xml for datasets
* @param chartDoc
* @param rootElement
* @param datasetResultSet
* @throws SQLException
*/</span>
private void buildDatasetXML(Document chartDoc, Element rootElement,
ResultSet datasetResultSet) throws SQLException {
String factoryName = null;
String previousFactoryName = null;
Element datasetElem = null;
if (datasetResultSet.next()) {
while (datasetResultSet.next()) {
factoryName = datasetResultSet.getString("FactoryName");
if (!factoryName.equals(previousFactoryName)) {
datasetElem = chartDoc.createElement("dataset");
datasetElem.setAttribute("seriesName", factoryName);
}
String quantity = datasetResultSet.getString("Quantity");
Element setElem = chartDoc.createElement("set");
setElem.setAttribute("value", quantity);
datasetElem.appendChild(setElem);
if (!factoryName.equals(previousFactoryName)) {
rootElement.appendChild(datasetElem);
}
previousFactoryName = factoryName;
}
}
}</pre>
<p>The code to construct the multi-series xml from the database (the <span class="codeInline">getFactoryXML()</span>method):</p>
<pre class="code_container prettyprint"><span class="codeComment">
// strXML will be used to store the entire XML document generated
</span>String strXML = "";
try {
DBConnection dbConn = new DBConnection();
Connection oConn = dbConn.getConnection();
<span class="codeComment"> // Database Objects - Initialization</span>
Statement st1 = null, st2 = null;
ResultSet rs1 = null, rs2 = null;
String strCategoryQuery = "";
String strQuery = "";
Map<String, String> chartAttributes = new HashMap<String, String>();
chartAttributes.put("caption", "Factory Output report");
chartAttributes.put("subCaption", "By Quantity");
chartAttributes.put("xAxisName", "Factory");
chartAttributes.put("yAxisName", "Units");
chartAttributes.put("showValues", "0");
chartAttributes.put("animation", "1");
chartAttributes.put("formatNumberScale", "0");
chartAttributes.put("rotateValues", "1");
DOMHelper domHelper = new DOMHelper();
Document chartDoc = domHelper.getDocument();
Element rootElement = chartDoc.createElement("chart");
domHelper.addAttributesToElement(rootElement, chartAttributes);
<span class="codeComment">// Generate the chart element
// Iterate through each factory</span>
strCategoryQuery = "select distinct DATE_FORMAT(factory_output.DatePro,'%c-%d-%Y')
as DatePro from factory_output order by DatePro";
strQuery = "select factory_master.FactoryName, DATE_FORMAT(factory_output.DatePro,'%c-%d-%Y')
as DatePro, factory_output.Quantity from factory_master factory_master, factory_output factory_output
where factory_output.FactoryID = factory_master.FactoryId
order by factory_output.FactoryID, factory_output.DatePro";st1 = oConn.createStatement();
rs1 = st1.executeQuery(strCategoryQuery);
<strong>buildCategoriesXML(chartDoc, rootElement, rs1);</strong>
st1 = oConn.createStatement();
rs1 = st1.executeQuery(strCategoryQuery);
<span class="codeComment"> // close the resultset,statement
// enclose them in try catch block</span>
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");
}
st2 = oConn.createStatement();
rs2 = st2.executeQuery(strQuery);
<strong> buildDatasetXML(chartDoc, rootElement, rs2);</strong>
<span class="codeComment">// close the resultset,statement
// enclose them in try catch block
// Finally, close <chart> element</span>
chartDoc.appendChild(rootElement);
strXML = domHelper.getXMLString(chartDoc);
<span class="codeComment"> // close the resultset,statement,connection
// enclose them in try catch block</span>
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");
}
try {
if (null != oConn) {
oConn.close();
oConn = null;
}
}catch (java.sql.SQLException e) {
System.out.println("Could not close the connection");
}
}catch (java.sql.SQLException e) {
System.out.println("Could not close the statement");
}
return strXML;</pre>
<p>The following actions are taking place in this code to construct the XML:</p>
<ol>
<li>We query database for all distinct dates to add them as categories labels of the chart. This is handled by the <span class="codeInline">buildCategoriesXML</span> method </li>
<li>We query database for all factory-wise and dates-wise output data to add them to chart (using the <span class="codeInline">buildDatasetXML</span> function) data where data for each factory becomes a dataset </li>
</ol>
<p>Finally, the chart will look as shown in the image below: </p>
<p><img height="401" src="../../guide-for-web-developers/Images/code/php/mschartfromdb.jpg" width="701"></p>
<p>In our next example, we will learn how to use the drill-down feature of FusionCharts XT. </p>
</tr>
</table>
<!-- footer links starts-->
<div id="fcfooter"></div>
<script type="text/javascript">
document.getElementById("fcfooter").innerHTML = addFCFooter("Using with data in Forms|JSP_Form.html","Creating Drill down charts|JSP_Drill.html");
</script>
<!-- footer links ends -->
<script type="text/javascript" language="javascript1.2">//<![CDATA[
<!--
highlightSearch();
//-->
//]]></script>
</body>
</html>