How to set up Tomcat for one Database Connection per Request

Question

I have a Sparkjava app which I have deployed on a Tomcat server. It uses SQL2O to interface with the MySQL-database. After some time I start to have trouble connecting to the database. I've tried connecting directly from SQL2O, connecting through HikariCP and connecting through JNDI. They all work for about a day, before I start getting Communications link failure. This app gets hit a handful of times a day at best, so performance is a complete non issue. I want to configure the app to use one database connection per request. How do I go about that?

The app doesn't come online again afterwards until I redeploy it (overwrite ROOT.war again). Restarting tomcat or the entire server does nothing.

Currently every request creates a new Sql2o object and executes the query using withConnection. I'd be highly surprised if I was leaking any connections.

Here's some example code (simplified).

public class UserRepositry {
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    protected Sql2o sql2o = new Sql2o("jdbc:mysql://mysql.server.name/dbname?serverTimezone=UTC", "username", "password");

    public List<Users> getUsers() {
        return sql2o.withConnection((c, o) -> {
            return c.createQuery(
                    "SELECT\n" +
                            "  id,\n" +
                            "  name\n" +
                            "FROM users"
            )
                    .executeAndFetch(User.class);
        });

    }
}


public class Main {
    public static void main(String[] args) {
        val gson = new Gson();

        port(8080);

        get("/users", (req, res) -> {
            return new UserRepository().getUsers();
        }, gson::toJson);
    }
}

Answer 1

If you rely on Tomcat to provide the connection to you: It's coming from a pool. Just go with plain old JDBC and open that connection yourself (and make sure to close it as well) if you don't like that.

So much for the answer to your question, to the letter. Now for the spirit: There's nothing wrong with connections coming from a pool. In all cases, it's your responsibility to handle it properly: Get access to a connection and free it up (close) when you're done with it. It doesn't make a difference if the connection is coming from a pool or has been created manually.

As you say performance is not an issue: Note that the creation of a connection may take some time, so even if the computer is largely idle, creating a new connection per request may have a notable effect on the performance. Your server won't overheat, but it might add a second or two to the request turnaround time.

Check configurations for your pool - e.g. validationQuery (to detect communication failures) or limits for use per connection. And make sure that you don't run into those issues because of bugs in your code. You'll need to handle communication errors anyways. And, again, that handling doesn't differ whether you use pools or not.

Edit: And finally: Are you extra extra sure that there indeed is no communication link failure? Like: Database or router unplugged every night to connect the vacuum cleaner? (no pun intended), Firewall dropping/resetting connections etc?