Why does my REST API return blank JSON on nested objects?

Question

I'm trying to retrieve all the posts from Heroku app database given an email id

I have a Query class that processes all the requests (for now just GET)

Following is Query.java

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;

import com.auro.assignment.wallpostapi.model.PostBundle;

@Path("query")
public class Query {

    public Query() {

    }

    @GET
    @Produces(MediaType.APPLICATION_JSON)
    public PostBundle getPostBundle() {

        PostManager postManager = new PostManager();
        return postManager.getPostBundle("xyz.123@test.in");

    }

}

Then I have something called PostManager that bundles all the posts with a status code and a message.

Following is the PostManager.java

    import java.io.PrintWriter;
    import java.io.StringWriter;
    import java.net.URISyntaxException;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;

    import com.auro.assignment.wallpostapi.dbconnection.DBConnectionManager;
    import com.auro.assignment.wallpostapi.model.*;

    public class PostManager {

        private PostBundle postBundle;
        private List<Post> posts;

        public PostManager() {
            postBundle = null;
            posts = new ArrayList<>();
        }

        public PostBundle getPostBundle(final String email) {

            try {
                Connection connection = DBConnectionManager.getConnection();
                final String query = "SELECT user_post, post_time FROM wall_post WHERE user_id = " +
                                                        "(SELECT user_id FROM user_info WHERE user_email = ?);";

                PreparedStatement preparedStatement = connection.prepareStatement(query);
                preparedStatement.setString(1, email);
                ResultSet resultSet = preparedStatement.executeQuery();

                if  (resultSet != null && resultSet.next()) {
                    while (resultSet.next()) {
                        posts.add(new Post(resultSet.getString("user_post"),resultSet.getString("post_time")));
                    }
                    postBundle = new PostBundle("200","SUCCESS!",posts);
                    return postBundle;
                }
                else {
                    postBundle = new PostBundle("404","NOT FOUND",null);
                    return postBundle;
                }           
            } catch (ClassNotFoundException | URISyntaxException | SQLException e) {
                StringWriter stringWriter = new StringWriter();
                PrintWriter printWriter = new PrintWriter(stringWriter);
                e.printStackTrace(printWriter);
                String stackTrace = stringWriter.toString();
                postBundle = new PostBundle("500",stackTrace,null);
                return postBundle;
            }

        }
    }

Following is the **PostBundle.java**

import java.util.List;

public class PostBundle {

    private String status;
    private String message;
    private List<Post> posts;

    public PostBundle() {
        status = null;
        message = null;
        posts = null;
    }

    public PostBundle(final String status, final String message, final List<Post> posts) {
        this.status = status;
        this.message = message;
        this.posts = posts;
    }

    public String getStatus() {
        return status;
    }

    public String getMessage() {
        return message;
    }

    public List<Post> getPosts() {
        return posts;
    }

}

And finally, following is the Post.java

public class Post {

    private String data;
    private String date;

    public Post() {
        data = null;
        date = null;
    }

    public Post(final String data, final String date) {
        this.data = data;
        this.date = date;
    }

    public String getData() {
        return data;
    }

    public String getDate() {
        return date;
    }

}

It is returning a blank JSON at this site

Is there anything wrong with how I designed the architecture? I'm guessing there is some kind of internal error taking place, but in PostManager method, I make sure any error stacktrace also gets bundled, yet I'm getting an absolute blank screen.

Answer 1

I see some errors here:

First, you call resultSet.next() twice

// Initially the cursor is positioned before the first row.
ResultSet resultSet = preparedStatement.executeQuery();

if  (resultSet != null && resultSet.next()) { // The cursor is moved to the first row
    while (resultSet.next()) { // The cursor is moved to the second row, so you skipped the
                               // first result and it only iterates from second to last one
        ...
    }
    postBundle = new PostBundle("200","SUCCESS!",posts); // posts only contains from
                                                         //second to last result. In case
                                                         //you only have one result,
                                                         //posts is an empty ArrayList

    return postBundle;
}

How many posts does the "xyz.123@test.in" email have? If it has only one, that's the issue.

Second, preparedStatement.executeQuery() never returns null

So you don't need to check if it is null.

Third, if an email has not any posts should never return a 404, but a 200 instead with an empty json.

200 OK - The request has succeeded. The information returned with the response is dependent on the method used in the request.

404 Not Found - The server has not found anything matching the Request-URI.

The 404 error is reserved for request-URI matching.

Finally, you should modify your code accordingly:

You should modify your code for the following:

...
try {
    Connection connection = DBConnectionManager.getConnection();
    final String query = "SELECT user_post, post_time FROM wall_post WHERE user_id = " +
            "(SELECT user_id FROM user_info WHERE user_email = ?);";

    PreparedStatement preparedStatement = connection.prepareStatement(query);
    preparedStatement.setString(1, email);
    ResultSet resultSet = preparedStatement.executeQuery();

    while (resultSet.next()) {
        posts.add(new Post(resultSet.getString("user_post"),resultSet.getString("post_time")));
    }
    postBundle = new PostBundle("200","SUCCESS!",posts);
    return postBundle;         
}
...