Saving multiple HTML row values to MySQL using JSP

Question

On clicking <button> btnSave on html page, saveRecords.jsp is invoked to save the multiple rows of HTML. The Code in saveRecords.jsp is as follows.

try{
        String[] table_id = request.getParameterValues("table_id");   
        String[] hall_name = request.getParameterValues("hall_name");
        String[] hall_capacity = request.getParameterValues("hall_capacity");   
        Class.forName("com.mysql.jdbc.Driver");  // MySQL database connection
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/chbs?" + "user=root&password=");    

        PreparedStatement pst = conn.prepareStatement("INSERT INTO `halls`(`Hall_ID`, `Hall_Name`, `Capacity`) VALUES (?, ?, ?);");
        pst.setString(1, table_id);
        pst.setString(2, hall_name);
        pst.setString(3, hall_capacity);
        int rset = pst.executeUpdate();       
    }

However, PreparedStatement.setString() doesn't accept Array. Since, the HTML table is in one .jsp and the code to save the values of HTML rows in in saveRecords.jsp which is different, the HTML table values are not getting posted and the PreparedStatement.setString() is throwing error. What is the correct way to do this?

Ori Marko · Accepted Answer · 2018-01-28 13:25:34Z

1

You need to update in batch. assuming the values are validated and the arrays length are equal you can addBatch each insert and executeBatch at the end;

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/chbs?" + "user=root&password=");    
String sql = "INSERT INTO `halls`(`Hall_ID`, `Hall_Name`, `Capacity`) VALUES (?, ?, ?);";
PreparedStatement ps = conn.prepareStatement(sql);
for (int i=0; i< table_id .length;i++) {
   ps.setString(1, table_id [i]);
   ps.setString(2, hall_name [i]);
   ps.setString(3, hall_capacity [i]);

    ps.addBatch();
}
ps.executeBatch();

edited Jan 28, 2018 at 13:25

answered Jan 28, 2018 at 12:56

Ori Marko

59.3k26 gold badges157 silver badges259 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Raky Over a year ago

Thanks for Prompt response. But now the problem is that the values of HTML Table id="table_id"> and rest are not getting posted for trapping at saveRecords.jsp. Can't I use <td> with ID to post values? In js it is working fine.

Raky Over a year ago

Also, in your this PreparedStatement ps = connection.prepareStatement(sql); connection becomes conn and in statement.executeBatch(); the statement becomes ps. Am I correct sir?

Raky Over a year ago

Sir, About the first comment... please advise

Ori Marko Over a year ago

It's a different question

Debasish Ghosh · Accepted Answer · 2018-01-31 19:56:56Z

try{
        String[] table_id = request.getParameterValues("table_id");   
        String[] hall_name = request.getParameterValues("hall_name");
        String[] hall_capacity = request.getParameterValues("hall_capacity");   
        Class.forName("com.mysql.jdbc.Driver");  // MySQL database connection
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/chbs?" + "user=root&password=");    

        PreparedStatement pst = conn.prepareStatement("INSERT INTO `halls`(`Hall_ID`, `Hall_Name`, `Capacity`) VALUES (?, ?, ?);");
for(int i=0;i<table_id.lenght;i++){
        pst.setString(1, table_id[i]);
        pst.setString(2, hall_name[i]);
        pst.setString(3, hall_capacity[i]);
        int rset = pst.executeUpdate();       
    }
}

It will also work for you.

Collectives™ on Stack Overflow

Saving multiple HTML row values to MySQL using JSP

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related