How to update database only for values entered by the user?

Question

The HTML page and Java Servlet below are responsible for updating employee details into database. According to the HTML page, the fields to be updated are optional, however, the empty fields will also be updated into the database as empty strings.

For example, if I only want to change the employee's hourly pay rate, I will leave everything else blank, therefore those blank values will also be updated into the database as empty strings instead of remaining at their previous values.

What is the proper way of supporting optional update fields?

UpdateEmployee.html

<div id="container">
<h1>Update Employee Details</h1>
<h3>(All update fields are optional)</h3>
<form action ="UpdateEmployeeAccount" method = "post"> 

    <table border ="1"> 

    <tr>
    <td>Employee ID (of employee to be updated)</td>
    <td><input type = "text" name = "update_id"  pattern="[0-9]{4}" title="4 digit number: e.g. 1234" maxlength="4" required></td>
    </tr>

    <tr>
    <td>New Admin Status</td>
    <td>
    <input type = "radio" name = "NewAdminStatus" value ="1">Yes
    <input type = "radio" name = "NewAdminStatus" value = "0" >No
    </td>
    </tr>

    <tr>
    <td>New Residential Address</td>
    <td><input type = "text" name = "newAddress">
    </tr>

    <tr>
    <td>Revised Hourly Pay Rate ($)</td>
    <td><input type = "number" name = "NewPay" min="0" step="0.01">
    </tr>

    <tr>
    <td>New Role:</td>
    <td><input type = "text" name = "NewRole" >
    </tr>

    <tr>
    <td>New BSB Number</td>
    <td><input type = "text" name = "NewBSB"  pattern="[0-9]{6}" title="6-digit BSB number e.g. 123456" maxlength="6" >
    </tr>

    <tr>
    <td>New Bank (if employee has changed bank)</td>
    <td><input type = "text" name = "NewBank">
    </tr>

    <tr>
    <td>New Bank Account Number</td>
    <td><input type = "text" name = "NewBAN"  pattern="[0-9]{12}" title="Please enter a valid Bank Account Number" maxlength="12">
    </tr>

    <tr>
    <td>New Superannuation Company</td>
    <td><input type = "text" name = "NewSAC"> 
    </tr>

    <tr>
    <td>New Superannuation Number</td>
    <td><input type = "text" name = "NewSAN"> 
    </tr>

    </table>
    <br>
    <br>
    <input type = "submit" value = "Update Employee Details">
    </form>.
    </div>
</body>

</html>

UpdateEmployeeAccount.java (Servlet)

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class UpdateEmployeeAccount extends HttpServlet { 

    protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter(); 

        String update_id = request.getParameter("update_id");
        String NewAdminStatus = request.getParameter("NewAdminStatus");
        String newAddress = request.getParameter("newAddress");
        String NewPay = request.getParameter("NewPay");
        String NewRole = request.getParameter("NewRole");
        String NewBSB = request.getParameter("NewBSB");
        String NewBank = request.getParameter("NewBank");
        String NewBAN = request.getParameter("NewBAN"); 
        String NewSAC = request.getParameter("NewSAC"); 
        String NewSAN = request.getParameter("NewSAN"); 

        try { 

            Class.forName("com.mysql.jdbc.Driver").newInstance(); 
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/payroll_system", "root", ""); 
            PreparedStatement ps = con.prepareStatement("UPDATE payroll_system.employee_info SET Admin=?, Address=?, HourlyRate=?, Role=?, BSB=?, BankName=?, Accnumber=?, SuperNumber=?, SuperCompany=? WHERE employeeID=?");
            ps.setString(1, NewAdminStatus);
            ps.setString(2, newAddress);
            ps.setString(3, NewPay); 
            ps.setString(4, NewRole);
            ps.setString(5, NewBSB);
            ps.setString(6, NewBank);
            ps.setString(7, NewBAN);
            ps.setString(8, NewSAN);
            ps.setString(9, NewSAC);
            ps.setString(10, update_id);

            int i = ps.executeUpdate(); 

            if(i>0) { 
                PreparedStatement pd = con.prepareStatement("UPDATE payroll_system.employee_login SET Admin=? WHERE employeeID=?");
                pd.setString(1, NewAdminStatus);
                pd.setString(2, update_id);
                pd.executeUpdate(); 
                out.println("Employee Details Successfully Updated");
                RequestDispatcher rs = request.getRequestDispatcher("UpdateEmployee.html"); 
                rs.include(request, response);
            }

         }catch(Exception e)
        {
         e.printStackTrace();
         out.println("Failed to update details");
         RequestDispatcher rs = request.getRequestDispatcher("UpdateEmployee.html"); 
         rs.include(request, response);
        }
      out.close();
}

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
               processRequest(request, response);
          }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
               processRequest(request, response);
          }

    }

Answer 1

You can set empty paramters to NULL in Java:

if(NewAdminStatus.isEmpty()) ps.setNull(1, Types.VARCHAR) else ps.setString(1, NewAdminStatus);
if(newAddress.isEmpty()    ) ps.setNull(2, Types.VARCHAR) else ps.setString(2, newAddress    );
if(NewPay.isEmpty()        ) ps.setNull(3, Types.VARCHAR) else ps.setString(3, NewPay        );
if(NewRole.isEmpty()       ) ps.setNull(4, Types.VARCHAR) else ps.setString(4, NewRole       );
if(NewBSB.isEmpty()        ) ps.setNull(5, Types.VARCHAR) else ps.setString(5, NewBSB        );
if(NewBank.isEmpty()       ) ps.setNull(6, Types.VARCHAR) else ps.setString(6, NewBank       );
if(NewBAN.isEmpty()        ) ps.setNull(7, Types.VARCHAR) else ps.setString(7, NewBAN        );
if(NewSAN.isEmpty()        ) ps.setNull(8, Types.VARCHAR) else ps.setString(8, NewSAN        );
if(NewSAC.isEmpty()        ) ps.setNull(9, Types.VARCHAR) else ps.setString(9, NewSAC        );

... and handle NULLs in SQL like that:

UPDATE payroll_system.employee_info
SET Admin        = COALESCE(?, Admin       ),
    Address      = COALESCE(?, Address     ),
    HourlyRate   = COALESCE(?, HourlyRate  ),
    Role         = COALESCE(?, Role        ),
    BSB          = COALESCE(?, BSB         ),
    BankName     = COALESCE(?, BankName    ),
    Accnumber    = COALESCE(?, Accnumber   ),
    SuperNumber  = COALESCE(?, SuperNumber ),
    SuperCompany = COALESCE(?, SuperCompany)
WHERE employeeID = ?