fetch and insert multiple rows into mysql database using jsp servlet

Ads
 

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

Ads









Related Tutorials/Questions & Answers:
fetch and insert multiple rows into mysql database using jsp servlet
fetch and insert multiple rows into mysql database using jsp servlet  ... 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
fetch record from oracle database using jsp-servlet?
fetch record from oracle database using jsp-servlet?  how can i fetch data from oracle database by using jsp-servlet. i'm using eclipse, tomcat server and oracle database and creating jsp pages and also using servlet
Advertisements
insert name city and upload image in database using mysql and jsp
insert name city and upload image in database using mysql and jsp   insert name city and upload image in database using mysql and jsp
Image upload in mysql database using jsp servlet
Image upload in mysql database using jsp servlet  Hello, I need code to insert image in mysql database, I have seen the code which is already in your portal but it is not inserting image into database it save in the folder
save multiple records into database using jsp/servlet mvc
save multiple records into database using jsp/servlet mvc  hai, this is my jsp where i have enter multiple username and password and save... set to bean and pass bean to dao insert() and insert into database thank u
insert name city image in database using mysql and jsp
insert name city image in database using mysql and jsp  how to insert name ,city and image in database in mysql and jsp   Here is an example in jsp that insert name, city and image to database. <%@ page import
retrive the employee details with image from mysql database using jsp servlet
retrive the employee details with image from mysql database using jsp servlet  im doing the web project to retrive the employee profile which i stored in the database using jsp servlet then want to show the result in the next jsp
how insert multiple images in mysql database with use of struts 1.3.8 or java method, with single bean,or using array
how insert multiple images in mysql database with use of struts 1.3.8 or java method, with single bean,or using array  i am using netbeans 7.0 ,with struts 1.3.8 and i want to insert multiple images in mysql database ,with use
how to fetch image from mysql using jsp
how to fetch image from mysql using jsp  how to fetch image from mysql using jsp
How to insert multiple drop down list data in single column in sql database using servlet
How to insert multiple drop down list data in single column in sql database using servlet  i want to insert date of birth of user by using separate drop down list box for year,month and day into dateofbirth column in sql server
how to insert multiple columns of a single row into my sql database using jsp
how to insert multiple columns of a single row into my sql database using jsp  hi sir, how to insert multiple columns of a single row into my sql database using jsp. when i click ADD ROW,rows are added.when i click submit
how to fetch data from mysql database table and draw a bar chart on that data using in jsp
how to fetch data from mysql database table and draw a bar chart on that data using in jsp  how to create bar chart fetch data from mysql database using in jsp.please give me a right code. yhanks in advance
How to insert image in sql database from user using servlet
How to insert image in sql database from user using servlet  pls tell me accept image from user and insert image in sql server 2005 database using servlet and jsp
Insert data in mysql database through jsp using Prepared Statement ---- Please Resolve it
Insert data in mysql database through jsp using Prepared Statement ---- Please Resolve it   I have tried the following code Database creation: create database studentdb; create table stu_info ( ID int not null auto
how to insert data into database using jsp & retrive
how to insert data into database using jsp & retrive  Hello, I have created 1 html page which contain username, password & submit button. in my oracle10G database already contain table name admin which has name, password
Insert data in mysql database through jsp using Prepared Statement ---- Please Resolve it
Insert data in mysql database through jsp using Prepared Statement ---- Please Resolve it   I have tried the following code Database creation: create database studentdb; create table stu_info ( ID int not null auto
how to insert data in database using html+jsp
how to insert data in database using html+jsp  anyone know what...; Here we have used MySQL database: 1)form.html: <html> <form... and database name. Here machine name id localhost and database name
edit database using jsp and servlet
edit database using jsp and servlet  I am creating a website using jsp and servlets that is used to view houses from a database. I want to be able... information from the database in the textboxes. Please help me to display
how to retrieve text and images from mysql database and show on html page using jsp servlet
how to retrieve text and images from mysql database and show on html page using jsp servlet  <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> Insert title here h3
how to insert checkbox value into database using jsp
how to insert checkbox value into database using jsp  how to insert check box value to the database using jsp my code is <link href="font&colors.css" rel="stylesheet" type="text/css"> <p></p>
how to insert checkbox value into database using jsp
how to insert checkbox value into database using jsp  How to insert check box value to the oracle database using jsp? I want to create hotel's...;   Here is a simple jsp code that insert the selected checkbox values
Populate dropdown menu from database using jsp and servlet
Populate dropdown menu from database using jsp and servlet  please i need code to populate dropdown menu from mysql database using jsp and servlet. thanks
how to insert checkbox value into database using jsp
how to insert checkbox value into database using jsp  How to insert check box value to the oracle database using jsp? I want to create hotel's package. where the admin will select the activities to insert in the PACKAGE table. I
get info from mysql using jsp and servlet
get info from mysql using jsp and servlet  HELLO! I wanna create a jsp page which able to let me get its name, phone and other info by asking the user to key in their email address from mysql database by using servlet and jsp too
insert rows from browsed file to sql database
insert rows from browsed file to sql database  i need to insert rows from excel to database by browsing the file in jsp. by connecting both..., content of the file has to go to database. how can i insert record into database
How to insert rows from Excel spreadsheet into database by browsing the excel file?
excel file and insert rows into MSSQL database in JSP???   Have a look...How to insert rows from Excel spreadsheet into database by browsing the excel...-Servlet/18638-Read-Excel-data-using-JSP-and-update-MySQL-databse.html
How to insert and retreive an image in mysql database using spring mvc framework???
How to insert and retreive an image in mysql database using spring mvc... for handling all application requests --> <servlet> <servlet-name>dispatcher</servlet-name> <servlet-class>
login page using jsp servlrt with mysql database?
login page using jsp servlrt with mysql database?  Description: example:total users are 3.each use have username and password save in mysql database table login. After successfully login user1 see only index page,if user2 login
Mysql Multiple Date Insert
Mysql Multiple Date Insert       Mysql Multiple Column Insert is used to add or insert... illustrate an example from 'Mysql Multiple Column Insert'.To understand
Retrieve image from database using servlet and display in JSP
to use MySQL Database from JSP page. How to retrieve image from database using servlet and display in JSP? Thanks...Retrieve image from database using servlet and display in JSP  Hi, I
Mysql Multiple Date Insert
Mysql Multiple Date Insert       Mysql Multiple Column Insert is used to add or insert... illustrate an example from 'Mysql Multiple Column Insert'. To understand an example

Ads