Enhancing Jira with PocketKnife QueryDSL: Solving Complex SQL Queries Beyond ActiveObjects

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 -

Integration Steps

To integrate Atlassian PocketKnife QueryDSL into your Maven project, follow these steps:

Add Maven Dependency
Include the following dependency in your pom.xml:

<dependency>
    <groupId>com.atlassian.pocketknife</groupId>
    <artifactId>atlassian-pocketknife-querydsl</artifactId>
    <version>5.2.1</version>
</dependency>

OSGi Integration
Include the following import package:

<Import-Package>
    <!-- exclude pocketknife bits we don't need -->
    !com.infradna.tool*,
    !net.sf.cglib.proxy,
    !org.jvnet.hudson.annotation_indexer,

    <!-- import (alltheotherthings) -->
    *;resolution='optional' 
</Import-Package>

The line *;resolution='optional' imports all other packages, which may include unnecessary components, potentially increasing build times and introducing unused dependencies. For optimal performance and maintainability, we recommend explicitly specifying only the packages your plugin requires rather than using a wildcard. This approach ensures your code remains efficient and minimizes dependency issues.

Add Scanned Dependencies
Ensure that the scanned dependencies include:

<dependency>
    <groupId>com.atlassian.pocketknife</groupId>
    <artifactId>atlassian-pocketknife-querydsl</artifactId>
</dependency>

//dependency management
<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>com.atlassian.jira</groupId>
            <artifactId>jira-project</artifactId>
            <version>${jira.version}</version>
            <type>pom</type>
            <scope>import</scope>
        </dependency>
    </dependencies>
</dependencyManagement>

    
//jira version
<jira.version>9.17.3</jira.version>

Now you are all set to use PocketKnife QueryDSL in your project!

Practical Examples

Example 1: Running a Simple GROUP BY Query with Count

Problem Statement: Retrieve the URLs and their respective counts from the TICKETS_DB table, where the request-raised field is set to true, within a specific date range.

Query-

SELECT
    "URL",count("URL")
FROM
    "AO_8FF32E_TICKETS_DB"
WHERE
    "REQUEST_RAISED" = true AND "TYPE" = 'TICKET' AND "CREATED" BETWEEN '2024-08-11' AND '2024-09-23'
group by "URL";

Query result-

To execute a simple GROUP BY query that returns counts, implement the following code:

//imports
import com.atlassian.pocketknife.api.querydsl.DatabaseAccessor;
import com.querydsl.core.Tuple;
import com.querydsl.core.types.Expression;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.core.types.dsl.Expressions;
import com.querydsl.sql.SQLQuery;

//Define you variables
Expression<Long> countExpression
Expression<String> urlExpression 
BooleanExpression commonCondition
BooleanExpression dateRangeCondition

SQLQuery<Tuple> query = databaseConnection
        .select(urlExpression, countExpression)
        .from(Expressions.template(String.class, tableName))
        .where(commonCondition.and(dateRangeCondition))
        .groupBy(urlExpression);

Example 2: Running a Join Query

Problem Statement: Retrieve search queries and their respective counts from the SEARCH_DB table, based on the session_state field in the SETTINGS_DB table, for a specified date range.

Query-

SELECT "AO_8FF32E_SEARCH_DB"."QUERY", COUNT("AO_8FF32E_SEARCH_DB"."QUERY")
                                ,"AO_8FF32E_SETTING_DB"."SESSION_STATE"
FROM "AO_8FF32E_SEARCH_DB"
JOIN "AO_8FF32E_SETTING_DB"  
ON "AO_8FF32E_SEARCH_DB"."TRACKER_DB_ID" = "AO_8FF32E_SETTING_DB"."ID"
WHERE public."AO_8FF32E_SEARCH_DB"."CREATED" BETWEEN '2024-09-12' AND '2024-09-19'
GROUP BY "AO_8FF32E_SEARCH_DB"."QUERY", "AO_8FF32E_SETTING_DB"."SESSION_STATE";

Query results-

To execute a join query, use the following code:

SQLQuery<Tuple> query = databaseConnection
        .select(queryColumn, countQuery, sessionStateColumn)
        .from(fromJoinTableName)
        .on(Expressions.booleanTemplate(joinCondition1).eq(Expressions.booleanTemplate(joinCondition2)))
        .where(dateRangeCondition)
        .groupBy(queryColumn, sessionStateColumn)

Testing and Debugging

For testing and debugging purposes, you can refer to our blog post: “How to start Atlassian applications in standalone debug mode?” for comprehensive guidelines.

Conclusion

PocketKnife QueryDSL unlocks advanced querying capabilities in Jira plugin development, overcoming the limitations of ActiveObjects. By following this guide, you can integrate complex queries, including grouped counts and joins, into your projects with ease. This powerful tool enhances data handling flexibility and efficiency, enabling more sophisticated and scalable database operations within Jira.

References:

We have tried and tested this on Jira versions 9.12.13 and 9.17.3. Please reach out to us if this no longer works using the Contact Us section; we may have a better solution by then.