Why is Hibernate having trouble with dashes?

laredotornado · **Joined:** Wed Oct 14, 2009 3:22 pm **Posts:** 16

Hi,

I'm using Java 1.5, Resin 3.0.19, Oracle 10g, and Hibernate 3.2.4.sp1. When I query my VARCHAR2 column with a value containing a dash ("-"), Hibernate is not able to find the value. However, I can execute the query normally in SQL developer and a row is returned. Can someone help me troubleshoot this? I am including what is in my hibernate.log ...

DEBUG [org.hibernate.jdbc.ConnectionManager, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,854]:
opening JDBC connection
DEBUG [org.hibernate.SQL, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,854]:
select count(*) as y0_ from DRIVER_MATCHING.IDENTITY this_ where UPPER(this_.mailing_zip5) = UPPER(?) and UPPER(this_.last_name) = UPPER(?) and UPPER(this_.first_name) = UPPER(?) and UPPER(this_.name_suffix) = UPPER(?) and UPPER(this_.mail
ing_street) = UPPER(?) and TO_CHAR(this_.dateOfBirth,'dd') = REPLACE(TO_CHAR(?, '00'), ' ', '') and TO_CHAR(this_.dateOfBirth, 'MM') = REPLACE(TO_CHAR(?, '00'), ' ', '') and TO_CHAR(this_.dateOfBirth, 'yyyy') = REPLACE(TO_CHAR(?, '0000'),
' ', '')
DEBUG [org.hibernate.jdbc.AbstractBatcher, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,854]:
preparing statement
DEBUG [org.hibernate.type.StringType, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,855]:
binding '43210' to parameter: 1
DEBUG [org.hibernate.type.StringType, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,855]:
binding 'LAST-NAME' to parameter: 2
DEBUG [org.hibernate.type.StringType, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,855]:
binding 'FIRST-NAME' to parameter: 3
DEBUG [org.hibernate.type.StringType, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,855]:
binding 'SFIX' to parameter: 4
DEBUG [org.hibernate.type.StringType, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,855]:
binding '123 MAILING STREET' to parameter: 5
DEBUG [org.hibernate.type.IntegerType, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,855]:
binding '1' to parameter: 6
DEBUG [org.hibernate.type.IntegerType, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,855]:
binding '1' to parameter: 7
DEBUG [org.hibernate.type.IntegerType, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,855]:
binding '1969' to parameter: 8
DEBUG [org.hibernate.jdbc.AbstractBatcher, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,856]:
about to open ResultSet (open ResultSets: 0, globally: 0)
DEBUG [org.hibernate.loader.Loader, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,856]:
processing result set
DEBUG [org.hibernate.loader.Loader, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,856]:
result set row: 0
DEBUG [org.hibernate.loader.Loader, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,856]:
result row:
DEBUG [org.hibernate.type.IntegerType, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,856]:
returning '0' as column: y0_
DEBUG [org.hibernate.loader.Loader, resin-tcp-connection-*:8082-9 2009-10-14 13:41:35,856]:
done processing result set (1 rows)

Thanks, - Dave

Froestel · **Posted:** Wed Oct 14, 2009 4:29 pm

Hi laredotornado,

I don't think your information is sufficient. Could you probably post the corresponding mappings and the HQL query you are talking about?

Oh, and please, use BBCode (see the buttons below the subject line). Makes your code much easier to read.

CU
Froestel

laredotornado · **Joined:** Wed Oct 14, 2009 3:22 pm **Posts:** 16

Hi,

Here is the mappings file ...

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
                "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
                "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory name="java:comp/env/myco-dor-dmv-driver-matching">

    <property name="connection.datasource">
      java:comp/env/jdbc/myco-dor-dmv-driver-matching
    </property>
    <property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>

    <mapping class="myco.dor.dmv.driver.matching.model.AgeVerificationRequest" />
    <mapping class="myco.dor.dmv.driver.matching.model.AgeVerificationResponse" />
    <mapping class="myco.dor.dmv.driver.matching.model.Identity" />
    <mapping class="myco.dor.dmv.driver.matching.model.IdentityMatchParameters" />
    <mapping class="myco.dor.dmv.driver.matching.model.MatchRequest" />
    <mapping class="myco.dor.dmv.driver.matching.model.MatchResponse" />

  </session-factory>

</hibernate-configuration>

I"m only querying a single table (the IDENTITY table), using the Java code below ...

Code:

              Criteria criteria = matchCodeCriteriaBuilder.build(session.createCriteria(Identity.class), matchRequest.getIdentityMatchParameters(),  enumByCode);
              criteria.setProjection(Projections.rowCount());
              List<Object> rsList = criteria.list();
              if(!rsList.isEmpty()){
                Integer rowCount = (Integer) rsList.get(0);
                if( rowCount > 0) {
                            matchResponse = new MatchResponse(matchRequest, matchCode);                      
                            break;
                 }
              } else {
                 log.debug("No results returned."); 
              } 

The query I'm using is autogenerated, but it is in the log I attached and here again for your convenience ...

select count(*) as y0_ from DRIVER_MATCHING.IDENTITY this_ where UPPER(this_.mailing_zip5) = UPPER(?) and UPPER(this_.last_name) = UPPER(?) and UPPER(this_.first_name) = UPPER(?) and UPPER(this_.name_suffix) = UPPER(?) and UPPER(this_.mailing_street) = UPPER(?) and TO_CHAR(this_.dateOfBirth,'dd') = REPLACE(TO_CHAR(?, '00'), ' ', '') and TO_CHAR(this_.dateOfBirth, 'MM') = REPLACE(TO_CHAR(?, '00'), ' ', '') and TO_CHAR(this_.dateOfBirth, 'yyyy') = REPLACE(TO_CHAR(?, '0000'),
' ', '')

As I mentioned, when I use the bindings that you see in the first post, I can run the query and get results from Oracle. The only problem seems to occur when one of the VARCHAR2 columns (like first name or last name) contains a dash ("-"). Then Hibernate tells me it wasn't able to return any rows.

Any more ideas you have for troubleshooting is appreciated. Thanks, - Dave

Froestel · **Posted:** Sat Oct 17, 2009 9:20 am

Hi laredotornado,

laredotornado wrote:

Hi,
Here is the mappings file ...

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
                "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
                "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
[..]
</hibernate-configuration>

That's your configuration file not a mapping file... Mapping files end with .hbm.xml. But looks like you probably use annotations, so posting the class(es) would be helpful.

laredotornado wrote:

... The only problem seems to occur when one of the VARCHAR2 columns (like first name or last name) contains a dash ("-")....

Do you mean the name of the column or column content when referring to the dashes problem?

CU
Froestel