Access database from REST plugin module in Confluence

[Jesús Jorge Muñoz]

Hi all, 

My team is developing a plugin for Confluence server and we got stuck in a point where we don't know how to continue.

We're developing a custom REST service that needs to access the Confluence database directly. Doing a google research, we've found several questions that suggest to use the following approach:

import com.atlassian.plugins.rest.common.security.AnonymousAllowed;
import com.atlassian.spring.container.ContainerManager;

import javax.ws.rs.*;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;
import java.io.InputStream;
import java.io.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import java.io.PrintWriter;
import java.io.StringWriter;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import org.json.simple.JSONObject;
import org.json.simple.parser.*; 
import com.atlassian.sal.api.component.ComponentLocator;
import com.atlassian.confluence.security.login.*;

/**
* A resource of message.
*/
@Path("/")
public class ServiceRest {


@POST
@AnonymousAllowed
@Consumes(MediaType.APPLICATION_JSON)
@Path("myrestservice")
public Response PostProject(InputStream inputStream)
{

...

// Process JSON request data

...

String myResult = "";
Connection connection = null;
ClassLoader threadClassLoader = Thread.currentThread().getContextClassLoader();
try
{
Thread.currentThread().setContextClassLoader(ContainerManager.class.getClassLoader());

InitialContext ctx = new InitialContext();
DataSource confluenceDs = (DataSource) ctx.lookup("java:comp/env/jdbc/ConfluenceDS");
connection = confluenceDs.getConnection(); 
Statement stmt = connection.createStatement();
String strSelect = "SELECT * from CONTENT"; // SQL query, change this as needed
ResultSet rset = stmt.executeQuery(strSelect);

...

// Process SQL query result

...

if(connection != null)
{
connection.close();
}

Thread.currentThread().setContextClassLoader(threadClassLoader);

if(myResult = "query_result"){
return Response.ok(new SignatureRestModel("OK")).build();
}else{
return Response.ok(new SignatureRestModel("NOT_OK")).build();
}
}
catch(SQLException | NamingException ex)
{
myResult = ex.toString(); 
StringWriter sw = new StringWriter();
PrintWriter pw = new PrintWriter(sw);
ex.printStackTrace(pw);
}

}

private String aString (InputStream inputStream){
StringBuilder stringBuilder = new StringBuilder();
String line = null;

try (BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream))) { 
while ((line = bufferedReader.readLine()) != null) {
stringBuilder.append(line);
}
}catch(UnsupportedEncodingException e){
System.out.println(e);
} catch (IOException e) {
System.out.println(e);
} 
return stringBuilder.toString();
}
}

This approach seems to not being working properly. We're not even getting a reply from the REST service, and also our Confluence server instance blocks. I suspect that this approach could be deprecated, but I'm not quite sure about that.

However, searching through Confluence developers documentation, I've found that it is possible to store Confluence plugin data using Active Objects. 

I have these questions: 

• Is the approach above appliable today? Are we missing something in it? 
• Knowing that with Active Objects is possible to store and retrieve custom plugin data, is it possible to access database data as well? E.g. searching a page in CONTENT SQL table

Thank you very much for your support!

Kind regards,
Jesús

[Nic Brough -Adaptavist-]

SQL access is the worst possible way to do anything in Atlassian applications.  I suspect you are running into the usual problem that it is hideously slow and inefficient.

I don't know if you really are using "select * from content", but that is a query pretty much guaranteed to uselessly slow on a Confluence with more than a couple of hundred pages, and will quickly fail on larger ones.

I would strongly recommend using the java API to do the work properly.