How do you access other data sources starting JIRA 4.3?

[Rob Baily]

In versions prior to 4.3 you could include a Resource in the Tomcat server.xml and then an entry in the entityEngine.xml file and access the data source through ConnectionFactory.getConnection( dataSourceName ). Per the notes at http://confluence.atlassian.com/display/JIRA/Plugin+Developer+Notes+for+JIRA+4.3#PluginDeveloperNotesforJIRA43-AccessingdelegatorsconnectionsanddatasourcesinOfBiz this is no longer supported. I cannot determine how our plugin should access a database outside of the JIRA database. Please help!

[tier-0 grump · Accepted solution]

Yeah, sure - firstly in your server.xml, add a Resource

        <Context path="/jira" docBase="/src/jira50/classes/artifacts/jira" workDir="/src/jira50/target/work" reloadable="false" useHttpOnly="true">
           <Resource name="UserTransaction" auth="Container" type="javax.transaction.UserTransaction" factory="org.objectweb.jotm.UserTransactionFactory" jotm.timeout="60"/>
            <Resource auth="Container" driverClassName="org.postgresql.Driver" name="jdbc/otherDS" username="jirauser" password="jirauser" maxActive="20" type="javax.sql.DataSource" url="jdbc:postgresql://localhost:5432/otherDB" />

            <Manager pathname=""/>
        </Context>

Then in your entityengine.xml simply create a datasource element that references this resource, you can see I simply ignored the dire warnings :-) As we're not going to get ofbiz to manage the connection it is important to set all the checks to false.

<!--
DATASOURCE

    You should no longer define a datasource in this file, the database is now configured through the UI at setup time.
    The only time you would want to configure it here is when you migrate from an older version and need to point the
    new installation at an existing db. This is considered a legacy method and will not work if dbconfig.xml exists
    in the home directory.
     -->
    <datasource name="otherDS" field-type-name="postgres72"
                schema-name="public"
                helper-class="org.ofbiz.core.entity.GenericHelperDAO"
                check-on-start="false"
                use-foreign-keys="false"
                use-foreign-key-indices="false"
                check-fks-on-start="false"
                check-fk-indices-on-start="false"
                add-missing-on-start="false"
                check-indices-on-start="false">
    <jndi-jdbc jndi-server-name="default" jndi-name="java:comp/env/jdbc/otherDS"/>
    </datasource>

Now fro your Java code you can obtain a JDBC connection using

org.ofbiz.core.entity.ConnectionFactory.getConnection("otherDS")

[Rob Baily]

This looks like it is working! Thank you for your time!

[Nabil Sayegh]

Do you need to close the connection?

[tier-0 grump]

Officially moving forward, we want plugin developers to use Active Objects to access databases, but this doesn't help you a great deal, as the default behaviour here is to use the JIRA database. If you want to use an external database you can still add it to a Resource section, and you still have access to the entityengine.xml, and although the documentation no longer suggests that this works, ConnectionFactory is still available to you. There are some problems with this approach though, the main JIRA database is no longer managed through the resource pool but via direct JDBC, so I'm a little unsure if you could get transactions to span from the JIRA db to your db.

[Rob Baily]

In this case I don't need to worry about transactions. I am reading an external database to pull in data to set some custom JIRA fields so the other database is not updated in my plugin, it is read only. Could you possibly elaborate a little more on the solution of using the ConnectionFactory or how I would get another data source from the entityengine.xml? Its not very clear to me. Thanks!