CODEWITHSUNDEEP
spring-boothibernatespring-data-jpa
mamoon
mamoon

Reputation: 47

i wish to find a unique record which matches mutiple column values supplied at once

i have a spring application where i wish to find a unique record which matches mutiple column values supplied at once. How should i write my own custom method for it in an interface implementing CrudRepository

below is the model and the interface

@Entity
@Table(name = "tenant_subscriptions")
public class TenantSubscriptions {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "userId")
    private Long userId;

    @Column(name = "service_id")
    private Long serviceId;

    @Column(name = "feature_id")
    private Long featureId;

    @Column(name = "subfeature_id")
    private Long subfeatureId;

    @Column(name = "status")
    private String Status;

    @Column(name = "subscription_id")
    private String SubscriptionId;




public interface TenantSubscriptionsRepository extends CrudRepository<TenantSubscriptions, Long> {

}

Upvotes: 0

Views: 62

Answers (2)

dev_in_progress
dev_in_progress

Reputation: 2494

You don't need to write your own query if it's not something super complex.

For matching multiple column values in the same table you can use query from method name.

There is two way according to documentation and Query creation:

By deriving the query from the method name directly.

By using a manually defined query.

TenantSubscriptions findByUserIdAndServiceIdAndFeatureId(Long userId, Long serviceId, Long featureId); //Hibernate will recognize your DB object and this will work (no extra thing needs to be done)

Query:

@Query(value = "SELECT u FROM User u WHERE u.status = 'ACTIVE' AND u.creationDate <= current_date()")
List<User> findUserCandidates();

Inner join query:

@Query(value = "SELECT DISTINCT u FROM User u INNER JOIN UserAccount ua ON u.id = ua.userId WHERE ua.status = 'ACTIVE' AND ua.companyId = :companyId")
List<Bank> findBanksByCompany(Integer companyId);

Upvotes: 1

Roland Weisleder
Roland Weisleder

Reputation: 10521

You can find an entry by multiple attributes by chaining them in the interface method name. Also, Spring Data also inspects the return type of your method.

Example:

TenantSubscriptions findOneByServiceIdAndFeatureId(Long serviceId, Long featureId);

This will return the one entry that matches both attributes.

See also this answer and the Spring Data Reference Guide.

Upvotes: 0

Related Questions