如何将CSV文件上传到JSP页面并将其中的数据发送到MySQL数据库？

huangapple
go评论112阅读模式
英文:

How to upload a csv to a JSP page and send the data in it to a MySQL database?

问题

  1. 我有一个 JSP 页面，用户可以使用 `<input type="file">` 上传 CSV 文件。我希望该 CSV 文件的内容能够存储到数据库中。我花了好几个小时搜索，只找到了关于在 PHP 中处理的方法，但我对 PHP 没有任何经验，因此非常感谢提供 Java 方面的任何帮助。
  3. 我的 JSP 页面：
  5.     <span>上传 CSV 文件：<input type="file"></span>
  6.     
  7.     <table style="margin-left: 20px">
  8.         <tr>
  9.             <th class="table-custom">员工姓名</th>
  10.             <th class="table-custom">上班时间</th>
  11.             <th class="table-custom">下班时间</th>
  12.         </tr>
  13.     </table>
英文:

I have a JSP where a user can upload a csv using input type=&quot;file&quot;. I want the content in that csv to go to a database. I searched for hours and only found methods in php for this, but I have zero experience in php, so any help in java is appreciated.

My JSP:

  1. &lt;span&gt;Upload CSV : &lt;input type=&quot;file&quot;&gt;&lt;/span&gt;
  3. &lt;table style=&quot;margin-left: 20px&quot;&gt;
  4.                             &lt;tr&gt;
  5.                                 &lt;th class=&quot;table-custom&quot;&gt;Employee Name&lt;/th&gt;
  6.                                 &lt;th class=&quot;table-custom&quot;&gt;In Time&lt;/th&gt;
  7.                                 &lt;th class=&quot;table-custom&quot;&gt;Out Time&lt;/th&gt;
  8.                                 
  9.                             &lt;/tr&gt;
  10. &lt;/table&gt;

答案1

得分: 1

尝试这段代码：

  1. <!-- 开始代码段: js 隐藏: false 控制台: true babel: false -->
  3. <!-- 语言: lang-js -->
  5. document.getElementById("upload").addEventListener("change", upload, false);
  6. var out = "";
  8. function upload(e) {
  9.   document.getElementById('csvForm').innerHTML = "";
  11.   var data = null;
  12.   var file = e.target.files[0];
  14.   var reader = new FileReader();
  15.   reader.readAsText(file);
  16.   reader.onload = function(event) {
  17.     var csvData = event.target.result;
  18.     var parsedCSV = d3.csv.parseRows(csvData);
  20.     parsedCSV.forEach(function(d, i) {
  21.       if (i == 0) return true; // 跳过标题
  22.       if (d.constructor === Array) {
  23.         createForm(d);
  24.       }
  25.     });
  27.   }
  28. }
  30. function createForm(csv) {
  32.   out += '<input value="' + csv[0] + '" name="date" type="hidden">';
  33.   out += '<input value="' + csv[1] + '" name="name" type="hidden">';
  34.   out += '<input value="' + csv[2] + '" name="in-time" type="hidden">';
  35.   out += '<input value="' + csv[3] + '" name="out-time" type="hidden">';
  37.   document.getElementById('csvForm').innerHTML = out;
  38.   document.getElementById('csvForm').setAttribute("formmethod", "post");
  39.   document.getElementById('csvForm').setAttribute("formaction", "testBrain.jsp");
  40.   out += '<br>';
  41. }
  43. <!-- 语言: lang-html -->
  45. <input id="upload" type="file">
  46. <form id="csvForm"></form>
  47. <input type="submit" value="submit" form="csvForm" formmethod="post" formaction="testBrain.jsp">
  48. <script src="https://d3js.org/d3.v3.js"></script>
  50. <!-- 结束代码段 -->

testBrain.jsp

  1. <%@ page contentType="text/html;charset=UTF-8" language="java" %>
  2. <%@page import="java.sql.*,java.util.*"%>
  4. <html>
  5. <head>
  6.     <meta http-equiv="Refresh" content="5;url=test.jsp">
  7. </head>
  8. </html>
  9. <%
  10.     String[] date=request.getParameterValues("date");
  11.     String[] name=request.getParameterValues("name");
  12.     String[] inTime=request.getParameterValues("in-time");
  13.     String[] outTime=request.getParameterValues("out-time");
  15.     try
  16.     {
  17.         Class.forName("com.mysql.jdbc.Driver");
  18.         Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testDb", "root", "1234");
  19.         Statement st=conn.createStatement();
  21.         for(int x=0; x<name.length;x++) {
  22.             int i = st.executeUpdate("insert into csv(date,name,`in`,`out`)values('"
  23.                                    + date[x] + "','" + name[x] + "','" + inTime[x] + "','" + outTime[x] + "')");
  24.         }
  25.         out.println("Data is successfully inserted! Redirecting.. Please wait");
  27.        // String redirectURL = "test.jsp";
  28.        // response.sendRedirect(redirectURL);
  30.     }
  31.     catch(Exception e)
  32.     {
  33.         System.out.print(e);
  34.         e.printStackTrace();
  35.         out.println("Error");
  36.     }
  37. %>

我使用了这个问题并做了一些修改使其能够工作。您可以通过在 createForm JS 中移除 &quot;type = hidden&quot; 来在提交前查看内容。

英文:

Try this code

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-js -->

  1. document.getElementById(&quot;upload&quot;).addEventListener(&quot;change&quot;, upload, false);
  2. var out = &quot;&quot;;
  4. function upload(e) {
  5.   document.getElementById(&#39;csvForm&#39;).innerHTML = &quot;&quot;;
  7.   var data = null;
  8.   var file = e.target.files[0];
  10.   var reader = new FileReader();
  11.   reader.readAsText(file);
  12.   reader.onload = function(event) {
  13.     var csvData = event.target.result;
  14.     var parsedCSV = d3.csv.parseRows(csvData);
  16.     parsedCSV.forEach(function(d, i) {
  17.       if (i == 0) return true; // skip the header
  18.       if (d.constructor === Array) {
  19.         createForm(d);
  20.       }
  21.     });
  23.   }
  24. }
  26. function createForm(csv) {
  28.   out += &#39;&lt;input value=&quot;&#39; + csv[0] + &#39;&quot; name=&quot;date&quot; type=&quot;hidden&quot;&gt;&#39;;
  29.   out += &#39;&lt;input value=&quot;&#39; + csv[1] + &#39;&quot; name=&quot;name&quot; type=&quot;hidden&quot;&gt;&#39;;
  30.   out += &#39;&lt;input value=&quot;&#39; + csv[2] + &#39;&quot; name=&quot;in-time&quot; type=&quot;hidden&quot;&gt;&#39;;
  31.   out += &#39;&lt;input value=&quot;&#39; + csv[3] + &#39;&quot; name=&quot;out-time&quot; type=&quot;hidden&quot;&gt;&#39;;
  33.   document.getElementById(&#39;csvForm&#39;).innerHTML = out;
  34.   document.getElementById(&#39;csvForm&#39;).setAttribute(&quot;formmethod&quot;, &quot;post&quot;);
  35.   document.getElementById(&#39;csvForm&#39;).setAttribute(&quot;formaction&quot;, &quot;testBrain.jsp&quot;);
  36.   out += &#39;&lt;br&gt;&#39;;
  37. }

<!-- language: lang-html -->

  1. &lt;input id=&quot;upload&quot; type=&quot;file&quot;&gt;
  3. &lt;form id=&quot;csvForm&quot;&gt;&lt;/form&gt;
  4. &lt;input type=&quot;submit&quot; value=&quot;submit&quot; form=&quot;csvForm&quot; formmethod=&quot;post&quot; formaction=&quot;testBrain.jsp&quot;&gt;
  6. &lt;script src=&quot;https://d3js.org/d3.v3.js&quot;&gt;&lt;/script&gt;

<!-- end snippet -->

testBrain.jsp

  1. &lt;%@ page contentType=&quot;text/html;charset=UTF-8&quot; language=&quot;java&quot; %&gt;
  2. &lt;%@page import=&quot;java.sql.*,java.util.*&quot;%&gt;
  4. &lt;html&gt;
  5. &lt;head&gt;
  6.     &lt;meta http-equiv=&quot;Refresh&quot; content=&quot;5;url=test.jsp&quot;&gt;
  7. &lt;/head&gt;
  8. &lt;/html&gt;
  9. &lt;%
  10.     String[] date=request.getParameterValues(&quot;date&quot;);
  11.     String[] name=request.getParameterValues(&quot;name&quot;);
  12.     String[] inTime=request.getParameterValues(&quot;in-time&quot;);
  13.     String[] outTime=request.getParameterValues(&quot;out-time&quot;);
  15.     try
  16.     {
  17.         Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
  18.         Connection conn = DriverManager.getConnection(&quot;jdbc:mysql://localhost:3306/testDb&quot;, &quot;root&quot;, &quot;1234&quot;);
  19.         Statement st=conn.createStatement();
  21.         for(int x=0; x&lt;name.length;x++) {
  22.             int i = st.executeUpdate(&quot;insert into csv(date,name,`in`,`out`)values(&#39;&quot; + date[x] + &quot;&#39;,&#39;&quot; + name[x] + &quot;&#39;,&#39;&quot; + inTime[x] + &quot;&#39;,&#39;&quot; + outTime[x] + &quot;&#39;)&quot;);
  23.         }
  24.         out.println(&quot;Data is successfully inserted! Redirecting.. Please wait&quot;);
  26.        // String redirectURL = &quot;test.jsp&quot;;
  27.        // response.sendRedirect(redirectURL);
  29.     }
  30.     catch(Exception e)
  31.     {
  32.         System.out.print(e);
  33.         e.printStackTrace();
  34.         out.println(&quot;Error&quot;);
  35.     }
  36. %&gt;

I used this question and changed some stuff for it to work.
You can view the content before submitting it by removing the &quot;type = hidden&quot; in the createForm JS.

huangapple
  • 本文由 发表于 2020年5月5日 20:35:40
  • 转载请务必保留本文链接：https://go.coder-hub.com/61613293.html
  • java
  • jsp
  • mysql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定