JDBC Driver for Jira

Build 23.0.8839

Issues

Create, modify, delete and query Issues in Jira.

Table Specific Information

You should set the IncludeCustomFields connection property to 'True' in order to dynamically retrieve the Custom Fields columns and values (like Sprint, Epic, ...) when querying this table.

You can search for Issues by using SQL and JQL (Jira Query Language).

Select

JQL


SELECT * FROM Issues WHERE JQL = ' project = "New project" AND Status WAS "open" '

SQL


SELECT * FROM Issues WHERE ProjectId > 10001 OR Updated <= '2016/03/29 15:00'
The driver uses the Jira API to process some of the filters. The driver processes other filters client-side within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refers to other columns will cause inconsistent data. For example, the following queries are processed server side.

SQL and JQL


SELECT * FROM Issues WHERE ProjectName = 'New project' AND JQL = ' status WAS "open" '

Filter time interval fields like TimeSpent, OriginalEstimate and RemainingEstimate by specifying hours, days, weeks, minutes and so on.

SELECT * FROM Issues WHERE RemainingEstimate = '1m 2d' AND TimeSpent = '3m 2w'

Insert

Create a new issue in the Project. Project (specify either ProjectId or ProjectKey), IssueType (specify either IssueTypeId or IssueTypeName), and Summary are required for Inserts. ProjectName may also be specified instead, but it will cost an additional request to the server to find the ProjectKey value that it belongs to. Depending on the Project and the IssueType more fields may be required.

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, CustomField1) VALUES (10000, 'Desc from prod', 10100, 'test')
INSERT INTO Issues (ProjectKey, Summary, IssueTypeName, CustomField1) VALUES ('PROD', 'Desc from prod', 'Task', 'test')

Update

Update info of existing issues by providing the Id

UPDATE Issues SET Summary = 'Desc FROM prod', CustomField1 = 'ValidValue' WHERE Id = '10003'

You can also modify some aggregate fields for a specific issue. You can modify FixVersionsAggregate, AffectedVersionsAggregate, ComponentsAggregate and IssueLinksAggregate. Using the special inputs for these columns, you can assign, remove and update the appropriate field for an issue. Note that the object must first be created in the Jira application to use it with the provider queries. NOTE: The IssuelinksAggregate field has slightly different input requirements, outlined further below.

Structure for adding a fix version:

{ "add": {"name":"example"} }

or

{ "add": {"id":"1234"} }
An example query:
UPDATE Issues SET FixVersionsAggregate = '{"add":{"name":"extra"}}' WHERE Id = 19683

You can only add one object at a time, but using the set command you can assign multiple objects. IMPORTANT NOTE: The set command will overwrite all the previous objects, meaning that previous entries will be discarded, and only the objects specified in the set command will be saved to the issue. The set command must be followed by an array:

{ "set":  [ {"name":"New Component 1"}, {"name":"Old Component 2"} ] }

or 

{ "set":  [ {"id":"1234"}, {"name":"Old Component 2"} ] }

And an example query would be:

UPDATE Issues SET ComponentsAggregate = '{ "set":  [ {"id":"1234"}, {"name":"Old Components 2"} ] }' WHERE Id = 19683

You can also remove an entry one at a time using the remove command:

{ "remove": {"name":"example"} }

or

{ "remove": {"id":"1234"} }

UPDATE Issues SET AffectedVersionsAggregate = '{"remove":{"name":"extra"}}' WHERE id = 19683

These commands are also applicable when inserting a new issue:

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"add":{"name":"Example1"}}')

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"set":[{"name":"Example1"}]}')

IssueLinksAggregate

Only the 'add' operation is supported with IssueLinksAggregate, and you must specify the type of the link and the direction of the link, either 'outwardIssue' or 'inwardIssue', but not both. The valid link types are Blocks, RootCause, Relates, Duplicate and Cloners

{ "add":
  { "outwardIssue":
      {"key":"COB-15"},
    "type":
      {"name":"Cloners"}
  }
}

or

{ "add":
  { "inwardIssue":
    {"key":"FIL-78"},
    "type":
    {"name":"Blocks"}
  }
}

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"add":{"outwardIssue":{"key":"COB-15"},"type":{"name":"Cloners"}}}')

"UPDATE Issues SET IssuelinksAggregate = '{"add":{"inwardIssue":{"key":"FIL-1"},"type":{"name":"Blocks"}}}' WHERE Id = '22204'"

Delete

Delete an issue by providing the Id. Note that subtasks that have not been completed will be deleted as well.

DELETE FROM Issues WHERE Id = '10003'

Columns

Name Type ReadOnly References SupportedOperators Platform Description
Id [KEY] Int True =,!=,>,>=,<,<=,IN,NOT IN Common

The Id of the issue.

Key String True =,!=,>,>=,<,<=,IN,NOT IN Common

The key of the issue.

IssueTypeId String False =,!=,IS,IS NOT,IN,NOT IN Common

The issue type Id.

IssueTypeName String True =,!=,IS,IS NOT,IN,NOT IN Common

The issue type name.

ProjectId String False =,!=,IS,IS NOT,IN,NOT IN Common

The project Id of the issue.

ProjectName String True =,!=,IS,IS NOT,IN,NOT IN Common

The project name of the issue.

ProjectKey String False =,!=,IS,IS NOT,IN,NOT IN Common

The project key of the issue.

ParentId Int False =,!=,IN,NOT IN Common

The Id of the issue's parent if the issue is a subtask.

ParentKey String True =,!=,IN,NOT IN Common

The key of the issue's parent if the issue is a subtask.

ParentIssueTypeId String True Common

The issue type id of the issue's parent.

ParentIssueTypeName String True Common

The issue type name of the issue's parent.

ParentStatusId String True Common

The status id of the issue's parent.

ParentStatusName String True Common

The status name of the issue's parent.

ResolutionId String True =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The resolution Id of the issue.

ResolutionName String True =,!=,IS,IS NOT,IN,NOT IN Common

The resolution name of the issue.

ResolutionDescription String True Common

The resolution description of the issue.

ResolutionDate Datetime True =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The resolution date of the issue.

Workratio Long True =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The work ratio of the issue.

LastViewed Datetime True =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The last time that the issue was viewed.

WatchCount Int True =,!=,>,>=,<,<=,IN,NOT IN Common

The number of watches of the issue.

IsWatching Bool True Common

Whether the currently authenticated user is watching the issue.

Created Datetime True =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The creation date of the issue.

PriorityId String False =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The priority Id of the issue.

PriorityName String True =,!=,IS,IS NOT,IN,NOT IN Common

The priority name of the issue.

TimeSpentSeconds Long True Common

The time spent in seconds on the issue.

TimeSpent String True =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The time spent on the issue.

RemainingEstimateSeconds Long True Common

The time estimate in seconds of the issue.

RemainingEstimate String False =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The time estimate of the issue.

OriginalEstimateSeconds Long True Common

The original time estimate in seconds of the issue.

OriginalEstimate String False =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The original time estimate of the issue.

AggregateTimeSpent Long True Common

The aggregate time spent of the issue.

AggregateTimeOriginalEstimate Long True Common

The original aggregate time estimate of the issue.

AggregateTimeEstimate Long True Common

The aggregate time estimate of the issue.

AssigneeDisplayName String True Common

Assignee display name.

AssigneeKey String True =,!=,IS,IS NOT,IN,NOT IN Common

[DEPRECATED] The assignee key of the issue.

AssigneeAccountId String False =,!=,IS,IS NOT,IN,NOT IN Common

The assignee account id.

AssigneeName String False =,!=,IS,IS NOT,IN,NOT IN Common

The assignee name of the issue.

AssigneeEmail String True =,!=,IS,IS NOT,IN,NOT IN Common

The assignee email of the issue.

Updated Datetime True =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The updated date of the issue.

StatusId String True =,!=,IS,IS NOT,IN,NOT IN Common

The status Id of the issue.

StatusName String True =,!=,IS,IS NOT,IN,NOT IN Common

The status name of the issue.

StatusCategoryId String True =,!=,IS,IS NOT,IN,NOT IN Common

The status category id of the issue.

StatusCategoryKey String True =,!=,IS,IS NOT,IN,NOT IN Common

The status category key of the issue.

StatusCategoryName String True =,!=,IS,IS NOT,IN,NOT IN Common

The status category name of the issue.

Description String False IS,IS NOT Common

The description of the issue.

Summary String False IS,IS NOT Common

The summary of the issue.

CreatorDisplayName String True Common

Issue creator display name.

CreatorName String True =,!=,IS,IS NOT,IN,NOT IN Common

The creator name of the issue.

CreatorAccountId String True =,!=,IS,IS NOT,IN,NOT IN Common

The creator account id.

CreatorKey String True =,!=,IS,IS NOT,IN,NOT IN Common

[DEPRECATED] The creator key of the issue.

CreatorEmail String True =,!=,IS,IS NOT,IN,NOT IN Common

The creator email of the issue.

ReporterDisplayName String True Common

Issue reporter display name.

ReporterName String False =,!=,IS,IS NOT,IN,NOT IN Common

The reporter name of the issue.

ReporterKey String True =,!=,IS,IS NOT,IN,NOT IN Common

[DEPRECATED]The reporter key of the issue.

ReporterAccountId String False =,!=,IS,IS NOT,IN,NOT IN Common

The reporter account id.

ReporterEmail String True =,!=,IS,IS NOT,IN,NOT IN Common

The reporter email of the issue.

AggregateProgress Long True Common

The aggregate progress of the issue.

TotalProgress Long True Common

The aggregate total progress of the issue.

Votes Int True =,!=,>,>=,<,<=,IN,NOT IN Common

Votes of the issue.

HasVotes Bool True Common

The vote status of the issue.

DueDate Date False =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN Common

The due date of the issue.

Labels String False =,!=,IS,IS NOT,IN,NOT IN Common

The labels of an issue.

Environment String False LIKE,NOT LIKE,IS,IS NOT Common

The environment of an issue.

SecurityLevel String False =,!=,IS,IS NOT,IN, NOT IN Common

The security level of an issue.

FixVersionsAggregate String False Common

The fix versions of the issue

ComponentsAggregate String False Common

Aggregate list of components included in the issue.

IssueLinksAggregate String False Common

The issue links of the issue.

AffectedVersionsAggregate String False Common

The affected versions of the issue.

SubtasksAggregate String True Common

The subtasks of the issue.

CustomField1 Any False Common

A custom field created for the Issues. The custom fields will be listed if the 'IncludeCustomFields' connection property is set to true.

CustomField2 Any False Common

A custom field created for the Issues. The custom fields will be listed if the 'IncludeCustomFields' connection property is set to true.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
JQL String

JQL (Jira Query Language) allows you to build structured queries.

Attachments String

Search for issues that have or do not have attachments. This column can be queried with IS EMPTY or IS NOT EMPTY.

Comment String

Search for issues that have a comment that contains particular text.

Category String

Search for issues that belong to projects in a particular category.

SprintId Integer

Search for issues that belongs to a specific sprint, by specifying the Id of the Sprint.

SprintName String

Search for issues that belongs to a specific sprint, by specifying the name of the Sprint.

Filter String

Search for issues of saved filters. This column can be queried with filter name or filter id.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839