Call MySQL query from javascript/AJAX

Question

I have a calendar function, where I need to call a function from my MySQL database. The system functions like this: you can choose a "from" date - "to" date. It looks like this:

http://postimg.org/image/5uurc5ycb/

The javascript/AJAX code works and it is successfully connected to my database. So what I want to do is to call the query:

SELECT *, (Day_hours + (Day_minutes / 100)) as Allday_hours FROM Workdata

so it will return the column Allday_hours. Does anyone knows how I can do that? Best Regards Mads

<form>
        <input id="start1" />
        <input id="start2" />
    </form>

    <script>

    $(function(){
        $("#start1").datepicker({
            dateFormat: 'yy-mm-dd',
            onSelect: function(dateText,inst){
                alert(dateText);

                $.ajax({
                      url: "../dataExchange",
                      type: "post",
                      data: Date,
                      success: function(){
                          alert("success");
                           $("#result").html('submitted successfully');
                      },
                      error:function(){
                          alert("failure");
                          $("#result").html('there is error while submit');
                      }   
                    });
            }
        });
    });


    $(function(){
        $("#start2").datepicker({
            dateFormat: 'yy-mm-dd',
            onSelect: function(dateText,inst){
                alert(dateText);

                $.ajax({
                      url: "../dataExchange",
                      type: "post",
                      data: Date,
                      success: function(){
                          alert("success");
                           $("#result").html('submitted successfully');
                      },
                      error:function(){
                          alert("failure");
                          $("#result").html('there is error while submit');
                      }   
                    });
            }
        });
    });

</script>

My connection to the database is going through a servlet, that looks like this:

package WorkPackage;

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

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

    private static final long serialVersionUID = 1L;

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

    public void init(ServletConfig config) throws ServletException{
        super.init(config);
    }

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

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

        res.setContentType("text/html");
        PrintWriter out = res.getWriter();

        String Date = req.getParameter("Date");
        String Name = req.getParameter("Name");
        String Address = req.getParameter("Address");
        String Day_hours = req.getParameter("Day_hours");
        String Day_minutes = req.getParameter("Day_minutes");
        String Km_to_address = req.getParameter("Km_to_address");
        String Time_to_address = req.getParameter("Time_to_address");

        try {

            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(connectionURL, "root", ""); 
            String sql = "INSERT INTO Workdata (Date, Name, Address, Day_hours, Day_minutes, Km_to_address, Time_to_address) VALUES (?,?,?,?,?,?,?)"; 
            PreparedStatement pst = connection.prepareStatement(sql);
            pst.setString(1, Date);
            pst.setString(2, Name);
            pst.setString(3, Address);
            pst.setString(4, Day_hours);
            pst.setString(5, Day_minutes);
            pst.setString(6, Km_to_address);
            pst.setString(7,  Time_to_address);

            pst.executeUpdate();
            pst.close();
        }
        catch(ClassNotFoundException e){

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

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

I have edited my question. I forgot to write that my connection goes through a servlet :-/ — McDuck4, Mar 05 '14 at 17:31
I guess I am not sure where you are getting hung up here. By looking at your servlet, it seems you already know how to make a query on the database. What is stopping you from simply running the query you desire against the database? In other words what is the specific problem you are running into when you try this? — Mike Brant, Mar 05 '14 at 17:33
Hello Mike. Thanks a lot for your answer. My problem is that I don't know where to put the: SELECT *, (Day_hours + (Day_minutes / 100)) as Allday_hours FROM Workdata. That query should be executed when I have chosen my dates. — McDuck4, Mar 05 '14 at 17:38
Better to use [JSON](http://stackoverflow.com/a/4113258/1391249) instead. — Tiny, Mar 05 '14 at 17:38
So I am thinking that from the Ajax calendar I have to get some code that call the query? — McDuck4, Mar 05 '14 at 17:39

Matúš Dúbrava · Accepted Answer · 2014-03-05T18:10:50.090

I do not have much experience with servlets, but basically, you can specify what action should be performed by ajax like this:

$.ajax({
    url: "../dataExchange",
    type: "post",
    data: {date: Date, action: "doTheSelect"},
    dataType: 'json', 
    success: function(data){
        alert("success");
        $("#result").html('submitted successfully');

        // do something with variable - data(returned json object)
    },
    error:function(){
        alert("failure");
        $("#result").html('there is error while submit');
    }   
});

And in your servlet specify an if condition like this(pseudocode):

if(posted_data.action == "doTheSelect") {
    // here goes the SQL query

    return "json encoded result of query";
} else {
    // do some other stuff like specifying another condition based on another value of action variable 
}

So that only the mentioned query is performed. And finally you can handle the received data in the ajax.success function.

Call MySQL query from javascript/AJAX

1 Answers1