CODEWITHSUNDEEP
jspservletsjakarta-eeactivejdbc
derelektrischemoench
derelektrischemoench

Reputation: 403

ActiveJdbc second connection to same database

I'm using activejdbc for a Java ee project I have to do for school and I really like it. I do however have a recurring problem whenever I need more than one connection to the same database in one HttpServletRequest.

The scenario is the following: I have a custom Tag which lists the contents of the shoppingcart of a user which is called on every request, since I placed it in my header.jsp. In this tag I ask activeJdbc for the contents of the shoppingcart of this user. It looks like this:

Base.open("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/wpr_webshop", "root", "root");
            Iterator<Record> rIter = shopCart.getAll(Record.class).iterator();


            if (rIter.hasNext()) {
                int shoppingCartTotal = 0;
                out.write("<div class='shoppingcartcontents'>Your cart contains:<br>");

                while (rIter.hasNext()) {
                    Record r = rIter.next();
                    Artist a = r.parent(Artist.class);

                    out.write(a.getString("artist_name") + " - " + r.getString("title") + "<br />");
                    shoppingCartTotal += r.getInteger("price");
                }
                Base.close();

Then, in a servlet which renders a jsp that includes the jsp which uses the tag from above, I do additional stuff with activejdbc like normal record selection; also using Base.open() on the same database which gives me a 

org.javalite.activejdbc.DBException: Cannot open a new connection because existing connection is still on current thread, name: default, connection instance: com.mysql.jdbc.JDBC4Connection@2c2552bd. This might indicate a logical error in your application.

exception. I get what the problem here is and I'm already really thorough on closing the base connection as soon as I don't require it anymore, I can't however seem to prevent this problem.

I guess a solution to this would be to allow the custom tag, which is called on every request, use a different connection to not conflict with the base connection. Unfortunately I couldn't find any information on how to open a second connection to the same database on the web.

Is this even possible? Or am I just doing this the wrong way?

Upvotes: 0

Views: 209

Answers (2)

Jonathan Coustick
Jonathan Coustick

Reputation: 1157

Using Base.open is not recommended when using Java EE, since there is a better mechanism already built in, that of JDBC Connection Pools.See https://stackoverflow.com/a/16261491/8707412 for how to configure them. These can then be included in your code with

@Resource(name="jdbc:app/myDS")
DataSource ds;

You can then get a connection from it with

Connection conn = ds.getConnection();

Upvotes: 1

ipolevoy
ipolevoy

Reputation: 5518

Generally, it is a terrible idea to open a connection in the tag or even the servlet. An entire request should not be opening many connections to the same database, but rather share it on the thread. Remove all Base.open()/close() calls from your code and write a simple ServletFilter which will open a connection before the request and close it after.

Here is an example: ActiveJdbcFilter - it uses JNDI, but you can use this code as guidance.

For docs on managing connections, refer to: http://javalite.io/database_connection_management.

On the other note, your code calls rIter.hasNext() twice - is this intentional?

Additionally, here is a better implementation of your loop:

List<Record> records  = shopCart.getAll(Record.class).include(Artist.class);

for(Record r: records){    
  Artist a = r.parent(Artist.class);
}

Upvotes: 1

Related Questions