Help creating Stacked Bar chart with JSP, Java and DB data

[spoclate]

Hi,

 

New to the forum. Loving Fusion charts. I am having some issues creating a stacked bar chart. I can create a normal bar chart and also the drill down version. Each row of my DB table has several columns. It is these columns I would like stacked. I cannot find any examples

 

An example is:

 

analyst attention delta theta lowAlpha highAlpha lowBeta highBeta lowGamma highGamma time Analyst 72 246836 119537 797902 677390 861178 944870 937669 636754 1.49E+12 Analyst 54 706781 641939 286041 605622 392724 433530 97563 253251 1.49E+12 Analyst 63 607845 131630 43136 709612 999633 110155 427353 918349 1.49E+12 Analyst 85 763426 249246 450154 726588 989483 740372 387877 440868 1.49E+12

 

Any help is appreciated.

 

Thanks

 

[Ayan Bhadury]

Hi,

 

For implementing stacked bar chart in JSP using mysql database, please check this demo (also attached the sql file) example from the given dropbox link - https://www.dropbox.com/s/igei6jr3mto0amu/stacked-bar-db.zip?dl=0

[spoclate]

 

Hi,

 

For implementing stacked bar chart in JSP using mysql database, please check this demo (also attached the sql file) example from the given dropbox link - https://www.dropbox.com/s/igei6jr3mto0amu/stacked-bar-db.zip?dl=0

 

Hi,

 

Thank you for your reply and sample code. I have implemented the JSP code and changed some areas to suit my DB and chart. I have hit an error on fusioncharts.js constructor.drawLabel as shown below

 

Uncaught TypeError: Cannot read property 'length' of undefined

    at constructor.drawLabel (fusioncharts.js:1508)

    at Q (fusioncharts.js:329)

 

I have only changed the areas shown in source below.

 // Form the SQL query that returns the top 10 most populous countries
            String sql="SELECT * from analystdata FETCH FIRST 50 ROWS ONLY";


 ArrayList categories = new ArrayList();
            categories.add(buildCategories("analystname", result,gson));
            
            //prepare dataset
            ArrayList dataset = new ArrayList();
            dataset.add(buildDataset("Beta Wave","highbeta", result, gson));
            dataset.add(buildDataset("Alpha Wave", "highalpha", result, gson));
            dataset.add(buildDataset("Theta Wave", "theta", result, gson));
            dataset.add(buildDataset("Gamma Wave", "highgamma", result, gson));
            

This is the extent of the changes I made. Have you had an experience with this issue?

 

Thanks in advance

[Ayan Bhadury]

Hi,

Thanks for replying back.

Please check whether you are retrieving the data correctly or not. It seems like the category object is not generating properly.

Please provide us your sql files so that we can investigate at our end.

[spoclate]

 

Hi,

Thanks for replying back.

Please check whether you are retrieving the data correctly or not. It seems like the category object is not generating properly.

Please provide us your sql files so that we can investigate at our end.

Hi,

My DB is in postgresql. The create script is attached. ID is incremental the others have large values in them. Analyst name is varchar and timestamp is set everytime a row is added. I am struggling to find various ways of representing the data over time. Thanks for your help.

create.sql

[Ayan Bhadury]

Hi, 

Thanks for replying back.

Please check the following 

1- Whether the database connectivity is properly made, and check whether you are able to fetch the required data using the sql query which you are using

2- Please check the whether you are able to restructure your data in fusioncharts datasource format in order to render it in the browser.

Please share your observations. 

[Vijaya Bhaskar V]

Hi .

I am also trying the same configuration with SQL server .

But it is not working .

I can be able to get the data from database when I tried to print the same .

 But when I tried to pass the result set in build categories and build dataset  the data is returning with empty values .

 Can u please help me on this

 

[Ayan Bhadury]

Hi,

Please let us know which chart you are trying to make and also check whether the data are in proper format for FusionCharts or not, also check in the ide console that you are able to fetch the value. If you are still facing problem kindly share the screenshot or a dummy sample with us replicating the problem you are facing.

[Vijaya Bhaskar V]

5 minutes ago, Ayan Bhadury said:

Hi,

Please let us know which chart you are trying to make and also check whether the data are in proper format for FusionCharts or not, also check in the ide console that you are able to fetch the value. If you are still facing problem kindly share the screenshot or a dummy sample with us replicating the problem you are facing.

Hi ,

I have pasted the log results from ide .

Result of sql oneSQLServerResultSet:9

Result of sql two12

Result of sql two220

Result of sql two3HCM

Result of sql two1100

Result of sql two2200

Result of sql two3ERP

Result of sql two1100

Result of sql two2200

Result of sql two3AVN

Result of sql buildCategories{}

Result of sql buildDataset{seriesname=PartnerCount}

Result of sql three[{seriesname=RamcoCount}, {seriesname=PartnerCount}]

stjavafile{"chart":{"showShadow":"0","showValues":"1","divLineDashLen":"1","caption":"Product-wise quarterly revenue in current year","showAxisLines":"0","showBorder":"0","showCanvasBorder":"0","valueFontColor":"#ffffff","usePlotGradientColor":"0","showAlternateHGridColor":"0","paletteColors":"#0075c2,#1aaf5d","captionFontSize":"14","bgcolor":"#ffffff","yAxisName":"Revenue(InUSD)","legendBorderAlpha":"0","divlineThickness":"1","xAxisName":"Quarter","divLineDashed":"1","subCaption":"Harrys SuperMart","divLineGapLen":"1","subcaptionFontBold":"0","showHoverEffect":"1","subcaptionFontSize":"14","legendShadow":"0"},"categories":[{}],"dataset":[{"seriesname":"RamcoCount"},{"seriesname":"PartnerCount"}]}

{chart={"showShadow":"0","showValues":"1","divLineDashLen":"1","caption":"Product-wise quarterly revenue in current year","showAxisLines":"0","showBorder":"0","showCanvasBorder":"0","valueFontColor":"#ffffff","usePlotGradientColor":"0","showAlternateHGridColor":"0","paletteColors":"#0075c2,#1aaf5d","captionFontSize":"14","bgcolor":"#ffffff","yAxisName":"Revenue(InUSD)","legendBorderAlpha":"0","divlineThickness":"1","xAxisName":"Quarter","divLineDashed":"1","subCaption":"Harrys SuperMart","divLineGapLen":"1","subcaptionFontBold":"0","showHoverEffect":"1","subcaptionFontSize":"14","legendShadow":"0"}, categories=[{}], dataset=[{"seriesname":"RamcoCount"},{"seriesname":"PartnerCount"}]}

jsonjavafile{type: "stackedbar2d",renderAt: "chart",width: "600",height: "400",dataFormat: "json",id: "chart1",dataSource: __dataSource__}

outputHTMLelseifjavafile

 

 

[Vijaya Bhaskar V]

4 minutes ago, Vijaya Bhaskar V said:

Hi ,

I have pasted the log results from ide .

Result of sql oneSQLServerResultSet:9

Result of sql two12

Result of sql two220

Result of sql two3HCM

Result of sql two1100

Result of sql two2200

Result of sql two3ERP

Result of sql two1100

Result of sql two2200

Result of sql two3AVN

Result of sql buildCategories{}

Result of sql buildDataset{seriesname=PartnerCount}

Result of sql three[{seriesname=RamcoCount}, {seriesname=PartnerCount}]

stjavafile{"chart":{"showShadow":"0","showValues":"1","divLineDashLen":"1","caption":"Product-wise quarterly revenue in current year","showAxisLines":"0","showBorder":"0","showCanvasBorder":"0","valueFontColor":"#ffffff","usePlotGradientColor":"0","showAlternateHGridColor":"0","paletteColors":"#0075c2,#1aaf5d","captionFontSize":"14","bgcolor":"#ffffff","yAxisName":"Revenue(InUSD)","legendBorderAlpha":"0","divlineThickness":"1","xAxisName":"Quarter","divLineDashed":"1","subCaption":"Harrys SuperMart","divLineGapLen":"1","subcaptionFontBold":"0","showHoverEffect":"1","subcaptionFontSize":"14","legendShadow":"0"},"categories":[{}],"dataset":[{"seriesname":"RamcoCount"},{"seriesname":"PartnerCount"}]}

{chart={"showShadow":"0","showValues":"1","divLineDashLen":"1","caption":"Product-wise quarterly revenue in current year","showAxisLines":"0","showBorder":"0","showCanvasBorder":"0","valueFontColor":"#ffffff","usePlotGradientColor":"0","showAlternateHGridColor":"0","paletteColors":"#0075c2,#1aaf5d","captionFontSize":"14","bgcolor":"#ffffff","yAxisName":"Revenue(InUSD)","legendBorderAlpha":"0","divlineThickness":"1","xAxisName":"Quarter","divLineDashed":"1","subCaption":"Harrys SuperMart","divLineGapLen":"1","subcaptionFontBold":"0","showHoverEffect":"1","subcaptionFontSize":"14","legendShadow":"0"}, categories=[{}], dataset=[{"seriesname":"RamcoCount"},{"seriesname":"PartnerCount"}]}

jsonjavafile{type: "stackedbar2d",renderAt: "chart",width: "600",height: "400",dataFormat: "json",id: "chart1",dataSource: __dataSource__}

outputHTMLelseifjavafile

 

 

 

Here I have pasted the code which I am using .please check 

 I have modified the sever name and password s 

I am using stacked bar chart using jsp .

 

 

<%--

Document : stacked-bar-db

Created on : Apr 17, 2017, 10:35:45 AM

Author : FusionCharts

--%>

 

 

<%@page import="javax.swing.JOptionPane"%>

<%@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);

 

String DBURL1 = "jdbc:sqlserver://servername;databaseName=testdb;user=test;password=test";

 

Connection con=null ;

Statement st=null;

ResultSet rs=null;

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

 

con = java.sql.DriverManager.getConnection(DBURL1);

st = con.createStatement();

 

 

%>

 

<!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="fusioncharts.js"></script>

</head>

<body>

<div id="chart"></div>

<!-- Step 2: Include the `FusionCharts.java` file as a package in your

project.

-->

<%@page import="fusioncharts.FusionCharts" %>

 

<!-- Step 3:Include the package in the file where you want to show

FusionCharts as follows.

 

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 that can be used to convert Java Objects into

their JSON representation. It can also be used to convert a JSON string to

an equivalent Java object. Gson can work with arbitrary Java objects including

pre-existing objects that you do not have source-code of.

link : https://github.com/google/gson  

*/

 

Gson gson = new Gson();

 

 

// Form the SQL query that returns the top 10 most populous countries

// String sql="SELECT * FROM stackedbarchart";

rs = st.executeQuery("SELECT * FROM stackedbarchart");

// Execute the query.

// PreparedStatement pt=con.prepareStatement(sql);  

// ResultSet result=pt.executeQuery();

System.out.println("Result of sql one"+rs);

// The 'chartobj' map object holds the chart attributes and data.

Map<String, String> chartobj = new HashMap<String, String>();//for getting key value pair

 

chartobj.put("caption", "Product-wise quarterly revenue in current year");

chartobj.put("subCaption", "Harrys SuperMart");

chartobj.put("captionFontSize", "14");

chartobj.put("subcaptionFontSize", "14");

chartobj.put("subcaptionFontBold", "0");

chartobj.put("paletteColors", "#0075c2,#1aaf5d");

chartobj.put("bgcolor", "#ffffff");

chartobj.put("showBorder", "0");

chartobj.put("showShadow", "0");

chartobj.put("showCanvasBorder", "0");

chartobj.put("valueFontColor","#ffffff");

chartobj.put("usePlotGradientColor", "0");

chartobj.put("legendBorderAlpha", "0");

chartobj.put("legendShadow", "0");

chartobj.put("showAxisLines", "0");

chartobj.put("showAlternateHGridColor", "0");

chartobj.put("divlineThickness", "1");

chartobj.put("divLineDashed", "1");

chartobj.put("divLineDashLen", "1");

chartobj.put("divLineGapLen", "1");

chartobj.put("xAxisName", "Quarter");

chartobj.put("yAxisName","Revenue(In USD)");

chartobj.put("showValues", "1");

chartobj.put("showHoverEffect","1");

 

 

 

//prepare categories

ArrayList categories = new ArrayList();

ArrayList dataset = new ArrayList();

int id1,id2,id3;

String a ="";

while (rs.next())

{

id1=rs.getInt(3);

id2=rs.getInt(4);

//id3=rs.getInt(2);

a=rs.getString(2);

System.out.println("Result of sql two1"+id1);

System.out.println("Result of sql two2"+id2);

System.out.println("Result of sql two3"+a);

//System.out.println("Result of sql two3"+id3);

}

 

 

 

categories.add(buildCategories("label",rs,gson));

System.out.println("Result of sql buildCategories"+buildCategories("label",rs,gson));

 

dataset.add(buildDataset("RamcoCount","ramco_imp", rs, gson));

dataset.add(buildDataset("PartnerCount", "partner_imp", rs, gson));

System.out.println("Result of sql buildDataset"+buildDataset("PartnerCount", "partner_imp", rs, gson));

// }System.out.println("Result of sql two"+categories);

//prepare dataset

 

 

 

System.out.println("Result of sql three"+dataset);

//close the connection.

rs.close();

 

 

//create 'dataMap' map object to make a complete FusionCharts 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("categories", gson.toJson(categories));

dataMap.put("dataset", gson.toJson(dataset));

 

FusionCharts mslineChart= new FusionCharts(

"stackedbar2d",// chartType

"chart1",// chartId

"600","400",// chartWidth, chartHeight

"chart",// chartContainer

"json",// dataFormat

gson.toJson(dataMap) //dataSource

);

System.out.println(dataMap);

 

%>

 

 

<%!

/**

* @description - Build the Json for the categories

* @param {String} data_item - Name of the column from table

* @param {ResultSet} rs - The object of ResultSet maintains a

* cursor pointing to a particular row of data.

 

* @param {Gson} gson - Gson is a Java library that can be used

* to convert Java Objects into their JSON representation.

* @return {Map Object}

*/

public Map buildCategories(String data_item, ResultSet rs,Gson gson) {

//creation of the inner category

Map<String, String> categoryinner = new HashMap<String, String>();

ArrayList category = new ArrayList();

int counter = -1;

try {

//to restore the position of the result set.

rs.beforeFirst();

while(rs.next()) {  

//for creating the key value for the category label from database.

Map<String, String> lv = new HashMap<String, String>();

lv.put("label", rs.getString(data_item));

category.add(lv);

 

counter ++;

 

}

categoryinner.put("category", gson.toJson(category));

System.out.println(categoryinner);

}catch(Exception ex) {/* if any error occurs */}

 

return categoryinner;

 

 

}

 

 

/**

* @description - Build the Json for datasets

* @param {String} seriesname - Lets you specify the series

* name for a particular dataset.

* @param {String} seriescolumnname - Name of the column from table

* @param {ResultSet} - The object of ResultSet maintains a

* cursor pointing to a particular row of data.

* @param {Gson} gson - Gson is a Java library that can be used

* to convert Java Objects into their JSON representation.

* @return {Map Object}

- */

public Map buildDataset(String seriesname, String seriescolumnname, ResultSet rs, Gson gson ) {

 

Map<String, String> datasetinner = new HashMap<String, String>();

datasetinner.put("seriesname", seriesname);

 

ArrayList makedata = new ArrayList();

try {

//is used to move the cursor to the first row in result set object.

rs.beforeFirst();

while(rs.next()) {

Map<String, String> preparedata = new HashMap<String, String>(); 

preparedata.put("value", rs.getString(seriescolumnname));

makedata.add(preparedata); 

}

datasetinner.put("data", gson.toJson(makedata));

 

} catch(Exception err) {/* if any error occurs */}

 

return datasetinner;

}

%>

 

<!-- Step 5: Render the chart -->  

 

<%= mslineChart.render() %>

 

</body>

</html>

[Ayan Bhadury]

Hi,

Thanks for sharing the information.

It seems like the categories and the dataset object are not structured properly kindly check the implementation properly once.

For reference you can take this example http://jsfiddle.net/8p4kjwjy/

Please note you to form a similar structure as in the above example, in order to render a multi series chart.

[Vijaya Bhaskar V]

On 7/10/2017 at 11:44 AM, Ayan Bhadury said:

Hi,

Thanks for sharing the information.

It seems like the categories and the dataset object are not structured properly kindly check the implementation properly once.

For reference you can take this example http://jsfiddle.net/8p4kjwjy/

Please note you to form a similar structure as in the above example, in order to render a multi series chart.

Hi ,

Can you please help me to resolve the issue .

i cant be able to generate the charts .

 

[Ayan Bhadury]

Please download and refer the sample from the attachment 

Fusioncharts_multiseriesline.zip