sohangp

@Repository
public interface CustomerRepository extends JpaRepository<CustomerEntity, Integer> {
  
    @Query(name = "customerEntity.getCustomerDetails", nativeQuery = true)
    List<CustomerDetailsDTO> getCustomerDetails();
}

sohangp

@Data
@Entity
@Table(name = "CUSTOMER")
public class CustomerEntity {

    @Id
    @Column(name = "ID")
    private Integer id;

    @Column(name = "FIRST_NAME")

sohangp

@Data
@Entity
@Table(name = "CUSTOMER_ORDER")
public class OrderEntity {

    @Id
    @Column(name = "ID")
    private Integer id;

    @Column(name = "ORDER_NUMBER")

sohangp

public interface CustomerDetailsDTO {

    Integer getCustomerId();

    @Value("#{target.firstName + ' ' + target.lastName}")
    String getCustomerName();

    String getCity();

    String getCountry();

sohangp

@Component
public class MapperUtility {

    public OrderDTO buildOrderDTO(Long orderNumber, Double totalAmount) {
        OrderDTO order = new OrderDTO();
        order.setOrderNumber(orderNumber);
        order.setTotalAmount("$" + totalAmount);
        return order;
    }
}

sohangp

@Component
public class MapperUtility {

    public CustomerOrderDTO buildOrderDTO(Long orderNumber, Double totalAmount) {
        CustomerOrderDTO customerOrderDTO = null;

        if (orderNumber != null) {
            customerOrderDTO = new CustomerOrderDTO();
            customerOrderDTO.setOrderNumber(orderNumber);
            customerOrderDTO.setTotalAmount("$" + totalAmount);

sohangp

<named-native-query name="customerEntity.getCustomerDetails">
    <query><![CDATA[
        SELECT
            cust.id as customerId,
            cust.FIRST_NAME as firstName,
            cust.LAST_NAME as lastName,
            cust.CITY as city,
            cust.COUNTRY as country,
            corder.ORDER_NUMBER as orderNumber,
            corder.TOTAL_AMOUNT as totalAmount

sohangp

public List<CustomerDetailsDTO> getCustomersAndOrderData() throws Exception {
    return customerRepository.getCustomerDetails();
}

sohangp

@Service
public class CustomerServiceImpl implements CustomerService {

    @Autowired
    private CustomerRepository customerRepository;

    public List<CustomerDetailsDTO> getCustomersAndOrderData() throws Exception {
        return customerRepository.getCustomerDetails();
    }
}

sohangp

@RestController
public class CustomerController { 
  
    @RequestMapping(value = "/customers", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
    public List<CustomerDetailsDTO> getCustomers() throws Exception {
        return customerService.getCustomersAndOrderData();
    }
}