Search is not performed after connecting to the Java database

Question

I’m trying to run a database search using java code, but I can’t. The database is connected, but the search is not performed. I created two classes, one that has the class with the connection method, and the other, which is the main one. I call it the connection method. I used as a basis the book of the author Deitel.

Follows the code:

public class Conexao {

    public static  Statement conectarBanco() {

            final String DATABASE_URL = "jdbc:mysql://localhost/locadora";
            final String ROOT = "root";
            final String SENHA = "";

            try(Connection connection = DriverManager.getConnection(DATABASE_URL, ROOT, SENHA);){
                Statement statement = connection.createStatement();

                System.out.println("Conectado!");
                return statement;
            }   

            catch(SQLException sqlException) {
                sqlException.printStackTrace();
                }
            return null;
        }
}   

---

public class Principal   {
    public static void main(String args[]) throws SQLException {
        Statement statement;
        int numberOfColumns;
        final String SELECT_QUERY = "select*from cliente";


        statement =  Conexao.conectarBanco();   
        ResultSet resultSet = statement.executeQuery(SELECT_QUERY);
        ResultSetMetaData metaData =  (ResultSetMetaData) resultSet.getMetaData();
        numberOfColumns = metaData.getColumnCount(); 

        for(int i = 1; i<= numberOfColumns; i++) {
            System.out.println(metaData.getColumnName(i));
        }

        while(resultSet.next()) {
            for(int i = 1; i<= numberOfColumns; i++) {
                System.out.println(resultSet.getObject(i));
                System.out.println();
            }
        }
    }
}

Answer 1

Some things should be modified in your code:

• You are using a Try-with-Resources on conectarBanco(), so at the end of the block execution the connection will be closed implicitly;

• Try returning an object of the Connection type instead of returning a Statement, it would be something like public Connection getConnection(). So you could create a Preparedstatement too;

• Use spaces in your query (and preferably check in the database if they work): select * from cliente.

See below the suggestions:

public static  Statement conectarBanco() {

   private final String DATABASE_URL = "jdbc:mysql://localhost/locadora";
   private final String ROOT = "root";
   private final String SENHA = "";

   try{
        Connection connection = DriverManager.getConnection(DATABASE_URL, ROOT, SENHA);

        if(connection != null) {
            System.out.println("Conectado!");
            return connection;
        }   

   } catch(SQLException sqlException) {
        sqlException.printStackTrace();
   }
   return null;
}

In the use you can use the Try-with-Resources:

public static void main(String args[]) throws SQLException {
    Statement statement = null;
    ResultSet rs = null;

    int numberOfColumns;
    final String SELECT_QUERY = "select * from cliente";

    try(Connection con = Conexao.conectarBanco()) {
        statement = con.createStatement(SELECT_QUERY) ;   
        rs = statement.executeQuery(SELECT_QUERY);
        ResultSetMetaData metaData =  (ResultSetMetaData) rs.getMetaData();
        numberOfColumns = metaData.getColumnCount(); 

        for(int i = 1; i<= numberOfColumns; i++) {
            System.out.println(metaData.getColumnName(i));
        }

        while(resultSet.next()) {
            for(int i = 1; i<= numberOfColumns; i++) {
                System.out.println(resultSet.getObject(i));
                System.out.println();
            }
        }
    } catch () {
        // Tratar sua exceção
    } finally {
        // Fechar o Statement e o ResultSet 
    }

Answer 2

Well, your job is to conectarBanco() open the connection, show "Conectado!" and then close the connection. Only after the connection has been closed do you try to extract some information from the database.

Maybe you didn’t understand how the Try-with-Resources that you use to make the connection. The purpose of the Try-with-Resources is to automatically manage the closure of open resources, which in your case should include the Connection, the PreparedStatement and the RsultSet, all of them. See more about this in that reply.

Your code should look like this:

public class Conexao {

    private static final String DATABASE_URL = "jdbc:mysql://localhost/locadora";
    private static final String ROOT = "root";
    private static final String SENHA = "";

    public static Connection conectarBanco() throws SQLException {
        return DriverManager.getConnection(DATABASE_URL, ROOT, SENHA);){
    }
}

public class Principal   {

    private static final String SELECT_QUERY = "select * from cliente";

    public static void main(String[] args) throws SQLException {
        try (
            Connection con = Conexao.conectarBanco();
            PreparedStatement statement = con.prepareStatement(SELECT_QUERY);   
            ResultSet resultSet = statement.executeQuery();
        ) {
            ResultSetMetaData metaData =  (ResultSetMetaData) resultSet.getMetaData();
            int numberOfColumns = metaData.getColumnCount(); 

            for (int i = 1; i <= numberOfColumns; i++) {
                System.out.println(metaData.getColumnName(i));
            }

            while (resultSet.next()) {
                for (int i = 1; i <= numberOfColumns; i++) {
                    System.out.println(resultSet.getObject(i));
                }
            }
        }
    }
}

Ah, by the way ignore exceptions and return null is bad programming practice. The only thing you will achieve with this is to hide the real cause of the mistakes and turn them into NullPointerExceptions of mysterious origin.