Using AG Grid in server side mode with Spring Data JPA

Documentation — every feature with a live grid you can click through

ag-grid-jpa-adapter — the library source

Showcase backend — the Spring Boot app all the code below is taken from

In AG Grid’s server side row model, the grid posts a request describing what it wants and expects the backend to answer it. ag-grid-jpa-adapter builds that query for you with the JPA Criteria API, against the entities you already have — no native SQL, no string concatenation. You define the columns once and hand the request to a QueryBuilder.

The data model

@Entity @Table(name = "trade") @Getter @Setter public class Trade { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "trade_id") private Long tradeId; // self-reference -> tree data @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "parent_trade_id") private Trade parentTrade; @OneToMany(fetch = FetchType.LAZY, mappedBy = "parentTrade", orphanRemoval = true) private Set<Trade> childTrades = new HashSet<>(); @Column(name = "product") private String product; @Column(name = "portfolio") private String portfolio; @Column(name = "book") private String book; @Column(name = "deal_type") private String dealType; @Column(name = "bid_type") private String bidType; @ManyToOne(fetch = FetchType.EAGER) @JoinColumn(name = "submitter_id") private Submitter submitter; @ManyToOne(fetch = FetchType.EAGER) @JoinColumn(name = "submitter_deal_id") private SubmitterDeal submitterDeal; @Column(name = "current_value") private BigDecimal currentValue; @Column(name = "previous_value") private BigDecimal previousValue; @Embedded private Statistics statistics; @Column(name = "birth_date") private LocalDate birthDate; @Column(name = "is_sold") private Boolean isSold; // materialised path, "1/7/12" -> tree data filtering and child counts @Column(name = "data_path") private String dataPath; }

@Entity @Table(name = "submitter") @Getter @Setter public class Submitter { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private String name; // the detail rows of a master/detail grid @OneToMany(mappedBy = "submitter") private Set<Trade> trades = new HashSet<>(); }

@Entity @Table(name = "submitter_deal") @Getter @Setter public class SubmitterDeal { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(name = "name") private String name; @Column(name = "created_at") private LocalDateTime createdAt; @OneToMany(mappedBy = "submitterDeal") private Set<Trade> trades = new HashSet<>(); }

@Embeddable public record Statistics( @Column(name = "pl1") BigDecimal pl1, @Column(name = "pl2") BigDecimal pl2, @Column(name = "gain_dx") BigDecimal gainDx, @Column(name = "sx_px") BigDecimal sxPx, @Column(name = "x99_out") BigDecimal x99Out ) {}

The metamodel generator

Columns are declared with static metamodel attributes so you need a generator on the build:

<annotationProcessorPaths> <!-- Lombok first, if you use it --> <path> <groupId>org.hibernate.orm</groupId> <artifactId>hibernate-jpamodelgen</artifactId> <version>${hibernate.version}</version> </path> </annotationProcessorPaths>

${hibernate.version} comes from spring-boot-starter-parent.

The QueryBuilder

<dependency> <groupId>io.github.smolcan</groupId> <artifactId>ag-grid-jpa-adapter</artifactId> <version>1.1.7</version> </dependency>

QueryBuilder<E, E_ID, D> — entity, id type, detail entity (Void unless you use master/detail). It is immutable and built once, so it belongs in the service constructor (It works just as well as a @Bean in a @Configuration class, autowired into the service.).

@Service public class TradeService { private final QueryBuilder<Trade, Long, Void> queryBuilder; public TradeService(EntityManager entityManager) { this.queryBuilder = QueryBuilder.builder(Trade.class, Trade_.tradeId, entityManager) .colDefs( ColDef.builder(Trade_.tradeId) .filter(new AgNumberColumnFilter<>()) .build(), ColDef.builder(Trade_.product) .filter(new AgTextColumnFilter()) .enableRowGroup(true, key -> key) .build(), ColDef.builder(Trade_.portfolio) .filter(AgSetColumnFilter.forString()) .enableRowGroup(true, key -> key) .build(), ColDef.builder(Trade_.currentValue) .filter(new AgNumberColumnFilter<>()) .enableValue(true) .build(), ColDef.builder(Trade_.birthDate) .filter(AgDateColumnFilter.forLocalDate()) .build(), // dot walk into related entities, the column comes back as "submitter.name" ColDef.builder(FieldPath.of(Trade_.submitter).to(Submitter_.name)) .filter(new AgTextColumnFilter()) .build() ) .build(); } @Transactional(readOnly = true) public LoadSuccessParams getRows(ServerSideGetRowsRequest request) { return this.queryBuilder.getRows(request); } }

The controller

@RestController @RequestMapping("/api/trades") @RequiredArgsConstructor public class TradeController { private final TradeService tradeService; @PostMapping("getRows") public ResponseEntity<LoadSuccessParams> getRows(@RequestBody ServerSideGetRowsRequest request) { return ResponseEntity.ok(this.tradeService.getRows(request)); } }

ServerSideGetRowsRequest is what AG Grid posts — startRow/endRow, sortModel, filterModel, rowGroupCols, groupKeys, valueCols, pivotCols, pivotMode. Everything the grid can ask for arrives in that one body, and one getRows call answers all of it.

Sorting

Sorting is enabled on column by default. Turn it off per column, and a sort request on it is rejected:

ColDef.builder(Trade_.product) .sortable(false) .build()

Grid sorted by Trade ID column

sortModel becomes order by

Sorting docs →

Filtering

Say the grid needs a text filter on product, a set filter on portfolio, a number filter on currentValue and a date filter on birthDate:

QueryBuilder.builder(Trade.class, Trade_.tradeId, entityManager) .colDefs( ColDef.builder(Trade_.product) .filter(new AgTextColumnFilter()) .build(), ColDef.builder(Trade_.portfolio) .filter(AgSetColumnFilter.forString()) .build(), ColDef.builder(Trade_.currentValue) .filter(new AgNumberColumnFilter<BigDecimal>()) .build(), ColDef.builder(Trade_.birthDate) .filter(AgDateColumnFilter.forLocalDate()) .build() ) .build();

Text filter on Portfolio

contains “port” becomes condition in where clause

Filtering docs →

Row grouping and aggregation

Group by portfolio and product, aggregate currentValue and previousValue:

QueryBuilder.builder(Trade.class, Trade_.tradeId, entityManager) .colDefs( ColDef.builder(Trade_.portfolio) .enableRowGroup(true, key -> key) .build(), ColDef.builder(Trade_.product) .enableRowGroup(true, key -> key) .build(), ColDef.builder(Trade_.currentValue) .enableValue(true) .build(), ColDef.builder(Trade_.previousValue) .enableValue(true) .build() ) .build();

Grouped by Portfolio and Product, aggregated average on current and previous value. Expanded group “Portfolio 7”

Group key becomes where, value columns become aggregation function (avg)

Row grouping docs →

Aggregation docs →

Pivoting

Pivot book into columns, grouped by product and portfolio, averaging currentValue and previousValue:

QueryBuilder.builder(Trade.class, Trade_.tradeId, entityManager) .colDefs( ColDef.builder(Trade_.product) .enableRowGroup(true, key -> key) .build(), ColDef.builder(Trade_.portfolio) .enableRowGroup(true, key -> key) .build(), ColDef.builder(Trade_.book) .enablePivot(true) .build(), ColDef.builder(Trade_.currentValue) .enableValue(true) .build(), ColDef.builder(Trade_.previousValue) .enableValue(true) .build() ) .build();

Book pivoted into column groups

One query finds the columns, one fills them

Two queries: select distinct book to find out which columns exist, then one aggregate with a case per generated column.

Pivoting docs →

Pagination

QueryBuilder.builder(Trade.class, Trade_.tradeId, entityManager) .colDefs(/* ... */) .paginateChildRows(true) .build();

The total count comes from a separate call — make it when the grid state changes the count, such as on a filter change:

@Transactional(readOnly = true) public long countRows(ServerSideGetRowsRequest request) { return this.queryBuilder.countRows(request); }

@PostMapping("countRows") public ResponseEntity<Long> countRows(@RequestBody ServerSideGetRowsRequest request) { return ResponseEntity.ok(this.tradeService.countRows(request)); }

100,000 rows, one block fetched

getRows fetches the block

countRows fills the pager

Same timestamp, two endpoints: getRows fetches the block, countRows fills in the pager.

Pagination docs →

Tree data

Trade points at its own parent, so the table is already a tree:

QueryBuilder.builder(Trade.class, Trade_.tradeId, entityManager) .colDefs(/* ... */) .treeData(true) .isServerSideGroupFieldName("hasChildren") .treeDataParentReferenceField(Trade_.parentTrade) .treeDataStringToParentIdTypeConverter(Long::valueOf) .treeDataDataPathFieldName(Trade_.dataPath) .treeDataDataPathSeparator("/") .build();

A self-referencing table as a tree (with expanded node with id 1)

hasChildren is an exists subquery, where children of node 1

hasChildren is the case when exists (...) subquery; expanding a node sends its key back as groupKeys and the children are fetched with where parent_trade_id = ?.

Tree data docs →

Master detail

A submitter on the master row, its trades in the detail grid:

QueryBuilder.builder(Submitter.class, Submitter_.id, Trade.class, entityManager) .colDefs( ColDef.builder(Submitter_.id).build(), ColDef.builder(Submitter_.name).build() ) .masterDetail(true) .masterDetailParams( QueryBuilder.MasterDetailParams.<Submitter, Long, Trade>builder() .detailClass(Trade.class) .detailColDefs( ColDef.builder(Trade_.tradeId).build(), ColDef.builder(Trade_.product).build(), ColDef.builder(Trade_.portfolio).build() ) .detailMasterReferenceField(Trade_.submitter...