I have an Entity called Item. Item has, among other things, a productNumber (Long) a category (own Entity), an itemType (Enum of own creation) and an isHidden (Boolean). I want to do queries like this:
List<Item> findByItemTypeAndCategoryAndIsHiddenAndNameIgnoreCaseContainingAndProductNumberAndManufacturerIgnoreCaseContainingAndWholesalerIgnoreCaseContainingOrderByName(ItemType itemType, Category category, Boolean isHidden, String name, Long productNumber, String manufacturer, String wholesaler);
A user of the application is able to search Items in such a manner:
Item type: All / Product / Service
Category: All / category1 / category2...
Hidden: All / don't show hidden / show only hidden
Product Number: a number field
Is there a way to use the above query if itemtype/category/isHidden are All? I know that I can create own queries for those situations by leaving out the particular property from which the user wants to find all. But surely there is a way to use that one query for each instance, I just don't know how to edit it to include null/empty so that it returns "All". And how about the productNumber? Is there an equivalent to "containing" for type Long? So that one could query "456" and it would return all Items with "456" included in the productNumber?
Thanks in advance!
Item:
package mortar.euroshopper.eCommerceApplication;
import java.io.Serializable;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;
@Entity
@NoArgsConstructor
@Getter
@Setter
@ToString
public class Item implements Serializable, Comparable<Item> {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private ItemType itemType;
@ManyToOne(cascade = CascadeType.MERGE)
@JoinColumn(name = "category")
private Category category;
private Long productNumber;
private String name;
private String amount;
private Unit unit;
private String manufacturer;
private String wholesaler;
private Double price;
private Double discountPrice;
private Double discountPercentage;
private LocalDate discountStarts;
private LocalDate discountEnds;
private Double stock;
private LocalDate expirationDate;
@Column(length = 10000)
private String description;
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private List<Image> images = new ArrayList<>();
private Long mainImageId;
private Boolean isHidden;
private PromotionLevel promotionLevel;
@Override
public int compareTo(Item other) {
return name.compareToIgnoreCase(other.name);
}
public Item(Long productNumber, String name, String manufacturer, String wholesaler, Double price, String description, Double stock, Category category) {
this.name = name;
this.price = price;
this.description = description;
this.stock = stock;
this.category = category;
}
@Override
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (!(o instanceof Item)) {
return false;
}
Item other = (Item) o;
return id != null && id.equals(other.getId());
}
@Override
public int hashCode() {
return getClass().hashCode();
}
}
ItemType:
public enum ItemType {
PRODUCT, SERVICE
}
Category:
package mortar.euroshopper.eCommerceApplication;
import com.fasterxml.jackson.annotation.JsonBackReference;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.validation.constraints.NotNull;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.LazyCollection;
import org.hibernate.annotations.LazyCollectionOption;
import org.springframework.data.jpa.domain.AbstractPersistable;
@Entity
@NoArgsConstructor
@Data
public class Category extends AbstractPersistable<Long> {
@NotNull
private String name;
@JsonBackReference
@ManyToOne(cascade = CascadeType.MERGE)
@JoinColumn(name = "parentCategory")
private Category parentCategory;
@OneToMany(mappedBy = "parentCategory")
private List<Category> subCategories = new ArrayList<>();
@LazyCollection(LazyCollectionOption.FALSE)
@OneToMany(mappedBy = "category")
private List<Item> items = new ArrayList<>();
public Category(String name) {
this.name = name;
}
public Category(String name, Category parent) {
this.name = name;
this.parentCategory = parent;
}
public Integer subCategoryLevel;
}
----------------------------------- EDIT -----------------------------------
I changed productNumber to type String and that solved my problem on that part. Otherwise, my solution for now looks like this (and I cant believe that this is the best, or even a good way to do this): HTML:
<form th:action="@{/items}" method="GET">
<div class="form-row">
<div class="form-group mr-2">
<label for="type">Type</label>
<select class="form-control" id="type" name="type">
<option value="">All</option>
<option value="product">Products</option>
<option value="service">Services</option>
</select>
</div>
<div class="form-group mr-2">
<label for="category">Category</label>
<input class="form-control" list="category" name="category" id="categories" placeholder="All"/>
<datalist id="category">
<option th:each="ca : ${categories}" th:value="${ca.name}"/>
</datalist>
</div>
<div class="form-group">
<label for="hidden">Hidden</label>
<select class="form-control" id="hidden" name="showHidden">
<option value="all">All</option>
<option value="true">Only hidden</option>
<option value="false">No hidden</option>
</select>
</div>
</div>
... non relevant stuff....
JpaRepositotry:
public interface ItemRepository extends JpaRepository<Item, Long> {
Item findByProductNumber(String productNumber);
List<Item> findByItemTypeIgnoreCaseContainingAndCategoryAndIsHiddenAndNameIgnoreCaseContainingAndProductNumberContainingAndManufacturerIgnoreCaseContainingAndWholesalerIgnoreCaseContainingOrderByName(String itemType, Category category, Boolean isHidden, String name, String productNumber, String manufacturer, String wholesaler);
List<Item> findByItemTypeIgnoreCaseContainingAndIsHiddenAndNameIgnoreCaseContainingAndProductNumberContainingAndManufacturerIgnoreCaseContainingAndWholesalerIgnoreCaseContainingOrderByName(String itemType, Boolean isHidden, String name, String productNumber, String manufacturer, String wholesaler);
List<Item> findByItemTypeIgnoreCaseContainingAndCategoryAndNameIgnoreCaseContainingAndProductNumberContainingAndManufacturerIgnoreCaseContainingAndWholesalerIgnoreCaseContainingOrderByName(String itemType, Category category, String name, String productNumber, String manufacturer, String wholesaler);
List<Item> findByItemTypeIgnoreCaseContainingAndNameIgnoreCaseContainingAndProductNumberContainingAndManufacturerIgnoreCaseContainingAndWholesalerIgnoreCaseContainingOrderByName(String itemType, String name, String productNumber, String manufacturer, String wholesaler);
List<Item> findByCategoryOrderByName(Category category);
}
Controller method:
@GetMapping("/items")
public String viewItems(Model model, @RequestParam(required = false) String type, @RequestParam(required = false) String category, @RequestParam(required = false) String showHidden, @RequestParam(required = false) String name, @RequestParam(required = false) String productNumber, @RequestParam(required = false) String manufacturer, @RequestParam(required = false) String wholesaler) {
itemService.addAttributesToModelForPageItems(model, type, category, showHidden, name, productNumber, manufacturer, wholesaler);
return "items";
}
addAttributesToModelForPageItems -method:
public void addAttributesToModelForPageItems(Model model, String type, String category, String showHidden, String name, String productNumber, String manufacturer, String wholesaler) {
List<Item> items = new ArrayList<>();
if (type != null) {
if (category.equals("") && showHidden.equals("all")) {
items = itemRepository.findByItemTypeIgnoreCaseContainingAndNameIgnoreCaseContainingAndProductNumberContainingAndManufacturerIgnoreCaseContainingAndWholesalerIgnoreCaseContainingOrderByName(type, name, productNumber, manufacturer, wholesaler);
}
if (!category.equals("") && showHidden.equals("all")) {
items = itemRepository.findByItemTypeIgnoreCaseContainingAndCategoryAndNameIgnoreCaseContainingAndProductNumberContainingAndManufacturerIgnoreCaseContainingAndWholesalerIgnoreCaseContainingOrderByName(type, categoryRepository.findByName(category), name, productNumber, manufacturer, wholesaler);
}
if (category.equals("") && !showHidden.equals("all")) {
items = itemRepository.findByItemTypeIgnoreCaseContainingAndIsHiddenAndNameIgnoreCaseContainingAndProductNumberContainingAndManufacturerIgnoreCaseContainingAndWholesalerIgnoreCaseContainingOrderByName(type, Boolean.valueOf(showHidden), name, productNumber, manufacturer, wholesaler);
}
if (!category.equals("") && !showHidden.equals("all")) {
items = itemRepository.findByItemTypeIgnoreCaseContainingAndCategoryAndIsHiddenAndNameIgnoreCaseContainingAndProductNumberContainingAndManufacturerIgnoreCaseContainingAndWholesalerIgnoreCaseContainingOrderByName(type, categoryRepository.findByName(category), Boolean.valueOf(showHidden), name, productNumber, manufacturer, wholesaler);
}
}
model.addAttribute(
"items", items);
model.addAttribute(
"categories", categoryRepository.findAll());
}
