如何在预备语句中使用变量进行sql查询？ [英] How to use a variable in preparedstatement for sql query?
问题描述
我正在eclipse Java EE中做一个web应用程序项目。目前，我的应用程序返回存储员工个人信息的数据库中的所有值。但是，我设置了默认的预准备语句来搜索employee_id = 1234的表，而不是登录员工的employee_id。这意味着无论哪个员工登录我的系统，它都只会显示员工的个人信息id为1234，如下面的预备声明所示：
I am doing a web-application project in eclipse Java EE. Currently, my application returns all values in the database which stores personal information of employees. However, I set the default prepared statement to search the table where employee_id = 1234, instead of the employee_id of the employee that logs in. This means that no matter which employee logs into my system, it will only display the personal information of the employee with id 1234, as shown in the prepared statement below:
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管理。如果有人可以帮助我，那将非常感谢，谢谢:)
Note how the employeeID above is set to 1234. That means my program (see code below) will only show the information for that employee. However, I want the employeeID to be set to the id of whoever logs in (login is managed by another servlet. If someone can please help me, that would be much appreciated, thanks :)
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的值。
Pass the value of employeeID from previous Servlet to current Servlet .
Replace id:1243 with placeHolder: ? .
然后设置其值 ps.setInt（1，你得到的值来自以前的servlet）;
对PersonalInfoOutput.java的更改：
changes to PersonalInfoOutput.java :
HttpSession session = request.getSession(false);
if(session != null) {
String employeeid = (String)session.getAttribute("employeeid");
}
to：
HttpSession session = request.getSession(false);
String employeeid="";
if(session != null) {
employeeid = (String)session.getAttribute("employeeid");
}
这篇关于如何在预备语句中使用变量进行sql查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
