Question

I m new to EJB and trying to get my head around translating SQL concepts to EJB entity beans.

Suppose I have two tables: PEOPLE (id, name), CONTACT(pid, phone_number). If I want to get a list of all people whether or not they have phone #s, in my EJB session bean I simply issue a SQL query via JDBC such as:

SELECT PEOPLE.name, CONTACT.phone_number 
FROM PEOPLE 
LEFT JOIN CONTACT ON PEOPLE.id = CONTACT.pid

Instead of using SQL/JDBC, I now want to use EJB entity beans. So I create corresponding EJB3 entity bean classes for my tables.

So I now have access to both entity classes from my session bean and I no longer wish to access my database tables directly via SQL/JDBC from my session bean. I only want to use my entity beans and capabilities of JPA. What s the proper EJB design so that in my session bean, I get the same results as my SQL query?

I m unclear about how to use the EJB entity bean classes to produce the same results as my SQL outer join query. Help.

Answer 1

First, create two JPA entities, something like that for People:

@Entity
@Table( name="PEOPLE" )
public class People {

    @Id @Column
    private Long id;

    @Column
    private String name;

    @OneToOne
    @JoinColumn( name="pid" )
    private Contact contact;

    // getters and setters
}

And for Contact:

@Entity
@Table( name="CONTACT" )
public class Contact {

    @Id @Column
    private Long pid;

    @Column
    private String phoneNumber;

    // getters and setters
}

Then, to generate the OUTER JOIN query using JPQL:

SELECT p FROM People p LEFT JOIN p.contact c

友情链接