JIRA Plugins: Data model access

In previous entries of this blog, we have shown how to create Atlassian plugins for Atlassian JIRA by extending built-in search functionallity with JQL functions. This new post will provide you with some information regarding OfBiz in order to access JIRA’s data model directly. Our goal will be to create a JQL function that can use any records stored in JIRA (or Greenhopper) data model.

¿What is Ofbiz?

Apache Ofbiz (The Apache Open For Business Project) is a java framework by the Apache Foundation that provides corporate tools automation. It offers corporate tools such as: ERP, CRM, etc. So, what does this have to do with JIRA? Traditionally, JIRA has used OfBiz as a Database abstraction layer to access records as map of fields (as an instance of the class GenericValue). The main benefit of this approach is keeping forward compatibility even if the database schema changes.

The matching betweern logical model and database schema is performed by defining OfBiz Entities (tables) as a set of Fields (columns) with a strong data type. This model map is stored as a set of XML documents (for different DB servers) in a folder named entitydefs inside a JIRA installation directory. The file entitymodel.xml stores all entities available via Ofbiz. This is a little sample from this file:

    <entity entity-name="Issue" table-name="jiraissue" package-name="">
        <field name="id" type="numeric"/>

        <field name="key" col-name="pkey" type="long-varchar"/>

        <field name="project" type="numeric"/>

        <field name="reporter" type="long-varchar"/>
        <field name="assignee" type="long-varchar"/>

        <field name="type" col-name="issuetype" type="long-varchar"/>
        <field name="summary" type="long-varchar"/>
        <field name="description" type="extremely-long"/>
        <field name="environment" type="extremely-long"/>
        <field name="priority" type="long-varchar"/>
        <field name="resolution" type="long-varchar"/>
        <field name="status" col-name="issuestatus" type="long-varchar"/>
        <field name="created" type="date-time"/>
        <field name="updated" type="date-time"/>
        <field name="duedate" type="date-time"/>
        <field name="resolutiondate" type="date-time"/>

Ofbiz is a complex tool and it’s API is out of the scope of this article so we will focus on an interface used by JIRA when accessing data: Delegator. This interface provides all required methods to search for different criteria.

Accesessing OfBiz from JIRA

JIRA public API contains interfaces to access OfBiz from plugins. com.atlassian.jira.ofbiz.OfBizDelegator is a wrapper interface to hide OfBiz Delegator. In the following snippet you can see how to retrieve an object of this interface:

OfBizDelegator delegator = new DefaultOfBizDelegator(CoreFactory.getGenericDelegator());

This interface provides methods to explore entities and fileds from JIRA datamodel. Bear in mind that values for fileds are usually indexes from some other tables. For instance, in order to retireve all issues of type Bug (assuming the numerical value for Bug is 1) we can use this code:

final List<GenericValue> bugs = delegator.findByField("Issue", "type", (Integer)1);

Every GenericValue object returned by delegator provides a simple interface to get field values from name:

String key = bugs.get(0).getString("key");

OfBizDelegator interface include some other methods for creating complex queries:

  • findByLike
  • findByPrimaryKey
  • findByCondition
  • findByAnd
  • findByOr

See JIRA API for more info on OfBizDelegator.

Conditional queries

So far we have created simple queries using OfBizDelegator but there is no way to use complex logic using these methods. In order to combine different fields and logical conditions we will need some other classes:

  • org.ofbiz.core.entity.EntityCondition: Stores a logical condition consisting of 1 field, 1 operator (instanceof EntityOperator) and a value.
  • org.ofbiz.core.entity.EntityExpr: Stores a logical expression between openrands or conditions (OR, AND, etc)
  • org.ofbiz.core.util.UtilMisc: Helper class to assist in creating complex conditions.

This is a sample of how to use the method findByCondition from OfBizDelegator to get a list of GenericValues using conditional statements.

java.sql.Timestamp fromTime = new Timestamp(new java.util.Date().getTime() - 365*24*3600);
List conditions = UtilMisc.toList(
                                  new EntityExpr("created", EntityOperator.GREATER_THAN, fromTime),
                                  new EntityExpr("field", EntityOperator.LIKE, "status"),
                                  new EntityExpr("fieldtype", EntityOperator.LIKE, "jira"),
                                  new EntityExpr("newvalue", EntityOperator.LIKE, (Integer)5) // "Resolved"
final EntityCondition condition = new EntityConditionList(conditions, EntityOperator.AND);

OfBizDelegator delegator = new DefaultOfBizDelegator(CoreFactory.getGenericDelegator());
final List<GenericValue> resolved = delegator.findByCondition("ChangeGroupChangeItemView", condition, EasyList.build("issue"));

 JQL WAS operator

From version 4.3 on, JIRA provides a new operator to search in the past using JQL: WAS. It is identical as operator IS except it also checks in the issue‘s histoy. For example, the following JQL sentence would return all issues ever assigned to the current user:

assignee was currentUser()

In the next section we will try to replicate this functionallity using a JQL function based on OfBiz.

¿IS the user the actual assignee?

Operator IS works as a subset of WAS but schema-wise, queries affect completely different entities. This way, the current assignee for a particular issue is stored in the Issue entity (shown previously).

final List<QueryLiteral> literals = new LinkedList<QueryLiteral>();
OfBizDelegator delegator = new DefaultOfBizDelegator(CoreFactory.getGenericDelegator());
final List<GenericValue> assignedToGVs = delegator.findByField("Issue", "assignee", userId);

for (GenericValue changeItem : assignedToGVs) {
    try {
        literals.add(new QueryLiteral(operand, changeItem.getLong("id")));
    catch (NumberFormatException e) {

Now, let’s look into the past…

However, in order to know if a certain user has ever been assigned to a particular issue we need to use another entity that stores previous changes for all issues: ChangeGroupChangeItemView (see the example for Conditional queries). In this case, the helper class EasyMap comes in handy to create a map using constructor arguments.

final List<QueryLiteral> literals = new LinkedList<QueryLiteral>();
Map<String, Object> paramsItem = EasyMap.build("field", field, "fieldtype", "jira", "newvalue", newValue);
final List<GenericValue> changeItemsForFieldGVs = delegator.findByLike("ChangeGroupChangeItemView", paramsItem);
for (GenericValue changeItem : changeItemsForFieldGVs) {
    try {
         literals.add(new QueryLiteral(operand, changeItem.getLong("issue")));
    catch (NumberFormatException e) {

The final test

We have already seen in previous posts how to write and bundle JIRA plugins as well as JQL functions so we will skip this step. The XML descriptor for the new JQL funcion will be as follows:

<jql-function key="issue-assigned-to-jql-function" name="Issue Ever Assigned To Function"

So let’s deploy and test our JQL function everAssignedTo.

Let’s double check with operator WAS.


Besides JIRA and OfBiz API links shown in this post, there is detailed information on JIRA’s datamodel in this page.

As with the rest of entries of our blog, you can download the source code from the repository publicly available at bitbucket.org: https://bitbucket.org/novagenia/elmanifiesto.git.

Let us know what you think about this post.

Author: Eduardo Mayor

Eduardo Mayor is a software engineer with 20+ years of experience. He is also the founder of Novagenia Information Technologies, a company focused on introducing agile methods and tools to companies worldwide.

Visit us in: http://www.novagenia.com
Share this...

6 thoughts on “JIRA Plugins: Data model access

  1. I’ve read several just right stuff here. Certainly worth bookmarking for revisiting.
    I surprise how so much effort you set to create
    any such fantastic informative website.

  2. Está la mejor tiempo para hacer algunos planes para
    A más largo plazo y se tiempo para ser feliz.
    He aprender soportar y si sólo puede I deseo de sugerir que algunos
    cuestiones o Consejos. Quizás Puede escribir subsiguientes con respecto a este artículo.
    I deseo de leído más lo sobre it!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>