Sharing DB Connections and already loaded scripts, styles on JSP Page

Question

Hello everyone,

I am currently trying to code our existing desktop application in JSP.

To be able to maintain sustainability, have visual effects and get functionality, I have decided to use JQuery.

After starting to code a month ago, we now realized that these pages are bringing a heavy usage of libraries (JQuery and JQueryUI libraries, extra plugins and CSS files) called in every single page. Also we need oracle database connection in every process.

What I want to have is a main page (lets say called Application.jsp). Which will

1. Connect to the DB, keep the connection alive (the connection is successful and its alive as long as i keep using it)
2. Calling the sub-pages with ajax. Sub-page is coded as raw html. (Tried load() and get() with Jquery, and successful)
3. Applying the JQuery functions and CSS on the loaded sub-page (couldn't have it working)
4. Getting the query result using getJSON() by using the alive DB connection. (Also couldn't have this working, might be because JQuery did not work on my loaded page or maybe database connection cannot be shared)

If, I can have this working in this way, that will save me loading hundreds of kB's libraries in every single sub-page call. Also i wont need to maintain a Database connection for every process.

Any help would be appreciated. Thanks...

Answer 1

Don't keep connections forever open yourself. The DB will close them when it's being kept open for too long and your application will break. The common practice is to use a connection pool. Decent servletcontainers offers facilities for this in flavor of a JNDI datasource. The connection pool implementation will itself worry about acquiring and closing the actual connection the right way.

It's unclear which servletcontainer you're using, so here are just some Tomcat-specific documents as example:

• Tomcat JNDI resources HOWTO
• Tomcat JNDI datasource HOWTO

Once done that, change the connection manager in your JDBC code to replace DriverManager#getConnection() by DataSource#getConnection() and keep the remnant of your JDBC code solid (acquire and close the connection in shortest possible scope, always close all resources in finally block). If you're uncertain about that as well, you can find some insights in this basic JDBC DAO tutorial.

Answer 2

On the connection management, definitely go with BalusC's recommendation. Lookup your App Server or Container's resource implementation and setup the database connections in the app server.

On the application of JQuery functions and event handlers to new AJAX loaded content, you will need to use a jquery selector and reapply anything previously applied to the whole document, but you can run it only against the newly loaded contents, not the entire document tree.