首先,我是 Spring Framework 和 Hibernate 的新手。请多多包涵。
我正在尝试实现一个搜索过滤器,用户可以选择在其中指定名字、姓氏、出生日期、性别、国家/地区、语言等。我有一个名为的表Guest
,用于存储这些记录。某些列(例如性别、国家/地区和语言)存储在具有唯一主键的单独表中。也就是说,我将这些记录的外键存储在Guest
表中。
搜索的所有字段都是可选的。对于每个缺失的搜索字段,程序必须假设“任何”。
考虑到这一点,我可以LIKE
对字符串列使用通配符,例如名字和姓氏。对于外键列,我使用了=
运算符。如果缺少搜索字段,该字段将替换为null
。
当我仅指定名字(或姓氏)时,我无法在输出中获得任何内容。
这是CrudRepository
我编写的接口代码。
public interface GuestRepository extends CrudRepository<Guest, Integer> {
@Query("SELECT guest FROM Guest guest WHERE guest.propertyIdentifier = ?1 AND guest.firstName LIKE %?2% AND guest.lastName LIKE %?3% AND guest.titleIdentifier = ?4 AND guest.dateOfBirth = ?5 AND guest.genderIdentifier = ?6 AND guest.countryIdentifier = ?7 AND guest.languageIdentifier = ?8 AND guest.passport LIKE %?9%")
Iterable<Guest> findGuests(Integer propertyIdentifier, String firstName, String lastName, Integer titleIdentifier, Date dateOfBirth, Integer genderIdentifier, Integer countryIdentifier, Integer languageIdentifier, String passport);
}
这是Guest
实体。
@Entity
public class Guest {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer identifier;
private Integer propertyIdentifier;
private String firstName;
private String lastName;
private Integer titleIdentifier;
@Temporal(TemporalType.DATE)
private Date dateOfBirth;
private Integer genderIdentifier;
private Integer countryIdentifier;
private Integer languageIdentifier;
private String passport;
private Integer status;
public Integer getIdentifier() {
return identifier;
}
public void setIdentifier(Integer identifier) {
this.identifier = identifier;
}
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 Date getDateOfBirth() {
return dateOfBirth;
}
public void setDateOfBirth(Date dateOfBirth) {
this.dateOfBirth = dateOfBirth;
}
public String getPassport() {
return passport;
}
public void setPassport(String passport) {
this.passport = passport;
}
public Integer getLanguageIdentifier() {
return languageIdentifier;
}
public void setLanguageIdentifier(Integer languageIdentifier) {
this.languageIdentifier = languageIdentifier;
}
public Integer getCountryIdentifier() {
return countryIdentifier;
}
public void setCountryIdentifier(Integer countryIdentifier) {
this.countryIdentifier = countryIdentifier;
}
public Integer getGenderIdentifier() {
return genderIdentifier;
}
public void setGenderIdentifier(Integer genderIdentifier) {
this.genderIdentifier = genderIdentifier;
}
public Integer getTitleIdentifier() {
return titleIdentifier;
}
public void setTitleIdentifier(Integer titleIdentifier) {
this.titleIdentifier = titleIdentifier;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
public Integer getPropertyIdentifier() {
return propertyIdentifier;
}
public void setPropertyIdentifier(Integer propertyIdentifier) {
this.propertyIdentifier = propertyIdentifier;
}
}
如果用户没有为搜索字段指定值,我如何实现假设为“任何”的搜索过滤器?我可以使用其他任何替代方法吗?
考虑使用Criteria API。它看起来像这样:
@Service
public class GuestService {
@PersistenceContext
private EntityManager em;
public List<Guest> findGuests(Guest searchedGuest) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Guest> cq = cb.createQuery(Guest.class);
Root<Guest> quest = cq.from(Guest.class);
List<Predicate> predicates = new ArrayList<Predicate>();
if (searchedGuest.getPropertyIdentifier != null) {
predicates.add(cb.equal(quest.get("propertyIdentifier"), searchedGuest.getPropertyIdentifier));
}
if (searchedGuest.getFirstName != null) {
predicates.add(cb.like(quest.get("firstName"), "%" + searchedGuest.getFirstName + "%"));
}
// other predicates
cq.select(quest).where(predicates.toArray(new Predicate[] {}));
List<Guest> guests = em.createQuery(cq).getResultList();
return guests;
}
其中searchedGuest是带有可选填充字段的对象。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句