Java MySQl - Opening and closing the connection too often?

Question

so for my little project I have a database which generates items as of now. I would like to implement a shop which retrieves the data aswell and saves the items in an ArrayList.

Currently everytime I spawn an item I connect to my database which worked but seems super unefficient to me. However, I have no clue how to work with a ResultSet without connecting to my database in the same method. Is there a static way to it so I can only open and close my database a single time and not everytime I spawn an item? And especially if I have like 10 shops which need to be initialized.

        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection con = DriverManager.getConnection(simple connection to database);

        Statement stmt = con.createStatement();

        ResultSet numberOfItemsInDataBase = stmt.executeQuery("SELECT count(*) FROM items;");

If I want to use the ResultSet, I need to go all the way back to my connection for it. Is that a bad approach or is it normal to open/close the connection every single time?

Cheers

Use only one connection for the entire program (or web page). — Rick James, Aug 04 '20 at 14:59

score 0 · Answer 1 · answered Aug 04 '20 at 13:50

The term you need to use is Connection Pooling.

Assuming you don't use an application server (which would manage connection pooling on your behalf) you can check out Java/JDBC implementations such as C3P0 or Apache Commons DBCP. You can find usage examples for DBCP and C3P0

Java MySQl - Opening and closing the connection too often?

1 Answers1