我希望在存储库层编写一些查询方法。这些方法必须忽略空参数。例如:
List<Foo> findByBarAndGoo(Bar barParam, @optional Goo gooParam);
根据这个条件,此方法必须返回Foo:
bar == barParam && goo == gooParam;
如果gooParam不为空。如果gooParam为空,则条件将更改为:
bar == barParam;
有解决方案吗?能有人帮帮我吗?
我希望在存储库层编写一些查询方法。这些方法必须忽略空参数。例如:
List<Foo> findByBarAndGoo(Bar barParam, @optional Goo gooParam);
根据这个条件,此方法必须返回Foo:
bar == barParam && goo == gooParam;
如果gooParam不为空。如果gooParam为空,则条件将更改为:
bar == barParam;
有解决方案吗?能有人帮帮我吗?
我不相信你可以使用方法名称来定义查询。根据文档(参考):
虽然从方法名称获取派生的查询非常方便,但可能会遇到一些情况,例如方法名称解析器不支持想要使用的关键字或方法名称变得非常难看。因此,您可以通过命名约定使用JPA命名查询(请参阅有关使用JPA NamedQueries的更多信息),或者使用@Query注释注释查询方法。
在这种情况下,我认为以下答案使用了@Query注释方法,这种方法几乎与方法名称方法一样方便(参考)。
@Query("select foo from Foo foo where foo.bar = :bar and "
+ "(:goo is null or foo.goo = :goo)")
public List<Foo> findByBarAndOptionalGoo(
@Param("bar") Bar bar,
@Param("goo") Goo goo);
回复已经太迟了。我不确定Bar和Goo之间的关系。请检查Example是否能帮到你。
这对我有用。我有一个类似的情况,实体User具有一组属性,并且有一个findAll方法,可以根据属性(这些属性是可选的)搜索用户。
例如,
Class User{
String firstName;
String lastName;
String id;
}
Class UserService{
// All are optional
List<User> findBy(String firstName, String lastName, String id){
User u = new User();
u.setFirstName(firstName);
u.setLastName(lastName);
u.setId(id);
userRepository.findAll(Example.of(user));
// userRepository is a JpaRepository class
}
}
object.property in (a, b, c)
,那么只有使用@Query
方法才能解决问题。 - WeGa补充@chaserb的答案,我个人会将该参数作为Java8的Optional类型添加到方法签名中,以明确表达它是一个可选过滤器的语义。
@Query("select foo from Foo foo where foo.bar = :bar and "
+ "(:goo is null or foo.goo = :goo)")
public List<Foo> findByBarAndOptionalGoo(
@Param("bar") Bar bar,
@Param("goo") Optional<Goo> goo);
nativeQuery = true
作为第二个@Query
参数,否则在运行应用程序时会出现错误:_IllegalArgumentException: Validation failed for query_。 - user1123432Optional
作为参数传递不是一个好主意:https://dev59.com/slwZ5IYBdhLWcg3wC8f4 - Theiazorg.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1
有什么想法如何解决这个问题吗? - jaletechs您可以使用JpaSpecificationExecutor
//import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
步骤1:在您的JPA Repository中实现JpaSpecificationExecutor
示例:
public interface TicketRepo extends JpaRepository<Ticket, Long>, JpaSpecificationExecutor<Ticket> {
步骤2:现在,您可以使用CriteriaBuilder构建Specification查询来根据可选参数获取票证。public Specification<Ticket> getTicketQuery(Integer domainId, Calendar startDate, Calendar endDate, Integer gameId, Integer drawId) {
return (root, query, criteriaBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
predicates.add(criteriaBuilder.equal(root.get("domainId"), domainId));
predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("createdAt"), startDate));
predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("createdAt"), endDate));
if (gameId != null) {
predicates.add(criteriaBuilder.equal(root.get("gameId"), gameId));
}
return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
};
}
步骤三:将Specification实例传递给jpaRepo.findAll(specification),它会返回实体对象的列表(在运行示例中是Tickets)。
ticketRepo.findAll(specification); // Pass output of function in step 2 to findAll
Ticket
、Movie
和Customer
。这里没有什么花哨的东西:import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
import java.util.UUID;
@Entity
@Table(name = "ticket", schema = "public")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
public class Ticket implements Serializable {
@Id
@Basic(optional = false)
@NotNull
@Column(name = "id", nullable = false)
private UUID id;
@JoinColumn(name = "movie_id", referencedColumnName = "id", nullable = false)
@ManyToOne(fetch = FetchType.EAGER)
private Movie movie;
@JoinColumn(name = "customer_id", referencedColumnName = "id", nullable = false)
@ManyToOne(fetch = FetchType.EAGER)
private Customer customer;
@Column(name = "booking_date")
@Temporal(TemporalType.TIMESTAMP)
private Date bookingDate;
}
电影:
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import java.io.Serializable;
@Entity
@Table(name = "movie", schema = "public")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
public class Movie implements Serializable {
@Id
@Basic(optional = false)
@NotNull
@Column(name = "id", nullable = false)
private UUID id;
@Basic(optional = false)
@NotNull
@Size(max = 100)
@Column(name = "movie_name", nullable = false, length = 100)
private String movieName;
}
客户:
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import java.io.Serializable;
@Entity
@Table(name = "customer", schema = "public")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
public class Customer implements Serializable {
@Id
@Basic(optional = false)
@NotNull
@Column(name = "id", nullable = false)
private UUID id;
@Basic(optional = false)
@NotNull
@Size(max = 100)
@Column(name = "full_name", nullable = false, length = 100)
private String fullName;
}
我创建一个类,包含我想要筛选的参数字段:
import lombok.AllArgsConstructor;
import lombok.Data;
import java.util.Date;
import java.util.UUID;
@Data
@AllArgsConstructor
public class TicketFilterParam {
private UUID movieId;
private UUID customerId;
private Date start;
private Date end;
}
接下来,我创建了一个类来根据过滤参数生成一个Specification
。注意嵌套对象的访问方式,以及查询中添加排序的方式。
import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.Predicate;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
public class TicketSpecifications {
public static Specification<Ticket> getFilteredTickets(TicketFilterParam params) {
return (root, criteriaQuery, criteriaBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
if (params.getMovieId() != null) {
predicates.add(criteriaBuilder.equal(root.get("movie").<UUID> get("id"), params.getMarketerId()));
}
if (params.getCustomerId() != null) {
predicates.add(criteriaBuilder.equal(root.get("customer").<UUID> get("id"), params.getDepotId()));
}
if (params.getStart() != null && params.getEnd() != null) {
predicates.add(criteriaBuilder.between(root.get("bookingDate"), params.getStart(), params.getEnd()));
}
criteriaQuery.orderBy(criteriaBuilder.desc(root.get("bookingDate")));
return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
};
}
}
接下来我定义仓库接口。这个接口不仅包含 JpaRepository
,还有 JpaSpecificationExecutor
:
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;
@Repository
public interface TicketRepository extends JpaRepository<Ticket, UUID>, JpaSpecificationExecutor<Ticket> {
}
最后,在某些服务类中,我会获得类似以下结果:
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
@Service
public class TicketService {
@Autowired
private TicketRepository ticketRepository;
public Page<Ticket> getTickets(TicketFilterParam params, PageRequest pageRequest) {
Specification<Ticket> specification = TicketSpecifications.getFilteredTickets(params);
return ticketRepository.findAll(specification, pageRequest);
}
}
PageRequest
和 TicketFilterParam
可能会从 rest 端点上的某些参数和值中获取。
List<Foo> findByBarAndOptionalGoo(Bar bar, Goo goo) {
return (goo == null) ? this.findByBar(bar) : this.findByBarAndGoo(bar, goo);
}
虽然已经晚了,但对于任何仍在寻找解决方案的人来说,以下是一种更简单的方法。我曾经遇到过同样的问题,最终找到了这个看起来比其他方法更简单有效的解决方案:
我的控制器类:
@RestController
@RequestMapping("/order")
public class OrderController {
private final IOrderService service;
public OrderController(IOrderService service) {
this.service = service;
}
@RequestMapping(value = "/{username}/", method = RequestMethod.GET)
public ResponseEntity<ListResponse<UserOrdersResponse>> getUserOrders(
@RequestHeader Map<String, String> requestHeaders,
@RequestParam(required=false) Long id,
@RequestParam(required=false) Long flags,
@RequestParam(required=true) Long offset,
@RequestParam(required=true) Long length) {
// Return successful response
return new ResponseEntity<>(service.getUserOrders(requestDTO), HttpStatus.OK);
}
}
正如您所看到的,我将Username
作为@PathVariable
以及length
和offset
作为必需参数,但是我接受id
和flags
用于过滤搜索结果,因此它们是我的可选参数,不需要调用REST服务。
我的Repository接口:
@Query("select new com.ada.bourse.wealth.services.models.response.UserOrdersResponse(FIELDS ARE DELETED TO BECOME MORE READABLE)" +
" from User u join Orders o on u.id = o.user.id where u.userName = :username" +
" and (:orderId is null or o.id = :orderId) and (:flag is null or o.flags = :flag)")
Page<UserOrdersResponse> findUsersOrders(String username, Long orderId, Long flag, Pageable page);
就是这样,你可以看到我使用了(:orderId is null or o.id = :orderId)
和(:flag is null or o.flags = :flag)
来检查我的可选参数,并且我认为需要强调的是我检查的是参数而不是我的列数据,因此如果客户端向我发送Id
和flags
参数,我将使用它们过滤结果,否则我只会使用@PathVariable
中的username
进行查询。