Friday, August 19, 2022

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() + "...");
}
}
}
}
}

Springboot app with hibernate 2 -pom.xml

 pom.xml


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.6</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.myapp.dxt</groupId>
<artifactId>central_scheduler</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>central_scheduler</name>
<description>central scheduler</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.json</groupId>
<artifactId>json</artifactId>
<version>20180813</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc -->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>7.0.0.jre8</version>
</dependency>

<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>

<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
<version>4.5.10</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.7</version>
</dependency>
<dependency>
<groupId>org.olap4j</groupId>
<artifactId>olap4j</artifactId>
<version>1.2.0</version>
</dependency>
<dependency>
<groupId>org.json</groupId>
<artifactId>json</artifactId>
<version>20180813</version>
</dependency>
<dependency>
<groupId>org.olap4j</groupId>
<artifactId>olap4j-xmla</artifactId>
<version>1.1.0</version>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>

</project>

Springboot app with hibernate 1

 1. application.properties

server.port=8203
spring.datasource.url=jdbc:mysql://localhost:3306/central_scheduler
spring.datasource.username=root
spring.datasource.password=root@123

spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.show-sql=false
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

logging.level.root=warn
logging.level.com.myapp.dxt.central_scheduler=debug