I found the issue is with the SOR field in the named query.
I tried different queries and found that is the one that did not work.
Looks like the Oracle mapping is the following:
CUST_ORG_ID VARCHAR2(50),
OOID VARCHAR2(20),
SOR CHAR(20),
My JPA:
@Column(name="SOR", length=20)
private String sor;
Is there a different way to Search on a CHAR field?
Thanks
jewettdiane wrote:
>
> 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-tp3237379p3268356.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
