How to connect to the database from a Confluence plugin

[Míkel Rodríguez Domínguez]

I need to connect to the Confluence database from a plugin to query a table directly. If I try to do it either I get an exception because the driver class is not found or I get an exception like this:

Unable to resolve ... [311](R 311.0): missing requirement [311](R 311.0)] osgi.wiring.package; (osgi.wiring.package=com.mchange.v2.c3p0)

What can I do to make my plugin connect to the databse?

[Mikail Karadeniz]

What I’m doing is allowing the admin to write their query in the UI, and then, via a button, it’s directed to the code where it should send the query to the database and retrieve the result so I can present it as a table in the UI. How did you achieve the database connection to the Confluence database? in Jira it was really easy but in Confluence not so much.

[Sooxin]

An easy way is `com.atlassian.sal.api.rdbms.TransactionalExecutor`: 

TransactionalExecutorFactory transactionalExecutorFactory = ComponentLocator.getComponent(TransactionalExecutorFactory.class);

TransactionalExecutor transactionalExecutor = transactionalExecutorFactory
.createReadOnly()
.newTransaction();

transactionalExecutor.execute(connection -> {
// your service
});

[Sooxin]

Just inject component `com.atlassian.activeobjects.spi.DataSourceProvider`, then you can get an app's datasource object from its API

[Thiago Masutti]

Hi @Míkel Rodríguez Domínguez 

You can access the information stored on the database through Java APIs, but you won't be able to write down your own queries.

The following pages might be helpful:

• Writing Confluence plugins 
• https://developer.atlassian.com/server/confluence/accessing-confluence-components-from-plugin-modules/ 

If you have more questions on developing a plugin, the Developer Community might have a better audience to help you with that.

Kind regards,
Thiago Masutti

[Míkel Rodríguez Domínguez]

Why do you say that I won't be able to write my own queries? I think I remember writing Jira plugins which ran SQL queries some years ago, the problem is I don't remember how we managed to do it; also, I know ScriptRunner and I've used it to make queries to the database in some cases, and I know it can do it, why should ScriptRunner be able to make those queries and a plugin developed by me cannot? I think there has to be a way to run SQL queries from a Confluence plugin.

It should be as easy as having the mysql driver as a dependency of the plugin but I'll always get some kind of an OSGi error which I don't know how to deal with...

[JanR]

As Thiago suggested: Try asking the developer community.

[Míkel Rodríguez Domínguez]

I've come up with the solution. You've got to import the corresponding database driver as a dependency in the pom.xml but then you'll get an error similar to this:

Unable to resolve ... [311](R 311.0): missing requirement [311](R 311.0)] osgi.wiring.package; (osgi.wiring.package=com.mchange.v2.c3p0)

It's because for some reason when you import the jar of the driver, all of its dependencies become mandatory, so for each dependency you see the error you've got to tell the framework that they're optional. For the MySQL driver I had to do this:

<plugin>
    <groupId>com.atlassian.maven.plugins</groupId>
    <artifactId>maven-confluence-plugin</artifactId>
    <version>${amps.version}</version>
    <extensions>true</extensions>
    <configuration>
        <productVersion>${confluence.version}</productVersion>
        <productDataVersion>${confluence.data.version}</productDataVersion>
        <enableQuickReload>true</enableQuickReload>
        <enableFastdev>false</enableFastdev>

        <!-- See here for an explanation of default instructions: -->
        <!-- https://developer.atlassian.com/docs/advanced-topics/configuration-of-instructions-in-atlassian-plugins -->
        <instructions>
            <Atlassian-Plugin-Key>${atlassian.plugin.key}</Atlassian-Plugin-Key>

            <!-- Add package to export here -->
            <Export-Package>

            </Export-Package>

            <!-- Add package import here -->
            <Import-Package>
                <!-- Entries needed for the MySQL driver to work. -->
                com.mchange.v2.c3p0;resolution:="optional",
                org.hibernate.service.jdbc.connections.spi;resolution:="optional",
                org.jboss.resource.adapter.jdbc;resolution:="optional",
                org.jboss.resource.adapter.jdbc.vendor;resolution:="optional",

                <!-- These entries were here by default since plugin creation. -->
                org.springframework.osgi.*;resolution:="optional",
                org.eclipse.gemini.blueprint.*;resolution:="optional",
                *
            </Import-Package>

            <!-- Ensure plugin is spring powered -->
            <Spring-Context>*</Spring-Context>
        </instructions>
    </configuration>
</plugin>