Java: Insert multiple rows into MySQL

Question

I want to know how I can insert data bulk at once to MySQL database I don't know how to do it.Insert table data when clicked submit button according to mvc.

farmer id and name came from another table I want to add all data into another table, farmer id and name are readonly

check this pic

milkload jsp page

<form action="MilkloadAdd" method="post"> 
    <table id="example" class="display" width="100%" cellspacing="0">
        <%
            List<Farmer> farmer = (List<Farmer>) 
            request.getAttribute("Farmer_list");
            for (Farmer emp1 : farmer) {
        %>
        <tr>
        <td> <input type="text" name="f_id" value="<%=emp1.getfId()%>" readonly="readonly" required/></td>
        <td> <input type="text" name="fname" value="<%=emp1.getfFname()%>"  readonly="readonly"required/></td>
        <td><input type="text" name="lactometer" value="" required onkeyup="snf();"/></td>
        <td><input type="text" name="fatvalue" value="" required onkeyup="snf();"/></td>
        <td><input type="text" name="weights" value=""required/> </td>
        <td><input type="text" name="snf" value=""required readonly="readonly"/></td>
        </tr>
<% } %>

milkload model class

    public void setLactometer(String lactometer) {this.lactometer = lactometer;}
    public String getFatvalue() {return fatvalue;}
    public void setFatvalue(String fatvalue) {this.fatvalue = fatvalue;}
    public String getWeights() {return weights;}
    public void setWeights(String weights) {this.weights = weights;}
    public String getDate() {return date;}
    private String date;
    private String cpid;
    private String milkindex;
    private String snf;
}

MilkLoadDAO Class

  package com.nestle.db;

    public class MilkloadDAO {
   private static final String SQL_INSERT_MILKLOAD="INSERT INTO 
  milk_load(Date,Cp_Id,F_Id,Fat,Weight,Lactormeter) VALUES (?,?,?,?,?,?)";

   public static void insertMilkLoad(Milkload milkload) {

}

You want to add farmerid and name along with the other details in the form? — Supun Amarasinghe, Sep 29 '17 at 05:44

score 3 · Answer 1 · answered Sep 29 '17 at 09:21

You can use this way to get values of multiple rows.

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

                MilkLoad milkload;
                List<MilkLoad> listMilkLoad= new ArrayList<MilkLoad>();


                String[] fid  = request.getParameterValues("f_id");
                String[] lactometer  = request.getParameterValues("lactometer");
                String[] weights = request.getParameterValues("weights");
                String[] date = request.getParameterValues("Date");
                //Set other values like this as well.


                for(int x=0;x<fid.length;x++){

                    milkload = new MilkLoad();
                    milkload.setFatvalue(fid[x]);
                    milkload.setLactometer((lactometer[x]));
                    //Set other values like this as well.

                    //Adding the object to a list
                    listMilkLoad.add(milkload);
                }

             MilkloadDAO.insertMilkLoad(listMilkLoad);

    }

Your MilkLoadDAO Class's method should be changed like this.

    public static void insertMilkLoad(List<MilkLoad> listMildLoad){

    }

And your MilkLoadDAO class would be like this.

    public class MilkloadDAO {
               private static final String SQL_INSERT_MILKLOAD="INSERT INTO 
              milk_load(Date,Cp_Id,F_Id,Fat,Weight,Lactormeter) VALUES (?,?,?,?,?,?)";

               public static void insertMilkLoad(List<listMilkLoad> listMildLoad) {

                   try (Connection connection = DbConnect.getConnection()) {
                    PreparedStatement statement = connection.prepareStatement(SQL_INSERT_MILKLOAD);

                    for(MilkLoad milkload:listMildLoad){
                         statement.setString(1, milkload.getDate());
                         statement.setString(2, milkload.getCpid());
                         statement.setString(3, milkload.getFid());
                         statement.setString(4, milkload.getFatvalue());
                         statement.setString(5, milkload.getWeights());
                         statement.setString(6, milkload.getLactometer());
                         System.out.println(SQL_INSERT_MILKLOAD);
                         statement.execute();
                    }
                } catch (Exception e) {
                    System.out.println("MILKLOAD DAO error"+ e.getMessage());
                   }
               }
            }

You may need slight adjustments based on your variables but I think the idea is clear.

Hope it helps :)

Then no need to set in the milkload object. Just get the parameter from the jsp form to a variable and set it directly in the sql query to save it with all the users.Your sql query does not require a change but how you handle the variable in the servlet needs to be changed. — Supun Amarasinghe, Sep 30 '17 at 06:59

Java: Insert multiple rows into MySQL

1 Answers1