如何在PreparedStatement中使用变量进行SQL查询？

邴俊民

2023-03-14

问题内容：

我正在使用Eclipse Java
EE进行Web应用程序项目。当前，我的应用程序返回数据库中存储员工个人信息的所有值。但是，我设置了默认的准备语句以搜索表employee_id =
1234，而不是登录员工的employee_id。这意味着无论哪个员工登录到我的系统，它都只会显示该员工的个人信息。 ID为1234，如下面的准备语句所示：

            PreparedStatement ps = con.prepareStatement("select employeeID,  FirstName, LastName, Admin, DOB, Address, Email, HourlyRate, Gender, ALeaveBalance, SLeaveBalance, ActiveStatus, Role, BSB, BankName, AccNumber, SuperNumber, SuperCompany from payroll_system.employee_info where **employeeID = 1234**");

请注意上面的employeeID如何设置为1234。这意味着我的程序（请参见下面的代码）将仅显示该员工的信息。但是，我希望将employeeID设置为登录者的ID（登录由另一个Servlet管理。如果有人可以帮助我，将非常感谢，谢谢:)

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

public class PersonalInfoOutput extends HttpServlet {

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

            boolean st = false;
            try { 
                Class.forName("com.mysql.jdbc.Driver").newInstance(); 
                Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/payroll_system", "root", ""); 
                **PreparedStatement ps = con.prepareStatement("select employeeID,  FirstName, LastName, Admin, DOB, Address, Email, HourlyRate, Gender, ALeaveBalance, SLeaveBalance, ActiveStatus, Role, BSB, BankName, AccNumber, SuperNumber, SuperCompany from payroll_system.employee_info where employeeID = 1234");** 
                ResultSet rs = ps.executeQuery(); 
                st = rs.next(); 
                if(st){
                boolean adminTrue = rs.getBoolean("Admin"); 
                boolean activeTrue = rs.getBoolean("ActiveStatus");

               out.println("<html>");
               out.println("<head>");
               out.println("<title> Personal Information </title>"); 
               out.println("</head>");
               out.println("<body>");
               out.println("<h1>Personal Information</h1>");
               out.println("<p><b>" + "Employee ID: " + "</b>" + rs.getString("employeeID") + "</p>");
               out.println("<p><b>" + "Name: " + "</b>" + rs.getString("FirstName") + " " + rs.getString("LastName")+ "</p>");
               if(adminTrue) { 
                   out.println("<p><b>"+ "Admin: " + "</b>" +"Yes" + "</p>"); 
               }
               else { 
                   out.println("<p><b>"+ "Admin: " + "</b>" +"No" + "</p>");
               }
               out.println("<p><b>" + "Date of Birth: " +"</b>" + rs.getString("DOB") + "</p>");
               out.println("<p><b>" + "Address: " + "</b>" + rs.getString("Address") + "</p>");
               out.println("<p><b>" + "Email: " + "</b>" + rs.getString("Email") + "</p>");
               out.println("<p><b>" + "Hourly Income: " + "</b>" + "$" + rs.getString("HourlyRate") + "</p>");
               out.println("<p><b>" + "Gender: " + "</b>" + rs.getString("Gender") + "</p>");
               out.println("<p><b>" + "Annual Leave Balance: " + "</b>" + rs.getString("ALeaveBalance") + "</p>");
               out.println("<p><b>" + "Sick Leave Balance: " + "</b>" + rs.getString("SLeaveBalance") + "</p>");
               if(activeTrue) { 
                   out.println("<p><b>"+ "Currently Active: " + "</b>" +"Yes" + "</p>");
               }
               else { 
                   out.println("<p><b>"+ "Currently Active: " + "</b>" +"No" + "</p>" );
               }
               out.println("<p><b>" + "Role: " +"</b>" + rs.getString("Role") + "</p>");
               out.println("<p><b>" + "BSB: " + "</b>" + rs.getString("BSB") + "</p>");
               out.println("<p><b>" + "Bank: " + "</b>" + rs.getString("BankName") + "</p>");
               out.println("<p><b>" + "Bank Account Number: " + "</b>" + rs.getString("AccNumber") + "</p>");
               out.println("<p><b>" + "Superannuation Company: " + "</b>" + rs.getString("SuperCompany") + "</p>");
               out.println("<p><b>" + "Superannuation Number: " + "</b>"+ rs.getString("SuperNumber") + "</p>");
                }
             }catch(Exception e)
              {
                  e.printStackTrace();
              }
            out.close();
    }
}

问题答案：

将employeeID的值从先前的Servlet传递到当前的Servlet。

Replace id:1243 with placeHolder:  ? .

然后设置它的值 ps.setInt(1,"value that you got from previous servlet");

更改为PersonalInfoOutput.java：

HttpSession session = request.getSession(false);

            if(session != null) { 
                String employeeid = (String)session.getAttribute("employeeid"); 
            }

至：

HttpSession session = request.getSession(false);
 String employeeid="";

            if(session != null) { 
                employeeid = (String)session.getAttribute("employeeid"); 
            }

如何在PreparedStatement中使用变量进行SQL查询？

相关阅读

相关文章

相关问答

相关工具

相关文档