Introduction
As Atlassian App developers, we are well-versed in using ActiveObjects, which is the go-to ORM for handling most database operations in Jira plugin development. ActiveObjects makes it easy to perform basic CRUD operations, create tables etc. However, when it comes to more advanced querying, such as aggregating data with groupBy or performing complex SQL operations like joining multiple tables, ActiveObjects falls short. For example, if you want to retrieve a specific count of grouped results, ActiveObjects doesn’t offer the flexibility needed, often returning only the count of the first row or limiting you to entity-specific operations.
To overcome these limitations, Atlassian provides a powerful library called PocketKnife QueryDSL. This library enables us to perform complex queries that ActiveObjects can't handle, such as joining multiple tables, aggregating data, and retrieving specific column counts with groupBy. PocketKnife QueryDSL offers type-safe queries and provides greater control and flexibility over how data is retrieved and manipulated in Jira, opening up possibilities for more sophisticated database operations. Below is step by step tutorial of how to integrate PocketKnife QueryDSL to your plugin -