Generating Google Chart on the fly with JSP_问答_开发者

I am using a Google Line Chart to graph values pulled from a database using JSP. I currently have have it working. The data.addColumn statements are generated from a database query when the page loads.

My problem is that I also want to have a couple of comboboxes that can be used to pick min and max values for the x-axis and then update the graph. Currently the only way I can think to accomplish this is refresh the page with new get parameters.

I would prefer to somehow only refresh the chart itself without reloading the entire page. Is this possible? Is there a way to overwrite the script tag and then reload the chart? Or is there some other way?

EDIT: I am having trouble figuring out how to pass the data from the database over to the loading page. Here is my code:

loader.html (page the holds the chart and date fields):

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <script type="text/javascript">
        google.load('visualization', '1', {packages: ['corechart']});
    </script>
    <script type="text/javascript">
    开发者_如何学运维    function loadChart()
        {
            var xmlhttp;            
            var minDate = document.getElementById('startDate').value;
            var maxDate = document.getElementById('endDate').value;

            if (!minDate)
                minDate = 'none';
            if (!maxDate)
                maxDate = 'none';

            if (window.XMLHttpRequest)
            {// code for IE7+, Firefox, Chrome, Opera, Safari
                xmlhttp = new XMLHttpRequest();
            }
            else
            {// code for IE6, IE5
                xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
            }
            xmlhttp.onreadystatechange=function()
            {
                if (xmlhttp.readyState == 4 && xmlhttp.status == 200)
                {
                    document.getElementById('lineChart').innerHTML = xmlhttp.responseText;
                }
            }
            xmlhttp.open("GET", "chart-loader.jsp?min=" + minDate + '&max=' + maxDate, true);
            xmlhttp.send();
        }
    </script>
</head>

<body>
    <div id="container">

        <div id="lineChart"></div>

        <div id="startDateBox">
            <label for="startDate">Min Date:</label>
            <input id="startDate" type="text" />
        </div>
        <div id="endDateBox">
            <label for="endDate">Max Date:</label>
            <input id="endDate" type="text" />
        </div>
        <input type="button" value="Update" id="updateBtn" onclick="loadChart()" />

    </div> <!-- End container -->
</body>
</html>

chart-loader.jsp (supposed to get data and load the chart)

<%@ page import="java.sql.*, java.util.ArrayList" %>
<%      
    Connection conn;

    try {
        Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://...", "user", "pass");
    }
    catch(SQLException e) 
    {
        out.println("SQLException: " + e.getMessage() + "<br/>");
        while((e = e.getNextException()) != null)
            out.println(e.getMessage() + "<br/>");
        throw new UnavailableException(this, "Cannot connect with the specified database.");
    }

    String data = "";
    String minDate = (String)request.getParameter("min");
    String maxDate = (String)request.getParameter("max");
    try
    {
        ResultSet rs;
        Statement stmt = conn.createStatement();
        if (minDate.equals("none") || maxDate.equals("none")) // First load, use whole table
        {
            rs = stmt.executeQuery("SELECT ...");
        }
        // ... more possible queries

        ArrayList<String> ptVals = new ArrayList<String>();
        ArrayList<String> colDates = new ArrayList<String>();

        while (rs.next())
        {
            ptVals.add(rs.getString("avgvalue"));
            colDates.add(rs.getString("collectiondate"));
        }           
        // ptVals.size()
        for (int i = 0; i < 5; i++)
            data += "\t\t\tdata.addRow(['" + colDates.get(i) + "'," + ptVals.get(i) + "]);\n";  

        // ... Use out.print to spit out script. 
    }
    catch(SQLException e)
    {
        data = "SQLException: " + e.getMessage() + "<br/>";
        while((e = e.getNextException()) != null)
            data += e.getMessage() + "<br/>";           
    }       
    finally
    {   // Clean up resources, close the connection.
        if(conn != null)
        {
            try { conn.close(); }
            catch (Exception ignored) {}
        }
    }
%>

Original script to load the chart

<script type="text/javascript">
    function drawVisualization() 
    {
        // Create and populate the data table.
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Date');
        data.addColumn('number', 'Temperature');
        data.addRow(["01/01/11", 70.2]);
        data.addRow(["01/02/11", 70.0]);
        data.addRow(["01/03/11", 69.8]);
        data.addRow(["01/04/11", 70.1]);
        // Create and draw the visualization.
        new google.visualization.LineChart(document.getElementById('lineChart')).
            draw(data, {backgroundColor: 'transparent',
                        width: 700, height: 400,
                        legend: 'none',
                        hAxis: {title: 'Dates', titleTextStyle: {color: 'black', fontSize: 12, fontName: 'Verdana, Arial'}},
                        vAxis: {title: 'Temperature', titleTextStyle: {color: 'black', fontSize: 12, fontName: 'Verdana, Arial'}},
                        chartArea: {left: 80, top: 20}
                    }
                );
    }

    google.setOnLoadCallback(drawVisualization);
</script>

Thanks, Ryan

We do exactly that where I work (refresh a google chart I mean). What we do is have the page load without the chart, and just Ajax-load it into whatever container it needs to be in. Then, when you change anything on it, have the ajax re-load it, sending whatever parameters you need to to the JSP (which gets a new chart).

EDIT: In case that didn't make sense, the chart itself is actually on it's own JSP that gets injected into the page (and refreshed) independently.

So our process is this:

Render main page (charts are NOT rendered yet). On this page, we put in something like this:

<div id="chartForData1"></div>

<script>Ajax_inject('chartForData1', 'pageThatGeneratesData1');</script>

Where Ajax_inject is just our wrapper for an Ajax call that loads data into an element. Now, when the page is rendered on the user's computer, that Ajax_inject (and all the others) will execute, thereby loading into the relevant div the corresponding rendered JSP. Those JSP's sole purpose to take a couple parameters (or none and use defaults) and render us a nice and pretty google chart.

Does that make more sense?

I was able to get this to work with Google Visualization API. Here's how:

I have an HTML page that has a container div for the graph. <div id="lineChart"></div>

Next I have two JavaScript functions in the HTML page. The getChartData() function is an AJAX function that gets the date range from two input fields and sends them to a JSP page. The JSP page uses the dates to query the database for records in that range, formats them as a JavaScript object, and sends it back to the HTML page using out.print().

Once the HTML page receives the data, it uses eval() to set the received text as a JavaScript object. Finally, the data object is sent to the second JavaScript function, loadChart(), which uses the data to call the Google functions and load the chart into the div.

Note: using eval() to set the responseText to a JavaScript object is what really makes it all work. This allows you to pass many pieces of data back. You simply pull them out of the JS object once you're back in the HTML page. You may even pass several JS objects inside each other.

var paramsObj;
var dataObj;
eval('paramsObj = ' + xmlhttp.responseText + ";");
eval('dataObj = paramsObj.data;');
loadChart(dataObj, paramsObj.title, paramsObj.xLabel);

In this case I'm passing three parameters in one JS object. paramsObj.data is the chart data, which came as a nested object which is formatted according to the Google Visualization API. I had to use eval() again to set the nested JS object as a separate variable, or it doesn't pass to the loadChart() function correctly. paramsObj.title and paramsObj.xLabel are simply strings.