How to get Oracle variables into Amchart in JSP

Question

I have a very basic JSP code. It returns 3 variables from Oracle Database

    String program_name = "";
        Integer projects = 0;
        Integer operations = 0;

        statement = connection.createStatement();
        if(deger.equals("All Priorities"))
        {
        rs = statement.executeQuery("SELECT PROGRAM_NAME prog_name, (SELECT COUNT(PRJ_TYPE) FROM EAD_PRJ_CURR_LIST LST WHERE LST.PROGRAM_ID= PG.PGM_PROGRAM_ID AND LST.PRJ_TYPE = 'Project') Project,(SELECT COUNT(PRJ_TYPE) FROM EAD_PRJ_CURR_LIST LST WHERE LST.PROGRAM_ID= PG.PGM_PROGRAM_ID AND LST.PRJ_TYPE = 'Operation') Operation FROM KCRT_FG_PFM_PROGRAM PG");
        }
        else
        {
        rs = statement.executeQuery("SELECT PROGRAM_NAME prog_name, (SELECT COUNT(PRJ_TYPE) FROM EAD_PRJ_CURR_LIST LST WHERE LST.PROGRAM_ID= PG.PGM_PROGRAM_ID AND LST.PRJ_TYPE = 'Project') AS Project,(SELECT COUNT(PRJ_TYPE) FROM EAD_PRJ_CURR_LIST LST WHERE LST.PROGRAM_ID= PG.PGM_PROGRAM_ID AND LST.PRJ_TYPE = 'Operation') as Operation FROM KCRT_FG_PFM_PROGRAM PG");
        }
        while(rs.next())



          program_name = rs.getString("prog_name");
          projects = rs.getInt("Project");
          operations = rs.getInt("Operation");

This is the code for the chart, it is a bar chart -

    <!-- Chart code -->
                <script>
                var chart = AmCharts.makeChart("chartdiv", {
                    "type": "serial",
                     "theme": "light",
                    "categoryField": "List_Of_Programs",
                    "rotate": true,
                    "startDuration": 1,
                    "categoryAxis": {
                        "gridPosition": "start",
                        "position": "left"
                    },
                    "trendLines": [],
                    "graphs": [
                        {
                            "balloonText": "Projects:[[value]]",
                            "fillAlphas": 0.8,
                            "id": "AmGraph-1",
                            "lineAlpha": 0.2,
                            "title": "Projects",
                            "type": "column",
                            "valueField": "Projects"
                        },
                        {
                            "balloonText": "Operations:[[value]]",
                            "fillAlphas": 0.8,
                            "id": "AmGraph-2",
                            "lineAlpha": 0.2,
                            "title": "Operations",
                            "type": "column",
                            "valueField": "Operations"
                        }
                    ],
                    "guides": [],
                    "valueAxes": [
                        {
                            "id": "ValueAxis-1",
                            "position": "top",
                            "axisAlpha": 0
                        }
                    ],
                    "allLabels": [],
                    "balloon": {},
                    "titles": [],
                    "dataProvider": [
                        {
                            "List_Of_Programs": <%=projects%>,
                            "Projects": <%=projects%>,
                            "Operations": <%=operations%>
                        }],
                    "export": {
                        "enabled": true
                     }

                });
                </script>


                  <div id="chartdiv"></div>

I have both this encapsulated in a JSP file. The code is compiling fine, without any issues. However, I need help with the logic of printing the data from the earlier query (which has around 20 rows) into the chart.

Please assist.

xorspark · Answer 1 · 2017-08-10T12:50:22.073

The dataProvider needs to be an array of objects (note, plural). What you're doing appears to be setting individual arrays(?) into each property into a single object in the dataProvider array. The dataProvider object should resemble something like this (using random data as an example):

"dataProvider": [{
  "List_Of_Programs": "Program A",
  "Projects": 13,
  "Operations": 10
}, {
  "List_Of_Programs": "Program B",
  "Projects": 25,
  "Operations": 28
}. 
// continue for each array element
]

(Note that this format is used in every demo on AmCharts' website.)

I'm not an expert on JSP or on best practices (although this popped up in my brief foray into JSP, which is probably something you should look into), but you can use a JSON library like org.json to put all of your values into an array of objects and then serialize the array into a string similar to the above example. For instance, using your code above:

//assumes you're using the org.json library
JSONArray jsonArray = new JSONArray();
while(rs.next()) {
  JSONObject obj = new JSONObject();
  obj.put("List_Of_Programs", rs.getString("prog_name"));
  obj.put("Projects", rs.getInt("Project"));
  obj.put("Operations", rs.getInt("Operation"));
  jsonArray.put(obj);
}

Once you have your array, print it out in your makeChart call in the dataProvider section:

var chart = AmCharts.makeChart("chartdiv", {
   // other properties omitted
  "dataProvider": <%=jsonArray%> //will serialize as the array from above
});

How to get Oracle variables into Amchart in JSP

1 Answers1