How to save multiple records using Ajax in JSP

Question

I want to save multiple records in database without refreshing the page. For that I am using Ajax. But It saves only one record at a time.

My JSP code (CustomerInvoice.jsp):

<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" import="java.util.*" %> 
<%@page import="connection.DBConnection"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Customer Invoice wise </title>
<script type="text/javascript">
$(document).ready(function() 
          {
$('#customersubmit').click(function(e) 
            {
                e.preventDefault();

                var c1 = $("#dte").val();
                var c2 = $("#cname").val();
                var c3 = $("#ccd").val();
                var c4 = $("#cred").val();
                var c5 = $("#inn").val();
                var c6 = $("#innamt").val();
                var c7 = $("#amtadjust").val();
                var c8 = $("#balamt").val();

                var value ='dte='+c1+'&cname='+c2+'&ccd='+c3+'&cred='+c4+'&inn='+c5+'&innamt='+c6+'&amtadjust='+c7+'&balamt='+c8;


                $.ajax({
                        type:"get",
                        url: "CustomerInvoiceServlet1", 
                        data:  value, 
                        cache: false,
                        success: function(data) 
                        {
                            //$('#ramountt').val(c8); 
                        }
                    });

                });
          });

    </script>


<script type="text/javascript" src="JS/jquery-1.4.2.min.js"></script>


</head>
<body>
<h2 align="center"> Customer Invoice wise outstanding</h2>
<form name="bankledgerreport" action="CustomerInvoiceServlet1" method="get" >

<table align="center" border=5 width=700>
<tr><th>Date</th>
<th>Invoice Number</th>
<th>Invoice Amount</th>
<th>Amount Adjusted</th>
<th>Balance Amount</th></tr>



<%  DBConnection dbc=new DBConnection();
            Connection con=dbc.getNewConnection();
            Statement st = null;
            ResultSet rs = null;
            String selectedcustcode = request.getParameter("acc");

            ///int count=1;
            double debtors=0, creditors=0, cashbank=0, fixedassets=0, other=0, capital=0, otherexpenses=0, sellingandmarketing=0, salaryandwages=0, administrativecharges=0, provision=0, unsecuredloans=0, securedloans=0, investments=0, closingstock=0, openingstock=0, loansandadvances=0, indirectincome=0, bankchargesandinterest=0, depreciation=0; //trial balance
            //out.println(selectedcustcode);
            double bal=0.0,in=0.0,adj=0.0,bal1=0.0,bal2=0.0;

            String dte="",nm="",cd="",inn="",inn1="";
            List li=new ArrayList();
            %>  

            <%
            try
            {
                  st=con.createStatement() ;



                          rs=st.executeQuery("SELECT innum,date,svname,ccode,balance,scred FROM salesinvoiceoutstanding WHERE  EXISTS (SELECT  innum FROM salesvoucher2  WHERE  salesinvoiceoutstanding.innum = salesvoucher2.innum ) and ccode='"+selectedcustcode+"'");
                          while(rs.next())
                          {

                              inn=rs.getString(1);
                                dte=rs.getString(2);
                                nm=rs.getString(3);
                                cd=rs.getString(4);
                                 bal=rs.getDouble(5);
                                 bal1=bal1+bal;
                                 int cred=rs.getInt(6);
                                 %>
                                    <input type=hidden name=cname id=cname class=cname value="<%=nm%>"/>
                  <input type=hidden name=ccd id=ccd class=ccd value="<%=cd%>"/>
                <input type=hidden name=cred id=cred class=cred value="<%=cred%>"/>
             <tr>
             <td><input type=hidden id=dte name=dte class=dte value="<%=dte %>"/><%=dte %></td>

             <td><input type=hidden id=inn name=inn class=inn value="<%=inn %>"/><%=inn %></td>



            <td><input type=hidden  name=innamt id=innamt class=innamt value="<%=bal %>"/><%=bal %></td>
             <td><input type=text name=amtadjust id=amtadjust class=amtadjust value="0"  /></td>
             <td><input type=text name=balamt id=balamt class=balamt value="<%=bal %>" /></td>

              </tr>
          <%} 
                          rs=st.executeQuery("SELECT innum,date,scname,ccode,stota,scredlim FROM salesvoucher2 WHERE not EXISTS (SELECT  innum FROM salesinvoiceoutstanding  WHERE  salesinvoiceoutstanding.innum = salesvoucher2.innum ) and (ccode='"+selectedcustcode+"' and status=1)");
                          while(rs.next())
                          {

                              inn=rs.getString(1);
                                dte=rs.getString(2);
                                nm=rs.getString(3);
                                cd=rs.getString(4);
                                 in=rs.getDouble(5);
                                 bal2=bal2+in;
                                 int cred=rs.getInt(6);   

                        %>  
                        <input type=hidden name=cname id=cname class=cname value="<%=nm%>"/>
                  <input type=hidden name=ccd id=ccd class=ccd value="<%=cd%>"/>
                <input type=hidden name=cred id=cred class=cred value="<%=cred%>"/>
             <tr>
             <td><input type=hidden id=dte name=dte class=dte value="<%=dte %>"/><%=dte %></td>

             <td><input type=hidden id=inn name=inn class=inn value="<%=inn %>"/><%=inn %></td>
            <td><input type=hidden  name=innamt id=innamt class=innamt value="<%=in %>"/><%=in %></td>
             <td><input type=text name=amtadjust id=amtadjust class=amtadjust value="0"  /></td>
             <td><input type=text name=balamt id=balamt class=balamt value="<%=in %>" /></td>

              </tr>

<%                
            }}
               catch(Exception e)
               {} %>
               <% double amount=bal1+bal2; %>
               <tr><td></td><td>Advanced Received</td><td></td><td><input type=text name=advance id=advance value="0"/></td><td></td></tr>
               <tr><td></td><td>Total</td><td></td><td><input type=text name=total id=total value="0" onblur="sub()" /></td><td><input type="text" name="totamt" value="<%=amount %>"  /></td></tr>

 </table>
 <br><br>
<center> <input type=button name="customersubmit" id="customersubmit" value=Save></center>
        </form>     
</body>
</html>

Image for above JSP coding

In above image, I have to save those two records that are SV2 and SV3 (Invoice number) in database using Ajax.

My servlet code:(CustomerInvoiceServlet1.java)

package com;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import connection.DBConnection;

/**
 * Servlet implementation class CustomerInvoiceServlet1
 */
public class CustomerInvoiceServlet1 extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public CustomerInvoiceServlet1() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub

        String dt1="",nm1="",cd1="",in1="",amt1="",amtadj1="",balamt1="",cr1="";
        int crd[] = null;
        //int cr1=0;
        DBConnection dbc=new DBConnection();
        Connection con=dbc.getNewConnection();
        Statement st = null;
        ResultSet rs=null;
        String dt[] = request.getParameterValues("dte"); 
        String nm[]=request.getParameterValues("cname");
        String cd[]=request.getParameterValues("ccd");
        String cr[]=request.getParameterValues("cred");
        String in[]=request.getParameterValues("inn");
        //int count[]=Integer.parseInt(request.getParameterValues("cnt1"));
        String amt[]=request.getParameterValues("innamt");
     String amtadj[]=request.getParameterValues("amtadjust");
    String balamt[]=request.getParameterValues("balamt");

    String  query="";
     double pyamt=0.0;

  try{
     st=con.createStatement();
    for (int i=0;i<in.length;i++)
    {
        cd1=cd[i];
        String  query1="delete from salesinvoiceoutstanding where ccode = '"+cd1+"';";
        st.executeUpdate(query1);
    }

  for (int i=0;i<in.length;i++) {

      dt1=dt[i];
     nm1=nm[i];
     cd1=cd[i];
     cr1=cr[i];
     in1=in[i];
     amt1=amt[i];
     amtadj1=amtadj[i];
     balamt1=balamt[i];

     query="INSERT INTO salesinvoiceoutstanding(date,innum,svname,ccode,svamount,adjamount,balance,scred) VALUES('"+dt1+"','"+in1+"','"+nm1+"','"+cd1+"','"+amt1+"','"+amtadj1+"','"+balamt1+"','"+cr1+"')";
    int j=st.executeUpdate(query);

    }

    con.close();


}catch(Exception e){
  e.printStackTrace();
}

    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }

}

Above all coding saves only first record i.e. SV2. So My question is how can I save those two records(SV2 and SV3) or more records in database at a time using Ajax.

Tim Biegeleisen · Answer 1 · 2015-03-23T09:29:23.810

1

You can do a query of the following form to INSERT multiple records with one query in MySQL:

INSERT INTO salesinvoiceoutstanding (date,innum,...) VALUES(dt1,in1,...),(dt2,in2,...),(dt3,in3,...);

Here is some sample code which will allow you to handle an arbitrary number of INSERTs coming from your AJAX call:

String query = "INSERT INTO salesinvoiceoutstanding(date,innum,svname,ccode,svamount,adjamount,balance,scred) ";
for (int i=0; i<in.length; ++i) {

    dt1=dt[i];
    nm1=nm[i];
    cd1=cd[i];
    cr1=cr[i];
    in1=in[i];
    amt1=amt[i];
    amtadj1=amtadj[i];
    balamt1=balamt[i];

    if (i > 0) {
        query += ",";
    }

    query += "VALUES('"+dt1+"','"+in1+"','"+nm1+"','"+cd1+"','"+amt1+"','"+amtadj1+"','"+balamt1+"','"+cr1+"')";
}

query += ";"; // don't forget the semi-colon at the end of the query

int j=st.executeUpdate(query);

By the way, you should be really careful about SQL injection attacks with your code.

edited Mar 23 '15 at 09:29

answered Mar 23 '15 at 09:15

Tim Biegeleisen

502,043
27
286
360

Ok. But there can be multiple records. It is dynamic. – Amit Shirke Mar 23 '15 at 09:19
I have used your query. But it still saves the first record only. I think its Ajax problem that it is not passing those two records to the servlet. How can I pass those two records or more than two records to the servlet through Ajax. – Amit Shirke Mar 23 '15 at 09:41
You are going to have to either use more parameters, or send arrays of values. – Tim Biegeleisen Mar 23 '15 at 09:45
How can I send array of values through Ajax from JSP. – Amit Shirke Mar 23 '15 at 09:49
See [this SO post](http://stackoverflow.com/questions/8890524/pass-array-to-ajax-request-in-ajax) for how to do that. – Tim Biegeleisen Mar 23 '15 at 09:58
Suppose I am using this code: info = []; info[0] = 'hi'; info[1] = 'hello'; $.ajax({ type: "POST", data: {info:info}, url: "index.php", success: function(msg){ $('.answer').html(msg); } }); – Amit Shirke Mar 23 '15 at 10:06
then what will be my servlet code. How can i request those parameters(info) in servlet. – Amit Shirke Mar 23 '15 at 10:11

How to save multiple records using Ajax in JSP

1 Answers1