Close connection in another method

5

I have a class called JavaConnect and I have two methods: ConnectDB and DesconnectDb .

Connection class:

public class JavaConnect {

    Connection conn = null;
    public static Connection ConnectDb(){
        try{
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:/home/leo/NetBeansProjects/Controller_workshop/dbControlworkshop.sqlite");
            /*JOptionPane.showMessageDialog(null, "Conexao realizada");*/
            return conn;      
        }
        catch(Exception e){
            JOptionPane.showMessageDialog(null, e);
            return null;
        }
    }
    public static void DesconnectDb(Connection conn){  
        try{  
            if(conn != null) {  
                conn.close();  
            }  
        }catch (SQLException e){ 
        }  
    }
}

So far I understood more or less the features. But I need to start them inside the JFrame.

Code below to help you understand my error:

public void PreencherTabela(String Sql){
    ArrayList dados = new ArrayList();
    String [] colunas = new String []{"ID","NOME","RG","CPF","RUA","TELEFONE","NUMERO","BAIRRO","CIDADE","ESTADO","CEP"};
    JavaConnect.ConnectDb();
    JavaConnect.DesconnectDb(conn);
}

When I call the method DesconnectDb , the "conn" is wrong and displays the error on the side as follows:

  

can not find symbol

How to solve this?

    
asked by anonymous 15.12.2016 / 21:53

3 answers

3

The Connection class is auto-closeable , that is, you can use a block try-with-resources to finalize automatically.

In addition, there are other things you can do to improve your code, such as:

  • Initialize the driver class only once, for example in a static boot block.
  • Do not return null of the connection method after showing the error to the user, otherwise the program will generate another NullPointerException exception soon after, when trying to use the reference conn returned. In this case, it is best to let SQLException be thrown and handle the call.

Let's look at an example fit:

public class JavaConnect {
    static {
        try {
            Class.forName("org.sqlite.JDBC"); //inicializa apenas uma vez
        } catch (ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null, "SQLite Driver não encontrado!");
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(
            "jdbc:sqlite:/home/leo/NetBeansProjects/Controller_workshop/dbControlworkshop.sqlite");
    }
}

So just use it as follows:

public class QualquerOutraClasse {
    public void qualquerOutroMetodo() {

        try (Connection conn = JavaConnect.getConnection()) {
            conn.createStatement().executeQuery("select * from tabela");
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Ocorreu um erro ao acessar o banco de dados: " + e.getMessage());
        }

    }
}

Alternative using lambda

An alternative implementation in Java 8 or higher could make it even easier if you use a ldbda to run SQL.

Example:

public class JavaConnect {
    static {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null, "SQLite Driver não encontrado!");
        }
    }

    public interface OperacaoNoBanco {
        void executar(Connection connection) throws SQLException;
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:sqlite:/home/leo/NetBeansProjects/Controller_workshop/dbControlworkshop.sqlite");
    }

    public static void executar(final OperacaoNoBanco operacaoNoBanco) {
        try (Connection conn = JavaConnect.getConnection()) {
            operacaoNoBanco.executar(getConnection());
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Ocorreu um erro ao acessar o banco de dados: " + e.getMessage());
        }
    }
}

And then you can use the new executar method like this:

class QualquerOutraClasse {
    public void qualquerOutroMetodoUsandoLambda() {
        JavaConnect.executar(con -> {
            con.createStatement().executeQuery("select * from tabela");
        });
    }
}

Finally, by calling the executar method, you can perform any operation on the database. Being confined to the blade, any error will be properly handled within that method. The use is very simple and safe, without risk of forgetting to close the connection.

    
16.12.2016 / 07:34
6

The problem is the scope of the conn variable, it only exists within the JavaConnect class. If you want to pass the variable just to close the connection, the PreencherTabela() method must be within the same class or create a local connection variable within this method, receiving a connection as a return from its ConnectDb() method (as it was presented in other answer), or simply delegate responsibility only to the JavaConnect class:

public class JavaConnect {

    Connection conn = null;

    public static void ConnectDb() {
        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:/home/leo/NetBeansProjects/Controller_workshop/dbControlworkshop.sqlite");
            /*JOptionPane.showMessageDialog(null, "Conexao realizada");*/
            return conn;
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
            return null;
        }
    }
    public static void DesconnectDb() {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {}
    }
}

And within the method you just need to call the methods when they are needed:

public void PreencherTabela(String Sql){
    ArrayList dados = new ArrayList();
    String [] colunas = new String []{"ID","NOME","RG","CPF","RUA","TELEFONE","NUMERO","BAIRRO","CIDADE","ESTADO","CEP"};
    JavaConnect.ConnectDb();
    JavaConnect.DesconnectDb();
}

Remembering that this solution might be good if your application was standalone, and has no concurrent connections.

    
15.12.2016 / 22:25
4

The error occurs because conn exists only within the JavaConnect class.

Try:

Connection conn = JavaConnect.ConnectDb();
JavaConnect.DesconnectDb(conn);

This way you can access the method return.

    
15.12.2016 / 21:59