how can you add, remove or change connections in a pool from within a web app?

[Tom Malia]

I want to create a Web application that uses JDBC connection pools for accessing several database. However, I want to be able to define the data sources for the elements in the connection pools via a "configuration page" in the web applications.

I've seen lots of examples of how to define the connection pools in the context.xml and that all works great. However, it doesn't appear possible to add new database connections or change the definitions of existing database connections in the pools once the web application is initiate.

There's got to be a way to do this. I'm guessing I would do something like instantiate a connection pool object in an initialization servlet and add it to the Web application's attributes but I'm not completely clear on how this would be done.

Can some one help me understand how I would go about this?

Thanks,

[Paul Clapham]

You don't say what connection pool you're using; however if your connection pool supports JMX then you'd use a JMX client such as Jconsole to control it.

And, since the connection pool belongs to the server and not to any of the applications running in the server, it's not realistic to assume that any of the applications would be allowed to meddle with the pool. That's not to say it's impossible, but it does imply that it is likely to not be easy.

[Tom Malia]

First, I'm a relative newbie to Java web application development in general, so I apologies if my questions are not clear or down right stupid.

Now, let me see if I can explain what I trying to do a bit better. I'm developing a web application that is used to read data from an Access database that is used for a commercial product. The commercial product is not the web app I'm trying to right. Its a program that manages registration and timing for races. My web app is just going to read the data from the database that program creates and updates so that it can make that information available to participants and spectators at the race. Think of like a Swim meet or a track meet.... so there's software for registering the races, scheduling the race and tracking the times. My web app lets a user pick the particular race database that the user wants to effectively "broadcast" to the attendies model devices.

The commercial product lets the user create new databases any time and lets the user put them any where.

I want my web application to have some configuration/setup pages where an administrator user can go and define the location of the particular Access database file that should be the source database for the Web Application.

So, when the data base is selected in the administrative page, a new connection pool should be created with this database as the source. This connection pool should be used by all the other parts of the webapp as the source data.

Ideally I want to create a web app that can be configured without asking someone to have to mess with the different application configuration XML files. The idea would be, you deploy the app to a server, then when someone tries to log into the app, the app would check to see if a connection pool to a valid database has already been setup. If not, then the would be routed to the maintenance page where they can select(or some how specify) the database that should be used as the source database for the application.

So I guess I'm looking for a way to initiate a connection pool and then store in the application context programmatically from a Servlet.

Does that better explain what I'm looking for?


I found some code on the Apache Tomcat site that looks like it might be headed in the right direction... would the following be a starting point? Would I do something like this and then to a SetAttribute on the application and stuff the instance of the DataSource object this code creates in there, then use that in other pages to get a connection from the Pool?




import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

public class SimplePOJOExample {

public static void main(String[] args) throws Exception {
PoolProperties p = new PoolProperties();
p.setUrl("jdbc:mysql://localhost:3306/mysql");
p.setDriverClassName("com.mysql.jdbc.Driver");
p.setUsername("root");
p.setPassword("password");
p.setJmxEnabled(true);
p.setTestWhileIdle(false);
p.setTestOnBorrow(true);
p.setValidationQuery("SELECT 1");
p.setTestOnReturn(false);
p.setValidationInterval(30000);
p.setTimeBetweenEvictionRunsMillis(30000);
p.setMaxActive(100);
p.setInitialSize(10);
p.setMaxWait(10000);
p.setRemoveAbandonedTimeout(60);
p.setMinEvictableIdleTimeMillis(30000);
p.setMinIdle(10);
p.setLogAbandoned(true);
p.setRemoveAbandoned(true);
p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
"org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
DataSource datasource = new DataSource();
datasource.setPoolProperties(p);

[Paul Clapham]

Access? Really?

Ouch. Access isn't really designed for access by multiple users at the same time, and you might have trouble finding a connection pool which works with it. Any other database would have been a better choice. However it looks like you're stuck with that decision, so let's assume that problem is overcome and you've got a connection pool which works.

Then I don't see any reason why the connection pool can't be managed by your application in the way you describe. Just forget that it's possible for Tomcat to manage connection pools -- i.e. you don't have to use the connection pool built into Tomcat, you can choose any implementation you like -- and write the code which implements what you said there.

[E Armitage]

Also, production setups usually mean that those databases are not infinite and going to come from unknown sources. You need to know that the database you are now using has tables that support your application too. So very likely the real life scenario here is that the possible databases that can be used are already known upfront before you deploy your application.

[Paul Clapham]

Tom Malia wrote:I'm developing a web application that is used to read data from an Access database that is used for a commercial product. The commercial product is not the web app I'm trying to right. Its a program that manages registration and timing for races. My web app is just going to read the data from the database that program creates and updates so that it can make that information available to participants and spectators at the race. Think of like a Swim meet or a track meet.... so there's software for registering the races, scheduling the race and tracking the times. My web app lets a user pick the particular race database that the user wants to effectively "broadcast" to the attendies model devices.

The commercial product lets the user create new databases any time and lets the user put them any where.

I want my web application to have some configuration/setup pages where an administrator user can go and define the location of the particular Access database file that should be the source database for the Web Application.

There's an ambiguity here which I would like to clear up. This web application you're developing... it needs to use an Access database on the race manager's computer, right? So that would mean that the web application would also have to be installed on that computer. So that would imply the process of downloading and installing both Tomcat and your web application. Was that what you envisaged? Or were you thinking that you would have a web application already installed and running on a server somewhere, and that it would reach out and use the Access database on that other computer?

[Tom Malia]

Regarding the use of Access.... ya I know. Not a great database engine for anything other than one user, local stuff. But like I said and as you noticed, I don't really have control over that.

Regarding:

Also, production setups usually mean that those databases are not infinite and going to come from unknown sources.

: In this case actually production setup means exactly that the number of possible database are infinite and the location are unknown. The user of the Race management software can create any new databases they want, anywhere they want. Think of it like someone going into Excel and just creating an new workbook (xls) file. The one significant difference from the Excel example is that the race management program always creates databases with the exact same schema structure which is why I can reliably connect to any database it creates and know I can read it.

Regarding:

So that would imply the process of downloading and installing both Tomcat and your web application.

:Yep... that's pretty much right.

A little more pertinent background.... given that NOTHING I'm trying to do would be recommended procedure for an serious web application.... This is REALLY small time stuff I'm doing here. For example, right now all I'm trying to do is make a Cub Scout Pinewood Derby event for my son's scout pack a little more fun. Eventually I might try doing the same kind of thing for my kid's Swim meets which are quite small events. So, these web apps will need to run for maybe a few hours (ok, if you've got kids on swim teams you now "a couple hours" is optimistic, but it's still not days or week... they only SEEM like they are taking days and weeks) and will not be accessed by more than maybe 100 people maximum at a time.

The only reason I want to do this whole thing with the "Administration page" for selecting the database source is because next year when I'm trying to do this all again for the next Pinewood Derby race, I'll probably have forgotten everything I taught myself about how to setup the connection pools in the Tomcat XML files. So, I want to make it easy for me to just deploy the WAR, and then get sent straight to the configuration page that let's me tell the app where this years race database is located to get it all setup again.

Thanks so much to all for all your very helpful advise.

I've done a fair bit of development on this web app already using the standard Tomcat connection pooling so I was hoping to implement the change to where and how the pools get created with as little disruption to my existing code as possible. So, if I try to use a connection pool other than the one built into Tomcat.... what would be the best way to go about that? The problem I've run into so far is that I'm getting type matching errors with the "DataSoure" class if I use the code that I posted early to create the connection pool.

Can anyone recommend an existing pool implementation that would work with the javax.sql.DataSource interface? (again, if this is a REALLY stupid questions, I'm happy to be educated about what I obviously don't understand... which could take I while because I know there's a LOT I don't understand).

Below is a snippet from one of the Servlets I've already written.... pretty much all my Servlets have this kind of code in them.

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
......
......
.....
        InitialContext initialContext = new InitialContext();
        Context context = (Context) initialContext.lookup("java:comp/env");
        DataSource ds = (DataSource) context.lookup("Pack965PWD2014");
        connection = null;
        try
        {
            connection = ds.getConnection();
        
            if (connection == null)
            {
                throw new SQLException("Error establishing connection!");
            }

[Tom Malia]

The code I posted earlier on for creating a connection pool.... I got that straight from the Apache Tomcat 7 website (The Tomcat JDBC Connection Pool) So I figured it should work....

However, that code calls a "setPoolProperties(p)" method on the dataSource object. The problem is, there's no such method. In fact, I did a Google search of the string "SetPoolProperties" and the only hit I get is that same page from the Apache Tomcat project website....
What's the scoop? Is there a way to define the Pool properties and then set a data source with those properties?

By the way. Thank goodness for the internet in general and you guys here in particular! Though I don't consider myself a geezer yet, my programming days do go back far enough that I have painful memories of spending hours in the book stored debating which of the $100+, multi-thousand page tomes on my current language of choice might answer my questions when I get home. Only to find myself back the book store again a few days later trying to find another book to get me through the next dead end I ended up in. My GOD, it's amazing how much more productive programming is when there's literally a world wide community willing to listen to questions and try to help! THANKS!

 import org.apache.tomcat.jdbc.pool.DataSource;
  import org.apache.tomcat.jdbc.pool.PoolProperties;

  public class SimplePOJOExample {

      public static void main(String[] args) throws Exception {
          PoolProperties p = new PoolProperties();
          p.setUrl("jdbc:mysql://localhost:3306/mysql");
          p.setDriverClassName("com.mysql.jdbc.Driver");
          p.setUsername("root");
          p.setPassword("password");
...
...
...
          DataSource datasource = new DataSource();
          datasource.setPoolProperties(p);

          Connection con = null;
          try {
            con = datasource.getConnection();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from user");
            int cnt = 1;
            while (rs.next()) {
                System.out.println((cnt++)+". Host:" +rs.getString("Host")+
                  " User:"+rs.getString("User")+" Password:"+rs.getString("Password"));
            }
            rs.close();
            st.close();
          } finally {
            if (con!=null) try {con.close();}catch (Exception ignore) {}
          }
      }

  }

[E Armitage]

But the method is there in the API http://tomcat.apache.org/tomcat-7.0-doc/api/org/apache/tomcat/jdbc/pool/DataSourceProxy.html#setPoolProperties(org.apache.tomcat.jdbc.pool.PoolConfiguration)

Is that the exact code you are using or are you perhaps using javax.sql.DataSource?

[Tom Malia]

E Armitage wrote:But the method is there in the API http://tomcat.apache.org/tomcat-7.0-doc/api/org/apache/tomcat/jdbc/pool/DataSourceProxy.html#setPoolProperties(org.apache.tomcat.jdbc.pool.PoolConfiguration)

Is that the exact code you are using or are you perhaps using javax.sql.DataSource?

Ah! You're right....

changing:
DataSource datasource = new org.apache.tomcat.jdbc.pool.DataSource();
to:
org.apache.tomcat.jdbc.pool.DataSource datasource = new org.apache.tomcat.jdbc.pool.DataSource();

Got me passed the problem with setting the properties. Thanks.

However... new problem....

when it executes:
connection = datasource.getConnection();

I get: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

I know the MySQL drivers are in the project. They are also in the LIB directory for the Tomcat Install.

I can get the connection just fine if I just load the driver myself and create a standard connection.

The following works:

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException
    {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try
        {
            Connection connection = null;
            
            // This will load the MySQL driver, each DB has its own driver
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/pack965pwd?"+ "user=pack965webapp&password=P@ssw0rd");

            if (connection == null)
            {
                System.out.println("Error establishing connection!");
            }
            else
            {
                Statement stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery("Select * from person");
                while (rs.next())
                {
                    System.out.println(rs.getString("FirstName"));
                }
            }

            /* TODO output your page here. You may use following sample code. */
            out.println("<!DOCTYPE html>");
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet TestingConnectionPoolStuff</title>");            
            out.println("</head>");
            out.println("<body>");
            out.println("<h1>Servlet TestingConnectionPoolStuff at " + request.getContextPath() + "</h1>");
            out.println("</body>");
            out.println("</html>");
        }
        catch (Exception ex)
        {
            Logger.getLogger(TestingConnectionPoolStuff.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

But the following here fails with the classNotFoundException:

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException
    {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try
        {
            Connection connection = null;
            
            // This will load the MySQL driver, each DB has its own driver
            // Tried it with and withouhg inlcuding the explicit loading of the class.  Neither works
            //Class.forName("com.mysql.jdbc.Driver");
            
            PoolProperties p = new org.apache.tomcat.jdbc.pool.PoolProperties();
            p.setUrl("jdbc:mysql://localhost:3306/pack965pwd");
            p.setDriverClassName("com.mysql.jdbc.Driver");
            p.setUsername("pack965webapp");
            p.setPassword("P@ssw0rd");
            p.setJmxEnabled(true);
            p.setTestWhileIdle(false);
            p.setTestOnBorrow(true);
            p.setValidationQuery("SELECT 1");
            p.setTestOnReturn(false);
            p.setValidationInterval(30000);
            p.setTimeBetweenEvictionRunsMillis(30000);
            p.setMaxActive(100);
            p.setInitialSize(10);
            p.setMaxWait(10000);
            p.setRemoveAbandonedTimeout(60);
            p.setMinEvictableIdleTimeMillis(30000);
            p.setMinIdle(10);
            p.setLogAbandoned(true);
            p.setRemoveAbandoned(true);
            p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
              "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
            org.apache.tomcat.jdbc.pool.DataSource datasource = new org.apache.tomcat.jdbc.pool.DataSource();
            datasource.setPoolProperties(p);
            connection = datasource.getConnection();

            if (connection == null)
            {
                System.out.println("Error establishing connection!");
            }
            else
            {
                Statement stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery("Select * from person");
                while (rs.next())
                {
                    System.out.println(rs.getString("FirstName"));
                }
            }

            /* TODO output your page here. You may use following sample code. */
            out.println("<!DOCTYPE html>");
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet TestingConnectionPoolStuff</title>");            
            out.println("</head>");
            out.println("<body>");
            out.println("<h1>Servlet TestingConnectionPoolStuff at " + request.getContextPath() + "</h1>");
            out.println("</body>");
            out.println("</html>");
        }
        catch (Exception ex)
        {
            Logger.getLogger(TestingConnectionPoolStuff.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
 

[Tom Malia]

By the way... I know I started this talking about connecting to an access database and now I'm trying to connect to MySQL... the fact is, my web app needs to connect to both a MySQL and and Access database.
Just wanted to clarify.

[Tom Malia]

I'm an idiot!
I could have sworn I had triple checked to make sure that the mysql JDBC jar was in the LIB directory for the tomcat install.... but apparently I didn't.
Copied it there and now that part is working.