Using scripted fields to extract information from the grid (old)

Warning

This is a deprecated approach! Inaccurate work with database connections can cause to JIRA crash. Please use recommended approach that relies on safe TGE Java API.

Introduction

Using a simple script, you can retrieve the values from the grid table and reuse these values in JQL and so on.

This script is implemented by using Scripted Fields. (This is functionality provided by the Script Runner )

import groovy.sql.Sql;
import groovy.sql.GroovyRowResult;
import java.sql.SQLException;
import java.sql.Connection;
import org.apache.log4j.Logger;
import org.ofbiz.core.entity.ConnectionFactory;
import org.ofbiz.core.entity.DelegatorInterface;
import com.atlassian.jira.ofbiz.OfBizDelegator;
import com.atlassian.jira.component.ComponentAccessor;
OfBizDelegator delegator = ComponentAccessor.getOfBizDelegator();
DelegatorInterface delegatorInterface = delegator.getDelegatorInterface();
String helperName = delegatorInterface.getGroupHelperName("default");
Connection connection = ConnectionFactory.getConnection(helperName);
 
Sql sql = new Sql(connection);
List<GroovyRowResult> resultRows = sql.rows("select count(*) countRow from EmployeeList_c10546 where issueid = ${issue.getId()}");
sql.close();
if (resultRows == null || resultRows.size() == 0) {
    log.debug("No result found");
    return;
}

// close the connection
Double result = (Double) resultRows.get(0).get("countRow")
return result;

About the connection to the database

The code block allows to connect to the JIRA database.

OfBizDelegator delegator = ComponentAccessor.getOfBizDelegator();
DelegatorInterface delegatorInterface = delegator.getDelegatorInterface();
String helperName = delegatorInterface.getGroupHelperName("default");
Connection connection = ConnectionFactory.getConnection(helperName);
 
Sql sql = new Sql(connection);

About the table name

gridtable_c12344 is the name of the table in the database.
The table name is generated by the addon using the base name and the context id so if you have in the gridconfiguration

gd.tablename = invoice

then the table in the database will be called

invoice_c12345

Where 12345 is the unique context id of the customfield.
Check connecting the grid to a database for more details