Java 在eclipse中使用servlet jsp在同一个数据库表中插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23776277/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
insert multiple rows in same database table using servlet jsp in eclipse
提问by Atiq
I m trying insert multiple rows in same database (each row had many fields) on submit but unable to do so. I m successfully able to insert single row but when it comes for multiple rows I got stuck.
我正在尝试在提交时在同一个数据库中插入多行（每行有很多字段），但无法这样做。我成功地能够插入单行，但是当涉及到多行时，我被卡住了。
This is the jsp page where you take an input to dynamically add no of rows. There is no separate submit button for each row instead single submit button to insert all rows.
这是 jsp 页面，您可以在其中输入以动态添加无行。每行没有单独的提交按钮，而是单个提交按钮来插入所有行。
1.> projmgrsuccessful.jsp
1.> projmgrsuccessful.jsp
<form name="allocate-project-task" action="AddProjectTaskDAO" method="get" enctype="multipart/form-data">
<h4>Another popup</h4>
<input type="hidden" name="proj_emp_comt" value="">
<input type="hidden" name="pid" value="<c:out value="${pid}" />">
<label>Enter the no of employee involved in project</label>
<input type="text" id="textbox1" />
<input type="button" value="Add" id="add" />
<table border="1" id="pt-tabledata">
</table>
<center><input type="submit" name="alloc_time" value="Allocate"><a href="#" id="closea">Close</a></center>
</form>
<script>
$("#add").click(function() {
$('#pt-tabledata').append('<tr><td>Username</td><td>Work description</td> <td>Allocate time</td><td>Allocate work and time</td></tr>');
for (var i = 0; i < $("#textbox1").val(); i++) {
$newdiv2 = $('<tr id="pt-ip-row" style="display:block;"><td><select name="uname"><c:forEach items="${ual}" var="u"><option value="<c:out value="${u.uname}"></c:out>"><c:out value="${u.uname}"></c:out></option> </c:forEach> </select></td><td><textarea rows="5" cols="15" name="pdesc_emp"></textarea></td><td><input type="text" name="pestd_time_alloc" /></td><td></td><td><a href="#" id="close-pt-row" onclick="return hide();">Close</a></td> </tr>').after("#add");
$('#pt-tabledata').append($newdiv2);
}
});
</script>
This is java code snippet where I processing the data like connecting to the database and inserting it into database table.
这是 Java 代码片段，我在其中处理数据，例如连接到数据库并将其插入到数据库表中。
2.> AddProjectTaskDAO.java
2.> 添加ProjectTaskDAO.java
package com.pms.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class AddProjectTaskDAO
*/
@WebServlet("/AddProjectTaskDAO")
public class AddProjectTaskDAO extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public AddProjectTaskDAO() {
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
response.setContentType("text/html");
PrintWriter pw = response.getWriter();
Connection conn=null;
String url="jdbc:mysql://localhost:3306/";
String dbName="projectmanagement";
String driver="com.mysql.jdbc.Driver";
String dbUsername="root";
String dbPassword="root";
try{
String Projid=" ";
String Projuname=" ";
String Projdesc_emp=" ";
String Projestd_time_alloc=" ";
String Projtask_emp_comment=" ";
String Pid[] = request.getParameterValues("pid");
String Uname[] = request.getParameterValues("uname");
String Pdesc_emp[] = request.getParameterValues("pdesc_emp");
String Pestd_time_alloc[] = request.getParameterValues("pestd_time_alloc");
String Projtask_emp_cmnt[] = request.getParameterValues("ptask_emp_cmnt");
//String Utype = request.getParameter("utype");
for(int i=0; i<100; i++){
Projid=Pid[i]+" "+ 1;
Projuname=Uname[i]+" "+ 1;
Projdesc_emp=Pdesc_emp[i]+" "+ 1;
Projestd_time_alloc=Pestd_time_alloc[i]+" "+ 1;
Projtask_emp_comment=Projtask_emp_cmnt[i]+" "+ 1;
}
Class.forName(driver);
conn = DriverManager.getConnection(url+dbName,dbUsername, dbPassword);
Statement st=conn.createStatement();
int i = st.executeUpdate("insert into projtaskallocate (pid,uname,ptdesc_emp,ptestd_time_alloc,ptask_emp_cmnt) values ('" +Projid+ "','" +Projuname+"','" +Projdesc_emp+ "','" +Projestd_time_alloc+ "','" +Projtask_emp_comment+ "')");
String msg=" ";
if(i!=0){
UserPMDAO dd = new UserPMDAO();
request.setAttribute("ual", dd.getUsers());
ProjectDAO pp = new ProjectDAO();
request.setAttribute("pal", pp.getProjects());
String nextJSP = "/projmgrsuccessful.jsp";
RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(nextJSP);
dispatcher.forward(request,response);
}
else
{
msg="Failed to Add Project";
pw.print("<font size='6' color=blue>" + msg + "</font>");
}
pw.close();
//pst.close();
st.close();
conn.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
}
}
Here the the problem is when I click on submit button only first row is inserted but my requirement is to insert all the rows.
这里的问题是当我点击提交按钮时只插入第一行，但我的要求是插入所有行。
Please help and if I m going wrong please guide me.
请帮忙，如果我出错了，请指导我。
回答by Santhosh
You can use Batch statements to insert multiple rows using jdbc,
您可以使用批处理语句插入多行jdbc，
for (int i=0:i<100;i++) {
String query = "insert into projtaskallocate (pid,uname,ptdesc_emp,ptestd_time_alloc,ptask_emp_cmnt) values ('" +Projid+ "','" +Projuname+"','" +Projdesc_emp+ "','" +Projestd_time_alloc+ "','" +Projtask_emp_comment+ "')";
statement.addBatch(query);
}
statement.executeBatch();
statement.close();
connection.close();
Hope this helps !!
希望这可以帮助 ！！
回答by Narayana Ganesh
The problem is in your for loop. The values read from parameter array are overwritten for each iteration and the variables will have only last element values. Use the below code.
问题出在您的 for 循环中。每次迭代都会覆盖从参数数组中读取的值，并且变量将只有最后一个元素值。使用下面的代码。
conn = DriverManager.getConnection(url+dbName,dbUsername, dbPassword);
String query = "insert into projtaskallocate (pid,uname,ptdesc_emp,ptestd_time_alloc,ptask_emp_cmnt) values (?,?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(query);
for(int i=0; i<100; i++){
Projid=Pid[i]+" "+ 1;
Projuname=Uname[i]+" "+ 1;
--
--
pstmt.setString(1, Projid);
pstmt.setString(2, Projuname);
--
--
pstmt.addBatch();
}
pstmt.executeBatch();
http://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/
http://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/
