How to fetch custom column in spring boot method 3

                    Spring Boot Fetch Custom column using Native Query

Purpose: In this post, we will learn how we can fetch custom columns from a database using an entity. The steps are given below.

1. Setup and Create Project: Visit here. Spring Boot CRUD Operation using in-memory database example.

Step: No new dependency is required.

Next Step. Write a query in the repository.

package com.bce.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.bce.model.Employee;
import com.bce.model.EmployeeCustom;

@Repository
public interface EmployeeRepositoory extends CrudRepository<Employee, Long> {

    // Using native query
    @Query(value = "select e.id,e.firstName from #{#entityName} e", nativeQuery = true)
    Object[][] findAllCustomEmloyee2();

}

Next Step. In service add highlighted method.

package com.bce.service;

import java.util.List;

import org.springframework.stereotype.Service;

import com.bce.model.Employee;

@Service
public interface EmployeeService {

    void saveOrUpdate(Employee employee);

    List<Employee> getAllEmployee();

    Employee getEmployeeById(long id);

    void delete(long id);

    List<Employee> getAllCustomerEmployeeData2();
}

Next Step. Add below-highlighted method in service implementation.

package com.bce.service.impl;

import java.util.List;

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

import com.bce.model.Employee;
import com.bce.repository.EmployeeRepositoory;
import com.bce.service.EmployeeService;

@Service
public class ServiceImpl implements EmployeeService {

    @Autowired
    EmployeeRepositoory employeeRepositoory;

    @Override
    public void saveOrUpdate(Employee employee) {
        employeeRepositoory.save(employee);
    }

    @Override
    public List<Employee> getAllEmployee() {
        return (List<Employee>) employeeRepositoory.findAll();

    }

    @Override
    public Employee getEmployeeById(long id) {

        return employeeRepositoory.findById(id).get();
    }

    @Override
    public void delete(long id) {

        employeeRepositoory.deleteById(id);

    }
    

    @Override
    public List<Employee> getAllCustomerEmployeeData2() {
        Object[][] obj = employeeRepositoory.findAllCustomEmloyee2();
        List<Employee> empList = new ArrayList<Employee>();
        for (Object[] o : obj) {
            empList.add(new Employee(Long.parseLong(o[0].toString()), String.valueOf(o[1])));
        }
        return empList;
    }


}

Next Step. Add below-highlighted method in Rest Controller.

package com.bce.controller;

import java.time.LocalDate;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import com.bce.exceptions.EmployeeNotFoundException;
import com.bce.model.Employee;
import com.bce.service.EmployeeService;

@RestController
public class EmployeeController {

    @Autowired
    EmployeeService employeeService;

    @PostMapping("/employee")
    public void saveEmployee(@RequestBody Employee employee) {
        employeeService.saveOrUpdate(employee);
    }

    @GetMapping("/employee")
    public List<Employee> findAll() {
        return employeeService.getAllEmployee();
    }
     
    @GetMapping("/employeecustom2")
    public List<Employee> findAllCustomEmployeeData2() {
        return employeeService.getAllCustomerEmployeeData2();
    }

}

Next Step. Server Port:  The default server port is 8080. Wish you want to change open application.properties and add the below line.

server.port=8081

Next Step: Test API

 Hit GET URL in the browser: http://localhost:8080/employeecustom2

Download Code from GitHub.  Source Code