Getting all records from sql database generates error

[Violet_82]

Hi there, I've come across an issue while trying to get all the records from a SQL database in java.
A bit of necessary introduction here.
I'm not using any framework just JDBC and the getAllBooks method is part of a REST call. The failing method is essentially doing this:
-getting the number of all records in the db;
-getting all the ids and use them to get the records, store them in an array and return that array;
Unfortunately the call generates the following error in the while loop:

SEVERE: Servlet.service() for servlet [Jersey Web Application] in context with path [/book-storage-REST] threw exception
java.lang.IllegalArgumentException: the object parameter to marshal() is not marshallable
    at com.sun.xml.internal.bind.v2.runtime.MarshallerImpl.write(MarshallerImpl.java:280)
    at com.sun.xml.internal.bind.v2.runtime.MarshallerImpl.marshal(MarshallerImpl.java:163)
    at com.sun.jersey.json.impl.provider.entity.JSONListElementProvider.writeList(JSONListElementProvider.java:145)
    at com.sun.jersey.core.provider.jaxb.AbstractListElementProvider.writeTo(AbstractListElementProvider.java:264)
    at com.sun.jersey.spi.container.ContainerResponse.write(ContainerResponse.java:302)
    at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1510)
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)
    at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)
    at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:558)
    at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:733)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748)

Ok, the code now:

public Book[] getAllBooks()
    {
        Book[] books = null;        
        try
        {
            resultSet = statement.executeQuery(SqlStrings.SELECT_NUMBER_ALL_RECORDS);//runs "SELECT COUNT(*) from books";           
            resultSet.next();
            int dbRecordNo = resultSet.getInt(1);//get number of record
            resultSet = statement.executeQuery("SELECT books.id FROM books");//get all ids
            books = new Book[dbRecordNo];

            int i = 0;
            while(resultSet.next() && i < dbRecordNo)
            {
                int bookId = Integer.parseInt(resultSet.getString("id"));
                books[i] = getBook(bookId);         
                System.out.println();
                i++;
            }

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

The problem seems to be occurring because I use && i < dbRecordNo in while(resultSet.next() && i < dbRecordNo). I run a few tests and if I remove that && statement the while loop runs absolutely fine. So, I can't quite understand why this while(resultSet.next()) goes through the resultSet absolutely fine and this instead while(resultSet.next() && i < dbRecordNo) goes through only once then returns and generates the error.
I could probably try other ways (I haven't explored any other yet but I guess I could create a hashmap of ids and book objects and then extract the book objects from there) but I'm interested to know why this isn't working as it's just a simple while loop with an additional condition. Debugging doesn't really clarify it because, as I said, it only runs for i = 0, when i is 1 it fails to get the record

[richard.p.hall.71]

Does && employ shortcut evaluaton or not?

[AndreRet]

I am not sure what your object books is suppose to return as you already run all id's. The error refers to you trying to run two objects in one loop and they crash as teh returned parameters are not met.

[Violet_82]

I am not sure what your object books is suppose to return as you already run all id's

Not sure what you mean. The method should essentially loop through all the ids, use each id to construct the Book object and insert that into a Book array. In here while((resultSet.next()) && (i < dbRecordNo)) both conditions should be true an equal amount of times because dbRecordNo contains the number of records and resultSet.next() should go through all the records (ids).

The error refers to you trying to run two objects in one loop

Right, is that not allowed :-)? dbRecordNo is an int so it's not an object

[Violet_82]

Also, let me give you another example.
I changed the code slightly and still fails to go through each record, it only goes through 1 but this time no error is generated. Here is the code:

    public Book[] getAllBooks()
    {

        HashMap<String, Book> booksRecords = new HashMap();
        try
        {
            resultSet = statement.executeQuery(SqlStrings.SELECT_NUMBER_ALL_RECORDS);           
            resultSet.next();
            int dbRecordNo = resultSet.getInt(1);//get number of record
            resultSet = statement.executeQuery("SELECT books.id FROM books");
            while(resultSet.next())
            {
                String id = resultSet.getString("id");

                System.out.println("");
                booksRecords.put(id, getBook(Integer.parseInt(id)));
            }
            return books;
        } 
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

This time I created a HashMap which would contain the id of the record and the actual Book object. Again, it only does one pass, then returns.
I'm starting to think that the problem might be inside the getBook method, so here it is, but it's all standard stuff:

  public Book getBook(int id)
    {
        Book bookQueried = null;
        try
        {
            resultSet = statement.executeQuery(String.format(SqlStrings.SELECT_RECORD, id));
            bookQueried = constructObject(resultSet);
        } 
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return bookQueried;
    }
    private Book constructObject(ResultSet resultSet) {
        Book bookToReturn = new Book();
        try
        {
            while (resultSet.next()) 
            {               
                bookToReturn.setId(Integer.valueOf(resultSet.getString("id")));
                bookToReturn.setTitle(resultSet.getString("title"));
                bookToReturn.setAuthor(resultSet.getString("author"));
                bookToReturn.setLocation_id(Integer.valueOf(resultSet.getString("location_id")));
             }
        } 
        catch (SQLException e)
        {
            e.getMessage();
            e.printStackTrace();
        }

        return bookToReturn;

    }

[mcriscolo]

I was suspecting the same thing. Unless I'm missing something, in "getBook", you are re-using the "resultSet" variable, assigning it new results from getting the data on a single book. When you return back to the loop in "getAllBooks()", it's referencing the same "resultSet" variable, which a) contains the results from a different query, and b) is past "end-of-data" which is why your loop only executes once.

Be sure to use different instance variables if you're going to be managing multiple result sets at the same time.

[Violet_82]

Which in turns means another Statement because I've just learned that you can have multiple ResultSets open with one Statement only (my sincere apologies for the long delay in updating the thread)
It works now with a local ResultSet in addition to the other one