[FIXED] Debugging JavaScript and a servlet

Issue

I am quite new at programming. This question is in preparation for an exam. I have a servlet which has a connection to my MySQL database. Futheron I have a JavaScript.

I have 2 calendars where you can choose a "from" date - "to" date. If I fx pick from 2014-03-06 - 2014-03-10 I want to have printed out how many hours I have been working in this time interval. That means that my servlet is getting the COLUMN "Allday_hours" from my MySQL database. The problem is that every time I choose a date from the javascript, it just returns all the hours from Allday_hours. That means that it is running through all the COLUMNS instead of the dates I have chosen.

Can anybody see what I have done wrong here?

Javascript:

  <form>
        <input id="startDate"/>                     
        <input id="endDate"/>
    </form>
    <div id="startresult"></div>
    <div id="endresult"></div>
    <script>

    $(function(){
        $("#startDate").datepicker({
            dateFormat: 'yy-mm-dd',
            onSelect: function(dateText,inst){
                $('.selected-date').html(dateText);

                $.ajax({
                      url: "../getHoursSQL",
                      type: "post",
                      data: JSON,
                      success: function(data){
                           start: $("#startDate").val();
                          alert("success");
                          $("#startresult").html(data);

                      },
                      error:function(){
                          alert("failure");
                          $("#startresult").html('there is error while submit');
                      }  
                    });
            }
        });
    });

    $(function(){
            $("#endDate").datepicker({
                dateFormat: 'yy-mm-dd',
                onSelect: function(dateText,inst){
                    $('.selected-date').html(dateText);

                    $.ajax({
                          url: "../getHoursSQL",
                          type: "post",
                          data: JSON,
                          success: function(data){
                              end: $("#endDate").val();
                              alert("success");
                              $("#endresult").html(data);
                          },
                          error:function(){
                              alert("failure");
                              $("#result").html('there is error while submit');
                          }  
                        });
                }
            });
        });

</script>

Servlet:

        package WorkPackage;

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;

@WebServlet("/getHoursSQL")
public class getHoursSQL extends HttpServlet{

    private static final long serialVersionUID = 1L;

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doPost(request, response);
    }

    public void doPost(HttpServletRequest req, HttpServletResponse res) 
        throws ServletException, IOException{

        String connectionURL = "jdbc:mysql://localhost/NekiWork";
        Connection connection=null;

        try {

            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(connectionURL, "root", ""); 
            String sql = "SELECT *, (Day_hours + (Day_minutes / 60)) AS Allday_hours FROM Workdata WHERE startDate='?' AND endDate='?'"; 
            PreparedStatement pst = connection.prepareStatement(sql);
                pst.setXXX(1,startDate);
                pst.setXXX(2,endDate);

                ResultSet rs = pst.executeQuery(sql);

            float Allday_hours_sum = 0;
                while (rs.next()){                                      
                    Allday_hours_sum += rs.getFloat("Allday_hours"); 


            }   
                res.setContentType("text/html;charset=UTF-8");          
                res.getWriter().print(Allday_hours_sum); 
                String startDate = req.getParameter("startDate");
                String endDate= req.getParameter("endDate");

            pst.close();


        }
        catch(ClassNotFoundException e){

            System.out.println("Couldn't load database driver: " + e.getMessage());
        }
        catch(SQLException e){
            System.out.println("SQLException caught: " + e.getMessage());
        }
        catch (Exception e){
            System.out.println(e);
        }
        finally {

            try {
                if (connection != null) connection.close();
            }
            catch (SQLException ignored){
                System.out.println(ignored);
            }
        }
    }
}

Solution

First of all, you forgot to send startDate&endData from your javascript code to servlet. You can do it with changing the "data" member of ajax method argument.

Also you should get it from the request in your servlet like below

String startDate = req.getParameter("startDate");
String endDate= req.getParameter("endDate");

And then you should supply values to your inputs in preparedStatement.

pst.setXXX(1,startDate);
pst.setXXX(2,endDate);

I hope these information help you to solve your problem

Answered By - Yucel