How to connect jira instance with a second database through a Jira Plugin?

Jira Development Jira Server
,
#1

I am developing a jira plugin where I would like to query a external Oracle database (create, update, get, delete). The host jira instance will still be connected to our postgres database for all jira functionalities. I just need to query the external database for one specific functionality built in the addon. My plugin would never access both postgress and external oracle database at the same time.

Is it possible to configure another database in the dbconfig.xml or some other db files and make database queries through a JDBC connection using the oracle drivers. I plan to wrap all the external queries in a jira-plugin-rest module, so my plugin’s client side can perform ajax request using that rest api and in return it will hit the external database.

Is this possible? Any guidance is appreciated.

Thanks!

#2

You’ll have to write your own data base pooling and manage the connections yourself. The Jira application only knows about it’s own database (and you’ll want to keep it that way :slight_smile: ).

Depending on what you’re doing, you might want to look at the Atlassian Marketplace - I know there are a couple of add-ons out there that pull external databases and exposes them as custom fields.

#3

@daniel

Hi Daniel,

can you provide a little more detail about what you mean by database pooling? How do I hook up the external database to Jira? What configurations are needed?

#4

You’ll need to take care of all of that in your add-on. Unfortunately Jira doesn’t know about anything about other databases. So you’ll need to write the java code that creates the connection and then makes sure it’s always available to the various threads.

You’ll want to use a database connection pool (i.e. a set of connections that are reused) since otherwise you’ll potentially create 1:1 request: database load on the remote database and as traffic goes up for your add-on so will it for the remote database (until it falls over at which point Jira will fall over).

1 Like
#5

I was looking through the JDBC usage in Jira, and it looks like the following is used for JDBC connection:

Connection conn = new DefaultOfBizConnectionFactory().getConnection();

I think this only gives me a jdbc connection to jira’s database (in my case it is a postgres).

If i want to establish a connection to a external oraacle database. I am assuming I have to do something like this in the plugin. For each ajax call made on the client side, the someRestMethod() gets executed and establish a new connection each time:

public data someRestMethod(some get params) {
         Class.forName("oracle.jdbc.driver.OracleDriver");
          Connection connection = null;
         connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:SID","username","password");
         //do some oracle procedure calls to retrieve data
         //parse data 

        connection.close();
}

Does this make sense?

#6

Yeah that’s the basics of what you’ll need to do, however this will create a new connection on each rest call which depending on traffic and the time of the database call may cause issues.

If your Oracle instance is set up to have a max 25 connections and you’re getting 52 http requests a minute - each one taking 30 seconds - there will be 2 that will be denied access as well as nobody else can get a connection to the database. Now this might not be the case in your environment (i.e. this is for an internal app and/or it’s an environment you control etc), but just bear it in mind.

http://www.oracle.com/technetwork/articles/vasiliev-oracle-jdbc-090470.html looks like a decent article on database pooling.

3 Likes