--Entity

package com.monogram.ngs.entity;

import com.monogram.ngs.dto.SampleDTO;
import com.monogram.ngs.dto.SampleRegionDTO;
import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.sql.NClob;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

@Entity
@NoArgsConstructor
@AllArgsConstructor
@Data
@Table(name = "NGS_SAMPLE")
@NamedNativeQuery(
        name = "sampleSummary_native_query_dto",
        query = """
            SELECT
                s.SAMPLE_NAME as sampleName,
                s.SAMPLE_PROJECT as sampleProject,
                s.SAMPLE_PLATE as samplePlate,
                s.STATUS as sampleStatus,
                s.SUBTYPE as subType,
                s.PNG_FILE as pngFile,
                s.CSV_FILE as csvFile,
                s.FASTA_FILE as fastaFile,
                s.ERR_FILE as errFile,
                s.CHECKED_OUT_BY_ID as checked_out_by_id,
                sr.ASSAY_REGION as assayRegion,
                sr.MIN_FINAL_DEPTH as minFinalDepth,
                sr.MUTLIST
            FROM
                NGS_SAMPLE s,
                NGS_SAMPLEREGION sr
            WHERE
                s.ID = sr.SAMPLE_ID
            AND
                s.SAMPLE_PLATE=:batchID
                """,
        resultSetMapping = "SampleDTO.SampleRegionDTO"
)
@SqlResultSetMapping(
        name = "SampleDTO.SampleRegionDTO",
        classes = {
                @ConstructorResult(
                        targetClass = SampleDTO.class,
                        columns = {
                                @ColumnResult(name = "sampleName", type = String.class),
                                @ColumnResult(name = "sampleProject", type = String.class),
                                @ColumnResult(name = "samplePlate", type = String.class),
                                @ColumnResult(name = "sampleStatus", type = String.class),
                                @ColumnResult(name = "subType", type = String.class),
                                @ColumnResult(name = "pngFile", type = String.class),
                                @ColumnResult(name = "csvFile", type = String.class),
                                @ColumnResult(name = "fastaFile", type = String.class),
                                @ColumnResult(name = "errFile", type = String.class),
                                @ColumnResult(name = "checked_out_by_id", type = Long.class)
                        }
                ),
                @ConstructorResult(
                        targetClass = SampleRegionDTO.class,
                        columns = {
                                @ColumnResult(name = "assayRegion", type = String.class),
                                @ColumnResult(name = "minFinalDepth", type = Long.class),
                                @ColumnResult(name = "mutList", type = String.class)
                        }
                )
        }

)
public class Sample implements Serializable {
    @Id
    @Column(name="ID")
    Long id;
    @Column(name="CREATEDDATE")
    LocalDateTime createdDate;
    @Column(name="MODIFIEDDATE")
    LocalDateTime modifiedDate;
    @Column(name="STATUS")
    String sampleStatus;
    @Column(name="IS_CONTROL")
    Long is_Control;
    @Column(name="ASSAY_TYPE")
    String assayType;
    @Column(name="SAMPLE_NO")
    Long sample_No;
    @Column(name="SAMPLE_NAME")
    String sampleName;
    @Column(name="SAMPLE_PLATE")
    String samplePlate;
    @Column(name="SAMPLE_WELL")
    String sampleWell;
    @Column(name="I7_INDEX_ID")
    String i7_index_id;
    @Column(name="INDEX")
    String index;
    @Column(name="I5_INDEX_ID")
    String i5_index_id;
    @Column(name="INDEX2")
    String index2;
    @Column(name="SAMPLE_PROJECT")
    String sampleProject;
    @Column(name="SAMPLE_SHEET_ID")
    Long sampleSheet_Id;
    @Column(name="DESCRIPTION")
    String description;
    @Column(name="SUBTYPE")
    String subType;
    @Column(name="TEST_CODE")
    String testCode;
    @Column(name="MFDPASSEDDATE")
    LocalDateTime mfdPassedDate;
    @Column(name="FASTA_FILE")
    String fastaFile;
    @Column(name="PNG_FILE")
    String pngFile;
    @Column(name="CSV_FILE")
    String csvFile;
    @Column(name="MUT_FILE")
    String mutFile;
    @Column(name="ERR_FILE")
    String errFile;
    @Column(name="REFERENCE")
    String reference;
    @Column(name="RAWNTSEQUENCE")
    NClob rawntsequence;
    @Column(name="BLAST_STATUS")
    String blastStatus;
    @Column(name="BLAST_ERROR")
    Integer blastError;
    @Column(name="BLAST_RESULT")
    Integer blastResult;
    @Column(name="ASSAY_ID")
    Long assay_id;
    @Column(name="CHECKED_OUT_BY_ID")
    Long checked_out_by_id;
    @Column(name="CREATEDBY_ID")
    Long createdBy_Id;
    @Column(name="LAB_ID")
    Long lab_id;
    @Column(name="MFDPASSEDBY_ID")
    Long mfdPassedBy_Id;
    @Column(name="MODIFIEDBY_ID")
    Long modifiedBy_Id;

    @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "SAMPLE_ID", insertable = false, updatable = false, nullable = false)
    @OrderBy("IS_CONTROL DESC,ID")
    private List<SampleRegion> sampleRegions = new ArrayList<>();


}
--repository

package com.monogram.ngs.repository;


import com.monogram.ngs.dto.SampleDTO;
import com.monogram.ngs.entity.Sample;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

@Repository
public interface SampleRepository extends JpaRepository<Sample, Integer> {
    @Query(name="sampleSummary_native_query_dto", nativeQuery = true)
    Page<SampleDTO> findSamplesUsingPage(String batchID, Pageable pageable);

}
--dto
package com.monogram.ngs.dto;

import java.util.List;

public record SampleDTO(String sampleName
                        , String sampleProject
                        , String samplePlate
                        , String sampleStatus
                        , String subType
                        , String pngFile
                        , String csvFile
                        , String fastaFile
                        , String errFile
                        , Long checked_out_by_id
                        , List<SampleRegionDTO> sampleRegionDTO) {
}

package com.monogram.ngs.dto;

import java.sql.NClob;
import java.util.List;

public record SampleRegionDTO(String assayRegion
                            , Long minFinalDepth
                            , String mutList
                            ) {
}
--service
package com.monogram.ngs.service;

import com.monogram.ngs.dto.SampleDTO;
import com.monogram.ngs.dto.SampleRegionDTO;
import com.monogram.ngs.repository.SampleRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;


import java.util.Arrays;
import java.util.List;

@Service
public class SampleSummaryServiceImpl implements ISampleSummaryService{
    @Autowired
    SampleRepository sampleRepository;

    @Override
    public Page<SampleDTO> getSampleSummary(String batchId, Pageable pageable) {
        return sampleRepository.findSamplesUsingPage(batchId, pageable);

    }
}

--controller
package com.monogram.ngs.controller;

import com.monogram.ngs.dto.SampleDTO;
import com.monogram.ngs.service.ISampleSummaryService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
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("/api/sampleSummary")
public class SampleSummaryController {
    @Autowired
    ISampleSummaryService isamplesummaryService;
    private static final Logger logger = LoggerFactory.getLogger(SampleSummaryController.class);

    @GetMapping("/getSamplesWithPage")
    public Page<SampleDTO> getSamplesWithPage(@RequestParam int page, @RequestParam int size, @RequestParam String batchId) {
        logger.debug("Entering SampleSummaryController getSamplesWithPage method");
        Pageable paging = PageRequest.of(page, size);
        return isamplesummaryService.getSampleSummary(batchId, paging);
    }

}