Hibernate version: 3.1.2
Mapping documents:
Working
Code:
<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.kewill.dashboard.db.system">
<class name="SystemParameter" table="SYSTEM_PARAMETER" lazy="false">
<comment>PowerHouse file location: TRM:SYSPRM.DAT</comment>
<id name="parameterKey" type="com.kewill.base.db.OracleCHARUserType" length="20">
<column name="PARAMETER_KEY" sql-type="character(20)" not-null="true" />
<generator class="assigned"/>
</id>
<property name="parameterValue" type="com.kewill.base.db.OracleCHARUserType" length="100">
<column name="PARAMETER_VALUE" sql-type="character(100)" not-null="true" />
</property>
</class>
</hibernate-mapping>
Not Working
Code:
<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.kewill.dashboard.db.system">
<class name="UserProfile" table="USER_PROFILE" lazy="false">
<comment>PowerHouse file location: TRM:USRPRF.DAT</comment>
<id name="logonId" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="12">
<column name="LOGON_ID" sql-type="character(12)" not-null="true" />
<generator class="assigned"/>
</id>
<property name="userClassId" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="12">
<column name="USER_CLASS_ID" sql-type="character(12)" not-null="true" />
</property>
<property name="userName" type="com.kewill.base.db.OracleCHARUserType" length="30">
<column name="USER_NAME" sql-type="character(30)" not-null="true" />
</property>
<property name="userAddr" type="com.kewill.base.db.OracleCHARUserType" length="30">
<column name="USER_ADDR" sql-type="character(30)" not-null="true" />
</property>
<property name="userCity" type="com.kewill.base.db.OracleCHARUserType" length="20">
<column name="USER_CITY" sql-type="character(20)" not-null="true" />
</property>
<property name="userStatecode" type="com.kewill.base.db.OracleCHARUserType" length="2">
<column name="USER_STATECODE" sql-type="character(2)" not-null="true" />
</property>
<property name="userZipcode" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="9">
<column name="USER_ZIPCODE" sql-type="character(9)" not-null="true" />
</property>
<property name="userPhone" type="long" length="15">
<column name="USER_PHONE" not-null="true" />
</property>
<property name="userPhoneExt" type="com.kewill.base.db.OracleCHARUserType" length="4">
<column name="USER_PHONE_EXT" sql-type="character(4)" not-null="true" />
</property>
<property name="userPassword" type="com.kewill.base.db.OracleCHARUserType" length="8">
<column name="USER_PASSWORD" sql-type="character(8)" not-null="true" />
</property>
<property name="userDept" type="com.kewill.base.db.OracleCHARUserType" length="15">
<column name="USER_DEPT" sql-type="character(15)" not-null="true" />
</property>
<property name="timeUpdated" type="short" length="4">
<column name="TIME_UPDATED" not-null="true" />
</property>
<property name="dateUpdated" type="com.kewill.base.db.DateUserType" length="8">
<column name="DATE_UPDATED" not-null="true" />
</property>
<property name="modifiedBy" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="12">
<column name="MODIFIED_BY" sql-type="character(12)" not-null="true" />
</property>
<property name="brokerTeam" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="4">
<column name="BROKER_TEAM" sql-type="character(4)" not-null="true" />
</property>
<property name="userCashGroup" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="12">
<column name="USER_CASH_GROUP" sql-type="character(12)" not-null="true" />
</property>
<property name="faxFrom" type="com.kewill.base.db.OracleCHARUserType" length="30">
<column name="FAX_FROM" sql-type="character(30)" not-null="true" />
</property>
<property name="manualPurgeInProcess" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="MANUAL_PURGE_IN_PROCESS" not-null="true" />
</property>
<property name="reincarnateInProcess" type="short" length="1">
<column name="REINCARNATE_IN_PROCESS" not-null="true" />
</property>
<property name="queueName" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="15">
<column name="QUEUE_NAME" sql-type="character(15)" not-null="true" />
</property>
<property name="spUseDefaults" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="1">
<column name="SP_USE_DEFAULTS" sql-type="character(1)" not-null="true" />
</property>
<property name="spDefCrFlag" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="SP_DEF_CR_FLAG" not-null="true" />
</property>
<property name="spDefEsFlag" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="SP_DEF_ES_FLAG" not-null="true" />
</property>
<property name="spDefBillFlag" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="SP_DEF_BILL_FLAG" not-null="true" />
</property>
<property name="spDefDoFlag" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="SP_DEF_DO_FLAG" not-null="true" />
</property>
<property name="spDefFdaFlag" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="SP_DEF_FDA_FLAG" not-null="true" />
</property>
<property name="spDef7512Flag" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="SP_DEF_7512_FLAG" not-null="true" />
</property>
<property name="spDefSebFlag" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="SP_DEF_SEB_FLAG" not-null="true" />
</property>
<property name="spDefBolFlag" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="SP_DEF_BOL_FLAG" not-null="true" />
</property>
<property name="spDefNoteFlag" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="SP_DEF_NOTE_FLAG" not-null="true" />
</property>
<property name="spDefRateFlag" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="SP_DEF_RATE_FLAG" not-null="true" />
</property>
<property name="spDefOthdocFlag" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="SP_DEF_OTHDOC_FLAG" not-null="true" />
</property>
<property name="spDefReprtFlag" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="SP_DEF_REPRT_FLAG" not-null="true" />
</property>
<property name="profileUserName" type="com.kewill.base.db.OracleCHARUserType" length="30">
<column name="PROFILE_USER_NAME" sql-type="character(30)" not-null="true" />
</property>
<property name="secKey" type="com.kewill.base.db.OracleCHARUserType" length="4">
<column name="SEC_KEY" sql-type="character(4)" not-null="true" />
</property>
<property name="emailAddress" type="com.kewill.base.db.OracleCHARUserType" length="60">
<column name="EMAIL_ADDRESS" sql-type="character(60)" not-null="true" />
</property>
<property name="dashboardTickerFlg" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="DASHBOARD_TICKER_FLG" not-null="true" />
</property>
<property name="supervisorFlg" type="com.kewill.base.db.YesNoUserType" length="1">
<column name="SUPERVISOR_FLG" not-null="true" />
</property>
<property name="supervisor" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="12">
<column name="SUPERVISOR" sql-type="character(12)" not-null="true" />
</property>
<property name="outOfOfficeUser" type="com.kewill.base.db.OracleCHARUserType" length="12">
<column name="OUT_OF_OFFICE_USER" sql-type="character(12)" not-null="true" />
</property>
<property name="outOfOfficeFlg" type="com.kewill.base.db.OracleCHARUserType" length="1">
<column name="OUT_OF_OFFICE_FLG" sql-type="character(1)" not-null="true" />
</property>
</class>
</hibernate-mapping>
Full stack trace of any exception that occurs:Quote:
Hibernate:
select
userprofil0_.LOGON_ID as LOGON1_61_0_,
userprofil0_.USER_CLASS_ID as USER2_61_0_,
userprofil0_.USER_NAME as USER3_61_0_,
userprofil0_.USER_ADDR as USER4_61_0_,
userprofil0_.USER_CITY as USER5_61_0_,
userprofil0_.USER_STATECODE as USER6_61_0_,
userprofil0_.USER_ZIPCODE as USER7_61_0_,
userprofil0_.USER_PHONE as USER8_61_0_,
userprofil0_.USER_PHONE_EXT as USER9_61_0_,
userprofil0_.USER_PASSWORD as USER10_61_0_,
userprofil0_.USER_DEPT as USER11_61_0_,
userprofil0_.TIME_UPDATED as TIME12_61_0_,
userprofil0_.DATE_UPDATED as DATE13_61_0_,
userprofil0_.MODIFIED_BY as MODIFIED14_61_0_,
userprofil0_.BROKER_TEAM as BROKER15_61_0_,
userprofil0_.USER_CASH_GROUP as USER16_61_0_,
userprofil0_.FAX_FROM as FAX17_61_0_,
userprofil0_.MANUAL_PURGE_IN_PROCESS as MANUAL18_61_0_,
userprofil0_.REINCARNATE_IN_PROCESS as REINCAR19_61_0_,
userprofil0_.QUEUE_NAME as QUEUE20_61_0_,
userprofil0_.SP_USE_DEFAULTS as SP21_61_0_,
userprofil0_.SP_DEF_CR_FLAG as SP22_61_0_,
userprofil0_.SP_DEF_ES_FLAG as SP23_61_0_,
userprofil0_.SP_DEF_BILL_FLAG as SP24_61_0_,
userprofil0_.SP_DEF_DO_FLAG as SP25_61_0_,
userprofil0_.SP_DEF_FDA_FLAG as SP26_61_0_,
userprofil0_.SP_DEF_7512_FLAG as SP27_61_0_,
userprofil0_.SP_DEF_SEB_FLAG as SP28_61_0_,
userprofil0_.SP_DEF_BOL_FLAG as SP29_61_0_,
userprofil0_.SP_DEF_NOTE_FLAG as SP30_61_0_,
userprofil0_.SP_DEF_RATE_FLAG as SP31_61_0_,
userprofil0_.SP_DEF_OTHDOC_FLAG as SP32_61_0_,
userprofil0_.SP_DEF_REPRT_FLAG as SP33_61_0_,
userprofil0_.PROFILE_USER_NAME as PROFILE34_61_0_,
userprofil0_.SEC_KEY as SEC35_61_0_,
userprofil0_.EMAIL_ADDRESS as EMAIL36_61_0_,
userprofil0_.DASHBOARD_TICKER_FLG as DASHBOARD37_61_0_,
userprofil0_.SUPERVISOR_FLG as SUPERVISOR38_61_0_,
userprofil0_.SUPERVISOR as SUPERVISOR61_0_,
userprofil0_.OUT_OF_OFFICE_USER as OUT40_61_0_,
userprofil0_.OUT_OF_OFFICE_FLG as OUT41_61_0_
from
USER_PROFILE userprofil0_
where
userprofil0_.LOGON_ID=?
20060417 04:30:38,717 WARN [] logExceptions(JDBCExceptionReporter:71) - SQL Error: 17024, SQLState: null
20060417 04:30:38,717 ERROR [] logExceptions(JDBCExceptionReporter:72) - No data read
Name and version of the database you are using: Oracle 10G 10.2.0.2.0
JDBC driver: 10.2.0.1.0
Custom User Types:OracleCHARUserType
Code:
package com.kewill.base.db;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;
public class OracleCHARUserType implements UserType {
private static final int[] SQL_TYPES = {OracleTypes.FIXED_CHAR};
public int[] sqlTypes() {
return SQL_TYPES;
}
public Class returnedClass() {
return String.class;
}
public boolean equals(Object x, Object y) throws HibernateException {
if (x == y) return true;
if (x == null || y == null) return false;
return x.equals(y);
}
public int hashCode(Object x) throws HibernateException {
return x.hashCode();
}
public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException {
if (rs.wasNull()) return null;
String val = StringUtils.stripEnd(rs.getString(names[0]), " ");
if (StringUtils.isEmpty(val)) {
return null;
}
return val;
}
public void nullSafeSet(PreparedStatement statement, Object value, int index) throws HibernateException, SQLException {
if (value == null) {
statement.setNull(index, OracleTypes.FIXED_CHAR);
} else {
String val = (String)value;
statement.setObject(index, val, 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;
}
public Serializable disassemble(Object value) throws HibernateException {
return (Serializable)value;
}
public Object assemble(Serializable cached, Object owner) throws HibernateException {
return (String)cached;
}
public Object replace(Object original, Object target, Object owner) throws HibernateException {
return null;
}
}
YesNoUserType
Code:
package com.kewill.base.db;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;
public class YesNoUserType implements UserType {
private static final int[] SQL_TYPES = {Types.CHAR};
public int[] sqlTypes() {
return SQL_TYPES;
}
public Class returnedClass() {
return Boolean.class;
}
public boolean equals(Object x, Object y) throws HibernateException {
if (x == y) return true;
if (x == null || y == null) return false;
return x.equals(y);
}
public int hashCode(Object x) throws HibernateException {
return x.hashCode();
}
public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException {
if (rs.wasNull()) return false;
String val = StringUtils.stripEnd(rs.getString(names[0]), " ");
if (StringUtils.equalsIgnoreCase("Y", val)) {
return true;
}
return false;
}
public void nullSafeSet(PreparedStatement statement, Object value, int index) throws HibernateException, SQLException {
boolean val = (Boolean)value;
if (val) {
statement.setString(index, "Y");
} else {
statement.setString(index, "N");
}
}
public Object deepCopy(Object value) throws HibernateException {
if (value == null) {
return new Boolean(false);
}
return new Boolean((Boolean)value);
}
public boolean isMutable() {
return false;
}
public Serializable disassemble(Object value) throws HibernateException {
return (Serializable)value;
}
public Object assemble(Serializable cached, Object owner) throws HibernateException {
return (Boolean)cached;
}
public Object replace(Object original, Object target, Object owner) throws HibernateException {
return null;
}
}
Problem description:
Ok, so here's the problem. I have to connect to a legacy database that only allows NUMERIC's and FIXED_CHAR values. To try and get around the FIXED_CHAR problem I wrote a custom user type as listed above. The Uppercase user class is exactly the same but uppercases the values in the get and set. Anyway, it appears that a call to the SYSTEM_PARAMETER table returns the values no problem, however when I try and pull a row from the USER_PROFILE table it tells me "No data read" with the SQL error of 17024. When I first implemented the FIXED_CHAR user types, which I had found in the wiki, I couldn't get the app to run because it didn't know how to map SQL type 999. I managed to fix this by creating a new Dialect which only adds the following mapping:
registerColumnType( OracleTypes.FIXED_CHAR, 4000, "char($l)" );
Does anyone have any thoughts on why this would be failing?
Thanks in advance.
Adam