Блог

У меня есть запрос именования, определенный в JPA, который извлекает данные из представления MySQL. Когда я выполняю представление с помощью запроса «ВЫБРАТЬ» в базе данных MySQL, он быстро загружает данные. Но когда я использую то же самое для веб-службы RESTful, загрузка данных занимает слишком много времени.

Вот мое мнение

     CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `dev`@`%` 
    SQL SECURITY DEFINER
VIEW `new_papertrue`.`OMSJob` AS
    SELECT 
        `j`.`job_id` AS `jobId`,
        `j`.`source` AS `source`,
        `j`.`job_no` AS `jobNo`,
        `j`.`english_type` AS `englishType`,
        `j`.`referencing` AS `referencing`,
        `j`.`created_at` AS `dateCreated`,
        `j`.`paid_at` AS `datePaid`,
        `j`.`currency` AS `currency`,
        `j`.`price` AS `price`,
        `j`.`payment_status` AS `paymentStatus`,
        `j`.`status` AS `status`,
        `j`.`coupon` AS `discountCode`,
        `j`.`discount_amount` AS `discountAmount`,
        `j`.`is_sample` AS `isFreeSample`,
        `j`.`requirements` AS `requirements`,
        `j`.`plagiarism_check` AS `plagiarismCheck`,
        `j`.`pt_service_type` AS `serviceType`,
        `j`.`country` AS `country`,
        `j`.`extDueDate` AS `extDueDate`,
        `j`.`originalJobNo` AS `originalJobNo`,
        `j`.`prevJobNo` AS `previousJobNo`,
        `j`.`review_not_required` AS `reviewNotRequired`,
        `j`.`job_owner_id` AS `jobOwnerId`,
        `j`.`dps` AS `documentPriorityScore`,
        `j`.`qualityOfWriting` AS `qualityOfWriting`,
        `j`.`pwa` AS `pwaScore`,
        `f`.`word_count` AS `wordCount`,
        `jd`.`editor_id` AS `editorId`,
        `jd`.`editorStatus` AS `editorStatus`,
        `jd`.`editor_dueDate` AS `editorDueDate`,
        `jd`.`is_enabled` AS `enabled`,
        `jd`.`reviewer_id` AS `reviewerId`,
        `jd`.`job_details_type` AS `jobDetailsType`,
        `psl`.`url` AS `plagCheckUrl`,
        `psl`.`plagStatus` AS `plagStatus`,
        `o`.`name` AS `jobOwnerName`,
        `s`.`name` AS `jobAccountManagerName`,
        `u`.`name` AS `userName`,
        `u`.`email` AS `userEmail`,
        `u`.`client_id` AS `clientId`,
        `j`.`is_apa_formatting_needed` AS `isAPAFormattingNeeded`,
        `j`.`other_formatting_requirements` AS `otherFormattingRequirements`,
        GETUSER(`j`.`user_id`) AS `user`,
        GETTURNAROUND(`j`.`turnaround_id`) AS `turnaround`,
        GETFILEDETAILS(`j`.`job_id`) AS `fileDetails`,
        GETNOTES(`j`.`job_id`) AS `notes`,
        GETSTAFFDUEDATE(`j`.`paid_at`, `t`.`hours`) AS `dueDate`,
        GETEDITORDETAILS(`j`.`job_id`) AS `editorDetails`,
        GETREVIEWINGPRIORITY(`jd`.`sales_tags`,
                `j`.`pt_service_type`,
                `f`.`word_count`,
                `j`.`is_sample`,
                `j`.`referencing`) AS `reviewingPriority`,
        GETINVOICES(`j`.`job_id`) AS `invoices`
    FROM
        ((((((((`new_papertrue`.`jobs` `j`
        LEFT JOIN `new_papertrue`.`jobDetails` `jd` ON ((`jd`.`job_id` = `j`.`job_id`)))
        JOIN `new_papertrue`.`turnarounds` `t` ON ((`j`.`turnaround_id` = `t`.`id`)))
        JOIN `new_papertrue`.`files` `f` ON ((`j`.`draft_file_id` = `f`.`id`)))
        LEFT JOIN `new_papertrue`.`files` `cf` ON ((`j`.`clean_file_id` = `cf`.`id`)))
        LEFT JOIN `new_papertrue`.`PlagScanLink` `psl` ON ((`cf`.`id` = `psl`.`id`)))
        LEFT JOIN `new_papertrue`.`staffs` `o` ON ((`j`.`job_owner_id` = `o`.`id`)))
        LEFT JOIN `new_papertrue`.`staffs` `s` ON ((`j`.`jobAccountManagerId` = `s`.`id`)))
        LEFT JOIN `new_papertrue`.`users` `u` ON ((`j`.`user_id` = `u`.`id`)))
    WHERE
        ((`j`.`is_enabled` = TRUE)
            AND (`j`.`created_at` > '2018-07-31 23:59:59'))
    ORDER BY `j`.`job_no` DESC
 

Вот мой запрос на присвоение имен для JPA

 Page<OmsJob> findDistinctByStatusNotInOrderByNoDesc(ArrayList<Integer> statusList, Pageable pageable);