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

Question

我正在使用 UDP 套接字编写一个简单的程序。我需要输入患者的姓名并从数据库中检索其详细信息。患者的姓名被输入到 Doctor 类中并发送到 Server 类。然后，服务器类执行查询来检索患者的详细信息。问题出在SQL语句上。当我仅使用变量名字时，它工作正常，但是当我输入第二个变量姓氏时，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 注入(inject)，因此建议使用 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();

希望这能与您合作。