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

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?

2 answers

1 accepted

0 votes
Accepted answer

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();
	}
}

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?

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.

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?

 

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Friday in Agile

Are you a Jira Service Desk agent? We want to talk to you!

Are you a whiz at handling tickets and looking at how you can further optimize your workflow with automation? Do you tackle detailed customer support questions while simultaneously getting flooded wi...

90 views 0 5
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you