CODEWITHSUNDEEP
mysqlhibernatejpaspring-data
Hayi
Hayi

Reputation: 6286

Query return null in JPA/Hibernate but not in mysql

I'm executing a query but it return null but when i try in sql line cmd it return result.

here is my entity :

@Entity
@Table(name = "appel_offre", catalog = "ao")
public class AppelOffre implements java.io.Serializable {

    private Integer idAppelOffre;
    ...
    private Admin userValidation;
    private Admin userSaisie;
    private Admin userControle;

    ....

    public AppelOffre(Integer idAppelOffre,Admin userSaisie,Admin userControle,Admin userValidation) {
        System.out.println("users"); // <-- code not executed
        this.idAppelOffre = idAppelOffre;
        this.userSaisie = userSaisie;
        this.userControle = userControle;
        this.userValidation = userValidation;

    }

my query is :

@Query(" select new AppelOffre( ao.idAppelOffre , ao.userSaisie , ao.userControle , ao.userValidation )  from AppelOffre  ao "
 AppelOffre  FindAOwithUsers(@Param("idao") Integer idao);

the query generated is :

select appeloffre0_.ID_APPEL_OFFRE as col_0_0_, appeloffre0_.USER_SAISIE as col_1_0_, appeloffre0_.USER_CONTROLE as col_2_0_, appeloffre0_.USER_VALIDATION as col_3_0_ 
from ao.appel_offre appeloffre0_ 
inner join ao.admin admin1_ on appeloffre0_.USER_SAISIE=admin1_.ID 
inner join ao.admin admin2_ on appeloffre0_.USER_CONTROLE=admin2_.ID 
inner join ao.admin admin3_ on appeloffre0_.USER_VALIDATION=admin3_.ID 
where appeloffre0_.ID_APPEL_OFFRE=?

I know that the problem is with the inner join they must be left join because in my database just USER_SAISIE which is not null.

i try it with left join like that :

@Query(" select new AppelOffre( ao.idAppelOffre , ao.userSaisie , ao.userControle , ao.userValidation )  from AppelOffre  ao "
        + " left join ao.userSaisie  "
        + " left join ao.userControle  "
        + " left join ao.userValidation  "
         + " where ao.idAppelOffre = :idao ")
 AppelOffre  FindAOwithUsers(@Param("idao") Integer idao);

but it generated double in query :

Hibernate: select appeloffre0_.ID_APPEL_OFFRE as col_0_0_, appeloffre0_.USER_SAISIE as col_1_0_, appeloffre0_.USER_CONTROLE as col_2_0_, appeloffre0_.USER_VALIDATION as col_3_0_ 
from ao.appel_offre appeloffre0_ 
left outer join ao.admin admin1_ on appeloffre0_.USER_SAISIE=admin1_.ID 
left outer join ao.admin admin2_ on appeloffre0_.USER_CONTROLE=admin2_.ID 
left outer join ao.admin admin3_ on appeloffre0_.USER_VALIDATION=admin3_.ID 
inner join ao.admin admin4_ on appeloffre0_.USER_SAISIE=admin4_.ID 
inner join ao.admin admin5_ on appeloffre0_.USER_CONTROLE=admin5_.ID 
inner join ao.admin admin6_ on appeloffre0_.USER_VALIDATION=admin6_.ID 
where appeloffre0_.ID_APPEL_OFFRE=?

And also the System.out.println("users"); in my constructor didn't display.

why this didn't work and how can I solve this problem

Upvotes: 0

Views: 644

Answers (1)

Predrag Maric
Predrag Maric

Reputation: 24433

The constructor isn't called because the query didn't return any results. And it didn't return any results because of inner join used, you're right about that. To make it work use left join, like this

@Query(" select new AppelOffre(ao.idAppelOffre, us, uc, uv)  from AppelOffre  ao left join ao.userSaisie us left join ao.userControle uc left join ao.userValidation uv "

Upvotes: 1

Related Questions