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.
