Can I use a value passed through ajax to make a database search using a prepared statement?

Question

Guys I been struggling with this issue quite a bit, I'm a novice in using ajax calls and jdbc related functions. Moving on to the point, I'm passing a datepicker value to the servlet side by using an ajax function, the function works right, I'm printing the value in a random div just to see if its working and it does work. Now how can I take that value and use it in a prepared statement to search on my database data depending on the date picked by the user, can this be done at all?

heres some code to add more context

$.ajax({
            type:'POST',
            data:{dateinput:dateinput}, 
            url:'/Rest/sales?action=param2',
            dataType: 'json',
            success:function(result){

                $('#altdate').html(result);
            },
            error: function (error) {
                console.log(error);
            }

        });

    });

I have a prepared statement with a static value just to see if it was working (it does) but here is where I can't make progress, how do I declare that value?

ConnectionPool pool = ConnectionPool.getInstance(DatosGeneral.DATABASE);
    Connection connection = pool.getConnection();
    PreparedStatement ps = null;
    ResultSet rs = null;

    String[] ids = null;
    double[] prices = null;


    try{
        String query = "SELECT * FROM [" + GeneralData.DATABASE + "].[dbo].[SalesDetails] "
                + "WHERE [Branch] = '" + GeneralData.bra + "' "
                + "AND [UpDate] = ?" ; //previous value 20190521
        System.out.println(query);
        ps = connection.prepareStatement(query,
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        rs = ps.executeQuery();
        int rowcount = 0;

        if (rs.last()) {
            rowcount = rs.getRow();
            rs.beforeFirst();
        }

        ids = new String[rowcount];
        prices = new double[rowcount];

        for (int i = 0; rs.next(); i++) {
            if (rs.getString("Type").replaceAll("\\s+", "").equals("PROD")) {
                ids[i] = rs.getString("ProductCode").replaceAll("\\s+", "");
                prices[i] = rs.getDouble("Price");
            }
        }

Lastly my dopost function where I'm requesting the parameters

protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    String action= request.getParameter("action");
    if(action.equals("param1")){
        int i = Integer.parseInt(request.getParameter("branch"));
        GeneralData.bra = GeneralData.branches[i];
        GeneralData.braName = GeneralData.braNames[i];
        System.out.println(GeneralData.bra);

    }else if (action.equals("param2")){
        String altdate = request.getParameter("dateinput");
        PrintWriter out = response.getWriter();
        out.print(altdate);
    }

any help would be appreciate it

Well, pass altdate as argument to the method containing your JDBC code, and use PreparedStatement.setParameter() to specify the parameer of your query. — JB Nizet, Jan 14 '20 at 19:11
before ps.executeQuery(); add 1 line ps.setDate(1,java.sql.Date.valueOf("2013-09-04")); Refer this link https://stackoverflow.com/questions/18614836/using-setdate-in-preparedstatement — Joginder Malik, Jan 14 '20 at 19:19
@JBNizet Can you give me an example, to clarify things more? — Miguel Sanchez, Jan 14 '20 at 19:31
1. `yourClass.yourMethod(altdate);` 2. `ps.setString(altdate):` — JB Nizet, Jan 14 '20 at 19:33

score 0 · Answer 1 · answered Jan 16 '20 at 18:15

Well I solved it, the only thing I did was to declare a variable where I could store the value passed through the ajax call(the value of the datepicker) and assign it with the doPost method. Something like this:

private String datePickerValue=null;

doPost method:

protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

      datePickerValue=altdate;
 }

and lastly in the query of the prepared statement just specified that variable as the parameter used to get the data from the db:

try{
    String query = "SELECT * FROM [" + GeneralData.DATABASE + "].[dbo].[SalesDetails] "
            + "WHERE [Branch] = '" + GeneralData.bra + "' "
            + "AND [UpDate] = '" + datePickerValue +"'" ; //previous value 20190521

Can I use a value passed through ajax to make a database search using a prepared statement?

1 Answers1