Accessing multiple tables

Question

I am learning EJB,JPA and have very basic doubt

I have 3 Tables A,B,C

A - ID, Name

B - ID, Name

C - ID, A_ID, B_ID

When i create a Entity Class from Database, i get 3 EJB classes with JPA stuff.

Now in my Managed bean i get either A.Name or B.Name and i need to find the matching entries using C.

Normal SQL Query will look like (may not be the best query)

SELECT a.name FROM schema.A a, schema.B b, schema.C c where b.Name='ABC' and c.B_ID=b.ID and a.ID = c.A_ID;

Now where do i do the above query in my classes.

I came across @SecondaryTable but could not understand how exactly its used.

I also saw em.createQuery( SQL query).getResultList().

Now is the above the best way or is there something available in EJB/JPA which is better.

UPDATE 1:

I was trying to execute the query in em.CreateQuery

em.CreateQuery(SELECT a.name FROM A a, B b, C c where b.Name='ABC' and c.B_ID=b.ID and a.ID = c.A_ID).getResultList();

but i get following error in my GlassFish Server (i am using EclipeLink JPA)

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
  Position: 
Error Code: 0
Call: SELECT t0.given_name FROM test.a t3, test.a_b t2, test.b t1, test.a t0 WHERE ((((t1.Name = ?) AND (t2.b_id = t1.id.t1.id)) AND (t0.id = )) AND (t3.id = t2.a_id))
bind => [1 parameter bound]
Query: ReportQuery(referenceClass=Consultant sql="SELECT t0.given_name FROM test.a t3, test.a_b t2, test.b t1, test.a t0 WHERE ((((t1.Name = ?) AND (t2.b_id = t1.id.t1.id)) AND (t0.id = )) AND (t3.id = t2.a_id))")

Now why is the SQl statement is messed up in the error log

1.There is an extra entry test.a t0

2.t2.b_id = t1.id.t1.id

3.t0.id=

How does the error log SQL statement gets generated.

Answer 1

As C is a many to many relationship between A and B it shouldn't be an entity. However I don't think JPA likes it that your join table (C) has it's own ID column. If possible remove the ID column from C and make the combination of A_ID, B_ID your primary key.

Then the entity class A could have:

@JoinTable(name = "C",
    joinColumns = { @JoinColumn(name = "A_ID", referencedColumnName = "ID") },
    inverseJoinColumns = { @JoinColumn(name = "B_ID", referencedColumnName = "ID") })
@ManyToMany
private Collection<B> bCollection;

I think it is clear what all those annotations mean. Class B would have:

@ManyToMany(mappedBy = "bCollection") 
private Collection<A> aCollection;

The mappedBy attribute tells JPA to use the JoinTable defenition of A::bCollection (A is deduced from the type Collection<A> of the field).

Now if you have an instance of A you can easily get all the B's for that A by getting the property. No need for any SQL/JPQL.

Now as for executing queries you should know that you have JPQL and SQL. JPQL is Java Persistence Query Language and is the language of JPA. SQL is the native language of your database. To execute SQL you need to use the createNativeQuery family of functions. The createQuery functions are for JPQL.

You should prefer JPQL over SQL. The most important difference between the two is that JPQL works on your entities and expect all identifiers to correspond to the names used for your classes and properties. For example if the Name column (first letter uppercase) is mapped to a property called name (lower case) then in a JPQL query you should use name (lower case). Same for entity class names and corresponding table names. JPQL also has build in support for the join table of the many to many relation ship the JPQL query for what you want would be

SELECT a
FROM B b JOIN b.aCollection a
WHERE b.name='ABC'

No need to specify all the join conditions JPA knows them from the annotations on your classes.

@SecondaryTable is not relevant here it is used when a single entity is split into more then one table.