Java Community | Help. Code. Learn.•2y 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.

13 Replies

JavaBot•2y 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•2y 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"?

emccue•2y ago

Just use HikariCP thats the one to use

emccue•2y ago

https://github.com/brettwooldridge/HikariCP

GitHub

GitHub - brettwooldridge/HikariCP: 光 HikariCP・A solid, high-perform...

光 HikariCP・A solid, high-performance, JDBC connection pool at last. - GitHub - brettwooldridge/HikariCP: 光 HikariCP・A solid, high-performance, JDBC connection pool at last.

emccue•2y ago

as to your code - it is not thread safe

Unknown User•2y ago

Message Not Public

emccue•2y ago

if you have multiple threads taking and releasing connections (which is what connection pools are for) you will have issues

DefectiveOP•2y 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

emccue•2y ago

emccue•2y ago

that good enough?

DefectiveOP•2y 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•2y 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•2y ago

Post Closed

This post has been closed by <@1014466568484294656>.

Gaming

Programming

Database Connection Pooling

Did you find this page helpful?