There must be a simpler way to do an insert

baront · **Joined:** Wed Jan 10, 2007 1:30 pm **Posts:** 3

Hi,

I am doing the following using hibernate to create a new record in the db.

Code:

   AssetUpload newAsset = new AssetUpload();
      
      String hqlQueryString = "from sysSecurityLogin where loginId = " + loginId;
      sysSecurityLogin login = (sysSecurityLogin) session.createQuery(hqlQueryString).uniqueResult();
      newAsset.setLogin(login);
      
      hqlQueryString = "from ClientProduct where productId = " + productId;
      ClientProduct product = (ClientProduct) session.createQuery(hqlQueryString).uniqueResult();
      newAsset.setProduct(product);
      
      hqlQueryString = "from AssetCategory where categoryId = " + categoryId;
      AssetCategory tempAssetCategory = (AssetCategory) session.createQuery(hqlQueryString).uniqueResult();
      newAsset.setCategory(tempAssetCategory);
      
      hqlQueryString = "from AssetFileType where fileTypeId = " + fileTypeId;
      AssetFileType fileType = (AssetFileType) session.createQuery(hqlQueryString).uniqueResult();
      newAsset.setFileType(fileType);
      
      hqlQueryString = "from VendorDetail where vendorId = " + fileTypeId;
      VendorDetail vendorDetail = (VendorDetail) session.createQuery(hqlQueryString).uniqueResult();
      newAsset.setDetail(vendorDetail);

      Date date = new Date();
      newAsset.setUploadedDate(date);
      
      newAsset.setSourceFileName(sourceFileName);
      newAsset.setPart(part);
      
      
      session.save(newAsset);

and the assetUpload.hql is as follows:

Code:

<hibernate-mapping>
    <class name="net.olas.hibernate.collaboration.asset.AssetUpload" table="upload" schema="asset" catalog="HARMONY">
        <id name="uploadId" type="java.lang.Integer">
            <column name="uploadId" />
            <generator class="native"></generator>
        </id>
        <many-to-one name="product" class="net.olas.hibernate.collaboration.client.ClientProduct" fetch="select">
            <column name="productId" not-null="true" />
        </many-to-one>
        <many-to-one name="detail" class="net.olas.hibernate.collaboration.vendor.VendorDetail" fetch="select">
            <column name="vendorId" not-null="true" />
        </many-to-one>
        <many-to-one name="login" class="net.olas.hibernate.collaboration.sysSecurity.sysSecurityLogin" fetch="select">
            <column name="loginId" not-null="true" />
        </many-to-one>
        <many-to-one name="fileType" class="net.olas.hibernate.collaboration.asset.AssetFileType" fetch="select">
            <column name="fileTypeId" not-null="true" />
        </many-to-one>
        <many-to-one name="category" class="net.olas.hibernate.collaboration.asset.AssetCategory" fetch="select">
            <column name="categoryId" not-null="true" />
        </many-to-one>
        <property name="uploadedDate" type="timestamp">
            <column name="uploadedDate" length="23" not-null="true" />
        </property>
        <property name="sourceFileName" type="string">
            <column name="sourceFileName" not-null="true" />
        </property>
        <property name="part" type="string">
            <column name="part" length="50" />
        </property>
        <set name="fileDetails" inverse="true">
            <key>
                <column name="uploadId" not-null="true" />
            </key>
            <one-to-many class="net.olas.hibernate.collaboration.asset.AssetFileDetail" />
        </set>
    </class>
</hibernate-mapping>

this all works fine. But it seems like bloat to have to create the objects first when a straight sql statement would just use the id's. I have looked at the insert using hql but this seemed impossible because I have so many one to many relationships to handle to insert this one record.

Code:

kochcp · **Posted:** Wed Jan 10, 2007 4:40 pm

Well, that's kind of the purpose of hibernate, to work with the database using java objects. You can grab the connection out of the SessionFactory and use a straight sql statement if you want and when you try to load that NewAsset it will load all the related rows.

I don't know why you are using hql when simple session.load calls will work for all those objects.

If you are trying to minimize the data returned by the load commands, you can load the classes lazily and simply use the proxy object that is generated by hibernate to set up the relationships.

mbrunecky · **Joined:** Wed Dec 07, 2005 4:19 pm **Posts:** 53

This is one of thuse cases where using Object model seems to be an extra work. As the previous poster suggests, you can make your code look much cleaner by using

Code:

Whatever object = (Whatever)session.load(Whatever.class, whateverId)

... but it will still make multiple querries, even though you can "insert" your entire object with a simple JDBC query.

If you strive for Hibernate encapsulation for the sake of hiding the SQL, you could probably burry this insert in a stored procedure.
(assuming your database supports stored procedures).

Some things can be also done thru bulk updates, but I am afraid it won't be possible for this, as your associations hide the Id.

david · **Posted:** Wed Jan 10, 2007 8:36 pm

If you are using session.load and the domain object are lasy loaded then you will NOT hit the DB. It will only create the proxy.

baront · **Joined:** Wed Jan 10, 2007 1:30 pm **Posts:** 3

Thanks for all your help... the solution makes absolute sense now. I missed the purpose of the session.load function. I was thinking I would need to instantiate the complete objects in a sql fashion rather than rely on the lazy loading as all I required was the id's.

If any one is interested the code now looks like this and as suggested only does one insert statement into the db. Brilliant!

Code:

// this is the one to be saved
      AssetUpload newAsset = new AssetUpload();
      
      sysSecurityLogin login = (sysSecurityLogin) session.load(sysSecurityLogin.class,Integer.valueOf(loginId));
      newAsset.setLogin(login);
      
      ClientProduct product = (ClientProduct) session.load(ClientProduct.class,Integer.valueOf(productId));
      newAsset.setProduct(product);
      
      AssetCategory tempAssetCategory = (AssetCategory) session.load(AssetCategory.class,Integer.valueOf(categoryId).intValue());
      newAsset.setCategory(tempAssetCategory);
      
      AssetFileType fileType = (AssetFileType) session.load(AssetFileType.class,Integer.valueOf(fileTypeId).intValue());
      newAsset.setFileType(fileType);
      
      VendorDetail vendorDetail = (VendorDetail) session.load(VendorDetail.class, Integer.valueOf(vendorId).intValue());
      newAsset.setDetail(vendorDetail);

      Date date = new Date();
      newAsset.setUploadedDate(date);
      
      newAsset.setSourceFileName(sourceFileName);
      newAsset.setPart(part);
      
      
      session.save(newAsset);