Insert,Update,Delete via Procedures and Functions

In the previous post, we saw how to create custom queries for Entity updating, insertion and deletion.Hibernate allows the use of both SQL procedures and functions to perform the Create/Delete and Update operations. I used the same entity class (Item) but instead of using the auto-increment property, I used the assigned generator. The reason for the change was issues faced in getting the insert procedure to work with auto-increment value. Hibernate needs this value which it is the result of the session.save method.
The procedures created for the CUD operations is as below:

delimiter |
CREATE PROCEDURE myInsertProc(
in_model VARCHAR(255),in_shelf_id INT(11),in_id INT(11)) 
BEGIN
        insert into ITEM (MODEL, SHELF_ID, ID) 
        values (in_model, in_shelf_id, in_id);
END|
CREATE PROCEDURE myUpdateProc(
OUT count INT(11),IN in_model VARCHAR(255),IN in_shelf_id INT(11),IN in_id INT(11)) 
BEGIN
        Update ITEM
        set MODEL =in_model,
            SHELF_ID =in_shelf_id
        where
        ID = in_id;
        SET count = ROW_COUNT();
END|
CREATE PROCEDURE myDeleteProc(IN in_id INT(11))
BEGIN
    delete from ITEM
    where ID = in_id;
END|

To get the last value for Id, I created a mysql function that returns the maximum value
for id in the Item table.

CREATE FUNCTION GET_LAST_ID ()
  RETURNS INT
    BEGIN
     DECLARE lastRecId INT;
     select 
        max(id)
     into
        lastRecId 
     from item;   
     RETURN lastRecId;
  END|

The logic inside the procedures or function is not the best and is certainly not production worthy. However the focus here is on simply performing the operations via Hibernate.The modified hbm file is as below.

<?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.custom.crud">
    <class name="Item" table="ITEM">
        <id name="id" type="integer" column="ID">
            <generator class="assigned" />
        </id>
        <property name="model" column="MODEL" />
        <many-to-one name="shelf" class="Shelf" column="SHELF_ID"
            foreign-key="ITEM_FK1" />
        
        <sql-insert callable ="true" check ="none">
            {call myInsertProc(?,?,?)} 
        </sql-insert>
        <sql-update callable ="true" check ="param">
            {call myUpdateProc(?,?,?,?)} 
        </sql-update>
        <sql-delete callable ="true" check ="none">
            {call myDeleteProc(?)} 
        </sql-delete>
    </class>
</hibernate-mapping>

The same elements as in the previous post have been used. However a callable attribute has been set to true.This enables support for callable statements in Hibernate. It also allows correct handling of the procedure parameters.
A second check attribute has been added to each of these elements. The default value is "count". In this case hibernate checks the number of modified rows via plain JDBC. This technique is used for when we use customized SQL statements.
In our elements we have used the none property for insert and delete procedures.In this case Hibernate expects the custom query to indicate if there was any failure in the operation.
In case of update we have used the param property. This is the last value. In this case Hibernate reserves an OUT parameter to get the return value of the procedure call. The update procedure returns the number of rows updated bu the query. The OUT parameter is always the first parameter of the procedures.  
The sequence of the other elements must be same as the sequence which would be used in Hibernate's queries. This allows Hibernate to assign the correct parameters to the attributes of the procedure.
The start up logs indicate the queries to use:

3031 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister  -  Ins
ert 0: {call myInsertProc(?,?,?)}
3031 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister  -  Upd
ate 0: {call myUpdateProc(?,?,?,?)}
3031 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister  -  Del
ete 0: {call myDeleteProc(?)}

Test methods similar to last time was used to test the code:

static SessionFactory sessionFactory;
    static final String MODEL_NAME = "Italiana";
    static Integer testObjectId ;
    public static void main(String[] args) {
        Configuration configuration = new Configuration();
        configuration = configuration.configure();
        sessionFactory = configuration.buildSessionFactory();
        testCreate();
        testUpdate();
        testDelete();
    }

    static void testCreate() {
        Item item = new Item();
        item.setModel(MODEL_NAME);
        
        Session session = sessionFactory.openSession();
        String query = "select GET_LAST_ID() as recId";
        Transaction t = session.beginTransaction();
        testObjectId = (Integer) session.createSQLQuery(query).uniqueResult();
        if (null == testObjectId) {
     //no records in db
     testObjectId = 0;
 }
        item.setId(++testObjectId);
        Shelf shelf = (Shelf) session.get(Shelf.class, 1);
        item.setShelf(shelf);
        session.save(item);
        t.commit();
        
    }

    static void testUpdate() {
        Session session = sessionFactory.openSession();
        Transaction t = session.beginTransaction();
        Item item = (Item) session.get(Item.class, testObjectId);
        item.setModel("Mark");
        t.commit();
    }
    
    static void testDelete() {
        Session session = sessionFactory.openSession();
        Transaction t = session.beginTransaction();
        Item item = (Item) session.get(Item.class, testObjectId);
        session.delete(item);
        t.commit();
    }

The highlighted lines indicate the use of the SQL function in the query. Hibernate treats it like the other MySQL functions and the call is successful returning the last id.

3234 [main] DEBUG org.hibernate.SQL  - 
    select
        GET_LAST_ID() as recId
...
4046 [main] DEBUG org.hibernate.SQL  - 
    {call myInsertProc(?,?,?)}
...
4171 [main] DEBUG org.hibernate.SQL  - 
    {call myUpdateProc(?,?,?,?)}
4265 [main] DEBUG org.hibernate.SQL  - 
    {call myDeleteProc(?)}