openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jewettdiane <jewettdi...@gmail.com>
Subject Re: Named Query fails but SQL works
Date Mon, 13 Jul 2009 14:43:00 GMT

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.

Mime
View raw message