Mapping a string property to an numeric column

homeijer · **Joined:** Wed Aug 27, 2003 9:12 am **Posts:** 4

Hi,

I have an application that uses business objects that have strings as keys. Some of them are mapped on tables where the key columns are numeric. These tables are reference tables and key columns are assigned and contain integers only.

What is happening is that when a load of such a command is issued, the following select statement is fired:
select ..... from atable where key = ?
And a parameter of type String is passed (while the key column is numeric).

This setup works on db2 on windows 2000 because it translates the "string containing an integer" to number. But on db2 on a mainframe the same load fails because the parameter is supplied with the wrong datatype.

Any ideas on a solution?

TIA.
Michael Homeijer

stolley · **Posted:** Wed Dec 03, 2003 3:55 am

You could use a custom UserType, I think.

emmanuel · **Posted:** Wed Dec 03, 2003 9:36 am

stolley wrote:

You could use a custom UserType, I think.

Or map a private numerical property and add a public string getter/setter doing the conversion and calling setNumerical

dberansky · **Posted:** Tue Dec 30, 2003 3:52 pm

I started getting this same error after switching from 2.0 to 2.1:

property mapping has wrong number of columns: edu.ucsd.som.business.tracker.TrackerComment.itemId type: edu.ucsd.som.hibernate.NumericString

My situation is the same as Michael, that is I have numerical keys in the database and string keys in the application. However, I'm already using a UserType that converts from long to string and back.

<property name="itemId" column="item_id" type="edu.ucsd.som.hibernate.NumericString" not-null="true"/>

Quote:

Or map a private numerical property and add a public string getter/setter doing the conversion and calling setNumerical

I'm not sure I understand this. What's setNumerical()? Could you offer a sample mapping of a "private numerical property"?

as I said, everything works just fine under 2.0. Any comments/suggestions?

Thanks
Dmitry

emmanuel · **Posted:** Tue Dec 30, 2003 4:18 pm

dberansky wrote:

I'm not sure I understand this. What's setNumerical()? Could you offer a sample mapping of a "private numerical property"?

Code:

/** @hibernate.property
private void setNumerical(Integer int) {
  _int = int;
}

/** non mapped property */
public void setPublicNumerical(String num) {
  setNumerical(new Integer(num));
}

The backward compatibility issue is strange: Gavin is very strict on that subject. Check API for updates, changelog don't talk about that.

max · **Posted:** Tue Dec 30, 2003 5:27 pm

...and show a stacktrace + code for complete explanation ;)

dberansky · **Posted:** Tue Dec 30, 2003 6:07 pm

changing the private property to a long and setting the access to "field" did the trick, thanks! Although, I would still prefer to use a custom UserType as that creates a better abstraction layer.

Here's the stack trace:

Code:

Caused by: net.sf.hibernate.MappingException: property mapping has wrong number of columns: edu.ucsd.som.business.tracker.RoutingTracker.itemId type: edu.ucsd.som.hibernate.NumericString
   at net.sf.hibernate.mapping.PersistentClass.validate(PersistentClass.java:269)
   at net.sf.hibernate.mapping.RootClass.validate(RootClass.java:199)
   at net.sf.hibernate.cfg.Configuration.validate(Configuration.java:576)
   at net.sf.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:733)
   at edu.ucsd.som.hibernate.AbstractHibernateDataStore.initFactory(AbstractHibernateDataStore.java:324)
   at edu.ucsd.som.hibernate.AbstractHibernateDataStore.getFactory(AbstractHibernateDataStore.java:296)
   ... 63 more

and the mapping:

Code:

<hibernate-mapping>
   <class name="edu.ucsd.som.business.tracker.RoutingTracker" table="routing_tracker">

      <id name="id" column="routing_tracker_id" type="long" access="field">
         <generator class="native"/>
      </id>

      <property name="name" column="name" not-null="false" type="string" />
      <property name="groupName" column="group_name" not-null="false" type="string" />
      <property name="itemId" column="item_id" not-null="true" type="edu.ucsd.som.hibernate.NumericString" />
      <property name="status" column="status" type="int"/>

      <list name="stages" lazy="false" table="routing_stage" cascade="all">
         <key column="routing_tracker_id"/>
         <index column="stage_num" type="int" />
         <one-to-many class="edu.ucsd.som.business.tracker.RoutingStage"/>
      </list>

      <property name="currentStageNum" column="current_stage_num" type="int"/>
   </class>
</hibernate-mapping>

and finally my initFactory() function:

Code:

   private void initFactory() throws HibernateException
   {
      Configuration config = new Configuration();
      Class[] classes = getPersistentClasses();

      for( int i=0; i<classes.length; i++ )
         config.addClass(classes[i]);

      Properties props = getProperties();
     
      props.put(Environment.DBCP_VALIDATION_QUERY, "select 1 from reclass_request");
      props.put(Environment.DBCP_VALIDATION_ONBORROW, Boolean.TRUE);
      props.put(Environment.DBCP_VALIDATION_ONRETURN, Boolean.FALSE);

      config.addProperties(props);

      this.factory = config.buildSessionFactory();

   }

thanks
-D.

max · **Posted:** Tue Dec 30, 2003 6:11 pm

...and how does NumericString look like ?

Does getColumnSpan() return something else than 1 ? Then the error is correct, because you are only specifying one column.

max · **Posted:** Tue Dec 30, 2003 6:13 pm

or it somehow makes:

return isFormula() ?
getColumnSpan()==0 :
getValue().isValid(mapping);

return false ?!

dberansky · **Posted:** Tue Dec 30, 2003 6:30 pm

I found getColumnSpan() in CustomType class. NumericString implements UserType. Should it instead extend CustomType?

Code:

public class NumericString
   implements UserType
{
   private static final java.sql.Types t = null;
   private static final int[] sqlTypes = new int[] {t.NUMERIC, t.SMALLINT, t.TINYINT, t.INTEGER,};

   public int[] sqlTypes()
   {
      return sqlTypes;
   }

   public Class returnedClass()
   {
      return String.class;
   }

   public boolean equals(Object o, Object o1) 
      throws HibernateException
   {
      if( (o==null) || (o1==null) )
         return o==o1;
      
      return o.equals(o1);
   }

   public Object nullSafeGet(ResultSet resultSet, String[] colNames, Object o) 
      throws HibernateException, SQLException
   {
      return String.valueOf(resultSet.getInt(colNames[0]));
   }

   public void nullSafeSet(PreparedStatement preparedStatement, Object o, int i) 
      throws HibernateException, SQLException
   {
      long number =  (o == null) ? 0 : Long.parseLong((String)o);

      preparedStatement.setLong(i, number);
   }

   public Object deepCopy(Object o)
      throws HibernateException
   {
      return o;
   }

   public boolean isMutable()
   {
      return false;
   }

}

dberansky · **Posted:** Tue Dec 30, 2003 6:36 pm

yeh, looks like changing NumericString to extend CustomType did the trick. Still, something seems wrong here :)

michael · **Posted:** Tue Dec 30, 2003 6:37 pm

I think your sqlTypes array is wrong. See the API:

Quote:

/**
* Return the SQL type codes for the columns mapped by this type. The
* codes are defined on <tt>java.sql.Types</tt>.
* @see java.sql.Types
* @return int[] the typecodes
*/

You have only one column, so your array should contain only one type ...

max · **Posted:** Tue Dec 30, 2003 6:44 pm

dberansky wrote:

yeh, looks like changing NumericString to extend CustomType did the trick. Still, something seems wrong here :)

No - it should be a UserType. CustomType is just an adapter for UserType to decouple those two.

And gloeglm is right about your sqlTypes problems - the length of the array is what determines the returntype of columnSpan!

4 types => 4 columns!

dberansky · **Posted:** Tue Dec 30, 2003 6:49 pm

yep, that was it. I misunderstood the spec. thanks guys