Close connection in another method

Question

I have a class called JavaConnect and in it 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){ 
        }  
    }
}

Until then I understood more or less the functionalities. However I need to start them inside Jframe.

Code below to help understand my mistake:

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" gets wrong and presents me the error on the side as follows:

cannot find Symbol

How to solve this?

Answer 1

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

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 just call the methods when necessary:

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 can be good if your application was standalone, and has no connections competition.

Answer 2

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

Try:

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

This way you can access the return of the method.

Answer 3

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

Besides, there are other things that can be improved in the code, such as:

• Initialize the class driver only once, for example in a static boot block.
• Don’t come back null of the connection method after showing the error to the user, otherwise the program will generate another exception NullPointerException then, when trying to use the reference conn returned. In this case, it is best to leave the SQLException be launched and handle on the call.

Let’s see what an adjusted example looks like:

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 lamppost

An alternative implementation in Java 8 or higher could make it even easier if you use a mbda 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 method executar thus:

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

Finally, calling the method executar, you can execute any operation in the bank. Being confined to the lampside, any error will be properly dealt with within that method. The use is very simple and safe, without risk of forgetting to close the connection.