How to update multiple records in database using jsp and servlet

debugcn Published at Dev

P.Kartheek

EditableFormat.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
   <%@page import ="techpanel.Getters" %>
    <%@page import="java.util.Vector" %>


<!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>Panel Details</title>
</head>

<%Vector<Getters> vec=(Vector<Getters>)request.getAttribute("vec"); %>

<h1>Technical Panel</h1>



<center>
<table width="80" id="customers" id="Add">


<tr>
<form action="UpdateTechpanelDetails" method="Post">

<td>Employee ID</td>

<th><u>First Name</td>

<th><u>Last Name</u></th>
<th><u>Mobile No</u></th>

<th><u>Email ID</u></th>

<th><u>Skills</u></th>

</tr>

 <% 
for(int i=0;i<vec.size();i++)
 {%>

<tr>
 <td><input type="text" name="EmployeeID" value=" %=vec.elementAt(i).getEmployeeID()%>"/> </td>
 <td><input type="text" name="FirstName" value="<%=vec.elementAt(i).getFirstName()%>"/> </td>
<td><input type="text" name="LastName" value="<%=vec.elementAt(i).getLastName()%>"/></td>
<td><input type="text" name="MobileNo" value="<%=vec.elementAt(i).getMobileNo()%>"/></td>
<td><input type="text" name="EmailID" value="<%=vec.elementAt(i).getEmailID()%>"/></td>
<td><input type="text" name="PanelPersonSkills" value="<%=vec.elementAt(i).getPanelPersonSkills()%>"/></td>
</tr>
<tr>

}
</table>
<br><br>
<center><table>
<tr>
 <td><input type="submit"    value="Update"  /></td></form>
<td><form action="RetrievingTechpanelDetails"><input type="submit" name="button"  value="Back"></form></td>
</tr>
</table>
</body>
</html>

UpdateTechpanelDetails.java

package techpanel;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import javax.servlet.RequestDispatcher;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;


public class UpdateTechpanelDetails extends HttpServlet {

    public UpdateTechpanelDetails() {
        super();
        }


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



         String Eid = request.getParameter("EmployeeID");
         System.out.println(Eid);
        String FName = request.getParameter("FirstName");
        System.out.println(FName);
        String LName = request.getParameter("LastName");
        System.out.println(LName);
        String MNo = request.getParameter("MobileNo");
        System.out.println(MNo);
        String EID = request.getParameter("EmailID");
        System.out.println(EID);
        String Skills = request.getParameter("PanelPersonSkills");
        System.out.println(Skills);

        String msg=("Data Updated");
        Connection con=null; 
         PreparedStatement st=null;
       try {
          Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306?user=root&password=kartheek");
         String sql = " UPDATE techpanel1.techpaneldetails SET EmployeeID=?,FirstName=?,LastName=?,MobileNo=?,EmailID=?,PanelPersonSkills=? WHERE EmployeeID=?";

        con.setAutoCommit(false);
          st = con.prepareStatement(sql);

         st.setString(1, Eid); 
         st.setString(2, FName ); 
         st.setString(3, LName); 
         st.setString(4, MNo ); 
         st.setString(5, EID );
         st.setString(6, Skills );
         st.setString(7, Eid); 



        st.executeUpdate();

        con.commit();
        request.setAttribute(".", msg);
         con.close();
         st.close();
                RequestDispatcher rd = request.getRequestDispatcher("AddingTechpanelDetails.jsp");
              rd.include(request, response);
        }

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

        }

       }


    private int getInitParameter() {
        // TODO Auto-generated method stub
        return 0;
    }

  }

Main Problem

I have fetched the values from database in a vector array(vec.elementAt(i).getEmployeeID(),vec.elementAt(i).getFirstName()) into a text boxes which is of editable format(input type="text" name=EmployeeID value=<%=vec.elementAt(i).getEmployeeID()%>/>), and i want to update multiple records into database.but only first record is getting updated.. remaining or not updating. can any one help me please

Shailesh

using batch you can insert or update number of records try this code

 String sql = "update people set firstname=? , lastname=? where id=?";


PreparedStatement preparedStatement = null;
try{
    preparedStatement =
            connection.prepareStatement(sql);

preparedStatement.setString(1, "Gary");
preparedStatement.setString(2, "Larson");
preparedStatement.setLong  (3, 123);

preparedStatement.addBatch();

preparedStatement.setString(1, "Stan");
preparedStatement.setString(2, "Lee");
preparedStatement.setLong  (3, 456);

preparedStatement.addBatch();

int[] affectedRecords = preparedStatement.executeBatch();

}finally {
    if(preparedStatement != null) {
        preparedStatement.close();
    }
}

Collected from the Internet

Please contact [email protected] to delete if infringement.