File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes getResultsList() returning nothing through OracleTopLink..works fine in Hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "getResultsList() returning nothing through OracleTopLink..works fine in Hibernate" Watch "getResultsList() returning nothing through OracleTopLink..works fine in Hibernate" New topic
Author

getResultsList() returning nothing through OracleTopLink..works fine in Hibernate

Chintan B Shah
Ranch Hand

Joined: Sep 23, 2008
Posts: 83
Hi everyone,

I am newbie to ORM and trying to run a simple select statement to fetch data by comparing first name using OracleTOPlink. I have tried following different options

1.em.createQuery("select o from FProfile as o where o.firstName='"+firstname+"'").getResultList(); //no luck
2. //return em.createQuery("select o from FProfile as o where o.firstName= ':firstname'").setParameter("firstname",firstname).getResultList();//no luck
3. em.createQuery("select object(o) from FProfile as o where o.firstName='"+firstname+"'").getResultList();//no luck
4. em.createQuery("select o from FProfile o where o.firstName like :firstname").setParameter("firstname",'%'+firstname+'%').getResultList();

The only option that works is 4.

However, if I remove the % sign, it stops working..and returns nothing.

Can someone explain me why is this happening?

The following query works wonderfully fine in Hibernate

em.createQuery("select o from Profile as o where o.firstName='"+firstname+"'

If I do the same thing through Oracle TopLink it fails.

The following query also fails in toplink.
em.createQuery("select o from FProfile o where o.firstName =:firstname").setParameter("firstname",'%'+firstname+'%').getResultList();

If i remove % from above query, then it returns nothing, but as soon as I put LIKE operator instead of = query works.

I would like to understand the difference...as to why do I have to put '%' sign and LIKE operator and do setParameters to make it work in Oracle Toplink.

Let me know if I am doing something wrong.

Thanks in advance.
Regards,
Chintan.


SCJA 1.0
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
My guess would be your field is a CHAR field, not a VARCHAR and TopLink uses parameter binding (parameterized SQL) by default, where as Hibernate does not (dynamic SQL). Your database would seem to have an issue with binding comparisons to CHAR fields, because the fields are space padded so comparisons (and other operations) can fail.

You can disable binding in TopLink through the persistence.xml property, "eclipselink.jdbc.bind-parameters"="false".

You could also disable binding for just the query.

In general I would recommend you use VARCHAR not CHAR for variable length strings. If you space padded the string to the size of the CHAR column it would also work with binding on.


TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Chintan B Shah
Ranch Hand

Joined: Sep 23, 2008
Posts: 83
Hi James,

Thanks a lot for reply. I apologize that I did not post the table structure but yes you are right..the fields that I am querying on are CHAR(100).

Unfortunately, I wont be able to change DB table structure now(as I will have to change code too)..probably its bad on our side to design it that way.

However,I would like to try the options that you have mentioned

1. You can disable binding in TopLink through the persistence.xml property, "eclipselink.jdbc.bind-parameters"="false".

I am posting my persistence.xml here..As its Netbeans oriented..I could not find the parameter that you have mentioned.

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
<persistence-unit name="OracleAndMySQL-ejbPU2" transaction-type="JTA">
<jta-data-source>OracleDS</jta-data-source>
<properties/>
</persistence-unit>
<persistence-unit name="OracleAndMySQL-ejbPU" transaction-type="JTA">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>Synchronization</jta-data-source>
<properties/>
</persistence-unit>
</persistence>

2. You could also disable binding for just the query.
-->How can I do this?

3. If you space padded the string to the size of the CHAR column it would also work with binding on.
-->I will try this..but this basically means, I have to get string length and set it to 100 dynamically(if not).

Here is the table structure and I am querying on state, firstname, lastname, city, country and zipcode.

SQL> desc f_profile;
Name Null? Type
----------------------------------------- -------- ----------------

USER_ID NOT NULL NUMBER
FIRST_NAME NOT NULL CHAR(100)
LAST_NAME NOT NULL CHAR(100)
EMAIL NOT NULL VARCHAR2(4000)
ADDRESS NOT NULL VARCHAR2(4000)
CITY NOT NULL CHAR(100)
STATE NOT NULL CHAR(100)
ZIPCODE NOT NULL NUMBER
COUNTRY NOT NULL CHAR(100)
PASSWORD NOT NULL VARCHAR2(20)

Thanks a lot for your response. Not sure why DB person kept it CHAR instead of VARCHAR2.


Regards,
Chintan.
Chintan B Shah
Ranch Hand

Joined: Sep 23, 2008
Posts: 83
Thanks for reply James.
Your reply was extremely helpful.

I was able to work out the following option.

3. If you space padded the string to the size of the CHAR column it would also work with binding on.
-->I will try this..but this basically means, I have to get string length and set it to 100 dynamically(if not).


Thanks once again.

Regards,
Chintan.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
For the first option, you can replace your <properties/> tag with the below, in your persistence.xml.



For second option,


[ October 22, 2008: Message edited by: Adeel Ansari ]
Chintan B Shah
Ranch Hand

Joined: Sep 23, 2008
Posts: 83
Thanks Aneel,

I will try that.

Regards,
Chintan.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: getResultsList() returning nothing through OracleTopLink..works fine in Hibernate