Configure a JNDI datasource

Definition

Configuring a JNDI datasource allows you to create the bridge between a JNDI resource in your Tomcat Application server and the Elements Connect add-on.

JNDI datasources use JDBC drivers that get a connection to a datasource. Once a JNDI datasource is configured, you'll be able to:

• Browse the datasource from the field configuration tester

• Query the datasource using SQL to feed your Elements Connect fields.

The advantages of using a JNDI datasource over a Database connection are multiple:

• JNDI datasources use underlying Tomcat connection pool thus optimising expensive connection resources creation and a providing faster access to Elements Connect fields data

• The direct database connection only supports 4 databases: Oracle, SQL Server, PostgresQL and MySQL. If you datasource is not in this list, there shall be a JDBC driver for it.

The configuration of a JNDI datasource is done in two steps:

• declare a JNDI datasource in Tomcat

• configure the JNDI datasource in Elements Connect

Declaring a JNDI datasource in Tomcat

Download and install the JDBC driver

There are JDBC drivers for many datasources. The following drivers have been tested and are compatible with Elements Connect.

If your datasource is not listed, chances are that a JDBC driver exists: JBDC is a well-established and common standard.

Add the JDBC driver to Tomcat

The first step is to add the JDBC driver in the Tomcat lib  folder. 

This folder is located in the Atlassian installation directory.
On Linux, the default installation directory is /opt/atlassian/jira the lib folder is /opt/atlassian/jira/application/lib 

Read the official Atlassian documentation about Jira application directory.

Example with the jdbc-driver-csv

jira@blue-dog:~$ cd /opt/atlassian/jira/application/lib/
jira@blue-dog:/opt/atlassian/jira/application/lib/$ ls
annotations-api.jar         catalina-ant.jar  catalina-storeconfig.jar  el-api.jar          jasper.jar                jonas_timer-1.4.3.jar      jotm-jrmp_stubs-1.4.3.jar  jul-to-slf4j-1.7.9.jar     objectweb-datasource-1.4.3.jar  servlet-api.jar          tomcat-api.jar     tomcat-i18n-es.jar  tomcat-i18n-ru.jar  tomcat-util.jar       websocket-api.jar
carol-1.5.2.jar             catalina-ha.jar   catalina-tribes.jar       hsqldb-1.8.0.5.jar  jaspic-api.jar            jotm-1.4.3.jar             jsp-api.jar                log4j-1.2.16.jar           ots-jts-1.0.jar                 slf4j-api-1.7.9.jar      tomcat-coyote.jar  tomcat-i18n-fr.jar  tomcat-jdbc.jar     tomcat-util-scan.jar  xapool-1.3.1.jar
carol-properties-1.5.2.jar  catalina.jar      ecj-4.6.3.jar             jasper-el.jar       jcl-over-slf4j-1.7.9.jar  jotm-iiop_stubs-1.4.3.jar  jta-1.0.1B.jar             mssql-jdbc-6.2.1.jre8.jar  postgresql-9.4.1212.jar         slf4j-log4j12-1.7.9.jar  tomcat-dbcp.jar    tomcat-i18n-ja.jar  tomcat-jni.jar      tomcat-websocket.jar
jira@blue-dog:/opt/atlassian/jira/application/lib/$ wget https://repo1.maven.org/maven2/org/xbib/jdbc/jdbc-driver-csv/1.2.0/jdbc-driver-csv-1.2.0.jar

Resolving repo1.maven.org (repo1.maven.org)... 151.101.200.209
Connecting to repo1.maven.org (repo1.maven.org)|151.101.200.209|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 215211 (210K) [application/java-archive]
Saving to: ‘jdbc-driver-csv-1.2.0.jar’

100%[===============================================================================================>] 215,211     --.-K/s   in 0.008s

2019-07-18 12:00:22 (24.8 MB/s) - ‘jdbc-driver-csv-1.2.0.jar’ saved [215211/215211]

Declare the JNDI datasource in Tomcat server.xml

Locate the server.xml Tomcat configuration file.

In a standard Linux installation, this file is located under /opt/atlassian/jira/application/conf

In the server.xml  file, locate the Context section (under Engine > Host):

<Engine name="Catalina" defaultHost="localhost">
    <Host name="localhost" appBase="webapps" unpackWARs="true" autoDeploy="true">
        <Context path="" docBase="${catalina.home}/atlassian-jira" reloadable="false" useHttpOnly="true">
            <!-- (...) -->
        </Context>
        <!-- (...) -->

You may have existing <Resource>  entries under the <Context> section, like: 

<Resource 
    name="UserTransaction" 
    auth="Container" 
    type="javax.transaction.UserTransaction"
    factory="org.objectweb.jotm.UserTransactionFactory" 
    jotm.timeout="60"/>

Add a new section to declare you JNDI connection - here is an example using the PostgreSQL JDBC driver:

<Resource 
    name="jdbc/CRMDS" 
    auth="Container" 
    type="javax.sql.DataSource"
    username="user"
    password="password"
    driverClassName="org.postgresql.Driver"
    url="jdbc:postgresql://servername:5432/CRM"/>

The final file is

<Context path="" docBase="${catalina.home}/atlassian-jira" reloadable="false" useHttpOnly="true">
    <Resource name="UserTransaction" auth="Container" 
      type="javax.transaction.UserTransaction"
      factory="org.objectweb.jotm.UserTransactionFactory" jotm.timeout="60"/>
    <Resource name="jdbc/CRMDS" auth="Container" type="javax.sql.DataSource"
      username="user"
      password="password"
      driverClassName="org.postgresql.Driver"
      url="jdbc:postgresql://servername:5432/CRM"/>
    <Manager pathname=""/>
    <JarScanner scanManifest="false"/>
    <Valve className="org.apache.catalina.valves.StuckThreadDetectionValve" threshold="120" />
</Context>

Each JDBC driver has its own specificities, read the JDBC driver documentation to learn more.

The important parts are:

• name - the JNDI resource name that you will use in the Elements Connect datasource configuration - it must be unique

• driverClassName - unique for every JDBC driver

• url - the JDBC connection URL, usual well documentation

To learn more, read the official Tomcat documentation.

Restart Jira

To be able to use the new JNDI connector in Elements Connect, Jira must be restarted.

Configuring a JNDI datasource in Elements Connect

Once you have declared a JNDI resource in Tomcat (and restarted Jira), you can declare a new datasource in Elements Connect.

From the datasource configuration section, click on "Create a datasource" then select "JNDI reference".

Name

It will be used later to reference the datasource connector to use in the configuration of Elements Connect fields

Description

A reminder of why you are using this connector

JNDI name

Name of the resource declared in the server.xml file (see above)

 Prepend the name declared in Tomcat with java:comp/env/

If the JNDI resource name is jdbc/crm-database , in Elements Connect the JNDI name will be java:comp/env/jdbc/crm-database.

Timeout

Retrieving data from a jndi datasource is done in two steps:

1. Connect - Establishing the connection to the datasource

2. Read - Waiting for the datasource to return the result of a query

Some queries may take a long time to return from the datasource because of the query itself or unusual load of the datasource.
A long delay can have a negative impact on Jira load times, whether a user is editing or viewing an Issue.

That's why two timeouts are set on JNDI datasources:

1. Connect timeout - A non-configurable 5 seconds timeout applied to the "Connection" step.

2. Read timeout - A configurable timeout with a default value of 10 seconds applied to the "Read" step
   This value can be configured from the datasource configuration, from 1 second to 600 seconds (10 minutes).
   The lower this value, the less impact a slow remote server might have on the Jira load time.

When a timeout is reached, the query to the datasource is aborted.
Elements Connect behavior depends on the context:

• Field edit: no option available in the field editor

• Issue display: you'll see the field "stored" value (the field key) on which the "No result template" is optionally applied

The last step is to Save the configuration!

Field configuration

Once you have declared a new JNDI datasource, you can use it in a field configuration and query with SQL.

See Configure a field query.

Drivers configuration

This section shows how to declare some drivers in the server.xml  file

Sql Server

Driver

jTDS JDBC Driver

Configuration

Datasource declaration in the Tomcat server.xml file:

<Resource name="jdbc/ext"
    auth="Container"
    type="javax.sql.DataSource"
    username="TfsToJira"
    password="password"
    driverClassName="net.sourceforge.jtds.jdbc.Driver"
    url="jdbc:jtds:sqlserver://SERVERNAME:1433/WSS_Content;user=TfsToJira;password=password"/>

JNDI Database configuration in Elements Connect:

PostgreSQL

Driver

PostgreSQL JDBC driver

Configuration

Datasource declaration in the Tomcat server.xml file:

<Resource name="jdbc/CRMDS" auth="Container" type="javax.sql.DataSource"
    username="user"
    password="password"
    driverClassName="org.postgresql.Driver"
    url="jdbc:postgresql://servername:5432/CRM"/>

JNDI Database configuration in Elements Connect:

csvjdbc

Driver

Project is hosted on GitHub.

Jar files can be downloaded from https://sourceforge.net/projects/csvjdbc/files/CsvJdbc/

Configuration

Datasource declaration in the Tomcat server.xml file:

<Resource name="jdbc/sales-csv"
    auth="Container"
    type="javax.sql.DataSource"
    driverClassName="org.relique.jdbc.csv.CsvDriver"
    url="jdbc:relique:csv:/Path/to/csv/files/folder?separator=,"
/>

Replace /Path/to/csv/files/folder by the path to the CSV file.

JNDI Database configuration in Elements Connect:

Once the JNDI data source has been configured, any new CSV files that are added to the specified path or updates to existing CSV files are immediately reflected in Elements Connect, and there is no need to restart Jira.

Documentation

If needed, see:

• Is it possible to configure dependent fields connected to a CSV datasource?

• Example of a CSV file used as a JNDI

The driver also offers advanced configuration options, like the ability to set the type of the columns: https://github.com/simoc/csvjdbc/tree/master/docs