Categories
Mastering Development

How to write custom findAll() with Specification in JPA repository

When I use skuRepository.getAll(), it works OK, but when I apply filters, defined in Specification (List filteredRegs = skuRepository.getAll(specification)) I still get all the rows of the table
What should i do to apply the specifications to my custom method?

public interface SkuRepository extends CrudRepository<Sku, Integer>, JpaSpecificationExecutor<Sku> {

    @Query("select s from Sku s join fetch s.unit un join fetch s.supplier sup WHERE un.id = sku_unit_id AND sup.id = supplier_id")
    List<Sku> getAll(@Nullable Specification<Sku> var1);

    @Query("select s from Sku s join fetch s.unit un join fetch s.supplier sup WHERE un.id = sku_unit_id AND sup.id = supplier_id")
    List<Sku> getAll();
}

UPD:
Here is my entities.
When I make sampling by a Sku table using the Specification API, I see three separate selects in log: one for Sku entity, one for Unit and one for Suppliers. I want my app to make one select with joins.
I read that this is due to the fact that I use EAGER fetch type, so I change it to LAZY, but then I got another problem: “InvalidDefinitionException: No serializer found…” This is logical because related entities Unit and Supplier are not loaded.
Then I decided to write my custom getAll() with request:

@Query("select s from Sku s join fetch s.unit un join fetch s.supplier sup WHERE un.id = sku_unit_id AND sup.id = supplier_id ORDER BY s.name")

But now it does not support Specification.
Please advise what to do.

@Entity
@Table(name = "sku")
public class Sku implements Cloneable, CloneableEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;

    @Column(name = "sku_code", length = 6, nullable = false, unique = true)
    private String code;

    @Column(name = "sku_name", nullable = false)
    private String name;

    @ManyToOne(cascade = CascadeType.PERSIST, fetch = FetchType.LAZY)
    @JoinColumn(name = "sku_unit_id", nullable = false)
    private Unit unit;

    @ManyToOne(cascade = CascadeType.PERSIST, fetch = FetchType.LAZY)
    @JoinColumn(name = "supplier_id", nullable = false)
    private Supplier supplier;

    @Column(name = "qty_in_sec_pkg")
    private int quantityInSecondaryPackaging;

    @Column(name = "sku_is_active", nullable = false)
    private boolean isActive;

//constructors, getters, setters

}

@Entity
@Table(name = "units")
public class Unit {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id ")
    private int id;

    @Column(name = "unit", nullable = false, unique = true)
    private String unit;

    @Column(name = "description")
    private String description;

//constructors, getters, setters
}

@Entity
@Table(name = "suppliers")
public class Supplier {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id ")
    private int id;

    @Column(name = "supplier_code", length = 6, nullable = false, unique = true)
    private String supplierCode;

    @Column(name = "supplier_name", nullable = false)
    private String name;

    @Column(name = "create_date", length = 19, nullable = false)
    private String createDate;

    @Column(name = "update_date", length = 19)
    private String updateDate;
//constructors, getters, setters
}

Leave a Reply

Your email address will not be published. Required fields are marked *