openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kevin Sutter <kwsut...@gmail.com>
Subject Re: Named Query fails but SQL works
Date Tue, 14 Jul 2009 17:52:21 GMT
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 <jewettdiane@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 <jewettdiane@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 <jewettdiane@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.
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message