MySQL stored procedures and hibernate

spoons10 · **Joined:** Tue Apr 19, 2005 9:39 am **Posts:** 45

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0

I have done alot of searching on the web for help on stored procedures and Hibernate (using mysql 5.0). I am having trouble finding valuable examples of mapping the the stored procedure. Many times it talks about returning a reference cursor as the first parameter in the stored procedure call; however, MySQL 5.0 doesn't allow referenced cursors, all cursors must be local to the stored procedure.

Can anyone direct me to a good example of MySQL, Hibernate stored procedure mapping? And/or provide an example?

Thank you for any advice.

B

VotTak · **Joined:** Tue Sep 05, 2006 11:02 am **Posts:** 2

Any news on this? Any examples?

spoons10 · **Joined:** Tue Apr 19, 2005 9:39 am **Posts:** 45

No I have heard nothing :(

max · **Posted:** Tue Sep 05, 2006 4:18 pm

so if mysql cannot return resultsets from a stored procedure/function then we can't do much about supporting it.....

spoons10 · **Joined:** Tue Apr 19, 2005 9:39 am **Posts:** 45

Max,
Thank you for responding, I almost forgot that I posted this message. Anywho, MySql can return result sets, but I have had troubles finding documentation on the requirements that Hibernate places on the stored procedures. I have to run to class but when I get back I will post some of my code and more specific questions. Thanks alot for responding!!

Brandon

spoons10 · **Joined:** Tue Apr 19, 2005 9:39 am **Posts:** 45

Max,

Here is my stored procedure, what it does is create a temporary table and populate it with the things that the passed user has reference to. This is because when I delete a user I want to see the things he refereneces before deleting him/her. I have set up FK constraints that will cascade when I delete a user. I can reassign certain items before deleting a person to insure they presist. Each one of the procedure calls within the 'getReferencedItems' will update the temporary table if they are any references. Mysql can not return a referenced cursor, but they can return a result set: noted by the select * statement at the end of the stored procedure.

Code:

CREATE PROCEDURE   getReferencedItems
(IN userID INTEGER) 
   BEGIN

   /* create the temporary table to place results in */
   DROP TEMPORARY TABLE IF EXISTS itemReference;
   CREATE TEMPORARY TABLE itemReference (
      itemID int NOT NULL,
      type int NOT NULL,
      name varchar(50) NOT NULL,
      PRIMARY KEY (itemID, type)
   );


   /* call the individual procedures that will check referenced items */
   Call getReferencedDocuments(userID);
   Call getReferencedFolders(userID);
   Call getReferencedEvents(userID);
   Call getReferencedTask_to(userID);
   Call getReferencedTask_by(userID);
   Call getReferencedTaskHistory(userID);
   Call getReferencedAnnouncements(userID);
   Call getReferencedLinks(userID);
   Call getReferencedForums(userID);
   Call getReferencedAcronyms(userID);

   select * from itemReference;

   END

Now I want to be able to use this stored procedure (hopefully using hibernate to keep the application consistant). However, I am pretty much a newbie with Hibernate so any direction and or examples would be very helpful. All the stored procedure examples I've seen have passed in a '?' as the first parameter, assuming this is the referenced cursor...is there another way?

Thanks for any help you can provide.

Brandon

max · **Posted:** Wed Sep 06, 2006 7:04 pm

so implement the Dialect methods that related to callable and register parameters ...then if mysql drivers work like most other servers it should work.

spoons10 · **Joined:** Tue Apr 19, 2005 9:39 am **Posts:** 45

Max, thanks I got it to work. I will make sure I give you a ranking :)

Another question:

I have 3 tables {Group, FeatureAccess, Feature}. The Group table and the Feature table have a many-to-many relationship, and per Database Design, I created an associative table that makes the relationships one-to-manies.

Okay without going into too much detail, here is the concept. An User can be part of MANY Groups and a Group has Many Features (this relationship is
Group (1) -----> (1..*) FeatureAccess (1..*) <----- (1) Feature

FeatureAccess has attributes Group, Feature, and access (int)

I want to be able to do group.getFeatureAccess() and feature.getFeatureAccess()

Here are my mapping files:

Code:

   Group.hbm.xml

<?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.saic.ide.group.Group" table="idegroup">
      <id name="id" column="groupID" type="long">
         <generator class="native"/>
      </id>   
      <property name="groupName" column="groupName"    type="string"/>
      <property name="description" column="groupDescription" type="string"/>
      
      <set name="features" table="featureaccess" lazy="false">
          <key column="groupID"/>
          <composite-element class="com.saic.ide.menu.feature.FeatureAccess">
              <property name="access" type="int" not-null="true"/>
             <many-to-one name="feature" class="com.saic.ide.menu.feature.Feature" lazy="false" column="featureID"/>
          </composite-element>
      </set>
   </class>
   
</hibernate-mapping>

Code:

Feature.hbm.xml


<?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.saic.ide.menu.feature.Feature" table="feature">
      <id name="id" column="featureID" type="long">
         <generator class="native"/>
      </id>   
      <property name="visible" column="visible"    type="boolean"/>
      <property name="iconPath" column="iconPath"    type="string"/>
      <property name="feature" column="name"   type="string"/>
      <property name="forward" column="forward"   type="string"/>   
      <many-to-one name="menu" column="menuID" class="com.saic.ide.menu.Menu" not-null="false" lazy="false"/>   
      <!--<set name="groups" table="featureaccess" lazy="false">
         <key column="featureID"/>
         <many-to-many class="com.saic.ide.group.Group" column="groupID"/>
      </set>
      -->
      <set name="accessGroups" table="featureaccess" lazy="false">
          <key column="featureID"/>
          <composite-element class="com.saic.ide.menu.feature.FeatureAccess">
              <property name="access" type="int" not-null="true"/>
             <many-to-one name="group" class="com.saic.ide.group.Group" column="groupID" lazy="false"/>
          </composite-element>
      </set>
      
   </class>

Code:

User.hbm.xml

<?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.saic.ide.user.User" table="user">
      <id name="id" column="userID" type="long">
            <generator class="increment"/>
      </id>
      <property name="firstName" column="firstname"   type="string"/>
      <property name="lastName"  column="lastname"   type="string" />
      <property name="middleName"  column="middle"   type="string" />
      <property name="email"  column="email"   type="string" />
      <property name="title"  column="title"   type="string" />

      <set name="groups" lazy="false" table="usergroup">
         <key column="userID"/>
         <many-to-many class="com.saic.ide.group.Group" column="groupID"/>
      </set>
   
   </class>
   
</hibernate-mapping>

When hibernate retrieves a User there are several random inserts and deletes they look like:

Code:

Hibernate: select this_.groupID as groupID5_0_, this_.groupName as groupName5_0_, this_.groupDescription as groupDes3_5_0_ from idegroup this_
Hibernate: select features0_.groupID as groupID0_, features0_.access as access0_, features0_.featureID as featureID0_ from featureaccess features0_ where features0_.groupID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: delete from featureaccess where groupID=? and access=?
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)

I did some research and it says Hibernate sometimes does brute force type retrieves but the blog was on bags and idbags. All these random inserts and deletes are killing performance. And as more elements are added to the FeatureAccess table these random inserts/deletes increase proportionally.

Do you have any advice. I will be more than happy to give you more detail if you need it.

max · **Posted:** Fri Sep 15, 2006 10:47 am

hibernate doesn't do random inserts and updates....it inserts and updates exaclty what the object graph contains/reports.

in any case, this is a different question and should be in a different thread.

spoons10 · **Joined:** Tue Apr 19, 2005 9:39 am **Posts:** 45

Okay, I will post this on another thread. Coming from a database concentration, when I do a select statement I don't expect inserts and deletes to show up in the log when show_sql = true. Sorry for dumb questions I am still trying to figure out all of nuts and bolts of hibernate.

Thanks for the quick reply.