java - 从 javascript/AJAX 调用 MySQL 查询

Question

我有一个日历函数，我需要从 MySQL 数据库调用函数。系统的功能是这样的:您可以选择“开始”日期 - “结束”日期。它看起来像这样:

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

javascript/AJAX 代码有效，并且已成功连接到我的数据库。所以我想做的是调用查询:

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

因此它将返回 Allday_hours 列。有谁知道我该怎么做？最好的问候麦兹

<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>

我与数据库的连接是通过 servlet 进行的，如下所示:

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);
            }
        }
    }
}

Answer 1

我对 servlet 没有太多经验，但基本上，您可以指定 ajax 应执行什么操作，如下所示:

$.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');
    }   
}); 

并在您的 servlet 中指定一个 if 条件，如下所示(伪代码):

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 
}

这样就只执行提到的查询。最后你可以在ajax.success函数中处理接收到的数据。