Close MySQL connection via Thread

Question

I have a problem with my code, my program fills a JTable form a MySQL DB using a thread, the program works fine but I get this error

//Error, com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections" Error java.lang.NullPointerException//

I think I need to close the connection or maybe don't use a Thread for fills the JTable, my idea it's fills the table with out using a JButton event, do you have a suggestion for this?

public class VentanaAdministrador extends javax.swing.JFrame{

    public static final String URL = "jdbc:mysql://localhost:3306/floreria?autoReconnet=true&useSSL=false";
    public static final String Usuario = "root";
    public static final String Contraseña = "";
    PreparedStatement ps;
    ResultSet rs;
    
    public Connection getConnection(){
        Connection conexion = null;
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            conexion = (Connection) DriverManager.getConnection(URL,Usuario,Contraseña);
        }catch(Exception ex){
            System.err.println("Error, "+ex);
        }
        
        return conexion;
    }
    ///////////////////////////////////////////////
    Thread llenado = new Thread(){
        public void run(){
        DefaultTableModel modeloTabla = new DefaultTableModel();
        TablaVentanaAsignar.setModel(modeloTabla);
        
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        
        try{
            VentanaAdministrador con = new VentanaAdministrador();
            Connection VentanaAdministrador = con.getConnection();
            
            ps = VentanaAdministrador.prepareStatement("select NumeroEmpleado,Nombre,Punto_de_venta,TiempoTrabajo from empleados where Puesto=?");
            ps.setString(1, "Diseñador");
            rs = ps.executeQuery();
            
            modeloTabla.addColumn("No. Empleado");
            modeloTabla.addColumn("Nombre");
            modeloTabla.addColumn("Sucursal");
            modeloTabla.addColumn("Tiempo trabajado");
            
            while(rs.next()){
                Object fila[] = new Object[4];
                fila[0] = rs.getObject(1);
                fila[1] = rs.getObject(2);
                fila[2] = rs.getObject(3);
                fila[3] = rs.getObject(4);
                
                modeloTabla.addRow(fila);
            }   
            }catch(Exception ex){
                    System.err.println("Error "+ex);
                    
        }
        }
    };    
    
    public VentanaAdministrador() {
        initComponents();
        llenado.start();
    }

Answer 1

Generally best in database work to focus on doing the following as quickly and directly as practical:

• (a) connect to database,
• (b) execute query,
• (c) retrieve data, and
• (d) terminate connection (or return connection to pool if you are using connection-pooling).

So there is no need to carry a PreparedStatement and ResultSet as member fields on your class as seen in your code. Such objects should be rapidly instantiated, utilized, and discarded (generally speaking).

Separate your database work from your GUI work. Interacting with the database can take a while, so run that on the background thread. When complete, use the appropriate access point in your GUI to ask that it update its widgets with the fresh data — but update itself within its own GUI thread. Never access a GUI widget from another thread. Whether you are using JavaFX/OpenJFX, Swing, SWT, Vaadin Flow, or Android, each GUI framework has a hook for asking the GUI to update its widgets on the GUI's own thread.

In modern Java we rarely need to address the Thread class directly. For concurrent work, use the Executors framework added to Java 5.

You'll get the most flexibility in development, testing, and deployment if you use the DataSource interface to contain connection info rather than mere strings as seen in your code. Usually a JDBC driver provides a basic implementation of DataSource. For example, the Connector/J driver provides com.mysql.cj.jdbc.MysqlDataSource class.

private DataSource prepareDataSource() {
    MysqlDataSource ds = new com.mysql.cj.jdbc.MysqlDataSource();
    ds.setServerName( "1.2.3.4" );
    ds.setPortNumber( 5432 );
    ds.setUser( "scott" );
    ds.setPassword( "tiger") ;
    ds.setDatabaseName( "invoicing" );
    … Set all your relevant properties …
    return ds ;
}

Keep this DataSource around in a handy place within your app, to be used by whatever code needs a database connection.

Also keep handy an implementation of ExecutorService to be used for your background threading work. Use Executors utility class to conveniently obtain an instance. Be sure to eventually shutdown the executor service before your app exits, as its backing thread pool may otherwise continue running indefinitely like a zombie.

ExecutorService executorService = Executors.newFixedThreadPool( 3 ) ;

Or, in the future when Project Loom technology arrives:

ExecutorService executorService = Executors.newVirtualThreadExecutor() ;

Use try-with-resources syntax when doing JDBC work. It simplifies your code in helpful ways, automatically closing your database resources.

Define your database work as a task object implementing the Runnable (or Callable) interface. Submit an instance to your executor service.

/// Code in your GUI detects need to load data.
/// … Fetch the `ExecutorService` from wherever you stored it.
executorService.submit( new DatabaseWorkRunnable() ) ;

The run method of that Runnable might look like something like this incomplete and untested code seen below.

Notice how this code has a nested pair of try-with-resources statements covering three AutoCloseable resource objects: a Connection, a PreparedStatement, and a ResultSet. If instantiated, each resource object will be closed whether or not your code completes successfully. The resources are closed in the reverse order in which they were declared.

The DataSource object is not a resource and is never “open”. So it is never closed. A DataSource simply holds information for opening a connection to the database.

String sql = "SELECT … ;";
try (
        Connection conn = dataSource.getConnection() ;
        PreparedStatement ps = conn.prepareStatement( sql ) ;
)
{
    … Make `PreparedStatement::set…` calls here.
    try (
            ResultSet rs = ps.executeQuery() ;
    )
    {
        if ( rs.next() )
        {
            LocalDate ld = rs.getObject( 1 , LocalDate.class );
            … Retrieve your data , add to collection to be delivered to GUI later.
        }
    }
}
catch ( SQLException e )
{
    e.printStackTrace();
}
// At this point, all three `Connection`, `PreparedStatement`, and `ResultSet` objects are closed automatically whether your code ran successfully or whether an exception/error was thrown.

… Use your GUI framework’s hook to ask the GUI to update its table widget with the fresh data in collection. 

All this has been covered many times already on Stack Overflow. Search to learn more and see many examples.