External SQL datasource in Confluence plugin Edited

[Reinier]

Hi, 

My goal is simple: within a Confluence servlet plugin, create a SQL connection to different database than Confluence uses and retrieve / store some data. Should be easy but after weeks it has become very frustrating and we're on the verge of dropping Confluence integration at all and just build a dedicated web application.

We took the following approaches:

• directly load the net.sourceforge.jtds driver (jtds-.1.3.1) jar using the 'provided' tag in the pom.xml file. The jar file is available on both /confluence/lib folder and confluence/confluence/WEB-INF/lib folder. This never seems to work, allways a java.lang.ClassNotFoundException: net.sourceforge.jtds.jdbc.Driver not found exception in the catalina.out. Checking /plugins/servlet/upm/osgi (ref) indicates that net.sourceforge.jtds is just not loaded, it is not on Bundle-Classpath (which is nowhere listed) and not listed under imported_packages 
• compile net.sourceforge.jtds driver. This required jcifs dependency. Everything compiles but running the plugin fails with an java.lang.ClassCastException: com.atlassian.plugins.tutorial.refapp.MyPluginServlet cannot be cast to javax.servlet.http.HttpServlet
• Using the JNDI approach. We started with just a very simple HelloWorld plugin example and included the following code which worked perfectly:

Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/DocControlDB");
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM DATABASE.dbo.TABLE");
etcetcetc

• Above code works so the JNDI configurations are correct. The same code works for a standalone 8.0 Tomcat servlet application- so completely outside an atlassian environment
• It fails when it is inserted in a very basic Confluence servlet application: it creates a java.lang.ClassNotFoundException: org.apache.naming.java.javaURLContextFactory error when calling Context ctx = new InitialContext();
• We've tried to included it directly in the main program or via rest.

Quite some topics can be found covering the ClassNotFoundException related to IntialContext. The one that seemed to work was listed here: 

ClassLoader origCL = Thread.currentThread().getContextClassLoader();
try {
Thread.currentThread().setContextClassLoader(ComponentManager.class.getClassLoader());
//::TODO:: do lookup here
} finally {
Thread.currentThread().setContextClassLoader(origCL);
}

The ComponentManager does not exist anymore. I tried with ContainerManager but doesn't work either. 

I found quite some reference material using google, but most of the comments are of the 2012-2014 era, and solutions posted there do not work anymore. 

If somebody could create a simple example on how to include SQL connectivity, preferrably using JNDI, that would be awesome :-)

[Pavel Potcheptsov]

But PocketQuery plugin works somehow..

Solution must exist.