public ListAppointment queryByNursePageModel page String key String docId ListAppointment list = new ArrayList; Connection connection = null; try 定义集合存放数据

1. 根据医生id和关键字模糊查询：

public List<Appointment> queryByDoctorAndKeyword(PageModel page, String docId, String keyword) {
    List<Appointment> list = new ArrayList<>();
    Connection connection = null;
    try {
        //获取连接
        connection = DbUtil.getConnection();
        //定义sql语句
        String sql = "SELECT av.*, u.qrcode\n" +
                "FROM user u\n" +
                "JOIN appointment_view av ON u.id = av.user_id\n" +
                "JOIN vaccinum v ON av.vaccinum_id = v.id\n" +
                "JOIN vaccinum_type vt ON v.type = vt.id\n" +
                "WHERE u.qrcode = 1 \n" +
                "AND doctor_id=?\n" +
                "AND vt.status = 1 \n" +
                "AND user_name LIKE ? \n" +
                "AND av.address LIKE ? \n" +
                "ORDER BY av.`status` ASC \n" +
                "LIMIT ?,?;";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, docId);
        statement.setString(2, "%" + keyword + "%");
        statement.setInt(3, page.getSqlStart());
        statement.setInt(4, page.getNums());
        //执行查询
        ResultSet resultSet = statement.executeQuery();
        //遍历结果集
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            int userId = resultSet.getInt("user_id");
            int vaccinumId = resultSet.getInt("vaccinum_id");
            int doctorId = resultSet.getInt("doctor_id");
            String address = resultSet.getString("address");
            String appDate = resultSet.getString("app_date");
            String injectedTime = resultSet.getString("injected_time");
            int status = resultSet.getInt("status");
            String createTime = resultSet.getString("create_time");

            String vaccinumName = resultSet.getString("vaccinum_name");
            String doctorName = resultSet.getString("doctor_name");
            String userName = resultSet.getString("user_name");

            Appointment appointment = new Appointment(id, userId, vaccinumId, doctorId, address, appDate, injectedTime, status, createTime, userName, vaccinumName, doctorName);
            int qrcode = resultSet.getInt("qrcode");
            appointment.setQrcode(qrcode);
            //添加对象到list集合
            list.add(appointment);
        }
        //返回结果集
        return list;
    } catch (Exception e) {
        System.err.println("出现异常" + e.getMessage());
        e.printStackTrace();
        return null;
    } finally {
        //关闭连接
        DbUtil.close(connection);
    }
}

2. 根据护士id和关键字模糊查询：

public List<Appointment> queryByNurseAndKeyword(PageModel page, String nurseId, String keyword) {
    List<Appointment> list = new ArrayList<>();
    Connection connection = null;
    try {
        //获取连接
        connection = DbUtil.getConnection();
        //定义sql语句
        String sql = "SELECT av.*, u.qrcode\n" +
                "FROM user u\n" +
                "JOIN appointment_view av ON u.id = av.user_id\n" +
                "JOIN vaccinum v ON av.vaccinum_id = v.id\n" +
                "JOIN vaccinum_type vt ON v.type = vt.id\n" +
                "WHERE u.qrcode = 1 \n" +
                "AND nurse_id=?\n" +
                "AND vt.status = 1 \n" +
                "AND user_name LIKE ? \n" +
                "AND av.address LIKE ? \n" +
                "ORDER BY av.`status` ASC \n" +
                "LIMIT ?,?;";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, nurseId);
        statement.setString(2, "%" + keyword + "%");
        statement.setInt(3, page.getSqlStart());
        statement.setInt(4, page.getNums());
        //执行查询
        ResultSet resultSet = statement.executeQuery();
        //遍历结果集
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            int userId = resultSet.getInt("user_id");
            int vaccinumId = resultSet.getInt("vaccinum_id");
            int doctorId = resultSet.getInt("doctor_id");
            String address = resultSet.getString("address");
            String appDate = resultSet.getString("app_date");
            String injectedTime = resultSet.getString("injected_time");
            int status = resultSet.getInt("status");
            String createTime = resultSet.getString("create_time");

            String vaccinumName = resultSet.getString("vaccinum_name");
            String doctorName = resultSet.getString("doctor_name");
            String userName = resultSet.getString("user_name");

            Appointment appointment = new Appointment(id, userId, vaccinumId, doctorId, address, appDate, injectedTime, status, createTime, userName, vaccinumName, doctorName);
            int qrcode = resultSet.getInt("qrcode");
            appointment.setQrcode(qrcode);
            //添加对象到list集合
            list.add(appointment);
        }
        //返回结果集
        return list;
    } catch (Exception e) {
        System.err.println("出现异常" + e.getMessage());
        e.printStackTrace();
        return null;
    } finally {
        //关闭连接
        DbUtil.close(connection);
    }
}
``