Hi,

I am using Native sql in hibernate. So one of my Class definition has <sql-insert> element that specifies the sql to be executed. But when i see the generated sql the sequence of columns have changed. Can somebody help me with how binding of parameters is done by hibernate when executing sql.

Here are the mapping files:

<hibernate-mapping>
<class name="com.dli.model.Customer" table="CUSTOMER">
<id name="customerId" column="INTCUSTID" type="long">
<generator class="native">
<param name="sequence">customer_sequence</param>
</generator>
</id>
<property name="username" column="VCHUSERNAME" type="string" not-null="true" index=""></property>
<property name="password" column="VCHPASSWORD" type="string" not-null="true"></property>
<property name="dateAdded" column="DTDATEADDED" type="timestamp" not-null="true" insert="false"></property>
<property name="lastLoginDate" column="DTDATELASTLOGIN" type="timestamp" not-null="true" insert="false"></property>
<one-to-one name="customerInfo" class="com.dli.model.CustomerInfo" cascade="all"></one-to-one>
<sql-insert>INSERT INTO CUSTOMER(VCHUSERNAME, VCHPASSWORD, INTCUSTID) VALUES ( ?,ENCRYPT(?),?)</sql-insert>
</class>
</hibernate-mapping>

Initally i was using the query
INSERT INTO CUSTOMER(INTCUSTID,VCHUSERNAME,VCHPASSWORD) VALUES (?,?,ENCRYPT(?)).

Encrypt is a user defined function that i created in oracle, so as to encrypt password. But hibernate is binding the parameters as
Username value to intcustid
password value to username
intcustid value to password(passing it in encrypt function)

How does hibernate arrive at what parameter is to be passed to which column?

From the documentation, chapter 17.4:


See:
http://www.hibernate.org/hib_docs/v3/re ... erysql-cud

Yes, very true. The debug mode is where i got the correct SQL sequence of column. I am acutally looking for a help to understand how the column sequnce is generated by Hibernate while preparing(binding parameters) the statement. The idea is that you can not every time go into debug mode and see what is the sql generated and then prepare your query accordingly.

Lorban, it would be helpful if you could suggest the binding sequence of hibernate query.

Thanks

Unfortunately if I remember well the ordering is always random because it is dependent on the order of the elements in a hashed collection or something like that. Nothing you can rely on unfortunetely.

I agree it's not terribly handy but that's the way it is right now.

Thanks Lorban. Its appreciated.

But if we see this logically, the hashing always generates the same binding and that is what we can rely upon and use to create a query which remains same for every time we reload tomcat and intialize hibernate. I mean, there is some way(logic) that is used to generate that hash. Can anybody from hibernate team put some focus on this?

the sequence is not influenced by any hashing (if it is its a bug).

It is fully controlled by the ordering in the mapping files.

/max

Ok, sorry for the mistake.

Wouldn't that be a useful piece of documentation to be added in the manual ?

as far as i know its already documented how you find out the ordering of the parameters - you do it by enabling debug logging for the persisters.

Thanks for your attention Max.

It seems that only the id element is placed at the last(INTCUSTID in the example) and all other are sequenced as per the mapping file.

By the way, how could i rate this article?

Sorry, i got the link as i posted the reply. Thanks a bunch

yes i think the general rule is that properties first then any special columns last (special columns are <version>, <timestamp>, <id>, <key>, etc.)

Why does Hibernate skip binding values to user defined function (i.g. mgdecrypt(?)? As a matter of fact, it binds values to col1, col2 and ID(sequence) but seems to skip completely at col3. As a result, it gave me an error saying :

Many thanks for your help.


====LOG======

- binding ' ' to parameter: 1
- binding 'This is a test' to parameter: 2
- binding '171' to parameter: 3
- SQL Error: 17041, SQLState: null
- Missing IN or OUT parameter at index:: 4
- Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: could not insert:
.
.
.
root cause

java.sql.SQLException: Missing IN or OUT parameter at index:: 4
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:190)
=============================================

Below is my Hibernate mapping file:

===========================

<hibernate-mapping>
<class name="com.mylocal.test.domain.MyTest" table="MYTEST" schema="tmt" lazy="false">
<id name="id" column="ID" type="int">
<generator class="sequence">
<param name="sequence">SEQID</param>
</generator>
</id>

<property name="col1" column="COL1"/>
<property name="col2" column="COL2" />
<property name="col3" column="COL3" formula="mgdecrypt(col3)" />

<sql-insert>
insert into MyTest ( col1, col2, ID, col3) values (?, ?, ?, mgencrypt(?) )
</sql-insert>
</hibernate-mapping>