Hibernate Session.seach() method

gpani · **Posted:** Mon May 03, 2004 1:29 pm

I am using Hibernate 2.1.2.

The following is the code in question.

Code:

Session hibernateSession = HibernateManager.currentSession();
Criteria criteria = hibernateSession.createCriteria(Market.class);
Example example = Example.create(requestMarket).excludeZeroes().ignoreCase().enableLike();
criteria.add(example);
criteria.addOrder(Order.asc("marketName"));
List marketList = criteria.list();

The Market object retrieves 900+ objects from the DB. The corresponding query output in stdout.log is as follows.

Code:

select market0_.MARKETID as MARKETID0_, market0_.MARKETNAME as MARKETNAME0_, market0_.NEWCODEVALUE as NEWCODEV3_0_, market0_.UPGRADECODEVALUE as UPGRADEC4_0_, market0_.MARKETREFERENCECODE as MARKETRE5_0_, market0_.ACTIVE as ACTIVE0_, market0_.CREATEBY as CREATEBY0_, market0_.CREATEON as CREATEON0_, market0_.UPDATEBY as UPDATEBY0_, market0_.UPDATEON as UPDATEON0_ from MARKETS market0_ where market0_.MARKETID=?

The only issue I have with this is that it is being executed 900+ times instead of executing with a wildcard WHERE 1=1 and then just going through the results and preparing the list. Is there any way to make this efficient that I am missing? I can't have the object be Lazy because I need all the instances of the object and I think that would defeat the purpose anyway.

Here is the mapping for the Market object

Code:

<?xml version="1.0"?>

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

<hibernate-mapping>
  <class name="com.brightpoint.alltel.beans.site.Market" table="MARKETS" dynamic-update="false" dynamic-insert="false">

    <id name="marketId" column="MARKETID" type="java.lang.Long" unsaved-value="null">
      <generator class="sequence">
        <param name="sequence">SEQ_MARKETID</param>
      </generator>
    </id>

    <property name="marketName" type="java.lang.String" update="true" insert="true" column="MARKETNAME" not-null="true"/>
      <property name="newCodeValue" type="java.lang.String" update="true" insert="true" column="NEWCODEVALUE" not-null="true"/>
    <property name="upgradeCodeValue" type="java.lang.String" update="true" insert="true" column="UPGRADECODEVALUE" not-null="true"/>
    <property name="marketReferenceCode" type="java.lang.String" update="true" insert="true" column="MARKETREFERENCECODE" not-null="true"/>
    <property name="active" type="java.lang.Boolean" update="true" insert="true" column="ACTIVE" not-null="true"/>
    <many-to-one name="createBy" class="com.brightpoint.alltel.beans.user.User" cascade="none" outer-join="false" update="false" insert="true" column="CREATEBY" not-null="true" unique="false"/>
    <property name="createOn" type="java.sql.Timestamp" update="false" insert="true" column="CREATEON"/>
    <many-to-one name="updateBy" class="com.brightpoint.alltel.beans.user.User" cascade="none" outer-join="false" update="true" insert="true" column="UPDATEBY" not-null="true" unique="false"/>
    <property name="updateOn" type="java.sql.Timestamp" update="true" insert="true" column="UPDATEON"/>
    <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-Market.xml
            containing the additional properties and place it in your merge dir.
        -->
  </class>
</hibernate-mapping>

emmanuel · **Posted:** Mon May 03, 2004 7:38 pm

Put outer-join=true on many-to-one and allow max_depth_fetch to a decent value.

gpani · **Posted:** Mon May 03, 2004 8:15 pm

Emmanuel,

Thanks for the response. Would setting the outer-join to true have any bearing on the Market object itself? The many-to-one relationships are for lazy objects and those aren't the ones being queried multiple times. The same probably applies to the max_depth_fetch because it isn't the many-to-one object that is being queried multiple times but the Market object itself. Can't hibernate just do a "WHERE 1=1" and retrieve all Market objects?

gpani · **Posted:** Mon May 03, 2004 9:15 pm

Emmanuel,

Thanks for your feedback. I missed the point and was being rather stupid. The Market object was being populated by a join object (called CatalogMarkets) instead of being called directly. The relationship in the join object was a many-to-one. Once I changed outer-join="true" for that object on the Market everything works fine. Sorry for the confusion.