Jira Connecting to databases Querying the Current JIRA Database

本文主要是介绍Jira Connecting to databases Querying the Current JIRA Database，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

本文转自：https://scriptrunner.adaptavist.com/latest/jira/recipes/misc/connecting-to-databases.html

Connecting to External Databases

You may want to connect to a database in your workflow function scripts, for instance read data from an external source in a validator.

The easiest method is to use groovy sql. But, there is a gotcha or two.

JDBC drivers must be loaded by the system classloader, and furthermore the DriverManager will make checks that the driver class is accessible from the classloader of the calling class. In an OSGi environment this causes problems.

So, the following code will not work:

import groovy.sql.Sql

Sql.newInstance("jdbc:postgresql://localhost:5432/jira_62", "jiradb", "")

you will get an error: No suitable driver found for jdbc:postgresql://localhost:5432/jira_62

Instead, manually load the driver class and create the connection:

import groovy.sql.Sql
import java.sql.Driver

def driver = Class.forName('org.postgresql.Driver').newInstance() as Driver 

def props = new Properties()
props.setProperty("user", "devtools") 
props.setProperty("password", "devtools")

def conn = driver.connect("jdbc:postgresql://localhost:5432/jira_6.4.6", props) 
def sql = new Sql(conn)

try {
    sql.eachRow("select count(*) from jiraissue") {
        log.debug(it)
    }
} finally {
    sql.close()
    conn.close()
}

Driver jar files should be placed in your tomcat/lib directory, eg <jira.install>/lib, but JIRA already ships with the major drivers.

Querying the Current JIRA Database

You can execute a query against the current JIRA database, for instance in reports. Here’s how:

import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default")

def sqlStmt = """
    SELECT     project.pname, COUNT(*) AS kount
    FROM       project
               INNER JOIN jiraissue ON project.ID = jiraissue.PROJECT
    GROUP BY project.pname
    ORDER BY kount DESC
"""

Connection conn = ConnectionFactory.getConnection(helperName)
Sql sql = new Sql(conn)

try {
    StringBuffer sb = new StringBuffer()
    sql.eachRow(sqlStmt) {
        sb << "${it.pname}\t${it.kount}\n"
    }
    log.debug sb.toString()
}
finally {
    sql.close()
}

https://community.atlassian.com/t5/Jira-Software-questions/How-to-run-an-SQL-query-to-get-data-from-JIRA-Project/qaq-p/607873

Iam using a groovy script in a transition postfunction to ask the database for the person that did a specific transition in the issue workflow. For that the might exist an api function too but writing the query and executing it was for me the fastest way.

import com.atlassian.jira.ComponentManager
import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import java.sql.Connection
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.MutableIssue;
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder
import com.atlassian.jira.user.util.UserManager
import com.atlassian.jira.util.ImportUtils
//import com.atlassian.crowd.embedded.api.User



//Issue issue = issue
//def id = issue.getId()
ComponentManager componentManager = ComponentManager.getInstance()
def delegator = (DelegatorInterface) componentManager.getComponentInstanceOfType(DelegatorInterface.class)
String helperName = delegator.getGroupHelperName("default");


def sqlStmt = """
                SELECT a.author as 'doer' FROM changegroup as a JOIN changeitem as b ON b.groupid = a.id
                WHERE b.field = 'status' AND a.issueid = ${issue.id} AND b.oldstring = 'In Progress' AND b.newstring = 'Review'
                ORDER BY a.created DESC LIMIT 1
"""

Connection conn = ConnectionFactory.getConnection(helperName)
Sql sql = new Sql(conn)
try {
        StringBuffer sb = new StringBuffer()
        sql.eachRow(sqlStmt)
    {
        sb << it.doer
    }
    def userManager = (UserManager) ComponentAccessor.getUserManager()
    def user = userManager.getUserByName(sb.toString())
issue.setAssignee(user)


} finally {
sql.close()
}

To execute groovy scripts in transition postfunction you need the ScriptRunner plugin.
Hope you will get an idea how to do what you want to do.

https://github.com/sparxsys/Jira-SQL-Queries/blob/master/getCustomFieldValuesCount.sql

select * from customfield order by cfname asc
select * from customfieldvalue

select 
    customfield.id, customfield.cfname, count(*) 
from customfield left join customfieldvalue 
on customfield.id = customfieldvalue.customfield
group by customfield.id
order by count(*) desc

这篇关于Jira Connecting to databases Querying the Current JIRA Database的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！