I have written a common function to connect to DB. This function should be used by multiple script JQL functions.
But it fails to load. Here is what I am doing,
STEP 1: Written a class called 'DBUtils' . Inside that class, defined function called executeQuery(). I have added this script under '
/var/atlassian/application-data/jira/scripts/com/onresolve/jira/groovy/jql' directory.
import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.ComponentManager;
import groovy.sql.Sql;
import java.sql.Connection;
import org.ofbiz.core.entity.ConnectionFactory;
import org.ofbiz.core.entity.DelegatorInterface;
import org.apache.log4j.Logger;
/**
* This script is to connect to JIRA DB and execute the give query
* @param sqlStmt query to be executed
* @return results as list of HashMaps Ex: [ {key, value}, {key, value} ]. Whoever call this function, has to parse the output and fetch the required input based on the given query
*
* @since 27th March, 2019
* @version 1.0
*/
class DBUtils {
def executeQuery(String sqlStmt) {
// Defining arrayList to store result returns by query.
def results = [];
Logger log = Logger.getLogger("Connecting to DB");
if(sqlStmt.split(' ')[0].toLowerCase() == 'select') {
// Connecting to the database.
def componentManager = ComponentManager.getInstance();
def delegator = (DelegatorInterface) componentManager.getComponentInstanceOfType(DelegatorInterface.class);
String helperName = delegator.getGroupHelperName("default");
Connection conn = ConnectionFactory.getConnection(helperName);
Sql sql = new Sql(conn);
// Execute the given query
try {
results = sql.rows(sqlStmt)
} catch (Exception e) {
log.error("Getting error while Script JQL Function trying to connect to DB and execute the query : ${sqlStmt}" + e.getMessage() + "\n");
} finally {
sql.close();
}
}
return results
}
}
STEP 2: Have written separate JQL function script and tried using DBUtils class's function (executeQuery()) by creating a object (new DBUtils().executeQuery(query)). But this script is failed to load.
package com.onresolve.jira.groovy.jql;
import com.atlassian.jira.bc.issue.search.SearchService;
import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.ComponentManager;
import com.atlassian.jira.jql.parser.JqlQueryParser;
import com.atlassian.jira.jql.query.LuceneQueryBuilder;
import com.atlassian.jira.jql.query.QueryCreationContext;
import com.atlassian.jira.jql.validator.NumberOfArgumentsValidator;
import com.atlassian.jira.user.ApplicationUser;
import com.atlassian.jira.util.MessageSet;
import com.atlassian.query.clause.TerminalClause;
import com.atlassian.query.operand.FunctionOperand;
import DBUtils;
import groovy.util.logging.Log4j;
/**
* The ChangedFrom program is implemented to get the list of feature issues which had given value for the given custom field.
* This program will enable history search for the custom fields on feature issue.
*
* @arguments It accepts two arguments.
* Field Name : Name of the custom field
* Changed from value : Value of the custom field
*
* @since 19th March, 2019
* @version 1.0
**/
@Log4j
class ChangedFrom extends AbstractScriptedJqlFunction implements JqlQueryFunction {
// Defining an objects to use JQL query parser
def queryParser = ComponentAccessor.getComponent(JqlQueryParser);
def luceneQueryBuilder = ComponentAccessor.getComponent(LuceneQueryBuilder);
def searchService = ComponentAccessor.getComponent(SearchService);
def applicationUser = ComponentAccessor.getJiraAuthenticationContext().getUser();
/**
* This method is to get the description of the script JQL function.
* This description will be displayed when we search for the ChangedFrom() function in issue navigator in jira application.
**/
@Override
String getDescription() {
"Return feature issues which had the given value for the given custom field"
}
/**
* validate function is to validate the operand and JQL query which is formed based on operand.
* @return It return the error message if operand is incorrect/null
**/
@Override
MessageSet validate(ApplicationUser user, FunctionOperand operand, TerminalClause terminalClause) {
def messageSet = new NumberOfArgumentsValidator(2, getI18n()).validate(operand);
if (messageSet.hasAnyErrors()) {
return messageSet;
}
// Forming the JQL and validating it
def field = '"' + operand.args.first() + '"';
def value = '"' + operand.args.last() + '"';
def queryStr = "${field} = ${value}";
def query = queryParser.parseQuery(queryStr);
messageSet = searchService.validateQuery(user, query);
messageSet
}
/**
* getArguments() function is to define the description and property of the arguments.
* @return argument property as a List<Map>
**/
@Override
List<Map> getArguments() {
[
[
description: "Field Name",
optional: false,
],
[
description: "Changed From Value",
optional: false,
]
]
}
// To define the function name for script JQL function.
@Override
String getFunctionName() {
"changedFrom"
}
/**
* getQuery function is to get the query context which is entered in issue navigator, perform defined logic/operation to get deseried outcome,
* form outcome and return the JQL result
*
* @return list of feature issues in issue navigator in JIRA application
**/
@Override
Query getQuery(QueryCreationContext queryCreationContext, FunctionOperand operand, TerminalClause terminalClause) {
//log.setLevel(org.apache.log4j.Level.DEBUG); //Uncomment this code to enable debugging logs
// Grep custom field name and it's value from operand
def fieldName = operand.args.first().toString();
def value = operand.args.last().toString();
//log.debug("Field Name = ${fieldName} - - - Value = ${value}"); //Uncomment this code to enable debugging logs
// Defining arrayList to store feature keys.
def featureLinkList = [];
// SQL query to fetch value from db
def sqlStmt = "select some query";
if(sqlStmt.split(' ')[0].toLowerCase() == 'select') {
// Connecting to JIRA DB and execute given query
def dbUtils = new DBUtils();
def temp = dbUtils.executeQuery(sqlStmt);
temp.each {
featureLinkList.add("${it.featurekey}");
}
}
//log.debug("SQL Query : ${sqlStmt}"); //Uncomment this code to enable debugging logs
/**
* Framing the Query(using the data fetched from DB) to fetch Feature issues through JQL
* if the SQL doesn't return anything, then it will return 'No Issues found'
*/
if(featureLinkList)
{
def queryTemp = featureLinkList.unique().join(',').toString();
def secondQuery = "key in (" + queryTemp + ")";
def secondquery = queryParser.parseQuery(secondQuery);
luceneQueryBuilder.createLuceneQuery(queryCreationContext, secondquery.whereClause);
}
else
{
def secondQuery = "key in (FEATURE-0)";
def secondquery = queryParser.parseQuery(secondQuery);
luceneQueryBuilder.createLuceneQuery(queryCreationContext, secondquery.whereClause);
}
}
}//End of the script;
What am i doing wrong? Can anyone pls help me?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.