getting NULL values from database

Question

I am trying to display some data from my database with the following method but it only shows NULL values. I am not sure I have the right implementation of the method so if there's anything I should change when calling the select() method. Thank you in advance for any help.

//SELECT method

public Res select(int id) {
            Res res = new Res();
            Connection connection = null;
            PreparedStatement preparedStm = null;
            ResultSet resultSet = null;

            try {
                connection = ConnectionConfiguration.getConnection();
                preparedStm = connection.prepareStatement("SELECT * FROM res WHERE id = ?");
                preparedStm.setInt(1, id);
                resultSet = preparedStm.executeQuery();

                while(resultSet.next()) {

                    res.setId(resultSet.getInt("id"));
                    res.setDay(resultSet.getString("res"));
                    res.setNoRooms(resultSet.getString("rooms"));
                    res.setNoNights(resultSet.getString("nights"));
                    res.setRoomType(resultSet.getString("room_type"));
                    res.setUser_email(resultSet.getString("email"));

                }

            } catch(Exception e) {
                e.printStackTrace();
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException e) {

                        e.printStackTrace();
                    } 
                }
                if (preparedStm != null) {
                    try {
                        preparedStm.close();
                    } catch (SQLException e) {

                        e.printStackTrace();
                    }
                }
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {

                        e.printStackTrace();
                    }
                }
            }

            return res;

        }

//JSP Page display

<h1 align="center"> Reservation </h1><br>
<% Reservation r = new Reservation(); 
   Res rb = new Res();
   r.select(rb.getId());
%>
<table border="1" width="50%" align="center">
<tr><th colspan="3">Date</th><th>Rooms</th><th>Nights</th><th>Room type</th><th>
Comments</th><th>Status</th></tr>
<tr><td><%= rb.getDay() %><td>
<%=rb.getRooms() %></td><td><%=rb.getNights() %></td><td><%=rb.getRoomType() %></td>
</table>

Answer 1

Firstly it's worth mentioning that we don't use scriptlets anymore. Here's some information explaining why:

The use of scriptlets (those <% %> things) in JSP is indeed highly discouraged since the birth of taglibs (like JSTL) and EL (Expression Language, those ${} things) way back in 2001.

The major disadvantages of scriptlets are:

1. Reusability: you can't reuse scriptlets.
2. Replaceability: you can't make scriptlets abstract.
3. OO-ability: you can't make use of inheritance/composition.
4. Debuggability: if scriptlet throws an exception halfway, all you get is a blank page.
5. Testability: scriptlets are not unit-testable.
6. Maintainability: per saldo more time is needed to maintain mingled/cluttered/duplicated code logic.

Sun Oracle itself also recommends in the JSP coding conventions to avoid use of scriptlets whenever the same functionality is possible by (tag) classes. Here are several cites of relevance:

From JSP 1.2 Specification, it is highly recommended that the JSP Standard Tag Library (JSTL) be used in your web application to help reduce the need for JSP scriptlets in your pages. Pages that use JSTL are, in general, easier to read and maintain.

...

Where possible, avoid JSP scriptlets whenever tag libraries provide equivalent functionality. This makes pages easier to read and maintain, helps to separate business logic from presentation logic, and will make your pages easier to evolve into JSP 2.0-style pages (JSP 2.0 Specification supports but deemphasizes the use of scriptlets).

...

In the spirit of adopting the model-view-controller (MVC) design pattern to reduce coupling between the presentation tier from the business logic, JSP scriptlets should not be used for writing business logic. Rather, JSP scriptlets are used if necessary to transform data (also called "value objects") returned from processing the client's requests into a proper client-ready format. Even then, this would be better done with a front controller servlet or a custom tag.

Quote above taken from this wonderful answer: How to avoid Java code in JSP files?

Okay, now you understand why you shouldn't use scriptlets. So how else are we supposed to do what we want to do in our JSP pages? This is where JSTL and EL comes in. All you have to do in order to use JSTL in your project (if you're not using maven) is to download JSTL .jar file: https://mvnrepository.com/artifact/javax.servlet/jstl/1.2 and include it in your lib folder (which is inside WEB-INF). If you don't have the folder create one and add the .jar file there. Here is a great resource showing all the things you can do with it.

Then in order to use it in your JSP just include the core library at the top of your JSP file:

<%@ taglib prefix = "c" uri = "http://java.sun.com/jsp/jstl/core" %>

Now to get back to your question. The problem lies here:

Reservation r = new Reservation(); 
   Res rb = new Res();
   r.select(rb.getId());

when you rb.getId() here and like that you are getting null because you're essentially doing this:

SELECT * FROM reservations WHERE id = null

because you created a new object right before that line without setting the id (or anything else)

Res rb = new Res();

What you need to do is pass the object from your servlet and then you can use it. (without making a new object of res)

for example if you do this it should work:

   Reservation r = new Reservation(); 
   Res rb = new Res();
   rb.setId(1); //set the id 
   r.select(rb.getId());

But again, you don't want to do it like this. Because it involves scriptlets. And they suck. Since you now have jstl in your project, here's an example on how to do it with JSTL:

ReservationStatus.jsp:

<h1 align="center"> Reservation </h1><br>
<table border="1" width="50%" align="center">
<tr>
<th colspan="3">Date</th>
<th>Rooms</th>
<th>Nights</th>
<th>Room type</th>
<th>Comments</th>
<th>Status</th>
</tr>
<tr>
<td>${Reservation.day}</td>
<td>${Reservation.month}</td>
<td>${Reservation.year}</td>
<td>${Reservation.noRooms}</td>
<td>${Reservation.noNights}</td>
<td>${Reservation.roomType}</td>
<td>${Reservation.comments}</td>
<td>${Reservation.status}</td>
</table>

Wayyy cleaner right? You actually don't need JSTL for this, cause you're only using EL here.

But again, if you just try and visit the jsp page directly, you will not see anything. You need the servlet to pass the data on to the jsp. What i would do to make this easier for you to test, is to change the doPost to a doGet in your RESERVATION servlet. This way, if type in the url that is mapped for this servlet in your browser: http://localhost:9191/ReservationServletUrl it will run the servlet and forward the details on to the jsp. (because doPost is not accessible via url directly like that, only doGet is)

Hope this helps. Let me know if you have trouble understanding anything!

Answer 2

I changed it and put the code in a Servlet like below and now I display the data by using the email address since it makes everything easier. I did it as follows.

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    HttpSession s = request.getSession();
    ResBean res = new ResBean();
    Connection connection = null;
    PreparedStatement preparedStm = null;
    ResultSet resultSet = null;
    String user_email = String.valueOf(s.getAttribute("uemail"));

    PrintWriter out = response.getWriter();
    out.println("<html><body><center><h1> Reservation Status</h1></center><br><h4 align=\"center\"> Rezervimi nga perdoruesi me email: " + user_email);
    out.println("<table border=\"1\" width=\"50%\" align=\"center\">");
    out.println("<tr><th colspan=\"3\">Data</th><th>Numri i dhomave</th><th>Numri i neteve</th>"
            + "<th>LLoji i dhomes</th><th>\r\n" + 
            "Kerkesa</th><th>Statusi</th></tr>");

    try {
        connection = ConnectionConfiguration.getConnection();
        preparedStm = connection.prepareStatement("SELECT * FROM reservations WHERE user_email = ?");
        preparedStm.setString(1, user_email);
        resultSet = preparedStm.executeQuery();

        while(resultSet.next()) {

            res.setDay(resultSet.getString("res_day"));
            res.setMonth(resultSet.getString("res_month"));
            res.setYear(resultSet.getString("res_year"));
            res.setNoRooms(resultSet.getString("no_rooms"));
            res.setNoNights(resultSet.getString("no_nights"));
            res.setRoomType(resultSet.getString("room_type"));
            res.setComments(resultSet.getString("add_comments"));
            res.setStatus(resultSet.getString("res_status"));
            res.setUser_email(resultSet.getString("user_email"));


            out.println("<tr><td>" + res.getDay() +"</td><td>"+ res.getMonth() + "</td><td>" + res.getYear() +"</td><td>"
                    + res.getNoRooms() + "</td><td>" + res.getNoNights() + "</td><td>" + res.getRoomType() + "</td><td>" +
                    res.getComments() + "</td><td>" + res.getStatus() + "</td></tr>");


        }

    } catch(Exception e) {
        e.printStackTrace();
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {

                e.printStackTrace();
            } 
        }
        if (preparedStm != null) {
            try {
                preparedStm.close();
            } catch (SQLException e) {

                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {

                e.printStackTrace();
            }
        }
    }
    out.println("</table></body></html>"); 

}

}