One other thing... Is the persistence.xml that you posted in the original e-mail complete? Specifically, I'm wondering whether you are using any of the caching capabilities within OpenJPA? I found a rather old posting on our forum [1] about using getJpaTemplate that returned "old" cached results instead of going to the database. The string of postings didn't look to be fully resolved. Just an idea to follow up on. Thanks.
[1] http://n2.nabble.com/Cache-Problem-td1092833.html#a1094743 On Wed, Jul 15, 2009 at 8:21 AM, Kevin Sutter <[email protected]> wrote: > Well, it's not really OpenJPA that turns this "find" back into a query. It > looks like it's Spring's getJpaTemplate that does the translation. Their > template is supposed to wrapper the provider (OpenJPA, Hibernate, etc). It > looks like they are trying to provide another programming model layer on top > of JPA. The translation of their "find" operation to Hibernate looks to be > different from their translation to OpenJPA. > > I'd like to understand the differences since I'd like to have OpenJPA work > with Spring. I just don't know enough about the getJpaTemplate to give a > complete answer. Have you tried touching base with Spring to see if they > have some ideas on how to resolve this? Or, maybe they are pointing fingers > back at us? :-) > > Thanks, > Kevin > > > On Tue, Jul 14, 2009 at 3:35 PM, jewettdiane <[email protected]>wrote: > >> >> The other thing I found is that the code: >> >> getJpaTemplate().find("SELECT new Organization(o.orgId, o.orgName) FROM >> Organization o where o.custOrgId='"+custOrgId+"' and o.ooid='"+ooid+"' and >> o.sor='"+sor+"'"); >> >> Only works for hibernate. Looks like openjpa turns this find operation >> back >> to a sql >> with parameters. >> >> openjpa.Query - Executing query: SELECT new Organization(o.orgId, >> o.orgName) >> FROM Organization o where o.custOrgId='AR' and o.ooid='ABC123123' and >> o.sor='Enterprise' >> openjpa.jdbc.SQL - <t 175639160, conn 570761733> executing prepstmnt >> 574169657 >> SELECT t0.ORG_ID, t0.ORG_NAME >> FROM ORGANIZATION t0 >> WHERE (t0.CUST_ORG_ID = ? AND t0.OOID = ? AND t0.SOR = ?) >> [params=(String) AR, (String) ABC123123, (String) Enterprise] >> >> >> >> Kevin Sutter wrote: >> > >> > Ahhh... I don't remember you indicating that you were using Spring... >> > :-) >> > Using wrappers like getJpaTemplate() around the JPA invocations can >> > introduce some anomalies... >> > >> > I would still be interested in understanding the issue after you touch >> > base >> > with Spring. For the most part, getJpaTemplate should be a pass-thru >> type >> > wrapper, but they must be doing something unique in this case. >> > >> > Thanks, >> > Kevin >> > >> > On Tue, Jul 14, 2009 at 11:06 AM, jewettdiane <[email protected]> >> > wrote: >> > >> >> >> >> I find out more. It is not open jpa problem. It looks like jpatemplate. >> >> >> >> This works: >> >> getJpaTemplate().find("SELECT new Organization(o.orgId, o.orgName) FROM >> >> Organization o where o.custOrgId='"+custOrgId+"' and o.ooid='"+ooid+"' >> >> and >> >> o.sor='"+sor+"'"); >> >> >> >> I was using: >> >> @NamedQuery ( >> >> name = "Organization.findOrgByCustOoidSOR", >> >> query = "SELECT new Organization(o.orgId, o.orgName) >> >> FROM Organization >> >> o WHERE o.custOrgId = :custOrgId AND o.ooid = :ooid AND o.sor = :sor" >> >> ) >> >> >> >> Map<String, Object> namedParams = new HashMap<String, Object>(); >> >> namedParams.put("custOrgId", custOrgId.trim()); >> >> namedParams.put("ooid", ooid.trim()); >> >> namedParams.put("sor", sor.trim()); >> >> >> >> getJpaTemplate().findByNamedQueryAndNamedParams(queryName, >> >> params); >> >> >> >> It must not be mapping the parameters correctly. >> >> >> >> >> >> >> >> Kevin Sutter wrote: >> >> > >> >> > Hmmm... Your find() operation is not quite the same as your query. >> >> > Besides >> >> > the obvious of using a single input parameter for the find and the >> >> three >> >> > input parameters for the query, the find operation is based off the >> >> ORG_ID >> >> > field and the query is not using that field at all. The query seems >> to >> >> be >> >> > using CUST_ORG_ID, ooid, and sor fields. So, I don't know if we're >> >> really >> >> > comparing apples to apples. But, then I don't know your schema and >> app >> >> > all >> >> > that well... >> >> > >> >> > In your first note, you claim that the generated SQL works in a >> command >> >> > prompt. You mention that you only have one database, but what about >> >> > multiple tables? I don't know Oracle all that well, but is there a >> >> chance >> >> > that you have multiple ORGANIZATION tables qualified by different >> >> schema >> >> > names? So, the command line access is going against one table and >> the >> >> > application is accidentally going against an empty table? >> >> > >> >> > But, then your find() probably wouldn't find anything, even with the >> >> > multiple id fields... >> >> > >> >> > I'm grabbing at straws. But, given what you've told us thus far, I >> >> can't >> >> > figure out how generated SQL would produce different results from >> >> within >> >> > JPA >> >> > vs a command line. Not sure if it will tell me anything of interest, >> >> but >> >> > could you provide your OpenJPA SQL trace file? >> >> > >> >> > Thanks, >> >> > Kevin >> >> > >> >> > On Mon, Jul 13, 2009 at 9:43 AM, jewettdiane <[email protected]> >> >> > wrote: >> >> > >> >> >> >> >> >> It finds the record if I just run >> getEntityManager().find(entityClass, >> >> >> id): >> >> >> >> >> >> SELECT t0.LST_UPDT_CD, t0.LST_UPDT_TMP, t0.LST_UPDT_USER_ID, >> >> >> t0.ADP_ACCOUNT_TYPE_CD, t0.BILLING_CYCLE_CD, >> >> >> t0.BILLING_FIRST_DT, t0.CONTRACT_SIGNED_DT, t0.CUST_ORG_ID, >> >> >> t0.DUNS_NBR, t0.fein, t0.INTEGRATION_HOST_SYS_ID, >> >> >> t0.INTEGRATION_TYPE_CD, t0.NAICS_CD, t0.NETSECURE_CLIENT_ID, >> >> >> t0.NETSECURE_STATUS, t0.ooid, t0.ORG_DBA_NAME, t0.ORG_NAME, >> >> >> t0.ORG_TYPE_ID, t0.ORG_URL, t0.EMPLOYER_ORG_ID, >> >> >> t0.PARENT_ORG_ID, t0.PAYROLL_VENDOR_CD, t0.SERVICES_BEGIN_DT, >> >> >> t0.SERVICES_END_DT, t0.SIC_CD, t0.sor >> >> >> FROM ORGANIZATION t0 >> >> >> WHERE t0.ORG_ID = ? >> >> >> [params=(long) 2] >> >> >> >> >> >> I only have one database running. >> >> >> I am running in RAD 7 with a local Oracle database. >> >> >> >> >> >> Here is the code: >> >> >> @NamedQuery ( >> >> >> name = "Organization.findOrgByCustOoidSOR", >> >> >> query = "SELECT o FROM Organization o WHERE >> >> >> o.custOrgId >> >> >> = :custOrgId >> >> >> AND o.ooid = :ooid AND o.sor = :sor" >> >> >> ) >> >> >> >> >> >> public Organization findOrganizationByCustSOR(String custOrgId, >> >> >> String ooid, String sor) { >> >> >> Map<String, Object> namedParams = new HashMap<String, >> >> >> Object>(); >> >> >> namedParams.put("custOrgId", custOrgId.trim()); >> >> >> namedParams.put("ooid", ooid.trim()); >> >> >> namedParams.put("sor", sor.trim()); >> >> >> return >> >> >> >> >> >> findSingleByNamedQueryAndNamedParams("Organization.findOrgByCustOoidSOR", >> >> >> namedParams); >> >> >> } >> >> >> >> >> >> >> >> >> public T findSingleByNamedQueryAndNamedParams(String queryName, >> >> >> Map<String, Object> params) { >> >> >> Query query = >> >> >> getEntityManager().createNamedQuery(queryName); >> >> >> for (String param : params.keySet()) { >> >> >> Object obj = params.get(param); >> >> >> if (obj instanceof Date) { >> >> >> query.setParameter(param, (Date) obj, >> >> >> TemporalType.TIMESTAMP); >> >> >> } else { >> >> >> query.setParameter(param, obj); >> >> >> } >> >> >> } >> >> >> try { >> >> >> >> >> >> return (T) query.getSingleResult(); >> >> >> } catch (NoResultException noResult) { >> >> >> LOGGER.warn("No Single Result"); >> >> >> } >> >> >> return null; >> >> >> } >> >> >> >> >> >> >> >> >> >> >> >> Kevin Sutter wrote: >> >> >> > >> >> >> > Hate to state the obvious, but is it possible that your sql window >> >> is >> >> >> > accessing the same database instance as your jpa application >> >> >> (production >> >> >> > vs >> >> >> > test vs sandbox, etc)? Your persistence.xml doesn't have the url >> >> for >> >> >> the >> >> >> > database, so is it possible that your application configuration is >> >> >> going >> >> >> > to >> >> >> > a different instance? >> >> >> > >> >> >> > Sorry, but that's about the only reason that I can come up with. >> >> You >> >> >> have >> >> >> > sql trace turned on, so you should be able to see if any results >> are >> >> >> being >> >> >> > returned by jdbc. And, any results should be massaged into >> objects >> >> for >> >> >> > the >> >> >> > application. >> >> >> > >> >> >> > Kevin >> >> >> > >> >> >> > On Fri, Jul 10, 2009 at 2:29 PM, jewettdiane < >> [email protected]> >> >> >> > wrote: >> >> >> > >> >> >> >> >> >> >> >> I have a named query that produces the following sql: >> >> >> >> SELECT t0.ORG_ID, t0.LST_UPDT_CD, t0.LST_UPDT_TMP, >> >> >> t0.LST_UPDT_USER_ID, >> >> >> >> t0.ADP_ACCOUNT_TYPE_CD, t0.BILLING_CYCLE_CD, >> >> >> >> t0.BILLING_FIRST_DT, t0.CONTRACT_SIGNED_DT, >> t0.CUST_ORG_ID, >> >> >> >> t0.DUNS_NBR, t0.fein, t0.INTEGRATION_HOST_SYS_ID, >> >> >> >> t0.INTEGRATION_TYPE_CD, t0.NAICS_CD, >> t0.NETSECURE_CLIENT_ID, >> >> >> >> t0.NETSECURE_STATUS, t0.ooid, t0.ORG_DBA_NAME, >> t0.ORG_NAME, >> >> >> >> t0.ORG_TYPE_ID, t0.ORG_URL, t0.EMPLOYER_ORG_ID, >> >> >> >> t0.PARENT_ORG_ID, t0.PAYROLL_VENDOR_CD, >> >> t0.SERVICES_BEGIN_DT, >> >> >> >> t0.SERVICES_END_DT, t0.SIC_CD, t0.sor >> >> >> >> FROM ORGANIZATION t0 >> >> >> >> WHERE (t0.CUST_ORG_ID = ? AND t0.ooid = ? AND t0.sor = ?) >> >> >> >> [params=(String) AR, (String) ABC123123, (String) Enterprise] >> >> >> >> >> >> >> >> When I paste this sql in my database sql window. I get a result >> but >> >> >> when >> >> >> >> I >> >> >> >> run it on my server it returns a NoResultException. >> >> >> >> >> >> >> >> My persistence.xml: >> >> >> >> <persistence-unit name="EI9DS" transaction-type="RESOURCE_LOCAL"> >> >> >> >> <provider> >> >> >> >> org.apache.openjpa.persistence.PersistenceProviderImpl >> >> >> >> </provider> >> >> >> >> >> >> >> >> >> <class>com.adp.ei9.common.entity.EntityBase</class> >> >> >> >> <class>com.adp.ei9.common.entity.Employee</class> >> >> >> >> >> <class>com.adp.ei9.common.entity.Employment</class> >> >> >> >> <class>com.adp.ei9.common.entity.I9User</class> >> >> >> >> >> >> <class>com.adp.ei9.common.entity.Organization</class> >> >> >> >> <class>com.adp.ei9.common.entity.OrgType</class> >> >> >> >> <properties> >> >> >> >> <property name="openjpa.TransactionMode" >> >> >> >> value="managed" /> >> >> >> >> <property >> >> name="openjpa.ConnectionFactoryMode" >> >> >> >> value="managed" /> >> >> >> >> <property name="openjpa.Log" >> >> >> >> value="File=stdout, >> >> DefaultLevel=TRACE, >> >> >> >> SQL=TRACE" /> >> >> >> >> <property name="openjpa.jdbc.DBDictionary" >> >> >> >> >> >> >> >> value="org.apache.openjpa.jdbc.sql.OracleDictionary" /> >> >> >> >> <property name="openjpa.ConnectionFactoryProperties" >> >> >> >> value="PrettyPrint=true, PrettyPrintLineLength=72" /> >> >> >> >> </properties> >> >> >> >> </persistence-unit> >> >> >> >> >> >> >> >> What else should I try? It creates good sql that works. Is there >> a >> >> >> reason >> >> >> >> objects won't return when the sql works? >> >> >> >> >> >> >> >> Thanks >> >> >> >> -- >> >> >> >> View this message in context: >> >> >> >> >> >> >> >> >> >> http://n2.nabble.com/Named-Query-fails-but-SQL-works-tp3237379p3237379.html >> >> >> >> Sent from the OpenJPA Users mailing list archive at Nabble.com. >> >> >> >> >> >> >> > >> >> >> > >> >> >> >> >> >> -- >> >> >> View this message in context: >> >> >> >> >> >> http://n2.nabble.com/Named-Query-fails-but-SQL-works-tp3237379p3250844.html >> >> >> Sent from the OpenJPA Users mailing list archive at Nabble.com. >> >> >> >> >> > >> >> > >> >> >> >> -- >> >> View this message in context: >> >> >> http://n2.nabble.com/Named-Query-fails-but-SQL-works-tp3237379p3257702.html >> >> Sent from the OpenJPA Users mailing list archive at Nabble.com. >> >> >> > >> > >> >> -- >> View this message in context: >> http://n2.nabble.com/Named-Query-fails-but-SQL-works-tp3237379p3259236.html >> Sent from the OpenJPA Users mailing list archive at Nabble.com. >> > >
