fetch and insert multiple rows into mysql database using jsp servlet

hello!!! I am building a attendance sheet in which, I am getting data from one jsp form and want inserting it into my mysql database table. but i am having a problem to insert multiple rows into database using a single insert query here is the code of both jsp and servlet

 <%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.0/themes/base/jquery-ui.css" />
  <script src="http://code.jquery.com/jquery-1.8.3.js"></script>
  <script src="http://code.jquery.com/ui/1.10.0/jquery-ui.js"></script>
  <link rel="stylesheet" href="/resources/demos/style.css" />
  <script>
  $(function() {
    $('.datepicker').datepicker();
    $.datepicker.formatDate('dd-mm-yy');
    $('div.ui-datepicker').css({ fontSize: '12px' });
  });
  </script>
        <title>JSP Page</title>
    </head>
    <body>
        <h1 align="center">Employee Attendance</h1>
        <form action="IsertAttendance" method="POST">
            <table align="center" cellspacing="10" border="0">
                <tr><td><strong>Date</strong></td><td><input type='text' class='datepicker' name='date'/></td></tr>
                <tr><td></td><td><strong>ID</strong></td><td><strong>Name</strong></td><td><strong>Status</strong></td></tr>

                <%
                try{
                Class.forName("com.mysql.jdbc.Driver");
                Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/scs","root","root");
                Statement st=con.createStatement();
                String sql="select * from staff";
                ResultSet rs=st.executeQuery(sql);
                while(rs.next()){
                    String id=rs.getString("id");
                    String name=rs.getString("fname");

                %>
                <tr><td></td><td><input type="text"value="<%=id %>" disabled="true" /></td><td><input type="text" value="<%=name %>" name="name" disabled="true"/></td>
            <td><select name="status">
            <option>Present</option>
            <option>Absent</option>
            <option>Leave</option>
            <option>Holiday</option>
        </select></td></tr>


                <% 
                }
                }catch(Exception e){
                out.println(e);
                }
                %>
                <tr><td></td><td></td><td><input type="Submit" value="Submit"/></td></tr>
            </table>    

        </form>
    </body>
</html>

**IsertAttendance.java**

    import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
public class IsertAttendance extends HttpServlet {


    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        int i;
        try {
            String s1=request.getParameter("date");
            String s2=request.getParameter("id");
            String s3=request.getParameter("name");
            String s4=request.getParameter("status");
                Class.forName("com.mysql.jdbc.Driver");
                Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/scs","root","root");
                Statement st=con.createStatement();
                String sql="insert into attendance(date,id,name,status) values('"+s1+"','"+s2+"','"+s3+"','"+s4+"')";

                st.executeUpdate(sql);

                st.close();
                con.close();
                response.sendRedirect("Attendance.jsp");
        }catch(Exception e){
        out.println(e);
        } finally {            
            out.close();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /** 
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /** 
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /** 
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}

this is my database table **attendance**

DROP TABLE IF EXISTS `scs`.`attendance`;
CREATE TABLE  `scs`.`attendance` (
  `date` varchar(30) NOT NULL default '',
  `id` varchar(45) NOT NULL default '',
  `name` varchar(45) NOT NULL default '',
  `status` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Please help me urgently it is my major project..

View Answers

February 18, 2013 at 10:15 AM

hi friend,

First you have done mistaken in your jsp page at

select * from staff

here, you are trying to get the records from unavailable database table.

Second you have done mistaken at

String name=rs.getString("fname");

here, you are trying to get the resultset from the unavailable field.

February 18, 2013 at 8:47 PM

I m right at that... actually i am also getting the records from the staff table and displayed on the jsp and problem is that from that jsp i want to insert all that records into the database

February 19, 2013 at 6:16 PM

hi friend,

To insert multiple records you can use the addBatch() method for executing the multiple insert query.

For detail tutorial please go through the following link, may this will be helpful for you.

http://www.roseindia.net/jdbc/prepared-statement-add-batch.shtml

February 19, 2013 at 7:07 PM

hi friend,

try this code

Attendance.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %> 
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" 
href="http://code.jquery.com/ui/1.10.0/themes/base/jquery-ui.css" />
<script src="http://code.jquery.com/jquery-1.8.3.js"></script>
<script src="http://code.jquery.com/ui/1.10.0/jquery-ui.js"></script>
<link rel="stylesheet" href="/resources/demos/style.css" />
<script>
$(function() {
$('.datepicker').datepicker();
$.datepicker.formatDate('dd-mm-yy');
 //$('.datepicker').datepicker({ dateFormat: 'dd-mm-yy' });
$('div.ui-datepicker').css({ fontSize: '12px' });
});
</script>
<title>JSP Page</title>
</head>
<body>
<h1 align="center">Employee Attendance</h1>
<form action="IsertAttendance" method="POST">
<table align="center" cellspacing="10" border="0">
<tr>
<td></td>
<td><strong>ID</strong></td>
<td><strong>Name</strong></td>
<td><strong>Status</strong></td>
<td><strong>Date</strong></td>
</tr>

<% 
try{ Class.forName("com.mysql.jdbc.Driver"); 
Connection con=DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/record","root","root"); 
Statement st=con.createStatement(); 
String sql="select * from staff"; 
ResultSet rs=st.executeQuery(sql); 
while(rs.next()){ 
    String id=rs.getString("id"); 
    String name=rs.getString("name");   
%>
<input type="hidden" name="id" value="<%= id %>"/>
<input type="hidden" name="name" value="<%= name %>"/>
<tr>
<td></td>
<td><%= id %></td>
<td><%= name %></td>
<td>
<select name="status">
<option>Present</option>
<option>Absent</option> 
<option>Leave</option> 
<option>Holiday</option> 
</select></td>
<td><input type='text' class='datepicker' name='date'/></td>
</tr>
<% 
}
}
catch(Exception e)
{
    out.println(e); 
}
%>
<tr></tr> 
<tr>
<td></td><td></td>
<td><input type="Submit" value="Submit"/></td>
</tr> 
</table>
 </form> 
</body>
</html>

continue.......

February 19, 2013 at 7:09 PM

IsertAttendance.java

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;

public class IsertAttendance extends HttpServlet {
    protected void processRequest(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        int i;
        try {
            String[] s1 = request.getParameterValues("date");
            String[] s2 = request.getParameterValues("id");
            String[] s3 = request.getParameterValues("name");
            String[] s4 = request.getParameterValues("status");
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/record", "root", "root");
            Statement st = con.createStatement();
            for(int j= 0; j<s2.length; j++)
            {
            String sql = "insert into attendance(date,id,name,status) values('"
                    + s1[j] + "','" + s2[j] + "','" + s3[j] + "','" + s4[j] + "')";
            st.addBatch(sql);
            }

            st.executeBatch();

            st.close();
            con.close();
            response.sendRedirect("Attendance.jsp");            
        } catch (Exception e) {
            out.println(e);
        } finally {
            out.close();
        }
    }

    // <editor-fold defaultstate="collapsed"
    // desc="HttpServlet methods. Click on the 
    //+ sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     * 
     * @param request
     *            servlet request
     * @param response
     *            servlet response
     * @throws ServletException
     *             if a servlet-specific error occurs
     * @throws IOException
     *             if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP <code>POST</code> method.
     * 
     * @param request
     *            servlet request
     * @param response
     *            servlet response
     * @throws ServletException
     *             if a servlet-specific error occurs
     * @throws IOException
     *             if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     * 
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}

February 20, 2013 at 8:50 PM

no its not working..........

Exception java.lang.ArrayIndexOutOfBoundsException: 1

May 21, 2013 at 2:41 PM

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package PhoneBook;

import com.mysql.jdbc.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author ignite084
 */
public class register extends HttpServlet {

    /**
     * Processes requests for both HTTP
     * <code>GET</code> and
     * <code>POST</code> methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
            /*
             * TODO output your page here. You may use following sample code.
             */
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet register</title>");
            out.println("</head>");
            out.println("<body>");
            out.println("<h1>Servlet register at " + request.getContextPath() + "</h1>");
            out.println("</body>");
            out.println("</html>");
        } finally {
            out.close();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP
     * <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP
     * <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        PrintWriter out = response.getWriter();
        String fname = request.getParameter("inputFName");
        String email = request.getParameter("inputREmail");
        String password = request.getParameter("inputRPassword");
        String mobile = request.getParameter("inputMNumber");
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/Exit", "root", "root");
            String queryString = "insert into register values('" + fname + "','" + email + "','" + password + "','" + mobile + "')";
            PreparedStatement ps = con.prepareStatement(queryString);

 int a = ps.executeUpdate();


            if (a==1) {
//                out.write("alert('Register Successfully');");
                response.sendRedirect("login.jsp");
            } else {
              //  response.sendRedirect("Home_1.jsp");
                out.write("alert('Invalid Details');");

            }

            ps.close();

            // String query1 = "select * from register1";
            // ps=con.prepareStatement(query1);
            //  ResultSet rs = ps.executeQuery();


            // ps.close();
            con.close();
        } catch (SQLException ex) {
            out.write(ex.toString());
        } catch (ClassNotFoundException ex) {
            out.write(ex.toString());
        } finally {
            out.close();
        }
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}