CODEWITHSUNDEEP
spring-boothibernatejpaspring-data-jpanhibernate-mapping
Vikas
Vikas

Reputation: 137

Fetch data from one table and Insert into another table JPA

I am a beginner in JPA. As per requirement I am trying to fetch data from multiple tables(emp_boarding_details,amex_emp_detail,emp_offboarding_details) and Insert into another table i.e. emp_boarding_history.

1. EmpBoardingHistoryRepository.java:

public interface EmpBoardingHistoryRepository extends JpaRepository<EmpBoardingHistory, Long> {

@Query(nativeQuery = true, value = "SELECT new com.boarding.boardinghistory.resource.EmpBoardingHistory**(ebd.exemption_type_txt, ebd.exemption_remarks_txt, ebd.emp_no_int, ebd.first_name_txt, ebd.middle_name_txt, ebd.last_name_txt, ebd.email_id_txt, ebd.emp_or_contractor_ind, ebd.vendor_laptop_desktop_asset_txt, ebd.country_origin_txt, ebd.status_txt, aed.billing_po_no_txt, aed.contractor_id_txt, aed.contract_type_txt, aed.proj_master_cd_txt, aed.amex_email_id_txt, aed.amex_laptop_desktop_asset_id_txt, aed.ads_id_txt, aed.cost_center_txt, aed.contractor_id_creation_date, aed.first_billing_date, aed.clarity_resource_role_txt, aed.platform_id_txt, aed.premium_technology_txt, aed.resource_status_txt, aed.onboard_completion_date, eod.resource_leaving_date, eod.departure_date) FROM emp_boarding_details ebd, amex_emp_details aed, emp_offboarding_details eod WHERE ebd.emp_no_int = aed.emp_no_int AND aed.emp_no_int = eod.emp_no_int AND ebd.emp_no_int = :empNo")
    List<EmpBoardingHistory> getEmpBoardingDetails(@Param("empNo") long empNo); 
}

2. EmpBoardingHistory.java: 

@Entity
@Table(name = "emp_boarding_history")
public class EmpBoardingHistory implements Serializable {
    private static final long serialVersionUID = 1L;

    public EmpBoardingHistory() {
        super();
    }

    public EmpBoardingHistory(String exemptionfromaxpreporting, String exemptionremarks,
            @NotNull(message = "Employee number is mandatory") @Range(min = 999, max = 999999999) int empno,
            String firstname, String middlename, String lastname, String emailid, String emporcontractor,
            String venderlaptopordesktopassetno, String countryoforigin, String status, String billponumber,
            String contractoridnumber, String contracttype, String projmastercd, String amexemailaddress,
            String amexlaptopordesktopassetnumber, String adsid, String costcenter, Date contractoridcreationdate,
            Date firstbillingdate, String clarityorresourcerole, String premiumtechnology, String resourcestatus,
            Date onboardCompletionDate, Date resourceleavingdate,
            @NotNull(message = "First Billing Date is mandatory") Date departuredate) {
        super();
        this.exemptionfromaxpreporting = exemptionfromaxpreporting;
        this.exemptionremarks = exemptionremarks;
.......

//For all setter and getter defined
}}

And when I am trying to call this getEmpBoardingDetails(), getting below exception:

020-06-03 16:24:15.146 DEBUG 68096 --- [nio-8080-exec-5] org.hibernate.SQL                        : SELECT new com.boarding.boardinghistory.resource.EmpBoardingHistory(ebd.exemption_type_txt, ebd.exemption_remarks_txt, ebd.emp_no_int, ebd.first_name_txt, ebd.middle_name_txt, ebd.last_name_txt, ebd.email_id_txt, ebd.emp_or_contractor_ind, ebd.vendor_laptop_desktop_asset_txt, ebd.country_origin_txt, ebd.status_txt, aed.billing_po_no_txt, aed.contractor_id_txt, aed.contract_type_txt, aed.proj_master_cd_txt, aed.amex_email_id_txt, aed.amex_laptop_desktop_asset_id_txt, aed.ads_id_txt, aed.cost_center_txt, aed.contractor_id_creation_date, aed.first_billing_date, aed.clarity_resource_role_txt, aed.platform_id_txt, aed.premium_technology_txt, aed.resource_status_txt, aed.onboard_completion_date, eod.resource_leaving_date, eod.departure_date) FROM emp_boarding_details ebd, amex_emp_details aed, emp_offboarding_details eod WHERE ebd.emp_no_int = aed.emp_no_int AND aed.emp_no_int = eod.emp_no_int AND ebd.emp_no_int = ?
2020-06-03 16:24:15.149 TRACE 68096 --- [nio-8080-exec-5] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [909089]
2020-06-03 16:24:15.282  WARN 68096 --- [nio-8080-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
2020-06-03 16:24:15.282 ERROR 68096 --- [nio-8080-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.boarding.boardinghistory.resource.EmpBoardingHistory(ebd.exemption_type_txt, eb' at line 1

Continuation of above question-> Now I am successfully able to retrieve data from multiple tables(Thanks to Jens!) but When I am trying to save that response into another table i.e. emp_boarding_history, data is not persisting.

//Retrieve and Save Logic 

public EmpBoardingHistory selectSaveEmpBoardingHistory(Integer empNo) { 
    //Retrieve Data FROM ONE TABLE 
    List<EmpBoardingHistory> empBoardingHistoryList = empBoardingHistoryRepository.findBoardingHistoryEmpNo(empNo); 
    EmpBoardingHistory empBoardingHistory = new EmpBoardingHistory(); 
    for (EmpBoardingHistory empBoardingHistoryObj : empBoardingHistoryList) 
    { 
    //SAVING DATA INTO ANOTHER TABLE 
    empBoardingHistoryRepository.save(empBoardingHistoryObj);
    empBoardingHistory = empBoardingHistoryObj; 
    } 
    return empBoardingHistory; 
    }

Upvotes: 0

Views: 7567

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81862

You are using a constructor expression in your query:

new com.boarding.boardinghistory.resource.EmpBoardingHistory**(...

This is a construct from JPQL. But you also mark it as nativeQuery = true which means it is interpreted as SQL and SQL doesn't have such a construct. Use either the constructor expression or mark it as a native query, not both.

Also the constructor expression contains ** which won't work.

Upvotes: 1

Related Questions