Populating chart from javascript data object object issue

Question

I'm currently trying to implement the use of Google Charts on my JSP page. I'm trying to return the result of an sql statement into an arraylist, from which I want to take the information from the arrayList and use it to populate a google chart.

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.util.ArrayList"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<%!public static String[] getLiveData() throws SQLException, ClassNotFoundException{
    ArrayList<String> a = new ArrayList<String>();

    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/project","root", "nbuser");
    PreparedStatement ps = con.prepareStatement("SELECT department, projectname, priority, cores, disk_space FROM project.booking GROUP BY department");
    ResultSet rs = ps.executeQuery();

    while(rs.next())
    {
        a.add(rs.getString(1));
        a.add(rs.getString(4));
    }

    return (String[]) a.toArray(new String[a.size()]);
}%>
<html>
  <head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = google.visualization.arrayToDataTable([ISSUE TO RETURN HERE]);


        var options = {
          title: 'Company Performance'
        };

        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>

  </head>

continues to call the graph etc in the body

I honestly feel like I'm going about it the wrong way, but I'm unsure how to get the data in the array and return it to create a graph while on my jsp page.

I'm not very good with java so I apologise if the approach is completely off, any help would be appreciated.

EDIT

    <head>
        <script type="text/javascript">

            var theData = [ // Start of JavaScript data object
            <%
                Class.forName("com.mysql.jdbc.Driver");
                Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/project", "root", "nbuser");
                PreparedStatement ps = con.prepareStatement("SELECT department,SUM(cores) FROM project.booking GROUP BY department;");
                ResultSet rs = ps.executeQuery();

                while (rs.next()) {
            %>
                    [" <%= rs.getString(1)%>",<%= rs.getInt(2)%>],
            <%
                };
                // End of JavaScript object holding the data
            %>
                ];
        </script>

        <script type="text/javascript" src="https://www.google.com/jsapi"></script>
        <script type="text/javascript">
            google.load("visualization", "1", {packages:["corechart"]});
            google.setOnLoadCallback(drawChart);
           function drawChart() {
                var data = google.visualization.arrayToDataTable([['Department', 'Cores']].concat(theData), false);

                var options = {
                    title: 'Initial graph test'
                };
                var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
                chart.draw(data, options);
            }
        </script>

    </head>
    <body>
        <div id="chart_div" style="width: 900px; height: 500px;"></div>
        <a href="/ProjectAndBackend/System?action=livecharts">Live Charts</a>
    </body>
</html>

Final fixed implementation for users viewing

Lee Meador · Accepted Answer · 2013-04-05 22:09:08Z

1

Yes, you are going about it the wrong way. You have to call the Java to get the values for the array and find a way to write it into the web page so that it can be read by some javascript and passed to Google Charts.

For example the HTML might need to look like this:

<html>
<head>
<script type="text/javascript">

var theData = {
    {"name1",1}, // <-- write this line with JSP below
    {"name2",2}, // <-- write this line with JSP below
};
</script>
</head>

And so forth with the rest of the Web page.

So you have to generate some JSP code to do that which will look something like this. I'm assuming your Java is correctly doing the JDBC stuff. Notice that we have the HTML just there and then we add scriplets for logic and then fall back into the HTML but have JSP expressions to output the data in among the HTML parts. (When I say HTML, its really a misnomer since its outputting JavaScript but cut me some slack ....)

    <html>
    <head>
    <script type="text/javascript">

    var theData = [ // Start of JavaScript data object
<% 
 Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/project","root", "nbuser");
    PreparedStatement ps = con.prepareStatement("SELECT department, projectname, priority, cores, disk_space FROM project.booking GROUP BY department");
    ResultSet rs = ps.executeQuery();

    while(rs.next())
    {
      ['Department', 'Cores'],
%>
      [" <%= rs.getString(1) %>", "<%= rs.getString(4)) %>"],
<%
    ]; // End of JavaScript object holding the data
%>
    };
    </script>
    </head>

Doing it this way gets you a javascript object holding the data. You will need to arrange the contents of that object however you need the data. There is more information on how to do this here https://developers.google.com/chart/interactive/docs/datatables_dataviews

Read the section toward the bottom where there are several examples of JavaScript objects formatted for display along with the JavaScript code to call Google Charts correctly.

Edit

I added in the headings in the above code so that you don't have to concat them in place. Put them in the JavaScript code to initialize the data object in the first place. If you look at the history of this answer, you can see what line I changed. To use it, remove the part in your new code where you concat the headings onto the front.

edited Apr 5, 2013 at 22:09

answered Apr 4, 2013 at 19:04

Lee Meador

13k2 gold badges38 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Curious_Bop Over a year ago

Thankyou for the very fast response, slightly lost on where I'm putting everything, I use the jsp code above to to pull the data from the database then name each string, then when I add data to the to the google charts arrayToDataTable([name1, name 2]) or am I completely off again

Lee Meador Over a year ago

Added a few notes on that. You'll need to adapt it to whatever kind of chart you are making.

Curious_Bop Over a year ago

Thanks for that Lee, I think I've changed it to how you've suggested but I'm still having a slight issue in populating the google charts, editted original question with current implementation

Collectives™ on Stack Overflow

Populating chart from javascript data object object issue

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related