Home > Back-end >  Error using ResultSet. It only returns a single row
Error using ResultSet. It only returns a single row

Time:06-08

I have created a program that is used to view the content of a database that contains more than 300 entries. But only get the first one:

public static Comics[] verTodo() throws SQLException
{
    int posicion = 0;
    String sentenciaSql = "SELECT * from comics.comicsbbdd";

    Comics comic []= null;

    ResultSet rs = DBManager.getComic(sentenciaSql);

    try {
        if(rs.last()) {
            comic = new Comics[(rs.getRow())];
            System.out.println();
//              rs.beforeFirst();

            do
            {
                String nombre = rs.getString("nomComic");
                String numero = rs.getString("numComic");
                String variante = rs.getString("nomVariante");
                String firma = rs.getString("firma");
                String editorial = rs.getString("nomEditorial");
                String formato = rs.getString("formato");
                String procedencia = rs.getString("procedencia");
                String anioPubli = rs.getString("anioPubli");
                String guionista = rs.getString("nomGuionista");
                String dibujante = rs.getString("nomDibujante");
                comic[posicion] = new Comics(nombre,numero,variante,firma,
                editorial,formato,procedencia,anioPubli,guionista,dibujante);
                posicion  ;
                
            }
            while(rs.next());
        }

    }
    catch(Exception ex)
    {
        System.out.println();
    }
    return comic;
}

All this using javaFX and the database is in mysql. Thank you very much for the help.

EDIT

I have managed to get it to work, but clearly the code is ugly, I have tried to improve it, but when I do, the entire content of the database is no longer visible:

public static Comics[] verTodo() throws SQLException
{
    int posicion = 0;
    String sentenciaSql = "SELECT * from comics.comicsbbdd";

    Comics comic []= null;

    ResultSet rs = DBManager.getComic(sentenciaSql);

    try {
        if(rs.last()) {
            comic = new Comics[(rs.getRow())];
            rs.beforeFirst();

            if(rs.first())
            {
                do
                {
                    String nombre = rs.getString("nomComic");
                    String numero = rs.getString("numComic");
                    String variante = rs.getString("nomVariante");
                    String firma = rs.getString("firma");
                    String editorial = rs.getString("nomEditorial");
                    String formato = rs.getString("formato");
                    String procedencia = rs.getString("procedencia");
                    String anioPubli = rs.getString("anioPubli");
                    String guionista = rs.getString("nomGuionista");
                    String dibujante = rs.getString("nomDibujante");
                    comic[posicion] = new Comics(nombre,numero,variante,firma,editorial,formato,procedencia,anioPubli,guionista,dibujante);
                    System.out.println(comic[posicion].toString());
                    posicion  ;
                }
                while(rs.next());
            }
            System.out.println(comic.length);
        }

    }
    catch(Exception ex)
    {
        System.out.println();
    }
    return comic;
}

CodePudding user response:

The line

if(rs.last()) {

moves the cursor of the ResultSet to the last row, so that's the only one you then get (since after the last row, there are no further rows and rs.next() will not return any more rows).

See the Javadoc for that method: https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#last()

CodePudding user response:

@dunni has pointed out the right thing in the answer.I have also upvoted the same.


Here I am just giving a suggestion.

You can achieve the same thing just using list and the code will be more easy.You will not have to do any of the cursor moving just to get the no of rows from the resultset.

Using List you will not need to worry about the size of resultset(For your case it is applicable) . Also, now you can simply use while-loop to iterate the resultset

Below is an example:

Comics comic []=null;
List<Comic> list = new ArrayList<>();
ResultSet rs = DBManager.getComic(sentenciaSql);

try {
    while(rs.next()){
        
        //fetch your values

        //add a object to the list;
        list.add(new Comics(nombre,numero,variante,firma,editorial,formato,procedencia,anioPubli,guionista,dibujante))
    }
             
}catch(Exception ex){
   e.printStackTrace(); // Try to use relevant Exception classes instead of calling the 'Exception' itself. 
}

// Convert the list to array
comic =  new Comics[list.size()];
comic = list.toArray(comic);

After adding all the values in the list just convert it to your array.

  • Related