hibernate 不兼容于 oracle char

Automatically trimming String properties (good for DB/2 and Oracle) （from hibernate forum https://www.hibernate.org/90.html）

This might be of use to anyone who uses Hibernate against DB/2 on various platforms that store spaces (padding) at the end of character columns. Specify type="cosmocracy.hibernate.TrimmedString" in your HBM files for the columns you want to be trimmed automagically. Enjoy!

c o s m o c r a c y @ y a h o o _dot_ c o m

package cosmocracy.hibernate;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.apache.commons.lang.ObjectUtils;
import cirrus.hibernate.type.DiscriminatorType;
import cirrus.hibernate.type.ImmutableType;

public class TrimmedStringType extends ImmutableType implements DiscriminatorType {

    public Object get(ResultSet rs, String name) throws SQLException {
        String result = rs.getString(name);
        if( result != null ) {
            result = result.trim(); 
        }
        return result;
    }

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

    public void set(PreparedStatement st, Object value, int index) throws SQLException {
        String stringValue = (String) value;
        if( stringValue != null ) {
            stringValue = stringValue.trim();
        }

        st.setString(index, stringValue);
    }

    public int sqlType() {
        return Types.VARCHAR;
    }

    public String getName() { return "trimmed-string"; }

    public String objectToSQLString(Object value) throws Exception {
        String stringValue = (String) value;
        if( stringValue != null ) {
            stringValue = stringValue.trim();
        }
        return '/'' + stringValue + '/'';
    }

    public Object stringToObject(String xml) throws Exception {
        return xml;
    }

    public boolean equals(Object x, Object y) {
        return ObjectUtils.equals(x, y);
    }

    public String toXML(Object value) {
        return (String) value;
    }
}

Note: I think it is better to implement UserType for this case; extending ImmutableType directly is likely to be less forward-compatible with future Hibernate versions. Your UserType could simply delegate to Hibernate.STRING.

This works well for DB2/400 -

/*
 * Created on 24-Feb-04
 */
package dao.hibernate;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;

/**
 * @author Colin Hawkett
 */
public class TrimmedString implements UserType {
    public TrimmedString() {
        super();
    }

    public int[] sqlTypes() {
        return new int[] { Types.CHAR };
    }

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

    public boolean equals(Object x, Object y) throws HibernateException {
        return (x == y) || (x != null && y != null && (x.equals(y)));
    }

    public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException {
        String val = rs.getString(names[0]);
        return val != null ? val.trim() : null;
    }

    public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
        st.setString(index, (String)value);
    }

    public Object deepCopy(Object value) throws HibernateException {
        if (value == null) return null;
        return new String((String)value);
    }

    public boolean isMutable() {
        return false;
    }
}

And here is an example mapping definition...

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

<hibernate-mapping>
    <class name="domain.Category" table="RECATM" mutable="false">
        <id name="id" type="big_decimal" unsaved-value="null" >
            <column name="CMCATC" sql-type="decimal(6, 0)" not-null="true"/>
            <generator class="assigned"/>
        </id>
        
        <property name="description" type="dao.hibernate.TrimmedString">
            <column name="CMDESC" sql-type="character(70)" not-null="true"/>
        </property>

        ...
    </class>
</hibernate-mapping>

Hope it helps!

Martin.Kneissl _at_ atosorigin.com:

Here is a variant supporting Oracle CHAR columns that share the same problem of padding with spaces.

The version above does not work because Oracle insists on its vendor specific sqltype FIXED_CHAR in the WHERE clause, otherwise Hibernate won't find its records...

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.driver.OracleTypes;

import org.apache.commons.lang.StringUtils;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;

/**
 * @author Martin Kneissl, Atos Worldline.
 */
public class OracleCHAR implements UserType {
   public OracleCHAR() {
      super();
   }

   public int[] sqlTypes() {
      return new int[] { OracleTypes.FIXED_CHAR };
   }

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

   public boolean equals(Object x, Object y) throws HibernateException {
      return (x == y) || (x != null && y != null && (x.equals(y)));
   }

   public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException {
      String val = rs.getString(names[0]);
      if (null == val)
      {
         return null;
      }
      else
      {
         String trimmed = StringUtils.stripEnd(val, " ");
         if (trimmed.equals(""))
         {
            return null;
         }
         else
         {
            return trimmed;
         }
      }
   }

   public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
      if (value == null)
      {
         st.setNull(index, OracleTypes.FIXED_CHAR);
      }
      else
      {
         st.setObject(index, value, OracleTypes.FIXED_CHAR);
      }
   }

   public Object deepCopy(Object value) throws HibernateException {
      if (value == null) return null;
        return new String((String)value);
   }

   public boolean isMutable() {
      return false;
   }
}