Appending blank space on sql search parameters

jongilmore · **Joined:** Tue Sep 13, 2005 3:57 am **Posts:** 5

I am performing a search for a mapped hibernate object. The object has a statusCode parameter. The respective STATUS_CODE column in the mapped table (an Oracle dbase) is specified as a CHAR (4 Byte).

I can successfully find and extract results out of the database if I search for a statusCode that has exactly four characters. However, if I search for a statusCode with less than four characters hibernate does not find any results. I think, but am unsure, that hibernate is appending blank space to make my 3 character query string into a 4 character string.

My hibernate object mapping for the statusCode is as follows:

<property
name="statusCode"
type="java.lang.String"
column="STATUS_CODE"
not-null="true"/>

Does anyone know what the problem is?

ramnath · **Posted:** Sat Jan 07, 2006 8:32 am

Hi,

Can you post ur Criteria or HQL Queries which you are using to retrieve?

rajasaur · **Joined:** Tue Aug 23, 2005 8:52 am **Posts:** 181

Replace your CHAR(4) with VARCHAR2(4).

From a random google search.

Quote:

Under Oracle 6, CHAR and VARCHAR both hold character
data up to a maximum of 255 characters. These two types contain variable
length character strings up to the size specified in the declaration. Under
Oracle 7 however, the CHAR datatype represents fixed length character
strings and strings which are shorter than the specified length are padded
with spaces to make them up to the length specified in the declaration.

So its ur DB doing the padding if the length is less than 4 characters.

jongilmore · **Joined:** Tue Sep 13, 2005 3:57 am **Posts:** 5

Thanks, that was the problem. The database appends blank space on CHAR column types to buffer them to the correct length.

zagarol · **Posted:** Wed Jul 05, 2006 5:18 am

Hi,

I am having the same problem as the original poster but alas I am unable to change the schema.

I am trying to find an existing record on the Oracle 10g DB via the primary key which is a CHAR(10) but the id does not neccesarily have 10 characters all the time.

I have instigated a UserType to trim out the result when it is returned but this does not seem to be working either.

Here is the relavent section of FixedString UserType

Code:

public class FixedString implements UserType, Serializable {

...

   /**
    * 
    * @see org.hibernate.usertype.UserType#nullSafeGet(java.sql.ResultSet,
    *      java.lang.String[], java.lang.Object)
    * 
    */

   public Object nullSafeGet(ResultSet inResultSet, String[] names, Object o)
         throws SQLException {
      String val = (String) Hibernate.STRING.nullSafeGet(inResultSet,
            names[0]);
      return StringUtils.trim(val);

   }

   /**
    * 
    * @see org.hibernate.usertype.UserType#nullSafeSet(java.sql.PreparedStatement,
    *      java.lang.Object, int)
    * 
    */

   public void nullSafeSet(PreparedStatement inPreparedStatement, Object o,
         int i) throws SQLException {
      String val = (String) o;
      inPreparedStatement.setString(i, val);
   }
}

And here is a simple hibernate mapping which recreates the problem that I am suffering.

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>

   <class name="com.yell.keywords.persistence.FixedCharTrialObject"
      table="PPC_FIXED_TEST" dynamic-insert="true" dynamic-update="true"
      optimistic-lock="version">

      <id name="id"
         type="com.yell.keywords.businessobjects.FixedString"
         column="IDENTIFIER" unsaved-value="null">
         <generator class="assigned"></generator>
      </id>

      <version name="itimestamp" type="timestamp"
         column="ITIMESTAMP" />
      <property name="something"
         type="com.yell.keywords.businessobjects.FixedString" column="SOMETHING" />
      <property name="somethingElse"
         type="com.yell.keywords.businessobjects.FixedString" column="SOMETHING_ELSE"/>


   </class>
</hibernate-mapping>

I have inserted a simple row into the table via the hibernate session:

Code:

public void testLoadingData() throws Exception {
    FixedCharTrialObject object = new FixedCharTrialObject();
    object.setId("1");
    object.setSomething("Hello");
    object.setSomethingElse("there");
    Transaction transaction = session.beginTransaction();
    session.save(object);
    transaction.commit();
  }