For the "Deposit" property I'm using a formula to retrieve a value from 1 of 2 DB columns. I debugged and found that the column name DEPOSIT_9_0_ is passed to DoubleType.get(ResultSet rs, String name) rather than f0_0_ which causes an Invalid Column error to be thrown.

So it looks like the "f" prefix is used for the alias, but not when mapping the results into my POJO. This is my first time using a formula so I may have missed something, but I've scoured HIA and docs and there is very little mentioned about formulas. So any info at all would be helpful.

Thanks!

Hibernate version:
2.1.6

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="com.workscape.oneforce.x2.DirectDepositAccount" table="DIRECT_DEPOSIT">
<id name="DirectDepositId" column="DIRECT_DEPOSIT_SEQNUM" type="com.workscape.persistence.EntityIdTypeUserType" unsaved-value="null">
<generator class="sequence"/>
</id>
<property name="FinancialInstitution" column="BANK_NAME" type="string"/>
<property name="Priority" column="PRIORITY_NUM" type="boolean"/>
<property name="DepositType" column="ALLOCATED_METHOD_CD" type="string"/>
<property name="RoutingNumber" column="ROUTING_TRANS_NUM" type="string"/>
<property name="AccountType" column="ACCOUNT_TYPE" type="string"/>
<property name="AccountNumber" column="ACCOUNT_NUMBER" type="string"/>
<property name="Deposit" formula="DECODE(ALLOCATED_METHOD_CD, '%', DEPOSIT_PCT, DEPOSIT_AMT)" type="double"/>
<property name="NetPayIndicator" column="NET_PAY_IND" type="string"/>
<!--<UserArea/>-->
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:
<Oct 21, 2004 15:00:20,634> ERROR [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] net.sf.hibernate.util.JDBCExceptionReporter: could not load: [com.workscape.oneforce.x2.DirectDepositAccount#<IdValue xmlns="http://ns.hr-xml.org">2</IdValue>
]
java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:6221)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1557)
at oracle.jdbc.driver.OracleResultSet.getDouble(OracleResultSet.java:1564)
at net.sf.hibernate.type.DoubleType.get(DoubleType.java:15)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.type.AbstractType.hydrate(AbstractType.java:66)
at net.sf.hibernate.loader.Loader.hydrate(Loader.java:686)
at net.sf.hibernate.loader.Loader.loadFromResultSet(Loader.java:627)
at net.sf.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:586)
at net.sf.hibernate.loader.Loader.getRow(Loader.java:501)
at net.sf.hibernate.loader.Loader.getRowFromResultSet(Loader.java:213)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:281)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:911)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:931)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:59)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:51)
at net.sf.hibernate.persister.EntityPersister.load(EntityPersister.java:415)
at net.sf.hibernate.impl.SessionImpl.doLoad(SessionImpl.java:2129)
at net.sf.hibernate.impl.SessionImpl.doLoadByClass(SessionImpl.java:1999)
at net.sf.hibernate.impl.SessionImpl.get(SessionImpl.java:1935)
at com.workscape.business.da.directdeposit.v2.DirectDepositDACBean.get(DirectDepositDACBean.java:105)
at com.workscape.business.da.directdeposit.v2.DirectDepositDAC_elveek_ELOImpl.get(DirectDepositDAC_elveek_ELOImpl.java:46)

Name and version of the database you are using:
Oracle 9.2.0.4.0

The generated SQL (show_sql=true):
select directdepo0_.DIRECT_DEPOSIT_SEQNUM as DIRECT_D1_0_, directdepo0_.BANK_NAME as BANK_NAME0_, directdepo0_.PRIORITY_NUM as PRIORITY3_0_, directdepo0_.ALLOCATED_METHOD_CD as ALLOCATE4_0_, directdepo0_.ROUTING_TRANS_NUM as ROUTING_5_0_, directdepo0_.ACCOUNT_TYPE as ACCOUNT_6_0_, directdepo0_.ACCOUNT_NUMBER as ACCOUNT_7_0_, directdepo0_.NET_PAY_IND as NET_PAY_8_0_, DECODE(directdepo0_.ALLOCATED_METHOD_CD, '%', directdepo0_.DEPOSIT_PCT, directdepo0_.DEPOSIT_AMT) as f0_0_ from DIRECT_DEPOSIT directdepo0_ where directdepo0_.DIRECT_DEPOSIT_SEQNUM=?

Debug level Hibernate log excerpt:
<Oct 21, 2004 14:55:54,432> DEBUG [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] net.sf.hibernate.type.StringType: returning 'Citizens Bank' as column: BANK_NAME0_
<Oct 21, 2004 14:56:47,217> DEBUG [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] net.sf.hibernate.type.BooleanType: returning 'true' as column: PRIORITY3_0_
<Oct 21, 2004 14:56:48,770> DEBUG [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] net.sf.hibernate.type.StringType: returning '$' as column: ALLOCATE4_0_
<Oct 21, 2004 14:56:51,984> DEBUG [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] net.sf.hibernate.type.StringType: returning '122121321' as column: ROUTING_5_0_
<Oct 21, 2004 14:56:53,707> DEBUG [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] net.sf.hibernate.type.StringType: returning 'Savings' as column: ACCOUNT_6_0_
<Oct 21, 2004 14:58:17,247> DEBUG [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] net.sf.hibernate.type.StringType: returning '992433331123' as column: ACCOUNT_7_0_
<Oct 21, 2004 15:00:20,474> WARN [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] net.sf.hibernate.util.JDBCExceptionReporter: SQL Error: 17006, SQLState: null
<Oct 21, 2004 15:00:20,474> ERROR [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] net.sf.hibernate.util.JDBCExceptionReporter: Invalid column name

try something like changing

DECODE(ALLOCATED_METHOD_CD, '%', DEPOSIT_PCT, DEPOSIT_AMT)
to
select DECODE(ALLOCATED_METHOD_CD, '%', DEPOSIT_PCT, DEPOSIT_AMT) from yourTable where yourTable.id = property

----------------------------
Thanks for the quick reply.

I tried your suggestion and it does the same exact thing. I will debug it further to see how it is coming up with DEPOSIT_9_0_ for the derived property with alias f0_0_.

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="com.workscape.oneforce.x2.DirectDepositAccount" table="DIRECT_DEPOSIT">
<id name="DirectDepositId" column="DIRECT_DEPOSIT_SEQNUM" type="com.workscape.persistence.EntityIdTypeUserType" unsaved-value="null">
<generator class="sequence"/>
</id>
<property name="FinancialInstitution" column="BANK_NAME" type="string"/>
<property name="Priority" column="PRIORITY_NUM" type="boolean"/>
<property name="DepositType" column="ALLOCATED_METHOD_CD" type="string"/>
<property name="RoutingNumber" column="ROUTING_TRANS_NUM" type="string"/>
<property name="AccountType" column="ACCOUNT_TYPE" type="string"/>
<property name="AccountNumber" column="ACCOUNT_NUMBER" type="string"/>
<property name="Deposit" formula="(select DECODE(dd.ALLOCATED_METHOD_CD, '%', dd.DEPOSIT_PCT, dd.DEPOSIT_AMT) from DIRECT_DEPOSIT dd where dd.DIRECT_DEPOSIT_SEQNUM=DIRECT_DEPOSIT_SEQNUM)" type="double"/> <property name="NetPayIndicator" column="NET_PAY_IND" type="string"/>
<!--<UserArea/>-->
</class>
</hibernate-mapping>

select directdepo0_.DIRECT_DEPOSIT_SEQNUM as DIRECT_D1_0_, directdepo0_.BANK_NAME as BANK_NAME0_, directdepo0_.PRIORITY_NUM as PRIORITY3_0_, directdepo0_.ALLOCATED_METHOD_CD as ALLOCATE4_0_, directdepo0_.ROUTING_TRANS_NUM as ROUTING_5_0_, directdepo0_.ACCOUNT_TYPE as ACCOUNT_6_0_, directdepo0_.ACCOUNT_NUMBER as ACCOUNT_7_0_, directdepo0_.NET_PAY_IND as NET_PAY_8_0_, (select DECODE(dd.ALLOCATED_METHOD_CD, '%', dd.DEPOSIT_PCT, dd.DEPOSIT_AMT) from DIRECT_DEPOSIT dd where dd.DIRECT_DEPOSIT_SEQNUM=directdepo0_.DIRECT_DEPOSIT_SEQNUM) as f0_0_ from DIRECT_DEPOSIT directdepo0_ where directdepo0_.DIRECT_DEPOSIT_SEQNUM=?

I have a similar problem. See http://forum.hibernate.org/viewtopic.php?t=935541. Unfortunately I have no answer to it yet. Maybe Anthony knows what's going on?

which version of hibernate are you using? which db?

i'm very surprised, i've been using formula in many project, it works...

Hibernate version: 2.1.3
Database: mysql-4.1.3
The generated SQL:
Debug level Hibernate log excerpt:



Actually I don't use "formula", I'm just trying to use a named query:

like this:

So, do you guys have an idea what's wrong with this approach?

Let me provide more info on my problem as it likely has a different cause than clonq's...

I'm using XMLBeans and they produce an Interface and a single concrete class for POJOs. I'm beyond the default-constructor XMLBean/Hibernate problems (needing both ClassPersister and Interceptor) and have found that I need to provide mappings for both interface and class even though they differ only by class name.

The root of my problem is that the interface's EntityPersister is used to create the SQL aliases and in EntityLoader.loadFromResultSet method the class' EntityPersister is used. That'd be fine, but the name of my formula property differs in the two EntityPersister's propertyColumnAliases field. The interface's uses f0_ and the class' uses f1_ so an Invalid Column error is thrown when the POJO is hydrated.

I'll keep digging for why they differ, but I was hoping someone could tell me that my mapping approach is wrong. I'd prefer just mapping the interface only. I can't use subclass as I don't have a discriminator and I can't use joined-subclass as there is just one table required (and interface and concrete mapping are identical).

Is there a way to only map one or the other rather than duplicating each mapping for every persistable POJO? Here is my code where I deal only with the DirectDepositAccount interface rather than specifically referring to the DirectDepositAccountImpl class:

Thanks for any advice you have!