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 <jewettdi...@gmail.com> > 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 <jewettdi...@gmail.com> >> > 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 <jewettdi...@gmail.com> >> >> > 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.