Disadvantages of static Connection object in Java standalone application

Question

I'm still pretty new to Java and was playing around with the GUI builder to learn some of Java's OO and other programming concepts.

I created a very basic banking system where customer can deposit money and withdraw money, basically.

I don't particularly have a problem with my code as everything works, I just have a problem about database connections.

Since the code to create a connection is always repeated, I created a database class such as below:

public class DB {
    static Connection c;

    public static void createConnection() throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        DB.c = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank", "root", "");
    }

    public static int insertUpdateDelete(String sql) throws Exception{
        if(c == null){
            createConnection();
        }
        return c.createStatement().executeUpdate(sql);
    }

    public static ResultSet search(String sql) throws Exception{
        if(c == null)
            createConnection();
        return c.createStatement().executeQuery(sql);
    }
}

So whenever I want to insert/update/delete from a database I just did:

DB.insertUpdateDelete(SQL); //SQL contains the query

And for search I'd do:

ResultSet rs = DB.search(SQL);

After a bit of reading, I learnt that having static connection objects aren't the best practice due to "resource leaking" and queries interfering with each other.

My question is, what is the best way to get the connection in a standalone Java application where one does not have to keep repeating the same code over and over again.

Thank you

There's also connection pools, which handle the low level work of creating and maintaining connections: https://stackoverflow.com/questions/520585/connection-pooling-options-with-jdbc-dbcp-vs-c3p0 — markspace, Jan 10 '18 at 19:40
Also more info here: https://stackoverflow.com/questions/2835090/how-to-establish-a-connection-pool-in-jdbc — markspace, Jan 10 '18 at 19:42
Apart from connection handling, you are currently not using _parameterized queries_, which are a must if you need to pass any variables to your queries (which you probably do). Here's a [tutorial of prepared statements](https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html). — Mick Mnemonic, Jan 10 '18 at 21:48

score 6 · Accepted Answer · answered Jan 10 '18 at 19:42

6

The normal approach is a connection pool.

A static connection object has problems:

You keep a connection per user session. With many users, it noticeably increases the load on the DB server (or your license costs if any).
If a connection dies or times out, you have no way to re-establish it.

Opening a connection every time is just slow.

A connection pool keeps a reasonable number of open connections, knows how to replace those that get disconnected due to an error, and can give you a connection, and take it back, very quickly.

There are several implementations of connection pools for JDBC.

answered Jan 10 '18 at 19:42

9000

39,899
9
66
104

1

One case where a static connection may be sort of fine could be a single-user desktop app, or a batch script. – 9000 Jan 10 '18 at 19:44
just to clarify, THEORETICALLY, if my application was only a single user desktop application, the code I posted, has no issue? Is returning a ResultSet bad practice? – user538578964 Jan 10 '18 at 19:55
Normally you want to close result sets to free up server-side resources. – 9000 Jan 10 '18 at 19:58
@9000 thanks for your help. A last question, if the application WAS a single user desktop application like you said, would there be any consequences of not closing connections, statements, resultsets? – user3583252 Jan 11 '18 at 12:02
You're welcome! Not closing resultsets for a _very long_ time _might_ end up with resources being exhausted on the DB side, or maybe running out of memory on the app side. How long would this take, is hard to tell; maybe days, maybe years of continuous operation. Or maybe never, if the implementations are smart enough to automatically free up (most of) the resources once a result set is completely iterated over. I'd say it's not a big deal for a sparingly used desktop app. – 9000 Jan 11 '18 at 17:44
@9000 sorry for taking so long to choose your answer as the best answer. I was a bit sick and had some other personal issues. Thanks a lot for your help – user3583252 Feb 18 '18 at 13:13
@user3536523: Never mind, it's totally fine. Correctness may take time. – 9000 Feb 18 '18 at 15:43

Disadvantages of static Connection object in Java standalone application

1 Answers1

Linked