DEV Community

Samuel Molčan
Samuel Molčan

Posted on Originally published at Medium

Using AG Grid in server side mode with Spring Data JPA

#java #springboot #aggrid #opensource

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;
}
Enter fullscreen mode Exit fullscreen mode 
@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<>();
}
Enter fullscreen mode Exit fullscreen mode 
@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<>();
}
Enter fullscreen mode Exit fullscreen mode 
@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
) {}
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

${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>
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

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));
    }
}
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

Grid sorted by Trade ID column

Grid sorted by Trade ID column
sortModel becomes order by

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();
Enter fullscreen mode Exit fullscreen mode

Text filter on Portfolio

Text filter on Portfolio
contains “port” becomes condition in where clause

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();
Enter fullscreen mode Exit fullscreen mode

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

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)

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();
Enter fullscreen mode Exit fullscreen mode

Book pivoted into column groups

Book pivoted into column groups
AG Grid and SQL example
One query finds the columns, one fills them

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();
Enter fullscreen mode Exit fullscreen mode

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);
}
Enter fullscreen mode Exit fullscreen mode 
@PostMapping("countRows")
public ResponseEntity<Long> countRows(@RequestBody ServerSideGetRowsRequest request) {
    return ResponseEntity.ok(this.tradeService.countRows(request));
}
Enter fullscreen mode Exit fullscreen mode

100,000 rows, one block fetched

100,000 rows, one block fetched
getRows fetches the block

getRows fetches the block
countRows fills the pager

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();
Enter fullscreen mode Exit fullscreen mode

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

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 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)
                        .build()
        )
        .build();
Enter fullscreen mode Exit fullscreen mode

Detail rows are loaded when a row is expanded, through a second endpoint:

@Transactional(readOnly = true)
public List<Map<String, Object>> getDetailRowData(Map<String, Object> masterRow) {
    return this.queryBuilder.getDetailRowData(masterRow);
}
Enter fullscreen mode Exit fullscreen mode 
@PostMapping("getDetailRowData")
public ResponseEntity<List<Map<String, Object>>> getDetailRowData(@RequestBody Map<String, Object> masterRow) {
    return ResponseEntity.ok(this.submitterService.getDetailRowData(masterRow));
}
Enter fullscreen mode Exit fullscreen mode

A submitter expanded into its trades

A submitter expanded into its trades
Masters only

Masters only
Details on expand

Details on expand

Expanding a row posts the master row itself, and the detail rows come back from where submitter_id = ?.

Master detail docs →

Top comments (0)