QBE (Query by Example: "Query by Example") is one of the query languages, and I quoted the explanation of wikipedia below.
[Example inquiry](https://ja.wikipedia.org/wiki/%E4%BE%8B%E7%A4%BA%E3%81%AB%E3%82%88%E3%82%8B% E5% 95% 8F% E3% 81% 84% E5% 90% 88% E3% 82% 8F% E3% 81% 9B) It is one of the query languages for relational databases. QBE was invented by Moshe Zroof in parallel with the development of another query language SQL at the IBM Research Center in Yorktown, NY, USA in the mid-1970s. QBE is the first visual query language.
Since Spring Data JPA has been supported since version 1.10, I wrote a sample code for a simple search process.
environment
reference
To make inquiries with QBE, use Spring Data JPA Example (Example is associated with sample code, but it is a class called org.springframework.data.domain.Example) and ExampleMatcher. I also use the interface QueryByExampleExecutor, but JpaRepository Since it inherits this, if you are using JpaRepository, you do not need to be aware of it again in the implementation.
Below is a simple but sample code to search with QBE. QBE allows you to write a simple search process concisely, but I felt that it was not suitable for searching with complicated conditions.
Item entity used in the QBE search sample code.
@Entity
@Table(name="item")
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@ToString(exclude = {"itemStocks"})
@EqualsAndHashCode(exclude = {"itemStocks"})
public class Item implements Serializable {
private static final long serialVersionUID = -3153084093423004609L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name="name", nullable = false)
private String name;
@Column(name="price", nullable = false)
private Integer price;
@Column(name="sales_from", nullable = false)
private LocalDateTime salesFrom;
@Column(name="sales_to", nullable = false)
private LocalDateTime salesTo;
@Enumerated(EnumType.ORDINAL)
@Column(name="standard_type", nullable = false)
private StandardType standardType;
@JoinColumn(name = "category_id", nullable = false)
@ManyToOne
private Category category;
@Column(name="del_flag", nullable = false)
private Boolean delFlag;
@Column(name="create_at", nullable = false)
private LocalDateTime createAt;
@Column(name="update_at", nullable = false)
private LocalDateTime updateAt;
@OneToMany(mappedBy = "item", cascade = CascadeType.ALL)
private List<ItemStock> itemStocks;
}
Item Repository. In particular, we have not implemented custom methods for QBE. Since JpaRepository inherits QueryByExampleExecutor, you can search with QBE as it is.
import com.example.domain.entity.Item;
import org.springframework.data.jpa.repository.JpaRepository;
public interface ItemRepository extends JpaRepository<Item, Long> { }
By the way, the methods defined in the QueryByExampleExecutor interface are findOne, findAll, count, exists.
The simplest search is to express the search condition as an entity, use it to create an Example instance, and pass it to the repository search method.
This sample searches for data with a price of 1000 and a standardType of D. If the property of the entity is set to a value other than null, it will be used as a search condition.
@Autowired
private ItemRepository repository;
public void find() {
//Set search criteria to entity
Item probe = new Item();
probe.setPrice(1000);
probe.setStandardType(StandardType.D);
//Create an Example instance from the search criteria
Example<Item> example = Example.of(probe);
List<Item> items = repository.findAll(example);
items.forEach(System.out::println);
}
If you want to search with a little more complicated condition, assemble the search condition with Example Matcher.
matching / matchingAll
It is a static method that generates ExampleMatcher that searches the data that matches all the properties specified in the search condition. Matching is internally called by ExampleMatcher # matchingAll.
This sample searches for data with a name starting with "kitchen" and a price of 800, regardless of case. The search condition for the name property is assembled with ExampleMatcher.
Item item = new Item();
item.setName("kitchen");
item.setPrice(800);
ExampleMatcher matcher = ExampleMatcher.matching()
.withMatcher("name", match -> match.ignoreCase().startsWith());
Example<Item> example = Example.of(item, matcher);
List<Item> items = repository.findAll(example);
items.forEach(System.out::println);
matchingAny
A static method that generates an ExampleMatcher that searches for data that matches one or more of the properties specified in the search criteria.
This sample looks for data whose name starts with "kitchen" or whose price is 800, regardless of case.
Item item = new Item();
item.setName("kitchen");
item.setPrice(800);
ExampleMatcher matcher = ExampleMatcher.matchingAny()
.withMatcher("name", match -> match.ignoreCase().startsWith());
Example<Item> example = Example.of(probe, matcher);
List<Item> items = repository.findAll(example);
items.forEach(System.out::println);
withIgnorePaths
If the property of the entity used for the search is other than null, it will be treated as a search condition. If you do not want to use a specific property as a search condition, you can exclude it by specifying the property name with withIgnorePaths.
This sample excludes the id, price, and standardType properties from the search criteria.
ExampleMatcher matcher = ExampleMatcher.matchingAny()
.withMatcher("name", match -> match.ignoreCase().startsWith())
.withIgnorePaths("id", "price", "standardType");
withIncludeNullValues
If you want to search even if it is null, use withIncludeNullValues. However, please note that if you do not narrow down the target property by using withIgnorePaths above, it will be applied to all properties. The SQL statement adds the condition "column name is null" to the corresponding column.
ExampleMatcher matcher = ExampleMatcher.matchingAny()
.withMatcher("name", match -> match.ignoreCase().startsWith())
.withIncludeNullValues();
Recommended Posts