Multiple Table Update from UserType

rosswilliamdrew · **Posted:** Tue Mar 03, 2009 7:00 am

Hello again,

I'm looking to do something that seems quite simple but I can't get my head around it.

I have two tables that are linked but I'm trying to avoid JOINs. For example, I have a Network class and NetworkUser class but in the database the Network class has a field containing a comma seperated list of all the users.

So when Hibernate is inserting into the database I want it to create a table row in the NetworkUsers table for every NetworkUser class in the Network class and generate a comma separated list for the Users field in the Network table.
When Hibernate is retrieving from the database I want it to get the Network class and the Users field and for every user in that field, create the NetworkUser class.

I have Hibernate generating the comma seperated NetworkUser list but have no idea how to also create/retrieve the NetworkUser entry in the NetworkUser table. There is no point in showing you the NullSafeGet at this point as it's useless but the NullSafeSet is as follows

Code:

   public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session)
         throws HibernateException, SQLException {
      
      /*DEBUG*///System.out.println("Object Value: "+value);
      
      if (value == null)
         throw new IllegalArgumentException( "Received object in nullSafeSet was null - " + returnedClass() );
 
      if (!(value instanceof Set 
            && (((Set)value).iterator().next()) instanceof Resource)){
         throw new IllegalArgumentException( "Received object in nullSafeSet was " + value.getClass().getName() + " in " + returnedClass() );
      }
            
      
      String resources="";
      if (value == null) {
         st.setString(index, "");
       } else {
          //Build the comma separated lists for database
             for (Resource r : (Set<Resource>)value){//if this is not a set of resources, the exception would have been thrown already
                resources+=r.getName()+","; 
             }
             
          //remove the additional comma
             resources=resources.substring(0,(resources.length()-1));
       }
      
   /*DEBUG*/System.out.println("Prepared Statement: "+st.toString());
      Hibernate.STRING.nullSafeSet(st, resources, index);
   }

and the mapping file looks like this

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="Event" table="EVENTS" mutable="false">
        <id name="id" column="EVENT_ID" unsaved-value="-1">
            <generator class="native"/>
        </id>
        
        <property name="date" type="timestamp" column="event_date"/>
        <property name="title"/>
        
      <set name="clients" cascade="save-update" table="CLIENT" >
         <key column="EVENT_ID" />
         <one-to-many class="Client" />
      </set>
      
        <property name="requiredResources" type="ResourcesUserType" column="resourceNames" />     
    </class>
    

    <class name="Client" table="Client" mutable="false">
        <id name="id" column="CLIENT_ID" >
            <generator class="native"/>
        </id>
        <property name="name"/>
    </class>

<!-- TODO: Make this into the comma separated list -->
    <class name="Resource" table="Resource" mutable="false"> 
        <id name="id" column="RESOURCE_ID" unsaved-value="-1">
            <generator class="native"/>
        </id>
        <property name="name"/>
    </class>
    
</hibernate-mapping>

rosswilliamdrew · **Posted:** Tue Mar 03, 2009 8:02 am

I have managed to get my program to create the required rows but it's an ugly solution, surely this isn't the only way it can be done in Hibernate

Code:

   public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session)
         throws HibernateException, SQLException {
      
      /*DEBUG*///System.out.println("Object Value: "+value);
      
      if (value == null)
         throw new IllegalArgumentException( "Received object in nullSafeSet was null - " + returnedClass() );
 
      if (!(value instanceof Set 
            && (((Set)value).iterator().next()) instanceof Resource)){
         throw new IllegalArgumentException( "Received object in nullSafeSet was " + value.getClass().getName() + " in " + returnedClass() );
      }
            
      String resources="";
      if (value == null) {
         st.setString(index, "");
       } else {
          //Build the comma separated lists for database
             for (Resource r : (Set<Resource>)value){//if this is not a set of resources, the exception would have been thrown already
                resources+=r.getName()+",";
[b]             //XXX: Insert resource into resource table (also add check to make sure name is unique)
                session.connection().prepareStatement("insert into resource(resource_id,name) values (null, '"+r.getName()+"'); ").executeUpdate();[/b]
             }
             
          //remove the additional comma
             resources=resources.substring(0,(resources.length()-1));
       }
      
   /*DEBUG*///System.out.println("Prepared Statement: "+st.toString());
      Hibernate.STRING.nullSafeSet(st, resources, index);
   }

drlaz · **Joined:** Tue Mar 03, 2009 2:58 pm **Posts:** 10

Needs a little polish, though. You will get significantly improved client-side performance if you replace all those String manipulations with a StringBuilder or StringBuffer. You may also find the built-in java.util.StringTokenizer to be useful compared to rolling your own.