Java application slow when connecting to MySQL server when not on LocalHost

Question

TLDR at the bottom

I'm fairly new to java and have been studying for around 2 years in my spare time.

I've recently decided to try my hand at making some retail software for my small business.

I am using Java to create the main application and all products, sales, etc, are stored on a MySQL server I have setup.

When I was initally putting my program together, I had the database on the local machine and it was all running smoothly.

I then made the database accessible over the network by changing the ip address from local host to the computer hosting the database.

I seem to be able to access the database quickly enough when connecting via MySQL Workbench, but when I run the program, it seems to take maybe 10/15 seconds for a simple query to be returned.

Here is the java code I use to connect to the server:

import javax.swing.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnection {
    private Connection connection;

    public DatabaseConnection() {
        try {
            // Load the MySQL JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Connection URL
            String url = SystemSettings.sqlAddress;
            String username = SystemSettings.sqlUsername;
            String password = SystemSettings.sqlPassword;

            // Establish the connection
            connection = DriverManager.getConnection(url, username, password);

            // Connection successful
            if (connection != null) {
                System.out.println("Connected to the database!");
            }

        } catch (ClassNotFoundException e) {
            System.out.println("MySQL JDBC driver not found.");
            JOptionPane.showMessageDialog(null, "error" + e.getMessage());
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Failed to connect to the database.");
            e.printStackTrace();
        }
    }

    public Connection getConnection() {
        return connection;
    }

    public void closeConnection() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
                System.out.println("Database connection closed.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The url I am using is "jdbc:mysql://192.168.56.1:3306/my_database";

I'm using the mysql-connector-j-9.0.0.jar module/driver.

I have looked around the posts on here and various places online, but I am mainly finding people that are having problems connecting to the database rather than just having a slow connection.

When I access files on the host computer or run a ping, it is a very quick response time. I have also switched off the firewall to see if that was having an impact and the result was the same

I'm still an amateur at coding at the moment so I apologise if it looks a mess

Edit

One thing to point out is that each time my program queries the database it will open a new connection and then close it when it has finished the query. I have just created a new testing class and kept the connection open then ran several queries which were coming back instantly. So should I re-write the code so it keeps connections to the database open while the program runs? I had it close the connection each time as I thought that was what you were meant to do

TLDR I made a java program that connects to a mysql server. It worked fine when the server was on local host, but became very slow (10+ seconds to retrieve queries) when I moved it to a machine that could be accessed within the network

Can anyone offer any advice on this?

Answer 1

Ok so I managed to solve the issue. It turns out that I am an idiot.

The url I was using was "jdbc:mysql://192.168.56.1:3306/my_database"

The actual IP address shown on MySQL workbench for the server is 192.168.1.60

I have no idea how the program was even connecting to the database when it was given the wrong IP address, but when I corrected it, my program was connecting instantly and returning all queries straight away!

Thank you for trying to help, guys. For the future, if you see me posting about a problem I am having, just tell me I'm an idiot and you will most likely be correct!

Answer 2

It is important to understand that the usual flow of a production application is not to open and close connections several times, as that is an expensive / slow operation.

Ideally, you would use some form of connection pooling - you keep some connections to the server open, and when you need to perform a query you pick one of the connections that is idle, perform the query, and return the connection to the pool. If no connections are idle, usually you can set a configuration to either wait for one to be idle or to open a new one (that will belong to the pool once it becomes idle).

There are several libraries that can perform connection pooling for you on Java; a quick search returned this result https://www.baeldung.com/java-connection-pooling