I posted to the Spring forum, but have no responses yet.
This is my spring config:
<bean id="entityManagerFactory"
class="org.springframework.orm.jpa.LocalEntityManagerFactoryBean">
<property name="jpaVendorAdapter">
<bean
class="org.springframework.orm.jpa.vendor.OpenJpaVendorAdapter">
<property name="showSql" value="true" />
<property name="generateDdl" value="false" />
<property name="database" value="ORACLE" />
</bean>
</property>
<property name="persistenceUnit" ref="EI9DS" />
<property name="jpaPropertyMap">
<map>
<entry key="openjpa.Log" value="File=stdout,
DefaultLevel=TRACE,
SQL=TRACE" />
<entry key="openjpa.jdbc.DBDictionary"
value="oracle" />
<entry key="openjpa.TransactionMode"
value="managed" />
<entry key="openjpa.ConnectionFactoryMode"
value="managed" />
<entry key="openjpa.jdbc.TransactionIsolation"
value="read-committed" />
<entry key="openjpa.ConnectionFactoryProperties"
value="PrettyPrint=true, PrettyPrintLineLength=72" />
</map>
</property>
<property name="jpaDialect">
<bean
class="org.springframework.orm.jpa.vendor.OpenJpaDialect" />
</property>
</bean>
<!--
Use websphere JTA transaction manager.
-->
<tx:jta-transaction-manager />
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="get*" propagation="SUPPORTS"
read-only="true" />
<tx:method name="find*" propagation="SUPPORTS"
read-only="true" />
<tx:method name="*" propagation="REQUIRED"
read-only="false" />
</tx:attributes>
</tx:advice>
<!--
Apply transaction advice to the DAO methods
-->
<aop:config>
<aop:pointcut id="daoMethods"
expression="execution(*
com.adp.ei9.common.dao.*DAO.*(..))" />
<aop:advisor advice-ref="txAdvice" pointcut-ref="daoMethods" />
</aop:config>
<!-- DAOs -->
<bean id="baseDAO" class="com.adp.ei9.common.dao.BaseDaoImpl"
abstract="true">
<property name="entityManagerFactory"
ref="entityManagerFactory" />
</bean>
<bean id="organizationDAO"
class="com.adp.ei9.common.dao.OrganizationDAOImpl"
parent="baseDAO" />
And persistence:
<persistence-unit name="EI9DS" transaction-type="JTA">
<provider>
org.apache.openjpa.persistence.PersistenceProviderImpl
</provider>
<jta-data-source>jdbc/EI9DS</jta-data-source>
<non-jta-data-source>jdbc/EI9DS</non-jta-data-source>
<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>
</persistence-unit>
BaseDAO:
public abstract class BaseDAOImpl<T, PK extends Serializable> extends
JpaDaoSupport implements BaseDAO<T, PK> {
private Class<T> type;
@SuppressWarnings("unchecked")
public BaseDAOImpl() {
this.type = (Class<T>) ((ParameterizedType) getClass()
.getGenericSuperclass()).getActualTypeArguments()[0];
}
public Collection findByNamedQueryAndNamedParams(String queryName,
Map<String, Object> params) {
return
getJpaTemplate().findByNamedQueryAndNamedParams(queryName,
params);
}
public Collection find(String query) {
return getJpaTemplate().find(query);
}
Organization:
@NamedQueries({
@NamedQuery (
name = "Organization.findOrgByCustOrgId",
query = "SELECT o FROM Organization o WHERE o.custOrgId =
:custOrgId"
),
@NamedQuery (
name = "Organization.findOrgByOoidSOR",
query = "SELECT new Organization(o.orgId, o.orgName) FROM
Organization
o WHERE o.ooid = :ooid AND o.sor = :sor"
),
@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"
)
})
@Entity
@Table( name="ORGANIZATION")
@SequenceGenerator(name="OrgSeq", sequenceName="SEQ_ORGANIZATION")
public class Organization extends EntityBase implements Serializable {
@Basic
@Column(name="CUST_ORG_ID", length=50)
private String custOrgId;
@Basic
@Column(name="OOID", length=20)
private String ooid;
@Basic
@Column(name="SOR", length=20)
private String sor;
......
I could try and eliminate the jpaTemplate, but I was trying to get Spring to
work also.
Thanks
Kevin Sutter wrote:
>
> 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.
>>>
>>
>>
>
>
--
View this message in context:
http://n2.nabble.com/Named-Query-fails-but-SQL-works-tp3237379p3263731.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
