Implementing basic report with input control using Highcharts, AngularJS and Spring Boot
As a sequel to my previous blog post, this time I will show you how to implement simple report with input control based on the same technology stack. The user will have the ability to select a car manufacturer from an input control and then execute report which represents the selected manufacturer quarter sales data.
How to Complete this Guide
You can start from scratch and create the described classes and resources independently or you can clone the project with:
| git clone https://github.com/boykodimitroff/basic-report-with-input-control.git |
Let’s begin!
Preparing the data base
For the purpose of this project we will use in-memory H2 database. Spring-boot provides database initialization out of the box. The only thing you should do is to create schema.sql file which contains the CREATE sql statements of your tables. If you want to populate the newly created tables you can create another file called data.sql which will defined the INSERT statements.
| src/main/resources/schema.sql |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE CAR_MANUFACTURER ( ID INTEGER PRIMARY KEY, NAME TEXT ); CREATE TABLE SALES ( ID INTEGER, QUARTER TEXT, AMOUNT DOUBLE ); |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
--POPULATE COUNTRY INSERT INTO CAR_MANUFACTURER VALUES (1, 'BMW'); INSERT INTO CAR_MANUFACTURER VALUES (2, 'Audi'); INSERT INTO CAR_MANUFACTURER VALUES (3, 'Mercedes-Benz'); --POPULATE SALES INSERT INTO SALES VALUES (1, '2016-Q1', 1250); INSERT INTO SALES VALUES (1, '2016-Q2', 1555); INSERT INTO SALES VALUES (1, '2016-Q3', 900); INSERT INTO SALES VALUES (1, '2016-Q4', 2200); INSERT INTO SALES VALUES (2, '2016-Q1', 3400); INSERT INTO SALES VALUES (2, '2016-Q2', 2333); INSERT INTO SALES VALUES (2, '2016-Q3', 1223); INSERT INTO SALES VALUES (2, '2016-Q4', 2233); INSERT INTO SALES VALUES (3, '2016-Q1', 1495); INSERT INTO SALES VALUES (3, '2016-Q2', 2323); INSERT INTO SALES VALUES (3, '2016-Q3', 988); INSERT INTO SALES VALUES (3, '2016-Q4', 788); |
| src/main/resources/data.sql |
Building the Back-end
My previous blog post can be used as a reference in case you don’t know how to create and import spring boot application.
First we will build REST Controller which will be used to retrieved the data displayed in the input control.
Our REST Controller will handle GET request at /input-control/data URL and will return 200 OK response with JSON object representing the input control values. The response looks like this :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
{ [ { "text":"BMW", "value":1 }, { "text":"Audi", "value":2 }, { "text":"Mercedes-Benz", "value":3 } ] } |
Create the following Data Transfer Object class:The text field will be the visible part populated in a simple select html element. The value is an id which identifies particular car manufacturer id. This id will be then passed to the back-end when the report is executed. The fields are represented by the following back-end DTO object.
| src/main/java/eu/dreamix/dto/InputControlDTO.java |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
package eu.dreamix.dto; /*** Created by bdimitrov on 1/22/17. */ public class InputControlDTO { private String text; private Integer value; public InputControlDTO(String text, Integer value) { this.text = text; this.value = value; } public String getText() { return text; } public Integer getValue() { return value; } } |
| src/main/java/eu/dreamix/rest/InputControlResource.java |
Create the following REST Controller class:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
package eu.dreamix.rest; import eu.dreamix.dao.InputControlDAO;import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.MediaType; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RestController; /** * REST Controller which retrieves input controls data. * Created by bdimitrov on 1/22/17. */ @RestController @RequestMapping("/input-control") public class InputControlResource { @Autowired private InputControlDAO inputControlDAO; @RequestMapping(value = "/data", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE) public ResponseEntity<Object> getData() { return ResponseEntity.ok(inputControlDAO.getData()); } } |
Create the following DAO class:As you can see the REST Controller uses Data Access layer which returns the input controls data. Let’s build the Data Access Object which will make a SQL query to the in-memory database to retrieve the relevant information.
| src/main/java/eu/dreamix/InputControlDAO.java |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
package eu.dreamix.dao; import eu.dreamix.dto.InputControlDTO;import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.ResultSetExtractor; import org.springframework.stereotype.Repository; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; /** * DAO class which retrieves data populated in the input controls of the report * Created by bdimitrov on 1/22/17. */ @Repository public class InputControlDAO { private static final String TEXT_COLUMN_NAME = "TEXT"; private static final String VALUE_COLUMN_NAME = "VALUE"; @Autowired private JdbcTemplate jdbcTemplate; public List<InputControlDTO> getData() { return jdbcTemplate.query(getQuery(), new ResultSetExtractor<List<InputControlDTO>>() { @Override public List<InputControlDTO> extractData(ResultSet resultSet) throws SQLException, DataAccessException { List<InputControlDTO> inputControlDTOs = new ArrayList<InputControlDTO>(); while(resultSet.next()) { inputControlDTOs.add(new InputControlDTO(resultSet.getString(TEXT_COLUMN_NAME), resultSet.getInt(VALUE_COLUMN_NAME))); } return inputControlDTOs; } }); } private String getQuery() { return "SELECT ID AS VALUE, NAME AS TEXT FROM CAR_MANUFACTURER ORDER BY ID"; } } |
Now we need to create the relevant Rest Controller, DAO and DTO class for the report or respectively SalesDataResource, SalesDAO and ChartDTO.Run the application and check the result at http://localhost:8080/input-control/data.
| src/main/java/eu/dreamix/rest/SalesDataResource |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
package eu.dreamix.rest; import eu.dreamix.dao.SalesDAO;import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.MediaType; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; /** * REST Controller which retrieves data used in the chart. * @author Boyko Dimitrov on 11/28/16. */ @RestController @RequestMapping("/sales") public class SalesDataResource { @Autowired private SalesDAO salesDAO; @RequestMapping(value = "/data", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE) public ResponseEntity<Object> getData(@RequestParam(required = true) Integer carManufacturerId) { return ResponseEntity.ok(salesDAO.getData(carManufacturerId)); } } |
| src/main/java/eu/dreamix/dao/SalesDAO.java |
In order the REST Controller to serve its purpose it needs a required parameter which is the car manufacturer id passed from the input control.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
package eu.dreamix.dao; import eu.dreamix.dto.ChartDTO;import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.ResultSetExtractor; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.stereotype.Repository; import java.sql.ResultSet; import java.sql.SQLException; /** * DAO class which retrieves reports data populated in the report's chart * Created by bdimitrov on 1/22/17. */ @Repository public class SalesDAO { private static final String QUARTER_COLUMN_NAME = "QUARTER"; private static final String AMOUNT_COLUMN_NAME = "AMOUNT"; private static final String CAR_MANUFACTURER_LABEL = "carManufacturerId"; @Autowired private NamedParameterJdbcTemplate namedParameterJdbcTemplate; public ChartDTO getData(Integer carManufacturerId) { return namedParameterJdbcTemplate.query(getQuery(), new MapSqlParameterSource(CAR_MANUFACTURER_LABEL, carManufacturerId), new ResultSetExtractor<ChartDTO>() { @Override public ChartDTO extractData(ResultSet resultSet) throws SQLException, DataAccessException { ChartDTO chartDTO = new ChartDTO(); while(resultSet.next()) { chartDTO.addXaxisValue(resultSet.getString(QUARTER_COLUMN_NAME)); chartDTO.addYaxisValue(resultSet.getDouble(AMOUNT_COLUMN_NAME)); } return chartDTO; } }); } private String getQuery() { return "SELECT "+QUARTER_COLUMN_NAME+", "+AMOUNT_COLUMN_NAME+" FROM CAR_MANUFACTURER CM " + "JOIN SALES S ON CM.ID = S.ID " + "WHERE CM.ID = :" +CAR_MANUFACTURER_LABEL+"\n"+ "ORDER BY "+QUARTER_COLUMN_NAME; } } |
package eu.dreamix.dto;
| src/main/java/eu/dreamix/dto/ChartDTO.java |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
import java.util.ArrayList;import java.util.List; /** * Created by bdimitrov on 1/22/17. */ public class ChartDTO { private List<String> xAxis; private List<Double> yAxis; public List<String> getxAxis() { return xAxis; } public List<Double> getyAxis() { return yAxis; } public void addXaxisValue(String value) { if(xAxis == null) { xAxis = new ArrayList<>(); xAxis.add(value); } else { xAxis.add(value); } } public void addYaxisValue(Double value) { if(yAxis == null) { yAxis = new ArrayList<>(); yAxis.add(value); } else { yAxis.add(value); } } } |
Run the application and check the result at http://localhost:8080/sales/data?carManufacturerId=1The values populated in the ChartDTO is later passed to the Highcharts library which is responsible for drawing a responsive chart.
Building the Front-end
Now when we have the data, we need to visualize it.
Create the following file:
| src/main/resources/static/index.html |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
<!DOCTYPE html> <html lang="en" ng-app="BasicReport"> <head> <meta charset="UTF-8"> <title>Title</title> <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.5.6/angular.min.js"></script> <script src="https://code.highcharts.com/highcharts.js"></script> <script src="/app.js"></script> <script src="/input-controls/rest.service.js"></script> <script src="/report/rest.service.js"></script> <script src="/chart/chart.service.js"></script> </head> <body> <div ng-controller="BasicReportController"> <select ng-options="item.value as item.text for item in inputControlData" ng-model="carManufacturerId"></select> <button ng-click="executeReport(carManufacturerId)" ng-disabled="carManufacturerId === undefined">Execute report</button> <div id="reportChart"></div> </div> </body> </html> |
Create the app.jsThe only thing I will mention here is that we include the AngularJS, Highcharts and application components(input control service, report service and chart service) separated in independent modules.
| src/main/resources/static/app.js |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
(function() { 'use strict'; var app = angular.module('BasicReport', []); app.controller('BasicReportController', BasicReportController); BasicReportController.$inject = [ '$scope', 'InputControlsResource', 'ReportDataResource', 'ReportChartService']; function BasicReportController($scope, InputControlsResource, ReportDataResource, ReportChartService) { InputControlsResource.getData().success(function(response) { $scope.inputControlData = response; }); $scope.executeReport = function(id) { ReportDataResource.getData(id).then(function(response) { ReportChartService.populateChart(response.data); }) } } })(); |
Let’s build the input controls service which will call the previously build REST Controller which serves the /input-control/data URL.The controller injects all dependencies which are used to get the input controls data, the reports data and then to populate the chart. The executeReport function is called when the button defined in the index.html is clicked.
| src/main/resources/static/input-control/input-control.service.js |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
(function() { 'use strict' var app = angular.module("BasicReport"); app.factory("InputControlsResource", InputControlsResource); InputControlsResource.$inject = ['$http']; function InputControlsResource($http) { var service = {}; service.getData = function() { return $http({ method : 'GET', url : '/input-control/data' }); } return service; } })(); |
| src/main/resources/static/report/report.service.js |
We need to create also the service which will call the REST Controller for the report’s data on /sales/data URL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
(function() { 'use strict' var app = angular.module("BasicReport"); app.factory("ReportDataResource", ReportDataResource); ReportDataResource.$inject = ['$http']; function ReportDataResource($http) { var service = {}; service.getData = function(id) { return $http({ method : 'GET', url : '/sales/data', params : { carManufacturerId : id } }); } return service; } })(); |
| src/main/resources/static/chart/chart.service.js |
The only thing which has left is to implement the chart service which will populate the defined div container in the index.html
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
(function () { 'use strict'; angular.module('BasicReport').factory('ReportChartService', ReportChartService); function ReportChartService() { var service = {}; service.populateChart = function (data) { var chart = new Highcharts.Chart({ chart: { type: 'column', renderTo: 'reportChart' }, title: { text: 'Sales Data' }, xAxis: { categories: data.xAxis, }, plotOptions: { column: { stacking: 'normal', } }, series: [ { data: data.yAxis }] }) } return service; } })(); |
Run the application and see the result at http://localhost:8080. Execute the report with different input controls values and see how the chart represents the data populated in the in-memory database
.
