Query return null in JPA/Hibernate but not in mysql

Question

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

Predrag Maric · Accepted Answer · 2015-04-14 15:01:14Z

1

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 "

answered Apr 14, 2015 at 15:01

Predrag Maric

24.5k5 gold badges57 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hayi Over a year ago

thakns man i already try left join (i update my question) but it didn't work so i use alias us, uc, uv and it works :)

Predrag Maric Over a year ago

Yes, if you use ao.userSaisie in select clause, regardless of left joins in from clause, the SQL will use inner join. And you're welcome.

Collectives™ on Stack Overflow

Query return null in JPA/Hibernate but not in mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related