How to connect to confluence database from plugin via an installed datasource?

[Ernesto Pin]

I've been asked to create a confluence plugin that displays some data about user's collaboration (blogs posted, likes given and received, etc). I didn't find API calls to do it in a performant way, so I tried to do it by direct SQL queries to confluence's database.

I did some tests getting hardcoded connections, but then, before moving the plugin to test I tried to switch to "install a datasource in confluence and get the connection from it". The datasource was created but I can't find the datasource to get a connection from it.

Datasource definition was defined in /conf/server.xml

...
<Context path="" docBase="../confluence" debug="0" reloadable="false"
	useHttpOnly="true">
	<Manager pathname="" />
	...
	<Resource name="jdbc/ConfluenceDB"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="net.sourceforge.jtds.jdbc.Driver"
		url="CONFLUENCE_DATABASE_URL"
		username="USER"
		password="PASSWORD"
		validationQuery="Select 1" />
</Context>
...

My code to get the datasource

InitialContext ctx = new InitialContext();
DataSource confluenceDs = (DataSource) ctx.lookup("java:comp/env/ConfluenceDB");

This fails:

javax.naming.NameNotFoundException: Name [java:comp/env/jdbc/ConfluenceDB] is not bound in this Context. Unable to find [java:comp].

My questions are

1. Is the datasource well-configured?
2. Which is the correct name for the datasource to do the lookup?
3. Is it possible that wrong values in the datasource prevent it from loading? I mean, if passowrd, user or URl are wrong, is the datasource loaded into the context or not?
4. Is the code to get the datasource correct?
5. Is there any datasource defined in confluence from which I can read confluence's database? how should I access it?

[Ernesto Pin · Accepted solution]

Implemented the solution suggested by the comment of @Radu Dumitriu from 11/13/2011 in https://answers.atlassian.com/questions/6374

Connection confluenceConnection = 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/ConfluenceDB");
			
	confluenceConnection = confluenceDs.getConnection();
			
	Statement stmnt = confluenceConnection.createStatement();
	ResultSet result = stmnt.executeQuery("SELECT SOMETHING FROM CONFLUENCE DATABASE");
 
	// Process result
				
} catch (NamingException e) {
	// Handle the exception
} finally {
	Thread.currentThread().setContextClassLoader(threadClassLoader);
		
	if(confluenceConnection != null){
		confluenceConnection.close();
	}
}

[Ben Weaver]

Thanks for posting your code! I am in a similar position to yours at the time of asking the question, but it's not clear to me how to adopt the approach you have here, or whether it answers all your questions.

What does "java:comp/env/ConfluenceDB" correspond to?

If it has to match something defined in the server config xml, then how can this method be used in a plugin that might be installed in Confluences with different configurations?

[Ernesto Pin]

Hi Ben! The java:comp/env is standard (at least in my case) "ConfluenceDB" (or "jdbc/ConfluenceDB", as I have in the xml snippet) comes from your config in server.xml.

For instance, in the question I posted, I defined "jdbc\ConfluenceDB" as the name of the datasource, so that's what I should have put in the code. This might have lead to some confussion.

[Reinier]

Hi Ernesto, 

Thanks for sharing. You mentioned that you used Radu's work but he uses a BundleActivator. How does your setup looks like? 

Futhermore, which class is the Container-Manager?

[Ernesto Pin]

I'm trying to get the datasource through services injected in a REST resource.