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.