conditional join clause

[Ado <cc...@gmail.com>]

Hi, I would like to ask for help with creating query from criteria api, but
I want to simulate the conditional join clause from standard SQL, for
example

"from VOUCHER v left join USER_VOUCHER uv on ((v.VOUCHER_ID =
uv.VOUCHER_ID) and uv.USER_ID=2).."

More info:

I have two entities mapped to tables

@Entity
@Table(name = "voucher")
public class Voucher implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "VOUCHER_ID")
    private Integer voucherId;
    @Column(name = "VOUCHER_NAME")
    private String voucherName;
    @OneToMany(mappedBy = "voucherId")
    private Collection<UserVoucher> userVoucherCollection;

    public Voucher() {
    }
//rest ommited
}

and

@Entity
@Table(name = "user_voucher")
public class UserVoucher implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "USER_VOUCHER_ID")
    private Integer userVoucherId;
    @JoinColumn(name = "USER_ID", referencedColumnName = "USER_ID")
    @ManyToOne
    private User userId;
    @JoinColumn(name = "VOUCHER_ID", referencedColumnName = "VOUCHER_ID")
    @ManyToOne
    private Voucher voucherId;

    public UserVoucher() {
    }
//rest omitted
}

I want to select all the vouchers in db and additional info about the
relationship for specified userId. Even if there is no record in
user_voucher for userId=2, I want to get it from database. I can get it
with native sql with the condition mentioned at the start. But when using
the criteria api and inserting the id as predicate, it is no longer left
(outer) join, but an inner joins nad the records are not returned.


Thanks in advance for any help
Andrej

Please consider your environmental responsibility before printing this
e-mail.

Re: conditional join clause

[Albert Lee <al...@gmail.com>]

By default @OneToMany is lazy fetch.

You may want to try:
1) make the userVoucherCollection collection eager fetch, or
2) use "left join fetch" in the JPQL (either using static or criteria) to
force it to load the collection.



On Fri, Jul 25, 2014 at 5:53 AM, Ado <cc...@gmail.com> wrote:

> Hi, I would like to ask for help with creating query from criteria api, but
> I want to simulate the conditional join clause from standard SQL, for
> example
>
> "from VOUCHER v left join USER_VOUCHER uv on ((v.VOUCHER_ID =
> uv.VOUCHER_ID) and uv.USER_ID=2).."
>
> More info:
>
> I have two entities mapped to tables
>
> @Entity
> @Table(name = "voucher")
> public class Voucher implements Serializable {
>     private static final long serialVersionUID = 1L;
>     @Id
>     @GeneratedValue(strategy = GenerationType.IDENTITY)
>     @Basic(optional = false)
>     @Column(name = "VOUCHER_ID")
>     private Integer voucherId;
>     @Column(name = "VOUCHER_NAME")
>     private String voucherName;
>     @OneToMany(mappedBy = "voucherId")
>     private Collection<UserVoucher> userVoucherCollection;
>
>     public Voucher() {
>     }
> //rest ommited
> }
>
> and
>
> @Entity
> @Table(name = "user_voucher")
> public class UserVoucher implements Serializable {
>     private static final long serialVersionUID = 1L;
>     @Id
>     @GeneratedValue(strategy = GenerationType.IDENTITY)
>     @Basic(optional = false)
>     @Column(name = "USER_VOUCHER_ID")
>     private Integer userVoucherId;
>     @JoinColumn(name = "USER_ID", referencedColumnName = "USER_ID")
>     @ManyToOne
>     private User userId;
>     @JoinColumn(name = "VOUCHER_ID", referencedColumnName = "VOUCHER_ID")
>     @ManyToOne
>     private Voucher voucherId;
>
>     public UserVoucher() {
>     }
> //rest omitted
> }
>
> I want to select all the vouchers in db and additional info about the
> relationship for specified userId. Even if there is no record in
> user_voucher for userId=2, I want to get it from database. I can get it
> with native sql with the condition mentioned at the start. But when using
> the criteria api and inserting the id as predicate, it is no longer left
> (outer) join, but an inner joins nad the records are not returned.
>
>
> Thanks in advance for any help
> Andrej
>
> Please consider your environmental responsibility before printing this
> e-mail.
>



-- 
Albert Lee.