Home > Software design >  Invalid Spring Data JPA query by method name in a repository. How to retrieve a list of object in a
Invalid Spring Data JPA query by method name in a repository. How to retrieve a list of object in a

Time:03-28

I am working on a Spring Boot project involving Hibernate and Spring Data JPA and I have the following problem trying to implement a query using the query by method name style.

I try to explain you my situation in details:

I have this main entity class named TransactionStatusLog:

@Entity
@Table(name = "transaction_status_log")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class TransactionStatusLog {
    
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;
    
    @Column(name = "reason")
    private String reason;
    
    @Column(name = "is_active")
    Boolean isActive;
    
    @Column(name = "created_at",columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
    @Temporal(TemporalType.TIMESTAMP)
    private Date createdAt;
    
    
    @ManyToOne
    @EqualsAndHashCode.Exclude          // Needed by Lombock in "Many To One" relathionship to avoid error
    @JoinColumn(name = "transaction_id_fk", referencedColumnName = "id")
    private TransactionLog transactionLog;
    
    
    @ManyToOne
    @EqualsAndHashCode.Exclude          // Needed by Lombock in "Many To One" relathionship to avoid error
    @JoinColumn(name = "transaction_status_type_id_fk", referencedColumnName = "id")
    private TransactionStatusType transactionStatusType;
    
    
    // The admin that changes the status:
    @OneToOne
    @EqualsAndHashCode.Exclude          // Needed by Lombock in "Many To One" relathionship to avoid error
    @JoinColumn(name = "user_id_fk", referencedColumnName = "id")
    private User user;          
        
}

As you can see it contains this MANY TO ONE relationship:

@ManyToOne
@EqualsAndHashCode.Exclude          // Needed by Lombock in "Many To One" relathionship to avoid error
@JoinColumn(name = "transaction_id_fk", referencedColumnName = "id")
private TransactionLog transactionLog;

Following the TransactionLog entity class code:

@Entity
@Table(name = "transaction")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class TransactionLog implements Serializable {

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

    @ManyToOne
    @EqualsAndHashCode.Exclude          // Needed by Lombock in "Many To One" relathionship to avoid error
    @JoinColumn(name = "source_user_fk", referencedColumnName = "id")
    //@JsonManagedReference(value = "sourceUser")
    private User sourceUser;

    @ManyToOne
    @EqualsAndHashCode.Exclude          // Needed by Lombock in "Many To One" relathionship to avoid error
    @JoinColumn(name = "destination_user_fk", referencedColumnName = "id")
    private User destinationUser;

    @ManyToOne
    @EqualsAndHashCode.Exclude          // Needed by Lombock in "Many To One" relathionship to avoid error
    @JoinColumn(name = "source_wallet_fk", referencedColumnName = "id")
    private Wallet sourceWallet;

    @ManyToOne
    @EqualsAndHashCode.Exclude          // Needed by Lombock in "Many To One" relathionship to avoid error
    @JoinColumn(name = "destination_wallet_fk", referencedColumnName = "id")
    private Wallet destinationWallet;

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


    @Column(name = "timestamp",columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
    @Temporal(TemporalType.TIMESTAMP)
    private Date timestamp;
    
    @Column(name = "amount")
    private BigDecimal amount;
    
    @ManyToOne
    @EqualsAndHashCode.Exclude          // Needed by Lombock in "Many To One" relathionship to avoid error
    @JoinColumn(name = "log_type_fk", referencedColumnName = "id")
    //@Column(name = "log_type_fk")
    private LogType logType;
    
    @ManyToOne
    @EqualsAndHashCode.Exclude          // Needed by Lombock in "Many To One" relathionship to avoid error
    @JoinColumn(name = "fk_network", referencedColumnName = "id")
    private Network network;

    @ManyToOne
    @EqualsAndHashCode.Exclude          // Needed by Lombock in "Many To One" relathionship to avoid error
    @JoinColumn(name = "fk_chain", referencedColumnName = "id")
    private Chain chain;

}

Then I have this repository interface that works on my main TransactionStatusLog entity class.

public interface TransactionStatusLogRepository extends JpaRepository<TransactionStatusLog, Integer>, JpaSpecificationExecutor {

    TransactionStatusLog findById(String id);
    
    List<TransactionStatusLog> findByTransactionLog_Id(Integer transactionId);
    
    List<TransactionStatusLog> findByIsActiveAndTransactionLog_Id(Integer transactionId);
        

I created this last method definition:

List<TransactionStatusLog> findByIsActiveAndTransactionLog_Id(Integer transactionId);

trying to implement the following query: find all the TransactionStatusLog having:

  • The Boolean isActive field having value true
  • and having the TransactionLog (the second entity class) having a specific value of the ID.

Finally I created this JUnit test method in order to test this repository query:

@Test
@Order(3)
@Transactional
public void findByIsActiveAndTransactionLog_IdTest() {
    List<TransactionStatusLog> result = this.transactionStatusLogRepository.findByIsActiveAndTransactionLog_Id(37);
    
    assertTrue(result.size() == 1, "The result contains 1 tactive transaction status changes related to the transaction having ID: 37");

}

The problem is that running this test method (it doesn't start because it seems that my Spring Data JPA query by name method definition is wrong) I obtain this exception in my stacktrace:

Caused by: org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List com.easydefi.users.repository.TransactionStatusLogRepository.findByIsActiveAndTransactionLog_Id(java.lang.Integer)! Reason: Failed to create query for method public abstract java.util.List 

What is wrong in my code? How can I try to fix it? (in case also using JPQL if query by name method is too complex for a use case like this)

CodePudding user response:

Can you try again by adding "True" and removing underscore. It should seem like ->

List<TransactionStatusLog> result = this.transactionStatusLogRepository.findByIsActiveTrueAndTransactionLogId(37);

CodePudding user response:

List<TransactionStatusLog> findByIsActiveAndTransactionLog_Id(Integer transactionId); 

should accept two parameters. One for isActive and one for transactionLogId.

The correct form is:

 List<TransactionStatusLog> findByIsActiveAndTransactionLog_Id(boolean isActive, Integer transactionId); 

Then call:

 List<TransactionStatusLog> result = transactionStatusLogRepository.findByIsActiveAndTransactionLog_Id(true, 37);
  • Related