Springboot app with hibernate 3 - sample sql

 1.sample sql - projAllocDetails.sql

SELECT [ProjectId]

      ,[EmployeeId]

      ,[AllocationStartDate]

      ,[AllocationEndDate]

      ,[AllocationType]

      ,[ProjectRole]

      ,[IsSheduledRole]

FROM [myapp].[myapp].[tblProjectAllocations]

2. ProjectAllocationsRepository 

package com.myapp.dxt.central_scheduler.repository;

import com.myapp.dxt.central_scheduler.model.ProjectAllocations;

import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.Modifying;

import org.springframework.data.jpa.repository.Query;

import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;

@Repository

public interface ProjectAllocationsRepository extends JpaRepository<ProjectAllocations,Integer> {

    @Transactional

    @Modifying

    @Query(value = "truncate table project_allocations", nativeQuery = true)

    void truncateDeleteEffortsDetail();

}

3.Allocation DTO class

package com.myapp.dxt.central_scheduler.model;

import javax.persistence.*;

import java.io.Serializable;

import java.sql.Timestamp;

/**

 * SELECT [ProjectId]

 * ,[EmployeeId]

 * ,[AllocationStartDate]

 * ,[AllocationEndDate]

 * ,[AllocationType]

 * ,[ProjectRole]

 * ,[IsSheduledRole]

 * FROM [myapp].[myapp].[tblProjectAllocations]

 */

@Entity

@Table(name = "project_allocations")

public class ProjectAllocations implements Serializable {

    @Id

    @GeneratedValue(strategy = GenerationType.AUTO)

    private int projectAllocationsId;

    @Column(name = "projectId")

    private Integer projectId;

    @Column(name = "employeeId")

    private Integer employeeId;

    @Column(name = "allocationStartDate")

    private Timestamp allocationStartDate;

    @Column(name = "allocationEndDate")

    private Timestamp allocationEndDate;

    @Column(name = "allocationType")

    private String allocationType;

    @Column(name = "projectRole")

    private String projectRole;

    @Column(name = "isSheduledRole")

    private int isSheduledRole;

    public int getProjectAllocationsId() {

        return projectAllocationsId;

    }

    public void setProjectAllocationsId(int projectAllocationsId) {

        this.projectAllocationsId = projectAllocationsId;

    }

    public Integer getProjectId() {

        return projectId;

    }

    public void setProjectId(Integer projectId) {

        this.projectId = projectId;

    }

    public Integer getEmployeeId() {

        return employeeId;

    }

    public void setEmployeeId(Integer employeeId) {

        this.employeeId = employeeId;

    }

    public Timestamp getAllocationStartDate() {

        return allocationStartDate;

    }

    public void setAllocationStartDate(Timestamp allocationStartDate) {

        this.allocationStartDate = allocationStartDate;

    }

    public Timestamp getAllocationEndDate() {

        return allocationEndDate;

    }

    public void setAllocationEndDate(Timestamp allocationEndDate) {

        this.allocationEndDate = allocationEndDate;

    }

    public String getAllocationType() {

        return allocationType;

    }

    public void setAllocationType(String allocationType) {

        this.allocationType = allocationType;

    }

    public String getProjectRole() {

        return projectRole;

    }

    public void setProjectRole(String projectRole) {

        this.projectRole = projectRole;

    }

    public int getIsSheduledRole() {

        return isSheduledRole;

    }

    public void setIsSheduledRole(int isSheduledRole) {

        this.isSheduledRole = isSheduledRole;

    }

}

4. Service class

package com.myapp.dxt.central_scheduler.service;

import com.myapp.dxt.central_scheduler.db.MyAppExtractor;
import com.myapp.dxt.central_scheduler.model.ProjectDetails;
import com.myapp.dxt.central_scheduler.repository.ProjectDetailsRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

import static com.myapp.dxt.central_scheduler.util.Constant.MY_APP_PROJECT_DETAILS;

@Service
public class ProjectDetailsService {
    private static final Logger logger = LoggerFactory.getLogger(ProjectDetailsService.class);

    ProjectDetailsService() {
    }

    @Autowired
    ProjectDetailsRepository projectDetailsRepository;

    private static List<Map<String, Object>> projectDetailsList = null;

    public static List<Map<String, Object>> getProjectDetailsList() {
        return projectDetailsList;
    }

    public static void setProjectDetailsList(List<Map<String, Object>> projectDetailsList) {
        ProjectDetailsService.projectDetailsList = projectDetailsList;
    }

    public void run() {
        // extract details from myapp db
        extract();
        //Save data to db
        save();
    }

    public void extract() {
        logger.info("Extracting project details data");
        MyAppExtractor myApp= new MyAppExtractor();
        List<Map<String, Object>> extractedList = myApp.process(MY_APP_PROJECT_DETAILS);
        setProjectDetailsList(extractedList);
    }

    public void save() {
        if (!getProjectDetailsList().isEmpty()) {
            logger.info("Delete existing project details table records");
            projectDetailsRepository.truncateDeleteEffortsDetail();
            Timestamp now = new Timestamp(System.currentTimeMillis());
            for (Map<String, Object> record : getProjectDetailsList()) {
                Integer projectId = (Integer) record.get("ProjectId");
                String projectName = (String) record.get("ProjectName");
                String account = (String) record.get("Account");
                String segment = (String) record.get("Segment");
                String sbu = (String) record.get("SBU");
                String groupSbu = (String) record.get("GroupSBU");
                String business = (String) record.get("Business");
                String atc = (String) record.get("ATC");
                String projectLocation = (String) record.get("ProjectLocation");
                String processTemplate = (String) record.get("ProcessTemplate");
                Timestamp chorusMigrationDate = (Timestamp) record.get("ChorusMigrationDate");
                Timestamp chorusClosedDate = (Timestamp) record.get("ChorusClosedDate");
                String practice = (String) record.get("Practice");
                String chorusStatus = (String) record.get("ChorusStatus");
                String managedBy = (String) record.get("ManagedBy");
                Integer totalTeamSize = (Integer) record.get("TotalTeamSize");
                String contractType = (String) record.get("ContractType");
                String largeProgram = (String) record.get("LargeProgram");
                String projectManager = (String) record.get("ProjectManager");
                String velocityProjectCode = (String) record.get("VelocityProjectcode");
                String velocityStatus = (String) record.get("VelocityStatus");
                String projectDuration = (String) record.get("ProjectDuration");
                String primaryProjectManager = (String) record.get("PrimaryProjectManager");
                String mitigationTiming = (String) record.get("MitigationTiming");
                String mitigationStage = (String) record.get("MitigationStage");
                String asmWorkType = (String) record.get("ASMWorkType");
                String underDxtPurview = (String) record.get("UnderDxTpurview");
                String reasonForNotSupporting = (String) record.get("ReasonforNotSupporting");
                String reason = (String) record.get("Reason");
                String projectComingUnderDecPurvieWas = (String) record.get("ProjectComingUnderDECPurviewas");
                String projectAccessForFacilitationAndAudits = (String) record.get("ProjectAccessforFacilitationandAudits");
                String dec = (String) record.get("DEC");
                String projectStartChampion = (String) record.get("ProjectStartChampion");
                String acceleratorsChampion = (String) record.get("AcceleratorsChampion");
                String sbuDecHead = (String) record.get("SBUDECHead");
                String canDecAttendClientMeetings = (String) record.get("CanDECAttendClientMeetings");
                String isTheProjectCanShowTheArtefactsUsingRemoteSharing = (String) record.get("IstheProjectCanShowtheArtefactsUsingRemoteSharing");
                String accessPermissionsComments = (String) record.get("AccessPermissionsComments");
                String remoteSharingComments = (String) record.get("RemoteSharingComments");
                String startRightApplicability = (String) record.get("StartRightApplicability");
                String reasonForSrNotApplicable = (String) record.get("ReasonforSRNotApplicable");
                String startRightChampion = (String) record.get("StartRightChampion");
                Timestamp velocityStartDate = (Timestamp) record.get("VelocityStartDate");
                Timestamp velocityEndDate = (Timestamp) record.get("VelocityEndDate");

                ProjectDetails projectDetailsEntity = new ProjectDetails();
                projectDetailsEntity.setProjectId(projectId);
                projectDetailsEntity.setProjectName(projectName);
                projectDetailsEntity.setAccount(account);
                projectDetailsEntity.setSegment(segment);
                projectDetailsEntity.setSbu(sbu);
                projectDetailsEntity.setGroupSbu(groupSbu);
                projectDetailsEntity.setBusiness(business);
                projectDetailsEntity.setAtc(atc);
                projectDetailsEntity.setProjectLocation(projectLocation);
                projectDetailsEntity.setProcessTemplate(processTemplate);
                projectDetailsEntity.setChorusMigrationDate(chorusMigrationDate);
                projectDetailsEntity.setChorusClosedDate(chorusClosedDate);
                projectDetailsEntity.setPractice(practice);
                projectDetailsEntity.setChorusStatus(chorusStatus);
                projectDetailsEntity.setManagedBy(managedBy);
                projectDetailsEntity.setTotalTeamSize(totalTeamSize);
                projectDetailsEntity.setContractType(contractType);
                projectDetailsEntity.setLargeProgram(largeProgram);
                projectDetailsEntity.setProjectManager(projectManager);
                projectDetailsEntity.setVelocityProjectCode(velocityProjectCode);
                projectDetailsEntity.setVelocityStatus(velocityStatus);
                projectDetailsEntity.setProjectDuration(projectDuration);
                projectDetailsEntity.setPrimaryProjectManager(primaryProjectManager);
                projectDetailsEntity.setMitigationTiming(mitigationTiming);
                projectDetailsEntity.setMitigationStage(mitigationStage);
                projectDetailsEntity.setAsmWorkType(asmWorkType);
                projectDetailsEntity.setUnderDxtPurview(underDxtPurview);
                projectDetailsEntity.setReasonForNotSupporting(reasonForNotSupporting);
                projectDetailsEntity.setReason(reason);
                projectDetailsEntity.setProjectComingUnderDecPurvieWas(projectComingUnderDecPurvieWas);
                projectDetailsEntity.setProjectAccessForFacilitationAndAudits(projectAccessForFacilitationAndAudits);
                projectDetailsEntity.setDeCoach(dec);
                projectDetailsEntity.setProjectStartChampion(projectStartChampion);
                projectDetailsEntity.setAcceleratorsChampion(acceleratorsChampion);
                projectDetailsEntity.setSbuDecHead(sbuDecHead);
                projectDetailsEntity.setCanDecAttendClientMeetings(canDecAttendClientMeetings);
                projectDetailsEntity.setIsTheProjectCanShowTheArtefactsUsingRemoteSharing(isTheProjectCanShowTheArtefactsUsingRemoteSharing);
                projectDetailsEntity.setAccessPermissionsComments(accessPermissionsComments);
                projectDetailsEntity.setRemoteSharingComments(remoteSharingComments);
                projectDetailsEntity.setStartRightApplicability(startRightApplicability);
                projectDetailsEntity.setReasonForSrNotApplicable(reasonForSrNotApplicable);
                projectDetailsEntity.setStartRightChampion(startRightChampion);
                projectDetailsEntity.setVelocityStartDate(velocityStartDate);
                projectDetailsEntity.setVelocityEndDate(velocityEndDate);

                logger.info("Upserting project details " + projectDetailsEntity.getProjectId() + "...");
                try {
                    projectDetailsRepository.save(projectDetailsEntity);
                    logger.info("Status: CREATED");
                } catch (RuntimeException e) {
                    throw new RuntimeException("Failed to upsert project details data with project id=" + projectDetailsEntity.getProjectId() + "...");
                }
            }
        }
    }
}