How to fetch custom column in spring boot method 1

                    Spring Boot Fetch Custom column using constructor

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. Create constructor in Employee entity. Line are highlighted with red color

package com.bce.model;

import java.time.LocalDate;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table
public class Employee {

    @Id
    @Column
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column
    private String firstname;

    @Column
    private String lastname;

    @Column
    private LocalDate dob;

    @Column
    private String email;

    public Employee() {
        super();
        // TODO Auto-generated constructor stub
    }

    public Employee(Long id, String firstname) {
        super();
        this.id = id;
        this.firstname = firstname;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public LocalDate getDob() {
        return dob;
    }

    public void setDob(LocalDate dob) {
        this.dob = dob;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

}

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 constructor
    @Query("select new com.bce.model.Employee(e.id,e.firstname) from #{#entityName} e")
    List<Employee> findAllCustomEmloyee();

}

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> getAllCustomerEmployeeData();
}

Next Step. Add below-highlighted method.

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> getAllCustomerEmployeeData() {
        return employeeRepositoory.findAllCustomEmloyee();
    }

}

Next Step. Add below-highlighted method.

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("/employeecustom")
     public List<Employee> findAllCustomEmployeeData() {
        return employeeService.getAllCustomerEmployeeData();
     }
}

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/employeecustom

Note: In response, we are getting only two fields value (id, firstname) and others are null.

To remove null fields from response please add the below line in appliction.properties.

spring.jackson.default-property-inclusion=non_null

Next Step. Database configuration in application.properties

spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
#enabling the H2 console
spring.h2.console.enabled=true
spring.jpa.show-sql=true
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:h2:C:/data/crudoperation

 
# Custom H2 Console URL
spring.h2.console.path=/h2

# Below line for exclude null filed from serilization object
spring.jackson.default-property-inclusion=non_null

Test Again: 

Download Code from GitHub.  Source Code