CODEWITHSUNDEEP
spring-data-jpa
user468587
user468587

Reputation: 5031

spring data jpa for multiple joined table

I have two tables: ProductUsage and Learner. ProductUsage have field Learner, Learner has fields id and guid. now I need to create a query to pull out all productUsage whose learner guid is in specified user ids:

SQL:

select * from product_usage 
inner join learner
on product_usage.learner_id = learner.id
where 
learner.guid in ("1234", "2345")

domain class:

@Data
@NoArgsConstructor
@Entity
@Table(name = "core_product_usage_increments")
public class ProductUsage {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @ManyToOne
    @JoinColumn(name = "learner_id", nullable = false)
    private Learner learner;

    @ManyToOne
    @JoinColumn(name = "learning_language_id", nullable = false)
    private Language language;
}

@Data
@NoArgsConstructor
@Entity
@Table(name = "learners")
public class Learner {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "user_guid", nullable = false, unique = true)
    private String guid;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;
}

and repository class:

@Repository
public interface ProductUsageRepository extends CrudRepository<ProductUsage, Integer> {
    @Query("SELECT p FROM ProductUsage p WHERE p.learnerGuid = :learnerGuid")
    List<ProductUsage> findByLearnerGuid(String learnerGuid);
}

client class that call the repository 

@Component
public class MyClient {
    @Autowired
    private ProductUsageRepository repository;

    public MyClient(ProductUsageRepository repository) {
        this.repository = repository;
    }

    public List<ProductUsage> getProductUageByLeanrerGuid(String learnerGuid) {
        return repository.findByLearnerGuid(learnerGuid);
    }
}

and my test:

@Test
    public void testClient() throws Exception {
        MyClient client = new MyClient(repository);
        List<ProductUsage> results = client.getProductUageByLeanrerGuid("1234");
        assertNotNull(result);
    }

and it failed:

Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: learnerGuid of: com.acme.domain.spectrum.ProductUsage [SELECT p FROM com.acme.domain.spectrum.ProductUsage p WHERE p.learnerGuid = :learnerGuid]
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1364)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1300)
    at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:294)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)

it cannot recognize the 'learnerGuid' field in ProductUsage, but that's actually defined in Learner class. how can I perform the query that join multiple tables?

Upvotes: 3

Views: 18679

Answers (2)

Shekar
Shekar

Reputation: 81

You do not have use a @query like you did

@Query("SELECT p FROM ProductUsage p WHERE p.learnerGuid = :learnerGuid")
    List<ProductUsage> findByLearnerGuid(String learnerGuid);

Spring JPA framework can build the query by method name itself. Try this

List<ProductUsage> findByLearnerGuid(String learnerGuid);

or

List<ProductUsage> findByLearner_guid(String learnerGuid);

as you have a relation to Learner from ProductUsage the findBy method can traverse through the related tables and their fields. "_" gives the framework a clear indication that query by joining the Learner table where guid =?

Otherwise the framework tries below two combinations:

where learnerGuid=?
join learner where guid=?

Upvotes: 1

Arthur
Arthur

Reputation: 1548

ProductUsage has no learnerGuid property, only learner. Try

@Query("SELECT p FROM ProductUsage p WHERE p.learner.guid = :learnerGuid")

If that doesn't work, I have another tip:

@Query("SELECT p FROM ProductUsage p join p.Learner l WHERE l.guid = :learnerGuid")

Upvotes: 2

Related Questions