Java Community | Help. Code. Learn.•12mo ago

Database Connection Pooling

I need to implement a simple connection pooling mechanism for my application. The problem is I have almost no knowledge working with database connections. What things should I take into account? Is there any lightweight library out there? I expect the code to open as few connections as possible.

8 Replies

JavaBot•12mo ago

⌛ This post has been reserved for your question.

Hey @Defective! Please use /close or the Close Post button above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically closed after 300 minutes of inactivity.

TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here.

DefectiveOP•12mo ago

Here is my current approach:

public final class ConnectionPool {
    private final String url, username, password;

    public static class Builder {

        private String url;
        private String username;
        private String password;

        public Builder setUrl(String url) {
            this.url = url;
            return this;
        }

        public Builder setUsername(String username) {
            this.username = username;
            return this;
        }

        public Builder setPassword(String password) {
            this.password = password;
            return this;
        }

        public ConnectionPool build() {
            return new ConnectionPool(url, username, password);
        }

        public Builder(String url) {
            this.url = url;
        }
    }

    private final List<Connection> availableConnections = new ArrayList<>();
    private final List<Connection> usedConnections = new ArrayList<>();

    private ConnectionPool(String url, String username, String password) {
        this.url = url;
        this.username = username;
        this.password = password;
    }

    public Connection getConnection() throws SQLException {
        Connection con = null;
        if (!availableConnections.isEmpty()) {
            con = availableConnections.get(0);
            availableConnections.remove(0);
            try {
                if (!con.isValid(1000))
                    throw new SQLException();
                usedConnections.add(con);
            } catch (Exception e) {
                con = null;
            }
        }

        if (con == null) {
            con = DriverManager.getConnection(url, username, password);
            usedConnections.add(con);
        }

        return con;
    }

    public boolean release(Connection con) {
        if (usedConnections.contains(con)) {
            usedConnections.remove(con);
            availableConnections.add(con);
            return true;
        }
        return false;
    }
}

public final class ConnectionPool {
    private final String url, username, password;

    public static class Builder {

        private String url;
        private String username;
        private String password;

        public Builder setUrl(String url) {
            this.url = url;
            return this;
        }

        public Builder setUsername(String username) {
            this.username = username;
            return this;
        }

        public Builder setPassword(String password) {
            this.password = password;
            return this;
        }

        public ConnectionPool build() {
            return new ConnectionPool(url, username, password);
        }

        public Builder(String url) {
            this.url = url;
        }
    }

    private final List<Connection> availableConnections = new ArrayList<>();
    private final List<Connection> usedConnections = new ArrayList<>();

    private ConnectionPool(String url, String username, String password) {
        this.url = url;
        this.username = username;
        this.password = password;
    }

    public Connection getConnection() throws SQLException {
        Connection con = null;
        if (!availableConnections.isEmpty()) {
            con = availableConnections.get(0);
            availableConnections.remove(0);
            try {
                if (!con.isValid(1000))
                    throw new SQLException();
                usedConnections.add(con);
            } catch (Exception e) {
                con = null;
            }
        }

        if (con == null) {
            con = DriverManager.getConnection(url, username, password);
            usedConnections.add(con);
        }

        return con;
    }

    public boolean release(Connection con) {
        if (usedConnections.contains(con)) {
            usedConnections.remove(con);
            availableConnections.add(con);
            return true;
        }
        return false;
    }
}

What can I improve here? Is the code even remotely "okay"?

Unknown User•12mo ago

Message Not Public

DefectiveOP•12mo ago

Thank you, I will check it out. I hope it is "lightweight" in that it takes least amount of space possible in the finished application

Unknown User•12mo ago

Message Not Public

DefectiveOP•12mo ago

Yeah, but I don't want to keep more connections open than needed at the given time Yes, looks nice. I will try it out Perfect! That's exactly what I was looking for, thank you!

JavaBot•12mo ago

If you are finished with your post, please close it. If you are not, please ignore this message. Note that you will not be able to send further messages here after this post have been closed but you will be able to create new posts.

JavaBot•12mo ago

Post Closed

This post has been closed by <@1014466568484294656>.

Gaming

Programming

Database Connection Pooling