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 

Springboot Sample App - GET list of values from database

 1. get full list - all

http://localhost:8011/myapp/lov/all

This will have

1. Controller
2. Service and Impl
3. Repository
4. Model class

Controller

package com.sample.controller;

import com.sample.model.Lov;

import com.sample.service.LovServiceImpl;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.http.HttpStatus;

import org.springframework.http.ResponseEntity;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController

@RequestMapping("/myapp/lov")

public class LovController {

    @Autowired

    LovServiceImpl lovService;

    @GetMapping("/all")

    public ResponseEntity<List<Lov>> retrieveAllValues() {

        return new ResponseEntity<List<Lov>>(lovService.retrieveAllValues(), HttpStatus.OK);

    }

    @GetMapping("/fieldName")

    public ResponseEntity<List<Lov>> findByFieldName(@RequestParam String fieldName) {

        return new ResponseEntity<List<Lov>>(lovService.findByFieldName(fieldName), HttpStatus.OK);

    }

    @GetMapping("/fieldName1")

    public ResponseEntity<List<Lov>> findLovByFieldName(@RequestParam String fieldName) {

        return new ResponseEntity<List<Lov>>(lovService.findLovByFieldName(fieldName), HttpStatus.OK);

    }

    @GetMapping("/fieldId")

    public ResponseEntity<List<Lov>> findByFieldId(@RequestParam String fieldId) {

        return new ResponseEntity<List<Lov>>(lovService.findByFieldId(fieldId), HttpStatus.OK);

    }

}

-----------------

Model class

package com.sample.model;

import lombok.Data;

import javax.persistence.*;

@Entity

@Table(name = "lov")

@Data

public class Lov {

    @Id

    private int lovId;

    @Column(name = "fieldName")

    private String fieldName;

    @Column(name = "fieldId")

    private String fieldId;

    @Column(name = "value")

    private String value;

}

--------------------------------------------------------------------------------------

Service interface

package com.sample.service;

import com.sample.model.Lov;

import com.sample.repository.LovRepository;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Repository;

import java.util.List;

public interface LovService {

    List<Lov> retrieveAllValues();

    List<Lov> findByFieldName(String fieldName);

    List<Lov> findLovByFieldName(String fieldName);

    List<Lov> findByFieldId(String fieldId);

}

--------------------------------

Service Impl

package com.sample.service;

import com.sample.model.Lov;

import com.sample.repository.LovRepository;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Repository;

import org.springframework.stereotype.Service;

import java.util.List;

@Service

public class LovServiceImpl implements LovService {

    private static final Logger logger = LoggerFactory.getLogger(LovServiceImpl.class);

    @Autowired

    LovRepository lovRepository;

    public List<Lov> retrieveAllValues() {

        return lovRepository.findAll();

    }

    public List<Lov> findByFieldName(String fieldName) {

        return lovRepository.findByFieldName(fieldName);

    }

    public List<Lov> findLovByFieldName(String fieldName) {

        return lovRepository.findLovByFieldName(fieldName);

    }

    public List<Lov> findByFieldId(String fieldId) {

        return lovRepository.findByFieldId(fieldId);

    }

}

-----------------------------------------

Repository

package com.sample.repository;

import com.sample.model.Lov;

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

import java.util.List;

public interface LovRepository extends JpaRepository<Lov,Integer> {

    List<Lov> findLovByFieldName(String fieldName);

    List<Lov> findByFieldName(String fieldName);

    List<Lov> findByFieldId(String fieldId);

}

Properties

server.port=8011

#your database name is mydb

spring.datasource.url=jdbc:mysql://localhost:3306/mydb

spring.datasource.username=username

spring.datasource.password=password

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.sample=debug 

Dependency

<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>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>commons-io</groupId>
   <artifactId>commons-io</artifactId>
   <version>2.6</version>
</dependency>