Optimize java code and mysql connections

0

I have developed a desktop application in Java and I use connections to MySQL , at the moment it is locally, but I have noticed that it uses my processor too much, reviewing the open processes of MySQL , I could find that it leaves many processes open, in a "sleep" state, I tried to do a sequence to stop them, but I closed the connection to the database and by logic it does not do any operation from the application anymore.

Any ideas to optimize the processes I do?

I use a class to connect that is like this:

public class Conexion {

    private static String servidor = "jdbc:mysql://localhost:3306/skywater?zeroDateTimeBehavior=convertToNull";
    private static String user = "root";
    private static String pass = "pass";
    private static String driver = "com.mysql.jdbc.Driver";
    private static Connection conexion;

    public Conexion() {
        try {
            Class.forName(driver);
            conexion = DriverManager.getConnection(servidor, user, pass);
        } catch (ClassNotFoundException | SQLException e) {
            JOptionPane.showMessageDialog(null, "Error al establecer conexion con base de datos \n Contacte Servicio: " + e.getMessage());
        }
    }

    public Connection getConnection() {
        return conexion;
    }

    public Connection getClosedConnection() throws SQLException {
        conexion.close();
        System.out.println("Conexion cerrada");
        return conexion;
    }
}

And another class for each type of operation in the database that contains different methods, for example a query method:

public class OperacionesDB {
    Conexion conector = new Conexion();

    Connection con = conector.getConnection();
    Statement st;
    ...
}

And in my opinion, the method that is generating many processes in MySQL is the following:

public DefaultTableModel consultaGeneral(String tabla, String columna, String valor) {

    System.out.println(tabla);
    DefaultTableModel modelo = new DefaultTableModel();

    String seleccion = "SELECT * FROM " + tabla + " WHERE " + columna + " LIKE '%" + valor + "%';";
    try {
        Statement ejecutor = con.createStatement();
        ResultSet rs = ejecutor.executeQuery(seleccion);
        String nvo[] = new String[rs.getMetaData().getColumnCount()];
        for (int j = 1; j <= rs.getMetaData().getColumnCount(); j++) {
            modelo.addColumn(rs.getMetaData().getColumnName(j));
        }

        while (rs.next()) {
            for (int i = 1; i <= modelo.getColumnCount(); i++)
                nvo[i - 1] = rs.getString(i);
            modelo.addRow(nvo);
        }

        ejecutor.close();
        //con.close();
        rs.close();

        return modelo;
    } catch (SQLException e) {
        System.out.println("Error en consulta general " + e.getMessage());
        return modelo;
    }
}

This query is activated upon entering the application and is for many JInternalFrame , and is also activated with a JText when a key is precious to generate the search.

    
asked by Isaí López Domínguez 16.10.2018 в 07:29
source

1 answer

0

I have been able to solve my problem using a "pool" of connections, because of the type of query I used to generate many processes in Mysql, using a "pool" significantly optimized these processes, they could be reduced.

I leave the link of a topic that helped me:

open / close connections

    
answered by 23.10.2018 в 08:24