个人中心
gpt4 book ai didi

java - 如何从sql表中扣除一个值？

转载 作者：行者123 更新时间：2023-11-29 20:54:06 24 4
gpt4 key购买 nike

下面，我有一个 html 页面，用户在其中将值输入到字段中。然后，这些字段进入一个 servlet，该 servlet 将这些值插入到数据库中，其中该表称为“monthly_timesheet”。正如您所看到的，html 中的两个输入字段要求用户输入“Annual 请假”和“病假”。我有另一个表“leave_remaining”(下面未显示)，其中包含名为“剩余病假”和“剩余年假”的字段。我想要的是，当执行下面的 servlet 时，例如用户在“年假”字段中输入“5”。我想要另一个表“leave_remaining”扣除5个年假并更新该表。感谢帮助，谢谢!

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);
          }

    }

最佳答案

您在这里看到的本质上是一个事务，您在其中插入一个表并更新另一个表，所以这就是您的操作方法，添加一个新方法，例如接受参数的 insertUpdateLeaves() ，所以它会是类似的东西

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();






);

}

关于java - 如何从sql表中扣除一个值？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/37771580/

24 4 0
文章推荐： PHP 检查 MySQL 数据库日期时间
文章推荐： javascript - 在 React.js 中实现输入标签的自动保存
文章推荐： php - 使用 PDO 不区分重音的 mysql 搜索
文章推荐： mysql - 相关名称在内部查询中如何工作？
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com