Get only selected columns from DB with multiple filtering criteria spring boot 2 JPA

Question

I'm trying to create a spring boot 2 web application which will fetch data from the db based on the filtering criteria passed to it, but will only fetch certain columns.

Here is my employee class:

@Entity
@Table("table=emplooyee")
class Employee{ 
         @column("name="fname")
         String fname;
         @column("name="lname")
         String lname;
         @column("name="phoneNo")
         String phoneNo;
         @column("name="address")
         String address;
     }

There are 25 more such fields in my entity and in the db.

From the front-end the user should be able to choose a filtering criteria such as: fname, lname, phoneNo, address etc. He may specify any combination like fname and phoneNo, or lname and address or may not specify anything in which I have to do a select *. In a way, I want multiple filtering criteria. I expect these filters to come as request parameters from the front end.

My repository is:

public interface EmployeeRepository extends JpaRepository<Employee,Long>, JpaSpecificationExecutor<Employee>{

}

So far, I've looked into specifications which is pretty cool.

So I created a specification,

import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

public class EmployeeSpecs {
    public static Specification<Employee> hasFname(String fname){
        return new Specification<Employee>() {
            @Override
            public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                return criteriaBuilder.equal(root.get("fname"),fname);
            }
        };
    }
    public static Specification<Employee> hasLname(String lname){
        return new Specification<Employee>() {
            @Override
            public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                return criteriaBuilder.equal(root.get("lname"), lname);
            }
        };
    }
    public static Specification<Employee> hasAddress(String address){
        return new Specification<Employee>() {
            @Override
            public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                return criteriaBuilder.equal(root.get("address"), address);
            }
        };
    }
    public static Specification<Employee> hasPhone(String phone){
        return new Specification<Employee>() {
            @Override
            public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                return criteriaBuilder.equal(root.get("phone"), phone);
            }
        };
    }
}

Now, from my service, I plan to do:

this.employeeRepository.findAll(EmployeeSpecs.hasFName(requestParameterFname).and(EmployeeSpecs.hasLName(requestParameterLname))).forEach(e->list.add(e));

However, this would fetch all the 25 columns in my db. My front end application has 6 pages, each requiring different columns to be displayed but a combination of these specifications as where clauses.

I tried looking into the concept of projection, but figured out that currently SpringBoot does not support Specification with Projection.

Is there a way to get only selected columns and have multiple filtering criteria? Any thoughts on being able to dynamically append the passed request parameters to my query and fetching only relevant fields?

Should I create separate entities so that I get only those fields from my repository and then a new specification for each of those each time? Won't this create too many unnecessary entities and specification files?

The other way I can think of is that, I'll have to manually extract those columns. This would sound stupid as I already know that I need to do a 'select column1, column2, column3 from db where condition1 = true and condition2= true' but I'm still doing a select *.

Can anyone please guide on what's the best approach to take in this case? What would look the most clean way of achieving this? Should I be writing a query manually, like a native query?

In a nutshell, I want the following:

1. Multiple filtering criteria - any combination possible, ie. multiple conditions to be passed to the 'where' clause of my sql select statement.
2. Only selected columns, not all - but different use cases require different columns.

Answer 1

Spring Data doesn't have any special feature or this. So you would need to create a custom method, where you combine the Predicate from the Specification with a selection list.

The custom method might look somewhat like this:

Employee findBySpecAndColumns(Specification spec, List<String> columns) {
    
    // create select list as described here, but from the list of columns or whatever you use to specify which columns you want to select: https://www.objectdb.com/java/jpa/query/jpql/select#SELECT_in_Criteria_Queries

    // use spec.toPredicate(...) to create the where clause

    // execute the query.

    // transform the result to the form you need/want.
}

See also: How to specify the select list using the Criteria API.

I wonder though, if this is worth the effort. I'd expect that selecting 25 columns for data to be displayed on a single page probable doesn't make much difference from selecting 4 columns from the same table.

Answer 2

You can use GraphQL or QueryDSL

Example using queryDSL

    QMenuItemRelation entity = new QMenuItemRelation("entity");
    QMenuItem menuItem = new QMenuItem("menuItem");
    QMenuItemRelationPrice menuItemRelationPrice = new QMenuItemRelationPrice("menuItemRelationPrice");
    return queryFactory.select(Projections.constructor(
                    MenuItemScalesExportDTO.class,
                    entity.menuItem.id,
                    entity.menuItem.name,
                    entity.menuItem.barcode,
                    entity.menuItem.unitType,
                    menuItemRelationPrice.price))
            .from(entity)
            .where(entity.active.eq(true), entity.menu.id.eq(menuId), entity.menuItem.usedByScales.eq(true))
            .leftJoin(entity.menuItem, menuItem)
            .leftJoin(menuItemRelationPrice).on(entity.eq(menuItemRelationPrice.menuItemRelation))
            .orderBy(entity.id.desc())
            .fetch();

You also can use Projections.bean if you want to map with getter/setter instead of constructor.

DTO

public class MenuItemScalesExportDTO implements Serializable {
private UUID id;
private String name;
private String code;
private String unit;
private List<PriceDTO> price;
private BigDecimal unitPrice;

public MenuItemScalesExportDTO(UUID id, String name, String code, String unit, List<PriceDTO> price) {
    this.id = id;
    this.name = name;
    this.code = code;
    this.unit = unit;
    this.price = price;
}