0

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?

halfer
  • 19,824
  • 17
  • 99
  • 186
derelektrischemoench
  • 253
  • 2
  • 14

2 Answers2

1

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);
}
ipolevoy
  • 5,432
  • 2
  • 31
  • 46
  • Hi, ipolevoy, as always, quite resourceful. I moved the connection to a webfilter, which solved all my issues. The loop was a work in progress, thanks for pointing it out, though. Again, I gotta say I'm quite impressed by ActiveJdbc. Not to mention the great support. Thanks a lot for that, you're making my life as a noob a whole lot better. Best wishes, derelektrischemoench – derelektrischemoench Mar 28 '18 at 18:33
  • @derelektrischemoench you are making great progress, good luck! You might want to upvote my answer:) – ipolevoy Mar 29 '18 at 19:38
1

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();
Jonathan Coustick
  • 1,127
  • 9
  • 19
  • sorry, but your advice is misleading,. ActiveJDBC shares a connection on the current Thread. See http://javalite.io/database_connection_management for more information. – ipolevoy Mar 29 '18 at 19:40