Scriptrunner - Ukážka práce s SQL

gistfile1.txt

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.MutableIssue
import groovy.sql.Sql
import java.sql.Driver

// spristupnenie customfieldu
def cfNumber = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("Number")
if (cfNumber == null) {
    return "ERROR: neznamy customfield"
}

// spristupnenie DB - vyzaduje xxx-jdbc.jar v tomcat/lib
def sqlProps = new Properties()
sqlProps.setProperty("user", "playsql")
sqlProps.setProperty("password", "test")
def conn = (Class.forName('org.postgresql.Driver').newInstance() as Driver).connect("jdbc:postgresql://192.168.210.37:5432/playsql", sqlProps)
def sql = new Sql(conn)

try {
    // pre potreby testovania mimo postfunkcie - ak nie je definovana issue, tak sa nacita konstantna
    MutableIssue issue = binding.variables.get("issue") as MutableIssue ?: ComponentAccessor.issueManager.getIssueObject("DEMO-4")

    // nacitanie hodnoty z issue - 0 ak nie je definovana
    def increment = issue.getCustomFieldValue(cfNumber) ?: 0

    // vyhladanie  v DB SQL prikazom
    def row = sql.firstRow("select a from values where b=0")
    if (row) {
        // riadok existuje - hodnoty v selecte sa pristupuju jednoducho menom
        def newValue = row.a + increment

        // zmena hodnoty v DB SQL prikazom
        sql.executeUpdate("update values set a=$newValue where b=0")
    } else {
        // vytvorenie riadku SQL prikazom
        sql.executeInsert("insert into values values ($increment, 0)")
    }
} finally {
    sql.close()
    conn.close()
}