how to access external database through Confluence Plugin

[Sreylin Doung]

Dear All,

I am using Confluence 4.3. I created a simple Confluence plugin. And I created a new database in MySQL. I want to get data from the database into my plugin. So how can I do this? Can I connect to MySQL and query data into my plugin?

Thanks,

[David at David Simpson Apps]

To access an external database from within Confluence...

You specify something like this in <install-dir>/conf/server.xml:

<Resource name="jdbc/myDataSource"
          auth="Container"
          type="javax.sql.DataSource"
          username="myusername"
          password="mypassword"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=utf8"
          maxActive="15"
          maxIdle="7"
          defaultTransactionIsolation="READ_COMMITTED"
          validationQuery="Select 1" />

-- source

Then reference the resource something like...

DataSource dataSource;
	try 
	{
		InitialContext initalContext = new InitialContext();		
		Context context = (Context) initalContext.lookup("java:comp/env");
		dataSource = (DataSource) context.lookup("jdbc/myDataSource");
	} 
	catch (NamingException ex) 
	{
		// ...
	}

See also: How to Setup Global JNDI Mapping for Oracle JDBC Connection Pooling with Tomcat

[Sreylin Doung]

Thanks David for your answer.

[David at David Simpson Apps]

@Sreylin Doung If it works, vote it up/mark as correct :)

[Kul]

You can create jndi external datasource as described here using the confluence sdk.

https://developer.atlassian.com/docs/developer-tools/declaring-jndi-datasources-in-amps

Just add in your pom.xml like i did for SDK version 5.8.6.

    <build>
        <plugins>
            <plugin>
               
                <groupId>com.atlassian.maven.plugins</groupId>
                <artifactId>maven-amps-plugin</artifactId>
                <version>${amps.version}</version>
                <extensions>true</extensions>
                <configuration>
                    <instanceId>instanceId1</instanceId>
                    <products>
                        <product>
                            <id>confluence</id>
                            <instanceId>instanceId1</instanceId>
                            <version>${confluence.version}</version>
                            <dataPath>src/main/resources/empty-home</dataPath>
                            <dataSources>
                                <dataSource>
                                    <jndi>jdbc/DefaultDS</jndi>
                                    <url>jdbc:postgresql://localhost:5432/confluence</url>
                                    <username>confluence</username>
                                    <password>confluence</password>
                                    <driver>org.postgresql.Driver</driver>
                                    <libArtifacts>
                                        <libArtifact>
                                            <groupId>postgresql</groupId>
                                            <artifactId>postgresql</artifactId>
                                            <version>9.1-901-1.jdbc4</version>
                                        </libArtifact>
                                    </libArtifacts>
                                </dataSource>
                                <dataSource>
                                    <jndi>jdbc/externalDataSource</jndi>
                                    <url>jdbc:oracle:thin:@host:1525:dbname</url>
                                    <username>SCHEMA</username>
                                    <password>PASSWORD</password>
                                    <driver>oracle.jdbc.OracleDriver</driver>
                                    <libArtifacts>
                                        <libArtifact>
                                            <groupId>com.oracle</groupId>
                                            <artifactId>ojdbc6</artifactId>
                                            <version>11.2.0</version>
                                        </libArtifact>
                                    </libArtifacts>
                                </dataSource>
                            </dataSources>
                        </product>
                    </products>
                </configuration>
            </plugin>
            <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>
                </configuration>
            </plugin>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.6</source>
                    <target>1.6</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <properties>
        <confluence.version>5.8.6</confluence.version>
        <confluence.data.version>5.8.6</confluence.data.version>
        <amps.version>5.0.13</amps.version>
        <plugin.testrunner.version>1.2.3</plugin.testrunner.version>
    </properties>

How to Use:

InitialContext initialContext = new InitialContext();

Context context = (Context) initialContext.lookup("java:comp/env");

dataSource = (DataSource) context.lookup("jdbc/externalDataSource");

You might need to clean up your m2 if you are playing with versions. altas-clean and then atlas-run will run the same for you. Hope it will help.