javaprogrammer javaprogrammer - 5 months ago 8
Java Question

How to deduct a value from sql table?

Below, I have a html page where a user inputs values into fields. These fields then go to a servlet which inserts these values into the database - where the table is called "monthly_timesheet". As you can see, two of the input fields in the html require the user to enter "Annual leave taken" and "Sick leave taken." I have another table "leave_remaining" (not shown below) which contains fields called "Sick leave remaining" and "Annual leave remaining." What I want is, when the below servlet is executed, say for example the user enters "5" in the field which says "Annual leave taken". I want the other table "leave_remaining" to deduct 5 annual leaves and update that table. Help appreciated, thanks!

TimeSheet.html

<!DOCTYPE html>
<html>
<head>
<meta charset = "UTF-8">
<link rel = "stylesheet" type = "text/css" href = "main.css">
<link rel = "stylesheet" type = "text/css" href = "sidebar.css">
<title>Maintain Time Sheet</title>
<style>
table { border-collapse: collapse; width: 50%; } th, td { text-align: left; padding: 8px; } tr:nth-child(even){background-color: #f2f2f2}
tr:hover {background-color: #e2f4ff;}
</style>
</head>

<body>

<ul>

<li><a href="PersonalInfoOutput">View Personal Information</a></li>
<li><a href="ExpenseClaim.html">View Expense Claims</a></li>
<li><a href=asdasd>View Payslips</a></li>
<li><a href="changePassAdmin.html">Change Password</a></li>
<li><a href="manageEmployee.html">Maintain Employee Information</a></li>
<li><a href=asdasdasd>Maintain Tax Information</a></li>
<li><a href=asdasd>Maintain Payroll Items</a></li>
<li><a class=active>Maintain Timesheet</a></li>
<li><a href=asda>Maintain Employee Expenses</a></li>
<li><a href=asda>Run Payroll</a></li>
<li><a href=asdasds>Generate Reports</a></li>

</ul>

<div style=margin-left:25%;padding:1px;>
</div>

<div id="container">
<h1>Maintain Time Sheet</h1>
<form action ="TimeSheetDB" method = "post">

<table border ="1">

<tr>
<td>Employee ID: </td>
<td><input type = "text" name = "employee_id" pattern="[0-9]{4}" title="4 digit number: e.g. 1234" maxlength="4" required></td>
</tr>

<tr>
<td>Hours Worked: </td>
<td><input type = "number" name = "HoursWorked" min="0" step="0.01" required></td>
</tr>

<tr>
<td>Date: </td>
<td><input type = "date" name = "date" required></td>
</tr>

<tr>
<td>Annual Leaves Taken: </td>
<td><input type = "number" min="0" name = "ALeave" required></td>
</tr>

<tr>
<td>Sick Leaves Taken: </td>
<td><input type = "number" min="0" name = "SLeave" required></td>
</tr>

</table>
<br>
<br>
<input type = "submit" value = "Submit">
</form>
</div>
</body>

</html>


TimeSheetDB.java

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

public class TimeSheetDB extends HttpServlet {

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

String employee_id = request.getParameter("employee_id");
String hours = request.getParameter("HoursWorked");
String date = request.getParameter("date");
String ALeave = request.getParameter("ALeave");
String SLeave = request.getParameter("SLeave");

try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/payroll_system", "root", "");
PreparedStatement ps = con.prepareStatement("INSERT INTO payroll_system.monthly_timesheet(employeeID, Date, Hours, AnnualLeave, SickLeave) values (?,?,?,?,?)");
ps.setString(1, employee_id);
ps.setString(2, date);
ps.setString(3, hours);
ps.setString(4, ALeave);
ps.setString(5, SLeave);


int count = ps.executeUpdate();

if(count>0) {
***//this is where I want the other table to deduct the annual/sick leave balance from its columns "sick leave remaining" and annual leave remaining"//***
out.println("Details Successfully Submitted");
RequestDispatcher rs = request.getRequestDispatcher("TimeSheet.html");
rs.include(request, response);
}

}catch(Exception e)
{
e.printStackTrace();
out.println("Failed to submit details");
RequestDispatcher rs = request.getRequestDispatcher("TimeSheet.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

What you are looking at here is essentially a transaction, where you insert one table and update another one, so this is how you do it, add a new Method, say insertUpdateLeaves() that takes the parameters, So it would be something like

public int insertUpdateLeaves(String employeeId,String date,String hours,String aLeave, String sLeave)
{
  Class.forName("com.mysql.jdbc.Driver").newInstance(); 
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/payroll_system", "root", ""); 
  con.setAutoCommit(false);
//When you set the autoCommit to false, it means no SQL statement is committed, unless you call it explicitly.



// Make two Prepared Statements one for Insert and another for Update
            PreparedStatement ps = con.prepareStatement("INSERT INTO payroll_system.monthly_timesheet(employeeID, Date, Hours, AnnualLeave, SickLeave) values (?,?,?,?,?)");
     String updateLeaveSQL=""; //Your Update query would be here
            PreparedStatement ps1 = con.prepareStatement(updateLeaveSQL);

//First run your insert statement
 ps.setString(1, employee_id);
            ps.setString(2, date);
            ps.setString(3, hours);
            ps.setString(4, ALeave);
            ps.setString(5, SLeave);
            int count = ps.executeUpdate(); 

//Since you need the existing Annual Leaves in your Leave_Remaining table, use a select Query to get the existing ones

int existingAnnualLeaves=<Value you get from Leave_Remaining Table>;

//Now update with existing Leaves
 existingAnnualLeaves=existingAnnualLeaves-ALeave;

//Run your update Query setting existingAnnualLeaves with this value
//Once you are finished with both insert and update call autoCommit.
            con.commit();






);

}
Comments