Can't get <sql-insert> to work in Version 3.2

hooknc · **Joined:** Mon Nov 29, 2004 2:00 pm **Posts:** 7

Hibernate version: 3.2

Mapping documents:

Code:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping>

<class name="com.kf.businessobject.impl.UserImpl" 
       table="user" >

    <id name="id"
        type="java.lang.Integer"
        column="user_id" >

        <generator class="identity" />
    </id>

    <property name="username"
              type="java.lang.String"
              column="user_name" />

    <property name="createDate"
              type="java.sql.Timestamp"
              column="create_datetime" />

    <property name="expireDate"
              type="java.sql.Timestamp"
              column="expire_datetime" />

    <property name="emailAddress"
              type="java.lang.String"
              column="email_address" />

    <property name="active"
              type="boolean"
              column="active_ind" />

    <property name="userType"
              type="java.lang.String"
              column="user_type_code" />

    <property name="lastLoginDate"
              type="java.sql.Timestamp"
              column="last_login_datetime" />

    <property name="password"
              type="java.lang.String"
              column="password_text" />

   <loader query-ref="user"/>

   <sql-insert>INSERT INTO user (user_name, create_datetime, expire_datetime, email_address, active_ind, user_type_code, last_login_datetime, password_text) VALUES (?, ?, ?, ?, ?, ?, ?, aes_encrypt(?, "test_aes"))</sql-insert>

   <sql-update>UPDATE user SET user_name=?, create_datetime=?, expire_datetime=?, email_address=?, active_ind=?, user_type_code=?, last_login_datetime=?, password_text=aes_encrypt(?, "test_aes") WHERE user_id=?</sql-update>

</class>

<sql-query name="user">
    <return alias="user" class="com.kf.businessobject.impl.UserImpl" lock-mode="upgrade" />
    SELECT user_id AS {user.id}, user_name AS {user.username}, create_datetime AS {user.createDate}, expire_datetime AS {user.expireDate}, email_address AS {user.emailAddress}, active_ind AS {user.active}, user_type_code AS {user.userType}, last_login_datetime AS {user.lastLoginDate}, aes_decrypt(password_text, "test_aes") AS {user.password}
    FROM user
    WHERE user_id = ?
    FOR UPDATE
</sql-query>


</hibernate-mapping>

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

Code from our test case.

Code:

   UserDAO userDAO = new UserDAOHibernateImpl();
   
   User user = new UserImpl();
   user.setUsername("testusername");
   user.setPassword("welcome");
   user.setActive(true);
   user.setUserType(User.NORMAL);
   user.setCreateDate(new Date());
   
   userDAO.saveUser(user);
   
   //  Methods in the parent test class used to open
   //  and close hibernate sessions.
   closeSession();
   openSession();
   
   User loadUser = new UserImpl();
   
   userDAO.getUserById(user.getId());
   
   loadUser = userDAO.getUserById(user.getId());
   
   assertEquals(user.getPassword(), loadUser.getPassword());

The code for our DAO:

Code:

Object object = session.load( clazz, id );

Full stack trace of any exception that occurs:

Just to be clear in MySQL if the hash used in aes_encrypt and aes_decrypt doesn't work right a null is returned. Like the null that is recieved below.

Code:

java.lang.AssertionError: expected:<welcome> but was:<null>
   at org.junit.Assert.fail(Assert.java:58)
   at org.junit.Assert.failNotEquals(Assert.java:259)
   at org.junit.Assert.assertEquals(Assert.java:80)
   at org.junit.Assert.assertEquals(Assert.java:88)
   at com.kf.dao.impl.UserDAOHibernateImplTest.saveUser(UserDAOHibernateImplTest.java:55)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)
   at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)
   at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
   at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
   at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
   at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:71)
   at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
   at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
   at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
   at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
   at junit.framework.JUnit4TestAdapter.run(JUnit4TestAdapter.java:32)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

Name and version of the database you are using:

mysql Ver 14.12 Distrib 5.0.22, for Win32 (ia32)

The generated SQL (show_sql=true):

For the insert statement. I am hoping that the password_text would be aes_encrypt(?, "test_aes") instead of just a ?. This did work in 3.1 without any changes.

Code:

Hibernate: 
    insert 
    into
        user
        (user_name, create_datetime, expire_datetime, email_address, active_ind, user_type_code, last_login_datetime, password_text, domain_id) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?)

Here is the select statement for the user. This does have the aes_decrypt(password_text, "test_aes")code as I was hoping.

Code:

Hibernate: 
    SELECT
        user_id AS user1_26_0_,
        user_name AS user2_26_0_,
        create_datetime AS create3_26_0_,
        expire_datetime AS expire4_26_0_,
        email_address AS email5_26_0_,
        active_ind AS active6_26_0_,
        user_type_code AS user7_26_0_,
        last_login_datetime AS last8_26_0_,
        aes_decrypt(password_text,
        "test_aes") AS password9_26_0_,
        domain_id  AS domain10_26_0_     
    FROM
        user     
    WHERE
        user_id = ?     FOR UPDATE

Debug level Hibernate log excerpt:

Selected interesting logging...

Code:

...
2006-10-23 18:55:43,000 org.hibernate.persister.entity.AbstractEntityPersister DEBUG-> Insert 0: INSERT INTO user (user_name, create_datetime, expire_datetime, email_address, active_ind, user_type_code, last_login_datetime, password_text, domain_id) VALUES (?, ?, ?, ?, ?, ?, ?, aes_encrypt(?, "sn0wB@11"), ?)
2006-10-23 18:55:43,000 org.hibernate.persister.entity.AbstractEntityPersister DEBUG-> Update 0: UPDATE user SET user_name=?, create_datetime=?, expire_datetime=?, email_address=?, active_ind=?, user_type_code=?, last_login_datetime=?, password_text=aes_encrypt(?, "sn0wB@11"), domain_id=? WHERE user_id=?
2006-10-23 18:55:43,000 org.hibernate.persister.entity.AbstractEntityPersister DEBUG-> Delete 0: delete from user where user_id=?
2006-10-23 18:55:43,000 org.hibernate.persister.entity.AbstractEntityPersister DEBUG-> Identity insert: insert into user (user_name, create_datetime, expire_datetime, email_address, active_ind, user_type_code, last_login_datetime, password_text, domain_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
...

Problem:

We just upgraded from 3.1 to 3.2 and the Identity Insert seems to be used instead of the <sql-insert> we've tried to specify in our mapping file. I am hoping it is something simple that I am overlooking.

I'm sure I can do a work around by usings a named query for our saves, but I would prefer to use the <sql-insert> statement if possible.

To upgrade we took all the latest jars from the Hibernate 3.2 distribution and added them to our application. Here is the list of jars we currently have:

antlr-2.7.6.jar
asm.jar
asm-attrs.jar
c3p0-0.9.0.jar
cglib-2.1.3.jar
commons-beanutils.jar
commons-codec-1.3.jar
commons-collections-3.1.jar
commons-digester.jar
commons-discovery.jar
commons-el-1.0.jar
commons-lang-2.1.jar
commons-logging-1.0.4.jar
commons-validator.jar
dom4j-1.6.1.jar
ehcache-1.2.jar
hibernate3.jar
jdbc2_0-stdext.jar
jdom.jar
jta.jar
junit-4.0.jar
log4j-1.2.11.jar
mysql-connector-java-3.0.17-ga-bin.jar
xerces-2.6.2.jar

Any assistance is greatly appreciated. Also, if this issue is covered in the documentation somewhere a friendly/gental push in the right direction (like a URL or two) would be fantastic.

max · **Posted:** Tue Oct 24, 2006 5:42 am

hmm..weird that identiy insert is being used instead in 3.2.

This is probably a bug (or a change in behavior), but in any case sounds like we need to add <sql-identity-insert> support too for it to work for you.

Bug repots and patches should go to our jira.

hooknc · **Joined:** Mon Nov 29, 2004 2:00 pm **Posts:** 7

Thank you for your reply max.

Do I need to report the bug/enhancement?

Also, can you think of a possible work around for us? I was thinking of trying to use a named query to do our inserts? Do you think that would work?

Thank you again for your time.

max · **Posted:** Tue Oct 24, 2006 11:10 am

enhancement yes.

inserts could probably work BUT be aware that you sometimes cannot set null on parameters to queries.

max · **Posted:** Tue Oct 24, 2006 11:12 am

note, using identity insert together with stored procedures sounds weird....

hooknc · **Joined:** Mon Nov 29, 2004 2:00 pm **Posts:** 7

FYI.

We found a work around for our issue. We do two calls to saveOrUpdate() on our user objects that do not have ids. That way we get the user in the database with the identity insert and then we use the <sql-update> in our user.hbm.xml file to encrypt the users password.

I will get a enhancement request submitted sometime next week.

sdwr98 · **Joined:** Tue Nov 28, 2006 3:48 pm **Posts:** 5

This issue is a blocker for us - we were using <sql-insert> with a stored procedure and identity ID generation in Hibernate 3.1.3 and this is broken in 3.2.

I would call this a bug rather than an enhancement...

hooknc - did you file a JIRA issue? If not, I will...

max · **Posted:** Tue Nov 28, 2006 4:06 pm

are you saying that identity inserts worked fine with stored procedures in 3.1 ? it shouldn't ;)

sdwr98 · **Joined:** Tue Nov 28, 2006 3:48 pm **Posts:** 5

Yep - that's what I'm saying. Here's my mapping:

Code:

<class name="com.xxx.security.product.business.ProductDisplay">
       <id name="id" column="ProductLocalizationID" type="long" unsaved-value="-1">
            <generator class="identity"/>
        </id>
        <property name="entityId" type="long"/>
       <property name="owningDisplayId" type="long"/>
       <property name="locale" type="com.xxx.framework.hibernate.LocaleUserType"/>
       <property name="displayText" type="string"/>
       <property name="rowStatus" type="com.xxx.appcore.rowstatus.RowStatusUserType"/>
       <property name="approved" type="boolean"/>
       <property name="translated" type="boolean"/>
        <property name="createdOn" type="com.xxx.framework.hibernate.TimestampType"/>
        <property name="createdByUserId" type="long"/>
        <property name="modifiedOn" type="com.xxx.framework.hibernate.TimestampType"/>
        <property name="modifiedByUserId" type="long"/>
        
        <sql-insert callable="true">{call ProductLocalizationInsert(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}</sql-insert>
        <sql-update callable="true">{? = call ProductLocalizationUpdate(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}</sql-update>
        <sql-delete callable="true">{? = call ProductLocalizationDelete(?)}</sql-delete>
    </class>

The insert stored procedure executes a SELECT statement that returns the identity of the newly inserted row and that ID value is picked up by Hibernate.

So what you're saying is that this is accidental functionality?

max · **Posted:** Wed Nov 29, 2006 4:25 am

hibernate never used the return value from a custom select nor stored procedure as the id. It have always used a secondary operation (defined by the dialect) to get the generated id.

So something must have changed between 3.1 and 3.2 and it wasn't discovered since identity + custom insert was not tested for.

I guess what happens for you is that the custom sql is simply ignored when and only when you use identity as the id generator, correct ?

Could you please submit that as a bugreport with a link to this forum ?

Thank you

sdwr98 · **Joined:** Tue Nov 28, 2006 3:48 pm **Posts:** 5

I have submitted a bug and a patch for this issue in JIRA: http://opensource.atlassian.com/project ... e/HHH-2301

czerwonka · **Joined:** Thu Aug 19, 2004 7:57 am **Posts:** 14

max wrote:

note, using identity insert together with stored procedures sounds weird....

I don't know how you use both the sql-insert and id mapping together. How would hibernate know?

sdwr98 · **Joined:** Tue Nov 28, 2006 3:48 pm **Posts:** 5

Quote:

I don't know how you use both the sql-insert and id mapping together. How would hibernate know?

It works in the same way that identity insert works with Hibernate-generated SQL, it's just that we customize the SQL used. The mechanism for retrieving the ID is exactly the same.

jimmycallaaghan · **Joined:** Mon Jan 22, 2007 2:33 pm **Posts:** 3

See post in forum:
http://forum.hibernate.org/viewtopic.ph ... 78#2338378

daniel.granat · **Joined:** Tue Feb 20, 2007 5:56 am **Posts:** 1

I'm Having the same problem, only i don't have JBoss, so the link the the other post, isn't relevant to the core problem.
Has anyone submitted a bug on this?
If so, is there a fix/enhancement in the near future?

btw- i am using Hibernate Core 3.2.2 GA