Java SQL Select语句在WHERE子句中使用多个变量

Question

我正在使用UDP Sockets编写一个简单的程序。我需要输入患者的姓名并从数据库中检索其详细信息。病人的名字在Doctor类中输入并发送到服务器类。 Server类然后执行查询来检索患者的详细信息。问题出在SQL语句中。当我只使用变量firstname时，它工作正常，但是当我把第二个变量姓氏放入PatientRecord变量时，它是NULL。

服务器类：

public class Server { 

    public static Connection con; 

    public static String PatientRecords; 

    public static String QueryPatientInfo(String PatientDetails) throws SQLException { 

     System.out.print("\nNew Patient query received:\n"); 

     String [] PatientDetArray = PatientDetails.split(","); 

     String firstname,lastname; 

     firstname = PatientDetArray[1]; 
     lastname = PatientDetArray[2]; 

     System.out.println("First Name: "+ firstname); 
     System.out.println("Last Name: "+ lastname); 

     Statement query = con.createStatement(); 

     query.execute("SELECT * FROM patient WHERE FirstName = '"+firstname+"' AND LastName = '"+lastname+"' "); 

     ResultSet rs = query.getResultSet(); 

     String sex; 
     String dob ; 
     String address ; 
     String occupation; 
     String phoneno ; 


     if(rs != null){ 

      while (rs.next()){ 

       sex = rs.getString("Sex"); 
       dob = rs.getString("DOB"); 
       address = rs.getString("Address"); 
       occupation = rs.getString("Occupation"); 
       phoneno = rs.getString("PhoneNo"); 

       PatientRecords = sex + "," + dob + "," + address + "," + occupation + "," + phoneno; 
      } 

      System.out.print("Patient records successfully retrieved from database !\n\n"); 

      return PatientRecords; 
     } 

     else { 

      System.out.print("Error occurred patient records not found !\n\n"); 

      return "Error occurred patient records not found !"; 
     } 

    } 

    public static void main(String[] args) throws IOException, SQLException { 

     // Connecting to database - using xampp 

     try 
     { 
      Class.forName("com.mysql.jdbc.Driver"); 
      con = DriverManager.getConnection("jdbc:mysql://localhost/patientrecord", "root", ""); 
      System.out.println("Database is connected !"); 

     } 
     catch(Exception e) 
     { 
      System.out.println("Database connection error: " + e); 
     } 

     DatagramSocket serverSocket = new DatagramSocket(8008); 

     byte[] receiveData = new byte[1024]; 

     byte[] sendData; 

     System.out.println("Server ready and waiting for clients to connect..."); 

     while (true) { 

      DatagramPacket receivePacket = new DatagramPacket(receiveData, receiveData.length); 

      serverSocket.receive(receivePacket); 

      String PatientDetails = new String(receivePacket.getData()); 

      String message; 

      message = QueryPatientInfo(PatientDetails); 

      System.out.print(message); 

      InetAddress IPAddress = receivePacket.getAddress(); 

      int port = receivePacket.getPort(); 

      sendData = message.getBytes(); 

      DatagramPacket sendPacket = new DatagramPacket(sendData, sendData.length, IPAddress, port); 

      serverSocket.send(sendPacket); 

     } 
    } 

} 

的医生级别：

public class Doctor { 

    public static void main(String[] args) throws IOException { 


     BufferedReader inFromUser = new BufferedReader(new InputStreamReader(System.in)); 

     DatagramSocket clientSocket = new DatagramSocket(); 

     InetAddress IPAddress = InetAddress.getByName("localhost"); 

     // Creating array of bytes to send and receive packet 
     byte[] sendData; 

     byte[] receiveData = new byte[1024]; 

     String request,firstName,lastName; 

     request = "query"; 

     System.out.print("Patient Registration"); 

     System.out.print("\n\nEnter Patient Details:\n"); 

     // User input 
     System.out.print("First name: \n"); 

     firstName= inFromUser.readLine(); 

     System.out.print("Last name: \n"); 
     lastName = inFromUser.readLine(); 

     String PatientDetails = request + ","+ firstName + "," +lastName; 

     sendData = PatientDetails.getBytes(); 

     DatagramPacket sendPacket = new DatagramPacket(sendData, sendData.length,IPAddress, 8008); 

     // Send data packet to server 
     clientSocket.send(sendPacket); 

     DatagramPacket receivePacket = new DatagramPacket(receiveData, receiveData.length); 

     //Receive data packet from server 
     clientSocket.receive(receivePacket); 

     String PatientRecords = new String(receivePacket.getData()); 

     //System.out.print(PatientRecords); 

     String [] PatientDetArray = PatientRecords.split(","); 

     String sex,dob,address,occupation,phoneno; 

     sex = PatientDetArray[0]; 
     dob = PatientDetArray[1]; 
     address = PatientDetArray[2]; 
     occupation = PatientDetArray[3]; 
     phoneno = PatientDetArray[4]; 

     System.out.println("FROM SERVER: "); 

     System.out.println("Details for patient : " + firstName + " " + lastName); 
     System.out.println("Sex: " + sex); 
     System.out.println("Date of birth: " +dob); 
     System.out.println("Address: " + address); 
     System.out.println("Occupation: " + occupation); 
     System.out.println("Phone number: " + phoneno); 

     clientSocket.close(); 

    } 

} 

Answer 1

这可能发生，所以为了避免这种情况，你可以使用trim()这样的：

query.execute("SELECT * FROM patient WHERE FirstName = '" + firstname.trim() + 
       "' AND LastName = '" + lastname.trim() + "' "); 

你的方式来设置变量是不安全的它可以使语法错误或导致SQL注入这样建议使用Prepapred Statement，这种方式是比较安全的，而不是使你的查询，你可以使用：

PreparedStatement preparedStatement = connection.prepareCall("SELECT * FROM patient WHERE FirstName = ? AND LastName = ? "); 
preparedStatement.setString(1, firstname.trim()); 
preparedStatement.setString(2, lastname.trim()); 
ResultSet result = preparedStatement.executeQuery(); 

希望这能与您合作。

Answer 2

这显然意味着，下面你WHERE条件不匹配的任何记录，因此没有记录牵强。尝试直接在SQL中运行查询并查看您获得的记录数。或者尝试将条件从AND更改为OR，这应该给你一个想法。

WHERE FirstName = '"+firstname+"' AND LastName = '"+lastname+"' 

顺便说一句，你的代码是开放的SQL Injection，因此考虑使用参数化查询来代替。当你的字符串有空格