Server-side operations with Java & Oracle

Learn how to perform server-side operations using the Oracle Database with a complete reference implementation.

In this guide we will show how large data sets, which are too big be loaded directly into the browser, can be managed by performing server-side operations inside the Oracle database.

We will develop a financial reporting application that demonstrates how data can be lazy-loaded as required, even when performing group, filter, sort and pivot operations.

The reference implementation covered in this guide is for demonstration purposes only. If you use this in production it comes with no warranty or support.

The source code can be found here: https://github.com/ag-grid/ag-grid-server-side-oracle-example

Overview

When designing a grid based application, one of the key considerations is how much data needs to be sent from the server to the client? As a developer using ag-Grid you won't need to switch between grids based on the answer to this question, instead just select the appropriate Row Model used by the grid.

Client-side Row Model

The simplest approach is to send all row data to the browser in response to a single request at initialisation. For this use case the Client-side Row Model has been designed.

This scenario is illustrated below where 10,000 records are loaded directly into the browser:

The Client-side Row Model only renders the rows currently visible, so the upper limit of rows is governed by the browsers memory footprint and data transfer time, rather than any restrictions inside the grid.

Server-side Row Model

However many real world applications contain much larger data sets, often involving millions of records. In this case it simply isn't feasible to load all the data into the browser in one go. Instead data will somehow need to be lazy-loaded as required and then purged to limit the memory footprint in the browser?

This is precisely the problem the Server-side Row Model addresses, along with delegating server-side operations such as filtering, sorting, grouping and pivoting.

The following diagram shows the approach used by the Server-side Row Model. Here there are 10 million records, however the number of records is only constrained by the limits of the server-side:

As the user performs operations such as sorting and grouping, the grid issues requests to the server that contains all the necessary metadata required, including which portion of data should be returned based on the users position in the data set.

The browser will never run out of heap space as the grid will automatically purge out-of-range records.

Throughout the rest of this guide we will demonstrate the power of the Server-side Row Model with the aid of a Java service connected to an oracle database.

Prerequisites

It is assumed the reader is already familiar with Java, Maven, Spring Boot and Oracle.

This example was tested using the following versions:

  • ag-grid-enterprise (v18.0.0)
  • Java(TM) SE Runtime Environment (build 1.8.0_162-b12)
  • Java HotSpot(TM) 64-Bit Server VM (build 25.162-b12, mixed mode)
  • Apache Maven (3.5.2)
  • Oracle 12c Release 2 (12.2.0.1)
  • Oracle JDBC Driver (ojdbc7-12.1.0.2)

Due to Oracle license restrictions the Oracle JDBC driver is not available in the public Maven repository and should be manually installed into your local Maven repository like so:

$ mvn install:install-file -Dfile={Path/to/ojdbc7.jar} -DgroupId=com.oracle -DartifactId=ojdbc7 -Dversion=12.1.0.2 -Dpackaging=jar

Also ensure the Oracle JDBC driver version also matches the Oracle POM dependency:

// pom.xml <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc7</artifactId> <version>12.1.0.2</version> </dependency>

Download and Install

Clone the example project using: git clone https://github.com/ag-grid/ag-grid-server-side-oracle-example.git Navigate into the project directory: cd ag-grid-server-side-oracle-example Install project dependencies and build project using: mvn clean install

To confirm all went well you should see the following maven output:

Configure Oracle

Update the Oracle configuration accordingly with your database connection details:

// src/main/resources/application.properties spring.datasource.url=jdbc:oracle:thin:@//localhost:1521/orcl spring.datasource.username=system spring.datasource.password=oracle

Setup Data

Run the following script in Oracle to create the table required in this example:

// src/main/resources/schema.sql CREATE TABLE trade ( product VARCHAR(255), portfolio VARCHAR(255), book VARCHAR(255), tradeId NUMBER, submitterId NUMBER, submitterDealId NUMBER, dealType VARCHAR(255), bidType VARCHAR(255), currentValue NUMBER, previousValue NUMBER, pl1 NUMBER, pl2 NUMBER, gainDx NUMBER, sxPx NUMBER, x99Out NUMBER, batch NUMBER );

Next run the following utility to populate it with data:

src/test/java/com/ag/grid/enterprise/oracle/demo/TradeDataLoader.java

Run the App

From the project root run: mvn spring-boot:run

If successful you should see something like this:

To test the application point your browser to: localhost:9090

Server-side Row Model Interfaces

Our Java service will use the following request and response objects:

// src/main/java/com/ag/grid/enterprise/oracle/demo/request/ServerSideGetRowsRequest.java public class ServerSideGetRowsRequest implements Serializable { private int startRow, endRow; // row group columns private List<ColumnVO> rowGroupCols; // value columns private List<ColumnVO> valueCols; // pivot columns private List<ColumnVO> pivotCols; // true if pivot mode is on, otherwise false private boolean pivotMode; // what groups the user is viewing private List<String> groupKeys; // if filtering, what the filter model is private Map<String, ColumnFilter> filterModel; // if sorting, what the sort model is private List<SortModel> sortModel; ... } // src/main/java/com/ag/grid/enterprise/oracle/demo/response/ServerSideGetRowsResponse.java public class ServerSideGetRowsResponse { private List<Map<String, Object>> data; private int lastRow; private List<String> secondaryColumnFields; ... }

We will discuss these in detail throughout this guide, however for more details see: Server-side Datasource

Service Controller

Our service shall contain a single endpoint /getRows with the request and response objects defined above:

// src/main/java/com/ag/grid/enterprise/oracle/demo/controller/TradeController.java @RestController public class TradeController { private TradeDao tradeDao; @Autowired public TradeController(@Qualifier("tradeDao") TradeDao tradeDao) { this.tradeDao = tradeDao; } @RequestMapping(method = POST, value = "/getRows") @ResponseBody public ServerSideGetRowsResponse getRows(@RequestBody ServerSideGetRowsRequest request) { return tradeDao.getData(request); } }

The TradeController makes use of the Spring Controller to handle HTTP and JSON Serialization.

Data Access

The OracleSqlQueryBuilder dynamically generates SQL based on the supplied request. We will query the Trade table with our generated SQL using the Spring JDBC Template.

Here is the implementation of our TradeDao:

// src/main/java/com/ag/grid/enterprise/oracle/demo/dao/TradeDao.java @Repository("tradeDao") public class TradeDao { private JdbcTemplate template; private OracleSqlQueryBuilder queryBuilder; @Autowired public TradeDao(JdbcTemplate template) { this.template = template; queryBuilder = new OracleSqlQueryBuilder(); } public ServerSideGetRowsResponse getData(ServerSideGetRowsRequest request) { String tableName = "trade"; // could be supplied in request as a lookup key? // first obtain the pivot values from the DB for the requested pivot columns Map<String, List<String>> pivotValues = getPivotValues(request.getPivotCols()); // generate sql String sql = queryBuilder.createSql(request, tableName, pivotValues); // query db for rows List<Map<String, Object>> rows = template.queryForList(sql); // create response with our results return createResponse(request, rows, pivotValues); } private Map<String, List<String>> getPivotValues(List<ColumnVO> pivotCols) { return pivotCols.stream() .map(ColumnVO::getField) .collect(toMap( pivotCol -> pivotCol, this::getPivotValues, (a, b) -> a, LinkedHashMap::new)); } private List<String> getPivotValues(String pivotColumn) { String sql = "SELECT DISTINCT %s FROM trade"; return template.queryForList(format(sql, pivotColumn), String.class); } }

Filtering

Our example will make use of the grids NumberFilter and SetFilter Column Filter's. The corresponding server side classes are as follows:

// src/main/java/com/ag/grid/enterprise/oracle/demo/filter/NumberColumnFilter.java public class NumberColumnFilter extends ColumnFilter { private String type; private Integer filter; private Integer filterTo; ... } // src/main/java/com/ag/grid/enterprise/oracle/demo/filter/SetColumnFilter.java public class SetColumnFilter extends ColumnFilter { private List<String> values; ... }

These filters are supplied per column in the ServerSideGetRowsRequest via the following property:

Map<String, ColumnFilter> filterModel;

As these filters differ in structure it is necessary to perform some specialised deserialization using the Type Annotations provided by the Jackson Annotations project.

When the filterModel property is deserialized, we will need to select the appropriate concrete ColumnFilter as shown below:

// src/main/java/com/ag/grid/enterprise/oracle/demo/filter/ColumnFilter.java @JsonTypeInfo( use = JsonTypeInfo.Id.NAME, include = JsonTypeInfo.As.PROPERTY, property = "filterType") @JsonSubTypes({ @JsonSubTypes.Type(value = NumberColumnFilter.class, name = "number"), @JsonSubTypes.Type(value = SetColumnFilter.class, name = "set") }) public abstract class ColumnFilter { String filterType; }

Here we are using the filterType property to determine which concrete filter class needs to be associated with the ColumnFilter entries in the filterModel map.

Sorting

The ServerSideGetRowsRequest contains the following attribute to determine which columns to sort by:

private List<SortModel> sortModel;

The SortModel contains the colId (i.e. 'book') and the sort type (i.e. 'asc')

// src/main/java/com/ag/grid/enterprise/oracle/demo/request/SortModel.java public class SortModel implements Serializable { private String colId; private String sort; ... }

The OracleSqlQueryBuilder then appends accordingly to the ORDER BY clause:

// src/main/java/com/ag/grid/enterprise/oracle/demo/builder/OracleSqlQueryBuilder.java orderByCols.isEmpty() ? "" : " ORDER BY " + join(",", orderByCols);

Grouping

Grouping is easily achieved in the OracleSqlQueryBuilder by appending rowGroups to the GROUP BY like so:

// src/main/java/com/ag/grid/enterprise/oracle/demo/builder/OracleSqlQueryBuilder.java private String groupBySql() { return isGrouping ? " GROUP BY " + join(", ", rowGroupsToInclude) : ""; } private List<String> getRowGroupsToInclude() { return rowGroups.stream() .limit(groupKeys.size() + 1) .collect(toList()); }

Pivoting

In order to perform pivoting we will use the OracleSqlQueryBuilder to generate a series of decode statements for each combination of pivot and value columns, such as:

sum ( DECODE(DEALTYPE, 'Financial', DECODE(BIDTYPE, 'Buy', CURRENTVALUE)) ) "Financial_Buy_CURRENTVALUE"

These new pivot columns (i.e. 'Secondary Columns') are created using the row values contained in the data and have field names such as: Financial_Buy_CURRENTVALUE.

These will need to be returned to the grid in the ServerSideGetRowsResponse in the following property:

List<String> secondaryColumnFields;

Our client code will then use these secondary column field to generate the corresponding ColDef's like so:

// src/main/resources/static/main.js let createSecondaryColumns = function (fields, valueCols) { let secondaryCols = []; function addColDef(colId, parts, res) { if (parts.length === 0) return []; let first = parts.shift(); let existing = res.find(r => r.groupId === first); if (existing) { existing['children'] = addColDef(colId, parts, existing.children); } else { let colDef = {}; let isGroup = parts.length > 0; if(isGroup) { colDef['groupId'] = first; colDef['headerName'] = first; } else { let valueCol = valueCols.find(r => r.field === first); colDef['colId'] = colId; colDef['headerName'] = valueCol.displayName; colDef['field'] = colId; colDef['type'] = 'measure'; } let children = addColDef(colId, parts, []); children.length > 0 ? colDef['children'] = children : null; res.push(colDef); } return res; } fields.sort(); fields.forEach(field => addColDef(field, field.split('_'), secondaryCols)); return secondaryCols; };

In order for the grid to show these newly created columns an explicit api call is required:

gridOptions.columnApi.setSecondaryColumns(secondaryColDefs);

Infinite Scrolling

The ServerSideGetRowsRequest contains the following attribute to determine the range to return:

private int startRow, endRow;

The OracleSqlQueryBuilder uses this information when limiting the results as follows:

// src/main/java/com/ag/grid/enterprise/oracle/demo/builder/OracleSqlQueryBuilder.java private String limitSql() { return " OFFSET " + startRow + " ROWS FETCH NEXT " + (endRow - startRow + 1) + " ROWS ONLY"; }

Conclusion

In this guide we presented a reference implementation for integrating the Server-side Row Model with a Java service connected to an Oracle database. This included all necessary configuration and install instructions.

A high level overview was given to illustrate the problem this approach solves before providing details of how to achieve the following server-side operations:

  • Filtering
  • Sorting
  • Grouping
  • Pivoting
  • Infinite Scrolling