CODEWITHSUNDEEP
openjpawid
SoftwareSavant
SoftwareSavant

Reputation: 9737

Recreating a query using OpenJPA

THere is a oracle query that I am trying to recreate using OpenJPA. I am writing a service in Websphere integration developer, and I am using OpenJPA as my ORM tool of choice. Previously this query was performed using a stored proc, a ref cursor was used and the information was retrieved like that. Now we are trying to use OpenJPA as our tool of choice. So I am thinking that I should then reconstruct the stored proc using OpenJPA... 

SELECT DISTINCT
   P.col1 as ID,
   P.col2,
   P.col3,
   P.col4,
   P.col5,
   S.col6,
   PC.col7,
   P.col8,
   A.ADDRESS_1,
   A.ADDRESS_2,
   A.ADDRESS_3,
   A.CITY,
   A.COUNTY,
   A.STATE,
   A.ZIP_CODE,
   P.CONFIRMED_BY,
   P.CONFIRMED_DATE,
   P.MOD_USERID,
   P.MOD_DATE
FROM   EPCD13.PROVIDER P, EPCD13.provider_channel PC, EPCD13.provider_channel_link pcl,
   EPCD13.provider_specialty ps, EPCD13.SPECIALTY S, EPCD13.Address A, EPCD13.ADDRESS_LINK AL
WHERE P.RESOURCE_ID = personID
  AND P.RESOURCE_ID = PS.RESOURCE_ID (+)
  AND 1 = PS.PRIMARY_SPECIALTY_ID (+)
  AND PS.SPECIALTY_ID = S.SPECIALTY_ID (+)
  AND P.RESOURCE_ID = PCL.RESOURCE_ID (+)
  AND PCL.PROVIDER_CHANNEL_ID = PC.PROVIDER_CHANNEL_ID
  AND 1 = PCL.PREFERENCE (+)
  AND 9 = pc.channel_type_id (+)
  AND PC.CHANNEL_ADDRESS NOT LIKE '%@%'
  AND P.RESOURCE_ID = AL.RESOURCE_ID (+)
  AND AL.ADDRESS_ID = A.ADDRESS_ID (+)
  AND 1 = A.ADDRESS_TYPE_ID (+) 
  AND 1 = AL.PREFERENCE (+);

Notice all those inner Joins and so forth. I am thinking right now of putting a named query in one my methods that will return the same results as above. As you may note, there are multiple tables that are being called there and joined at various points... I am thinking I can just put this query into the createNamedQuery() function with minor changes... But I am thinking there has to be a simpler way to do this? Maybe not. Can I just call a stored proc using JPA?

Upvotes: 0

Views: 158

Answers (2)

Victor
Victor

Reputation: 17077

You can create a JPA entity for each table and then join the entities by doing something similar to this: http://openjpa.apache.org/builds/1.1.1-SNAPSHOT/docs/jpa_overview_mapping_full.html Look at the @OnetoMany and @ManytoOne and @ManytoMany annotations in the example for some ideas.

Upvotes: 0

Yaocl
Yaocl

Reputation: 194

Because your SQL is very complex it's not easy to convert to JPQL, I suggest preserve it. You can use OpenJPA's NativeQuery, which can do SQL query. If your SQL is not started with SELECT, it will be treated as stored proc.

Upvotes: 1

Related Questions