XPages JDBC Configuration via NotesDocuments

If you have used the Extension Library’s Relational controls, then you are probably familiar with the process of configuring your JDBC Connection details.

You have the option of setting up a ‘named’ connection so you can reference a database connection by name. This involves creating an xml file <yourconnection>.jdbc in the WebContent\WEB-INF\jdbc\ directory. This xml file specifies the driver, url, username, password, and perhaps some information about connection pooling.

Alternatively your can just build your own connection url and use this directly in your Java code or XPages controls.

Named connections are a much nicer way to go, and they allow for connection pooling, however the method of using the xml file within the WEB-INF\jdbc directory is a little annoying.

This is not a very accessible or flexible way to manage your connection information. Often your local development SQL Database will have different username / password / url etc. So you often end up changing the config files back and forth everytime you want to develop and deploy.

So I decided to come up with a way to store JDBC Connection information somewhere else, and it seemed logical to store it in NotesDocuments. This way the JDBC connections are configurable at run-time and do not require a change to a design.

Additionally, these JDBC configuration documents can be protected from read-access so that only admins and database signer’s can access them. The session as signer should be able to access them with elevated rights.

Short Demonstration Video

If you  would like to see the JDBC Provider in action please have a look at this video

How can I use this JDBCNotesDocumentProvider system?

Download the Alpha Release in my fork of the ExtLibX project and have a look at the documentation in the PDF in the release. You can also have a look in the demo video above.

As mentioned in my previous post, I have decided to release all my future contributions through the ExtLibX project.

If this solution is working for you then you should be fine to use it in production but I am just calling it a alpha release because I am looking to get some feedback before making a final ‘release’.

How does the JDBCNotesDocumentProvider work?

I basically copied the way the Extension Library initializes it’s JDBC Information from the xml files in the WEB-INF/jdbc folder, and modified it to instead look in NotesDocuments! Sounds simple but there was a little bit of fiddling around.

Basically:

• Someone Access the XPages application
• The JDBC Configuration Initialization is triggered
  • If it is set to use ‘local’ configuration it will search the current database for JDBCConnection info
  • If it is set to use ‘global’ configuration it will search the central configuration database for JDBCConnection info

Actually in my original solution, I was using an ‘ApplicationInitializer’ however this runs before you have access to sessionAsSigner, so this was a bit of a problem. I had trouble getting a full-access session but managed to do it with some trickery, however I don’t think this ‘trickery’ is suitable to be shared, so I came up with an alternative.

I moved the initialization code to a PhaseListener, and set it up so the initialization will only run if it hasn’t been successfully done yet. I still don’t like this so much but it works.

Feedback / Comments

Would love to hear if this is something you think you would use. If you have any suggestions / improvements please let me know!