Search is not performed after connecting to the Java database

1

I'm trying to search the database using the code in java, but I can not. The database is connected, but the search is not performed. I created two classes, one that owns the class with the connection method, and the other, which is the main one. In it, I call the connection method. I used Deitel's book as a base.

Follow 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();
            }
        }
    }
}
    
asked by anonymous 27.07.2018 / 16:02

2 answers

3

Some things should be changed in your code:

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

  • Try to return an object of type Connection 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 the suggestions below:

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 use you can use 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 
    }
    
27.07.2018 / 17:03
1

Now, its conectarBanco() function opens the connection, shows "Conectado!" and then closes the connection . Only after the connection has been closed will you attempt to extract some information from the database.

You may not have understood correctly how the try-with-resources you use to make the connection works. The purpose of try-with-resources is to automatically manage the closure of open resources, which in their case should include Connection , PreparedStatement and RsultSet , all of them. See more about this on this answer .

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 ignoring exceptions and returning null is a bad programming practice. The only thing you can do with this is to hide the real cause of the errors and turn them into NullPointerException s of mysterious origin.

    
27.07.2018 / 17:08