First() and next() methods do not work in Resultset, even when editing parameters

Question

I need to do two iterations on the same ResultSet. For that I did the following:

I set parameters in a Statement to make the cursor editable, according to my connection class constructor:

public ConexaoComDb(){


        try {

            Class.forName("org.h2.Driver");

        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


        try {

            cn = DriverManager.getConnection("jdbc:h2:./lib/BDContaNoBB", "sa", "");

             stmt = cn.createStatement(
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

Then I make one while(resultSet.next()). After that, I get the result with the first(). In a following method I try to iterate again with the resultSet.next(), but instead of showing the complete table with the print, what appears is twice the first line, as if there had been no iteration in either of the two whiles.

Below follows the code of the method that performs the query, generating content for the resultSet, and the method that has the first while (rs.next()):

public void executaBusca(String nomeTable, String colunas, String condicao){


        try {


            rs = stmt.executeQuery("SELECT " +colunas+ " FROM " +nomeTable+
                    " "+condicao);

            imprimeConsulta(rs);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

First while (rs.next()):

public void imprimeConsulta(ResultSet result){

        try {

            metaDadosDoRs = result.getMetaData();
            numeroColunas = metaDadosDoRs.getColumnCount();
            int greaterCel;

            for (int coluna=1; coluna<=numeroColunas; coluna++){
                greaterCel= tamanhoDaMaiorCelula(coluna);
                formatarCelulas(greaterCel, coluna);
            }

            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }

According to while(rs.next()):

public int tamanhoDaMaiorCelula(int coluna){

        int TamConteudoDaAtual=0;
        int TamConteudoDaAnterior=0;
        int indiceMaiorCel=0;
        int tamMaiorCel=0;
        String conteudo;



        try {


        while(rs.next()){           

            conteudo = rs.getString(coluna);


            TamConteudoDaAtual = conteudo.length();

                if (TamConteudoDaAtual > TamConteudoDaAnterior){
                    indiceMaiorCel = TamConteudoDaAtual;
                    tamMaiorCel = TamConteudoDaAtual;
                 }

            TamConteudoDaAnterior = TamConteudoDaAtual;

        }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        try {

            formatarCelulas(tamMaiorCel, coluna);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return tamMaiorCel;
        }

And finally, the method that prints the formatted table. That was to print all of it, which has 5 lines, but only prints twice the contents of the first line...

public void formatarCelulas(int tamMaiorCel, int coluna) throws SQLException{

        rs.first();

        ArrayList<String> stringFormatada = new ArrayList<String>();

        do {

                int tamQueFalta = rs.getString(coluna).length() - tamMaiorCel;
                char[] arrayDoTamQueFalta = new char[tamQueFalta];

                String tamLast = new String (arrayDoTamQueFalta);

                stringFormatada.add(rs.getString(coluna));
                stringFormatada.add(tamLast);

                System.out.println(stringFormatada.toString());

                stringFormatada.clear();

            }while(rs.next());

        }

Answer 1

1. Do not store the Statements and ResultSets in instance variables without having a very strong reason to do so. This way you’re using makes them much more difficult to manage properly and much easier to screw up with them.

2. Although you can read one ResultSet More than once, that’s not usually a good idea. Ideally, you can read it only once and build into memory a structure that corresponds to what you read (not necessarily in a 1-to-1 mapping, you can do optimizations, simplifications and totals while reading the ResultSet). In your case you are trying to traverse twice for each column only to determine the size of the largest string in the first and read the data in the second. This is terribly inefficient, as the ideal is to minimize data traffic with the database (even if it is the H2 in memory) and therefore you should only go through the ResultSet one, and only once to fulfill the purpose of the method executaBusca.

3. Avoid forming Sqls by concatenating pieces of Strings. Doing this is asking for security issues with injection of SQL, and for this reason it is considered a bad programming practice.

4. NEVER do this:

       } catch (SQLException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }
   

   That’s why ask your program to have mysterious and difficult to track errors. It’s trying to keep on doing something that you already know has gone wrong as if it had worked.

5. This should no longer be necessary:

       try {
   
           Class.forName("org.h2.Driver");
   
       } catch (ClassNotFoundException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }
   

   See more about this in that other question of mine.

6. Managing your resources becomes much easier by using the Try-with-Resources. In your case you are not managing them properly and not even with something that would be close to proper.

7. Do not put the modifier public in internal methods. The method made available to be used externally is the executaBusca. The other methods are internal functionalities of executaBusca which should not be made available for other classes to use.

8. The way you try to format a column does not work. To demonstrate why, see this code:

   import java.util.ArrayList;
   import java.util.List;
   
   class VaiDarErrado {
       public static void main(String[] args) {
           String a = "batata";
           char[] teste = new char[123];
           String s = new String(teste);
           List<String> lista = new ArrayList<>();
           lista.add(a);
           lista.add(s);
           System.out.println(lista.toString());
       }
   }
   

   Here’s the way out:

   [batata, ]
   

   See the result in ideone.

   That is, all the calculation of column size and spaces to be filled is in vain because the size of the char[] passed to the builder of String is irrelevant and what matters in it is which positions are filled, which is none in your case. Also it is not a good idea to use ArrayList to concatenate or format Strings. Use the StringBuilder or else the method format(String, Object...) for this. The fact that you are using System.out.println means that you break lines after each dice, and therefore the alignment of the extra spaces would be irrelevant. Finally, even if you settle this issue of extra spaces and line breaks, it will go wrong because you will show the data grouped by column, and not by row. That is, this part is completely wrong and has to be redone from scratch.

9. Use System.out.println how data output is a very bad thing. This makes this method almost impossible to be reused in an environment where the idea would be to put the results on a screen or else in an HTML table or something without the code having to undergo significant changes. And if the code is in a situation where it would need to undergo significant changes to be reused, then it means it’s not good code.

10. Avoid using variables with types that are implementations of interfaces such as ArrayList and remember the diamond syntax when using generics. For example, instead:

    ArrayList<String> stringFormatada = new ArrayList<String>();
    

    Use this:

    List<String> stringFormatada = new ArrayList<>();
    

    Note that although the constructor called is still the ArrayList, the type of the variable is List simply. This becomes quite important when you have type parameters ArrayList or methods that return ArrayList. When you only use List, you eliminate various interoperability issues that would result when you have to interact with other types of lists.

Well, now that I’ve listed the problems, let’s start by creating a class to represent a table row:

import java.util.List;

public final class Linha {
     private final List<String> dados;

     public Linha(List<String> dados) {
         this.dados = dados;
     }

     public void computarTamanho(int[] tamanhosColunas) {
         if (tamanhosColunas.length != dados.size()) throw new IllegalArgumentException();
         for (int i = 0; i < tamanhosColunas.length; i++) {
             String dado = dados.get(i);
             if (dado == null) dado = "<null>";
             int t = dado.length();
             if (t > tamanhosColunas[i]) tamanhosColunas[i] = t;
         }
     }

     public void formatar(StringBuilder sb, int[] tamanhosColunas) {
         if (tamanhosColunas.length != dados.size()) throw new IllegalArgumentException();
         for (int i = 0; i < tamanhosColunas.length; i++) {
             if (i != 0) sb.append(' ');
             String dado = dados.get(i);
             if (dado == null) dado = "<null>";
             sb.append(dado);
             for (int j = dado.length(); j < tamanhosColunas[i]; j++) {
                 sb.append(' ');
             }
         }
         sb.append('\n');
     }
}

Now a class to represent the results table:

import java.util.ArrayList;
import java.util.List;

public final class Tabela {
     private final List<Linha> linhas;
     private final int[] tamanhosColunas;

     public Tabela(int qtdColunas, List<List<String>> listao) {
         this.tamanhosColunas = new int[qtdColunas];
         this.linhas = new ArrayList<>();

         for (List<String> listinha : listao) {
             Linha proxima = new Linha(listinha);
             linhas.add(proxima);
             proxima.computarTamanho(tamanhosColunas);
         }
     }

     @Override
     public String toString() {
         StringBuilder sb = new StringBuilder();
         for (Linha lin : linhas) {
             lin.formatar(sb, tamanhosColunas);
         }
         return sb.toString();
     }
}

And then, let’s build an instance of Tabela from the database:

Here is the suggestion of how your class might be connecting to the database:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public final class TabelaDoBd {

    private static Connection conectar() throws SQLException {
        return DriverManager.getConnection("jdbc:h2:./lib/BDContaNoBB", "sa", "");
    }

    public static Tabela executarBusca(String sql) throws SQLException {
        try (
            Connection con = conectar();
            PreparedStatement st = con.prepareStatement(sql);
            ResultSet rs = st.executeQuery();
        )
        {
            ResultSetMetaData metaDadosDoRs = rs.getMetaData();
            int numeroColunas = metaDadosDoRs.getColumnCount();

            List<List<String>> listao = new ArrayList<>();
            while (rs.next()) {
                List<String> listinha = new ArrayList<>(numeroColunas);
                for (int i = 1; i <= numeroColunas; i++) {
                    listinha.add(rs.getString(i));
                }
                listao.add(listinha);
            }
            return new Tabela(numeroColunas, listao);
        }
    }
}

These codes above solve all the problems I listed, but end up delegating the SQL assembly out. This detail of SQL coming from outside can be solved if you tell me under what circumstances you use or expect to use the method executarBusca. To use this code, do so:

Tabela t = TabelaDoBd.executarBusca("SELECT ID, NOME, TELEFONE FROM CLIENTES");
System.out.println(t);

You can add other methods in Tabela and Linha if you want to do other operations with the data read. Note that I made them immutable for the reasons I explain in that other answer.