The PreparedStatement gets stuck when I ask a lot of questions

Question

The PreparedStatement gets stuck when I ask a lot of questions

Navigation

#1 by (5 votes)

0

I have a method that has to be constantly sending requests to the server where the database is hosted, the place where I try to connect to the internet via satellite dish.

The function of the program is to send a number also keeping date and time to the server every time I press a button, the problem I have is that it gets stuck when I send X requests to the server.

I thought it was the executeUpdate() method and I set to see if the setQueryTimeout(2) method was fixed so that it cancels the execution after 2 seconds if it stays stuck but does not solve the problem.

public class PanelPrincipal extends javax.swing.JFrame {
    private PreparedStatement pstInsert = null;
    Connection connection = null;

   /*Constructor*/
   public PanelPrincipal(){

   /*Se establece la conexión con la base de datos*/ 
   connection = DriverManager.getConnection("jdbc:mysql://midominio.com:3306/baseDatos", "usuario", "contraseña");

   /*Se crea el PreparedStatemnt con la consulta correspondiente*/
   pstInsert = connection.prepareStatement("INSERT INTO registro_asistencia_empleado(id_persona, fecha, hora) VALUES (?, CURDATE(), CURTIME())");
   }

/*Este es el método que cuando pulso un botón inserto el Id de la persona que he insertado en el TextField(He omitido dicha parte para simplificar)*/
    private boolean insertarRegistro(int id_persona){            
        try  {                              
                pstInsert.setInt(1, id_persona);

                pstInsert.executeUpdate();

                return true;
            } catch (SQLException ex) {  

               Logger.getLogger(PanelPrincipal.class.getName()).log(Level.SEVERE, null, ex);                        

            } 
        return false;
    }

}

mysql java prepared-statements

asked by Arkhan6 16.06.2017 в 13:21

source

1 answer

Can you make method calls by clicking on a button in HTML5? Convert String to Json in php

score 5 · Accepted Answer

The problem you have is that you are not closing neither the PreparedStatement nor the Connection each time you make the insert. For each time you click you are creating a new connection pool keeping all open.

public class PanelPrincipal extends javax.swing.JFrame {
    private PreparedStatement pstInsert = null;
    Connection connection = null;

    /* Constructor */
    public PanelPrincipal() {

        /* Se establece la conexión con la base de datos */
        connection = DriverManager.getConnection("jdbc:mysql://midominio.com:3306/baseDatos", "usuario", "contraseña");

        /* Se crea el PreparedStatemnt con la consulta correspondiente */
        pstInsert = connection.prepareStatement("INSERT INTO registro_asistencia_empleado(id_persona, fecha, hora) VALUES (?, CURDATE(), CURTIME())");
    }

    /*
     * Este es el método que cuando pulso un botón inserto el Id de la persona
     * que he insertado en el TextField(He omitido dicha parte para simplificar)
     */
    private boolean insertarRegistro(int id_persona) {
        /* boolean return */
        Boolean retorno = false;
        try {
            pstInsert.setInt(1, id_persona);

            pstInsert.executeUpdate();

            retorno = true;
        } catch (SQLException ex) {

            Logger.getLogger(PanelPrincipal.class.getName()).log(Level.SEVERE, null, ex);

        } finally {
            // Cerramos el PreparedStatement
            if (pstInsert != null) {
                pstInsert.close();
            }
            // Cerramos la conexión
            if (connection != null) {
                connection.close();
            }
            return retorno;
        }
    }

}