How to handle multiple request in Java servlet

Question

I have a UI where there is HTML table with input fields as cells and some input field outside the HTML table, so there I am entering data and on click of save saving data into my data base using servlets

It is working fine when only one user is using it at a time but causing issue when two user clicks save at same time

What I am doing is:

• After Clicking on save I am calling my servlet class where I have written a code to insert the data into DB
• So in my servlet class firstly I am running a query to get max+1 no. of a column from db and then saving it and using that value further in my insert query

Issue I am facing is:

• When two user clicks save on same time, the query which is to get max+1 runs and the next query which is to insert data runs but the second one throws error as java.sql.BatchUpdateException: Duplicate entry '2' for key 'PRIMARY' because that one is primary key column in db and it can't be duplicate

• but why it is taking only 2 like only one no when two user has clicked save button it should take 2,3 I think Here I am not handling the multiple request in my servlet which I don't even know how to handle, I have googled a lot and read there that servlets it self manages the multiple processing at one time

• So my issue is I want to insert data into my db when multiple user clicks save button at single time, if there are two user clicking save at same time it is causing issue

My code

int grnNo;
Connection con = null;
Statement statement = null;

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

}

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

    String[] itemCode = request.getParameterValues("itemCodetd");
    String[] unitCode = request.getParameterValues("unittd");

    try {
        con = DBConnection.createConnection();
        statement = con.createStatement();

        String grnNoSql = "select max(GRNNUMBER)+1 as GRNNo from egoodsreceived";
        ResultSet resultSet1 = statement.executeQuery(grnNoSql);

        while (resultSet1.next()) {
            int grnNoLocal = resultSet1.getInt("GRNNo");

            if (grnNo != 0) {
                grnNo = grnNoLocal;
                System.out.println("in if  :" + grnNo);
            } else {
                grnNo = 1;
                System.out.println("in else  :" + grnNo);
            }

        }

        String query1 = "query to insert data";

        PreparedStatement ps = con.prepareStatement(query1);

        for (int i = 0; i < itemCode.length; i++) {
            if (itemCode[i] != "") {
                System.out.println("in for :" + grnNo);
                ps.setInt(1, grnNo);
                ps.setString(2, itemCode[i]);
                ps.setString(3, unitCode[i]);

                ps.addBatch();
            }
        }

        ps.executeBatch(); // here getting erro as duplicate primary key

    } catch (SQLException e) {
        System.out.println("SQL EXCPTION   91");
        e.printStackTrace();
    }

    doGet(request, response);
}

Answer 1

The problem is that you are using instance variables in servlets. There is only a single instance of each servlet in the JVM, so concurrent requests will attempt to use the same variable at the same time.

Refactor grnNo, con and statement to local variables, then wrap the select and insert into a single transaction.

Answer 2

You should define the column to use MySQL's auto increment functionality:

https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

CREATE TABLE X(
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (id)
);

You need not then specify the column or its value in the insert statement: the value will then be automatically assigned by the database.

The other answer makes a valid point about thread safety with instance variables in Servlets however, without adding a synchronized block, making grnNo a local variable does not address the issue:

//Thread T1 executes the below and get the next val
//before T1 has written its changes, T2 executes the below and gets the next val
String grnNoSql = "select max(GRNNUMBER)+1 as GRNNo from egoodsreceived";

If you need the generated identifier in the application after the insert then you can use the getGeneratedKeys() method of java.sql.statement:

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys()