Wednesday, July 3, 2013

Dynamic Chart Using Google Chart API, Jquery Ajax, JDBC & JSON

Hello Friends,

I was exploring and playing with the codes of various Charts APIs since many days, First it was JFreeChart for Java Applications attracted my attention and I loved using the same. Then I started looking upon JavaScript based Charting APIs and while Googling about the same what caught my eye was Google Chats. I have also mentioned in my earlier posts that I'm a Big Fat fan of Google and love using their APIs.

Regarding Google Charts, so I started to explore Google Charts, tried those extremely simple yet useful examples given for various types of charts pie, bar, line etc...and without any setup needed they worked like WOW! 

Now the thought came to me that using this examples, I need my JavaScript client side charts to display the data stored at remote database at server, And so I started my work to accomplish this.

What I did was created a Java Srevlet which connects to my database, get the data from table, converts the data to JSON Format and sends the same as a Response, And At Client Side, Jquery - Ajax Function which will call this Servlet, Gets the data which is returned as Response, parse the Json Data to javascript array which is being used by Google Chart API to Display Charts.

Following Is the HTML / JavaScript Client Side Code,

<html>
    <head>
        <script type="text/javascript" src="js/json2.js"></script>
        <script type="text/javascript" src="js/jsonStringify.js"></script>
        <script type="text/javascript" src="js/jquery-1.9.1.js"></script>
        <script type="text/javascript" src="https://www.google.com/jsapi"></script>
        <script type="text/javascript">
            ////////////// Getting data from server
            var queryObject="";
            var queryObjectLen="";
            $.ajax({
                type : 'POST',
                url : 'http://localhost:8084/GoogleChartTest/GetStudentData',
                dataType:'json',
                success : function(data) {
                    queryObject = eval('(' + JSON.stringify(data) + ')');
                    queryObjectLen = queryObject.studDetails.length;
                },
                error : function(xhr, type) {
                    alert('server error occoured')
                }
            });
            var arrdata="";
            google.load("visualization", "1", {packages:["corechart"]});
            google.setOnLoadCallback(drawChart);
            function drawChart() {
                arrdata = new google.visualization.DataTable();
                arrdata.addColumn('string', 'Names');
                arrdata.addColumn('number', 'Physics');
                arrdata.addColumn('number', 'Chemistry');
                arrdata.addColumn('number', 'Biology');
                for(var i=0;i<queryObjectLen;i++){
                    var studname = queryObject.studDetails[i].studname;
                    var physics = queryObject.studDetails[i].physics;
                    var chemistry = queryObject.studDetails[i].chemistry;
                    var biology = queryObject.studDetails[i].biology;
                    arrdata.addRows([
                        [studname,parseInt(physics),parseInt(chemistry),parseInt(biology)]
                    ]);
                }
                var options = {
                    title: 'Students Performance'
                };
                var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
                chart.draw(arrdata, options);
            }
        </script>
    </head>
    <body>
        <div id="chart_div" style="width: 900px; height: 500px;"></div>
    </body>
</html>

Following is the Server Side Servlet Code Snippet, 


PrintWriter out = response.getWriter();
        Connection con = null;
        ResultSet resultSet = null;
        List<JSONObject> studDetails = new LinkedList<JSONObject>();
        JSONObject responseObj = new JSONObject();
        
            Class.forName("<jdbc driver>").newInstance();
            String url = "<jdbc url>";
            con = DriverManager.getConnection(url);
            String query = null;
            query = "SELECT * from APP.STUDENTS";
            PreparedStatement statement = con.prepareStatement(query);
            resultSet = statement.executeQuery();
            JSONObject studObj = null;
            while (resultSet.next()) {
                String studname = resultSet.getString("studname");
                String physics = resultSet.getString("physics");
                String chemistry = resultSet.getString("chemistry");
                String biology = resultSet.getString("biology");
                studObj = new JSONObject();
                studObj.put("studname", studname);
                studObj.put("physics", physics);
                studObj.put("chemistry", chemistry);
                studObj.put("biology", biology);
                studDetails.add(studObj);
            }
            responseObj.put("studDetails", studDetails);

            out.write(responseObj.toString());


And Finally, The O/P,  i.e. chart is as follows,


Preview of the Data in the Database table from which the chart is displayed is as follows,



That is All From my end for now, just thought of sharing what I did so could be useful for somebody.

Happy Coding!

~ Mayuri :)