Home
Answers
Viewqa
JSP-Servlet
fetch and insert multiple rows into mysql database using jsp servlet
QuestionAsk Questions?
Amyn Kapadia
fetch and insert multiple rows into mysql database using jsp servlet
6 Answer(s) 3 months and 4 days ago
Posted in : JSP-Servlet
6 Answer(s) 3 months and 4 days ago
Posted in : 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
Related Pages:
If you are facing any programming issue, such as compilation errors or not able to find the code you are looking for.
Ask your questions, our development team will try to give answers to your questions.
Tutorial Topics
