JDBC

SQL+JQL  allows running queries remotely via JDBC over the HTTP protocol. You need the SQL+JQL Driver running on the Jira Server for that but also the binary Java library installed on your Java app (file with the jar extension available on this page for downloading).

The driver will connect to JIRA over HTTP (with the Jira user's credentials). This will login in Jira in order to create an HTTP session, just like it happens when you log in Jira from the web application.

There are two editions of the same JDBC Driver library depending on the type  of the application: Java Desktop or Application Container (as Tomcat,...)

Download JDBC driver

Configuration parameters

• Driver class: com.kintosoft.jira.jdbc.Driver
• JDBC URL: jdbc:jira:<JIRA_BASE_URL>
• Database user's credentials: same than Jira user's credentials

Examples

Testing the Driver

Type in the command line:

java  -jar sql4jira-jdbc-driver-1.4.jar jdbc:jira:${JIRA_BASE_URL} ${JIRA_USERNAME} ${JIRA_PASSWORD}

and replace all the three variables above.

• ${JIRA_BASE_URL}
• ${JIRA_USERNAME}
• ${JIRA_PASSWORD}

If you need to connect via proxy, then you have to append the proxyUrl property to the ${JIRA_BASE_URL} and provide its value:

java  -jar sql4jira-jdbc-driver-1.4.jar jdbc:jira:${JIRA_BASE_URL}[;proxyUrl=${PROXY_URL}] ${JIRA_USERNAME} ${JIRA_PASSWORD}

• ${PROXY_URL}

Example:

java  -jar sql4jira-jdbc-driver-1.4.jar jdbc:jira:https://jirahost:8080;proxyUrl=http://proxyHost user password 

Connect from a custom Java program

//Register the JDBC driver 
Class.forName("com.kintosoft.jira.jdbc.Driver");

//JIRA authentication values 
String jiraBaseUrl = "http://localhost:8080/jira/";
String jiraUsername = "john";
String jiraPassword = "smith"; 

//Connect to JIRA just like to any other regular relational database
Connection conn = DriverManager.getConnection("jdbc:jira:" + jiraBaseUrl, jiraUsername, jiraPassword);
...

The JDBC driver authenticates the user against JIRA by using the JIRA base URL and the JIRA user's credentials. In order to authenticate by using the Windows/NTLM protocol, the users have to include the domain name in their usernames: DOMAIN\username as shown below:

String jiraUsername = "MYDOMAIN\\john"; 

Connect from a third party client reporting tool (BIRT)

It depends on each tool. The example below is for Eclipse BIRT however other tools would work similarly.

• Add the JDBC driver for JIRA in the tool's Java classpath.
• Add a new data source to your reporting project as usual. Then fill out the driver class, the JDBC URL (JIRA base URL) and the JIRA user's credentials: