I have a problem with closing connections to MSYQL using JAVA.

Question

I have a problem with closing connections to MSYQL using JAVA.

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

After executing a prepareStatement my connection to the database is closed and it does not let me continue executing sentences.

I throw the exception: java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.

Connection Class

public class ConexionMySql {

private static Connection objConexion = null;
private LoggerRegistros log4j = new LoggerRegistros(this.getClass());

private ConexionMySql() {
    final String user = "root";
    final String pass = "";
    final String server = "jdbc:mysql://localhost/";
    final String DB = "db_cfprojets";
    final String DriverMySQL = "com.mysql.cj.jdbc.Driver";
    final String zonaHoraria = "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
    try {
        Class.forName(DriverMySQL);
        objConexion = DriverManager.getConnection(server + DB + zonaHoraria, user, pass);
    } catch (ClassNotFoundException | SQLException ex) {
        log4j.setFatal(ex.getMessage(), ex.fillInStackTrace());
    }
}
public static Connection getConnection() {
    if (objConexion == null) {
        new ConexionMySql();
    }
    return objConexion;
}}

Method to insert

public void insertClient(Cliente cliente) throws Exception {
    sql = "INSERT INTO tb_cliente VALUES(?,?,?,?,?,?,?,?,?,?,?)";
    try {
        ps = con.prepareStatement(sql);
        ps.setString(1, cliente.getRfc());
        ps.setString(2, cliente.getNombre());
        ps.setString(3, cliente.getDireccion().getCalle());
        ps.setString(4, cliente.getDireccion().getNum_calle());
        ps.setString(5, cliente.getDireccion().getColonia());
        ps.setInt(6, cliente.getDireccion().getCp());
        ps.setString(7, cliente.getDireccion().getMucipio());
        ps.setString(8, cliente.getDireccion().getEstado());
        ps.setString(9, cliente.getDireccion().getPais());
        ps.setString(10, cliente.getContacto().getNum_movil());
        ps.setString(11, cliente.getContacto().getEmail());
        int a = ps.executeUpdate();
        if (a == 0) {
            loggger.setInformacion("No se actualizo ninguna fila");
        }

    } catch (SQLException ex) {
        loggger.setError(ex.getMessage(), ex.fillInStackTrace());
        JOptionPane.showMessageDialog(null, ex.fillInStackTrace(), ex.getMessage(), JOptionPane.ERROR_MESSAGE);
    } finally {
        if (con != null) {
            con.close();
        }
         if (ps != null) {
            ps.close();
        }
    }
}

mysql java mysqlconnector

asked by Anthony Tepach 07.11.2018 в 17:56

source

2 answers

Solve concatenation of document elements can the value of a numeric variable that comes in an array be converted, as a numeric value?

score 1 · Answer 1

What I can tell you is that the connections can caducar or quedarse colgadas , that's why a variable that stores a connection to the database and that is declared as static is a very bad practice and can cause you Many headaches.

The correct thing is to open the connection, do the database operation and close the connection. The exception that confirms this rule is when you use transacciones , in that case it is advisable to keep the connection open until the transaction ends. It is also recommended that connections be made using pool de conexiones .

The error that appears to you seems to respond to this situation: Java believes that the connection is open because the connection is static but for mysql the connection is inactive or closed. That's why you answer No se permiten operaciones después de la conexión cerrada .

score 1 · Answer 2

private static Connection objConexion = null;

and

} finally {
  if (con != null) {
    con.close();
  }

You only have a single instance of Connection ; you return that same instance every time you do getConnection() , and when you execute the first query, you close that single connection. Naturally, when executing any other instruction through that same connection, it will fail.

Three options:

Never close the connection. You have the problem that if there is inactivity you can close the connection by timeout. Also, if there are multiple threads using it (eg on a web server) it will give you problems.
Create a connection every time you start a process to access the database. Naturally, each thread will have its own connection. It consumes more resources (creating connections is slow) but it is reasonably simple if your program is not very complicated. Make sure to close all connections once you do not need them.
Use a pool of connections that keeps several connections open and will assign you connections as needed. The most normal thing is that you use some as a separate library (eg c3p0) instead of implementing it yourself. More complicated, but the route to follow if we are talking about something that has to be continuously working and opening connections to the BD.

To begin with, I recommend the middle option.