java - 向 MySQL 数据库中插入数据

Question

我有如下的 html 表单。我想将数据添加到数据库表 itemspoinfo。我想按如下方式完成该功能；

• 如果给出的第一个药物名称和数量将其添加到数据库中。

• 如果给出的前 2 个药物名称和数量将其添加到数据库中。(作为 2 个单独的记录以及 POno 和日期)

• 因为这最多 5 条记录必须能够记录到数据库中

我找不到解决方案，请帮助我。

到目前为止我做了什么；

   <%
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection(CONN_STRING, USERNAME, PASSWORD);

    if(request.getParameter("send")!=null){
        String scom=request.getParameter("scompany");
        String porderno=request.getParameter("pono");
        String bdate=request.getParameter("date");

        String drug1=request.getParameter("d1");
        String qty1=request.getParameter("q1");
        String drug2=request.getParameter("d2");
        String qty2=request.getParameter("q2");
        String drug3=request.getParameter("d3");
        String qty3=request.getParameter("q3");
        String drug4=request.getParameter("d4");
        String qty4=request.getParameter("q4");
        String drug5=request.getParameter("d5");
        String qty5=request.getParameter("q5");


        //getting todaydate
        Date date = new Date();
        Timestamp timestamp = new Timestamp(date.getTime());

        String sql = "INSERT INTO purchaseorderinfo SET Supplier ='"+scom+"', PONo='"+porderno+"', ExpectedDate='"+bdate+"', PODate='"+timestamp+"' ";
        pst=conn.prepareStatement(sql);



        if((scom!=null && scom.length()>0)
            && (porderno!=null && porderno.length()>0)
            && (bdate!=null && bdate.length()>0)
            && (drug1!=null && drug1.length()>0)
            && (qty1!=null && qty1.length()>0)){


            pst.execute();

            String sql2="INSERT INTO itemspoinfo SET PODate=?, PONo=?, ItemName=?, Qty=?,";
            pst2=conn.prepareStatement(sql2);
            pst2.setTimestamp(1, timestamp);
            pst2.setString(2, porderno);



          %>
          <script language="javascript">
                alert("Sucessfuly Sent to "+scom);
          </script>
          <%    
        }
    }
%>

Answer 1

您可以使用 addBatch() 和 executeBatch()。

试试下面的代码:

String sql2="INSERT INTO itemspoinfo (PODate, PONo, ItemName,Qty) VALUES(?,?,?,?)";
        pst2=conn.prepareStatement(sql2);
        // to check drugs details are added or not.
        int noofdrugs = 0;
        if (drug1!=null && drug1.length()>0)
            && (qty1!=null && qty1.length()>0)) {
            pst2.setTimestamp(1, timestamp);
            pst2.setString(2, porderno);
            pst2.setString(3, drug1);
            pst2.setInt(4, Integer.parseInt(qty1));
            pst2.addBatch();

            noofdrugs++;
        }

        if (drug2!=null && drug2.length()>0)
            && (qty2!=null && qty2.length()>0))
        {
            pst2.setTimestamp(1, timestamp);
            pst2.setString(2, porderno);
            pst2.setString(3, drug2);
            pst2.setInt(4, Integer.parseInt(qty2));
            pst2.addBatch();

            noofdrugs++;
        }

        if (drug3!=null && drug3.length()>0)
            && (qty3!=null && qty3.length()>0))
        {
            pst2.setTimestamp(1, timestamp);
            pst2.setString(2, porderno);
            pst2.setString(3, drug3);
            pst2.setInt(4, Integer.parseInt(qty3));
            pst2.addBatch();

            noofdrugs++;
        }

        if (drug4!=null && drug4.length()>0)
            && (qty4!=null && qty4.length()>0))
        {
            pst2.setTimestamp(1, timestamp);
            pst2.setString(2, porderno);
            pst2.setString(3, drug4);
            pst2.setInt(4, Integer.parseInt(qty4));
            pst2.addBatch();

            noofdrugs++;
        }

        if (drug5!=null && drug5.length()>0)
            && (qty5!=null && qty5.length()>0))
        {
            pst2.setTimestamp(1, timestamp);
            pst2.setString(2, porderno);
            pst2.setString(3, drug5);
            pst2.setInt(4, Integer.parseInt(qty5));
            pst2.addBatch();

            noofdrugs++;
        }

        if (noofdrugs>0) {
            pstmt.executeBatch();
        }