CODEWITHSUNDEEP
javasqldatabasespring
Pocho
Pocho

Reputation: 51

Spring repository query with two table

I have a Database with two tables :

1) NumberEntity

|------|-------|--------|
|  id  |numero | volume |
|------|-------|--------|
|  1   |    1  |   1    |
|------|-------|--------|
|  2   |    1  |   2    |
|------|-------|--------|
|  3   |    2  |   1    |
|------|-------|--------|

2) ArticleEntity

|------|-------|------------|
|  id  |Article| numbers_id |
|------|-------|------------|
|  5   |    7  |   1        |
|------|-------|------------|
|  6   |    5  |   2        |
|------|-------|------------|
|  7   |    6  |   3        |
|------|-------|------------|

Where numbers_id is the relation with the first table. I would like to extract with a query through the first table, the articles ordered by article desc. I don't know how I can do it, I started with this query:

public List<NumberEntity> findByVolumeAndNumero(String volume, String number);

I got the list of the articles but the first is the article number 7, instead I would like to extract like first article the number 5, 6 and 7.

These are my model:

    @Entity
    public class NumberEntity implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String volume;
    private String numero;
    @OneToMany(mappedBy="numbers", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<ArticleEntity> articles = new ArrayList<ArticleEntity>();

The other:

 @Entity
    public class ArticleEntity implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String article;
    @ManyToOne
    private NumberEntity numbers;

So I need a query like this one (even if itsn't correct, but it's only pseudo code):

public List<NumberEntity> findByVolumeAndNumeroOrderByArticleDesc(String volume, String number);

The problem is, that I didn't understand how to join the other table with a single query using Spring

Upvotes: 0

Views: 4647

Answers (4)

Yrineu Rodrigues
Yrineu Rodrigues

Reputation: 1492

@Query("SELECT a FROM ArticleEntity a, NumberEntity n WHERE ...")

Upvotes: 0

RazvanParautiu
RazvanParautiu

Reputation: 2938

First of all try to not create bidirectional relationship because when you are making a select you will face a loop; in your case it is enough to create a relation ManyToOne from article to number. The query should look like this:

@Query("select * from ArticleEntity article inner join article.numbers number where article.numbers.volume=:volume and article.numbers.id=:id")
    List<ArticleEntity> findAll(@Param("volume") String volume , @Param("id") long id);

Try this query (p.s. check the name of fields to be the same as they are in java class (entity) )

After you can add to your query order desc by article.number.volume or what after you would like...

Also please read about Criteria . This way to write query is much more simple because has some method which implement a lot of SQL queries.

Upvotes: 1

Grzegorz Osak
Grzegorz Osak

Reputation: 61

entityManager.createQuery("SELECT ae FROM ArticleEntity ae WHERE ae.numbers.volume = :volume AND ae.numbers.numero = :numero ORDER BY ae.article DESC).setParameter("volume", volume).setParameter("numero", numero)

If you are using session you can swap entityManager with it.

Upvotes: 0

mrtasln
mrtasln

Reputation: 614

You can do it with using Query .Try this code .

 @Query("SELECT * FROM number n join article a on n.id = a.numbers_id order by a.id  ")

Upvotes: 1

Related Questions