Hibernate Community • View topic - Weird query results..

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Weird query results..

Page 1 of 1

[ 4 posts ]

Previous topic | Next topic

Author

Message

tr1be

Post subject: Weird query results..

Posted: Wed Sep 17, 2003 3:03 pm

Newbie

Joined: Wed Sep 17, 2003 1:59 pm
Posts: 3

I have the following code being executed.. NumericCapability and StringCapability are subclasses of DeviceCapability.

Code:

public List findByDevice(Device device) {
  List result = (List) getHibernateTemplate().find(
    "select deviceCapType " +
    "from DeviceCapType as deviceCapType, NumericCapability as numericCapability, " +
    "StringCapability as stringCapability, Device as device " +
    "where ((deviceCapType.id = numericCapability.capability.id and device.id = numericCapability.device.id) "+
    "   or (deviceCapType.id = stringCapability.capability.id and device.id = stringCapability.device.id)) " +
    "  and device.id = ? " +
    "order by deviceCapType.name",
    device.getId(),
    Hibernate.LONG);
  if (result.size() > 0) {
    return result;
  }
  return null;
}

The query is seemingly correct in syntax, and in design -- but Hibernate prints it like this in STDOUT:

select deviceca0_.CAPABILITY_ID as CAPABILITY_ID, deviceca0_.CLASSIFICATION_ID as CLASSIFI2_,
deviceca0_.CAPABILITY_NAME as CAPABILI3_
from DEVICE_CAPABILITY_TYPE deviceca0_,
DEVICE_CAPABILITIES numericc1_, DEVICE_CAPABILITIES stringca2_, DEVICE device3_
where numericc1_.TYPE='N' and stringca2_.TYPE='S'
and (
((deviceca0_.CAPABILITY_ID=numericc1_.CAPABILITY_ID)and(device3_.DEVICE_ID=numericc1_.DEVICE_ID ))
or((deviceca0_.CAPABILITY_ID=stringca2_.CAPABILITY_ID)and(device3_.DEVICE_ID=stringca2_.DEVICE_ID ))
and(device3_.DEVICE_ID=? ))
order by deviceca0_.CAPABILITY_NAME

NB: if the volded area contained an OR instead of an AND, this query would work.

Sorry for the hibernate ugliness :)

Top

tr1be

Post subject:

Posted: Wed Sep 17, 2003 3:06 pm

Newbie

Joined: Wed Sep 17, 2003 1:59 pm
Posts: 3

Sorry, I didn't make this clear in the intial email:

The first bit of code always returns null, because the type cannot be both S and N at the same time.

If this and were to be an OR, then the query would work properly.. but since I am not specifying this anywhere, I'm not sure how to control it.

Any ideas?

Thanks,

A. Shah

Top

steve

Post subject:

Posted: Wed Sep 17, 2003 8:38 pm

Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX

I'm going to take a wild guess that NumericCapability and StringCapability are mapped as subclasses with discriminator values of 'N' and 'S' respectively. If so, referencing those subclasses directly caused the inclusion of their discriminator values.

How about posting your mappings as relate to the classes DeviceCapType, NumericCapability, StringCapability, and Device; as well as a short description in english of what you want the query to accomplish?

Top

tr1be

Post subject:

Posted: Fri Sep 19, 2003 9:49 am

Newbie

Joined: Wed Sep 17, 2003 1:59 pm
Posts: 3

Hi Steve,

here are the mappings and what i'm trying to do:

I want to find the capability (whether its Numeric or String) given a specific Device and capability_id. The initial post contains the query I used, and the second post contains the SQL outputted by hibernate.

Any ideas on how I can make that bolded AND an OR?

Thanks,

Ameet

---------------------------

DeviceCapType:

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.tira.coreserv.domain.base.DeviceCapType"
        table="DEVICE_CAPABILITY_TYPE"
        dynamic-update="true"
        dynamic-insert="false"
    >

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

        <many-to-one
            name="classification"
            class="com.tira.coreserv.domain.base.DeviceCapClass"
            cascade="save-update"
            outer-join="auto"
            update="true"
            insert="true"
            column="CLASSIFICATION_ID"
            not-null="true"
        />

        <property
            name="name"
            type="java.lang.String"
            update="true"
            insert="true"
            column="CAPABILITY_NAME"
            not-null="true"
        />

        <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-DeviceCapType.xml
            containing the additional properties and place it in your merge dir.
        -->

    </class>
</hibernate-mapping>

DeviceCapability:

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.tira.coreserv.domain.base.DeviceCapability"
        table="DEVICE_CAPABILITIES"
        dynamic-update="true"
        dynamic-insert="false"
    >

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

        <discriminator
            column="TYPE"
            type="string"
            length="1"
        />

        <many-to-one
            name="capability"
            class="com.tira.coreserv.domain.base.DeviceCapType"
            cascade="save-update"
            outer-join="auto"
            update="true"
            insert="true"
            column="CAPABILITY_ID"
            not-null="true"
        />

        <many-to-one
            name="device"
            class="com.tira.coreserv.domain.base.Device"
            cascade="save-update"
            outer-join="auto"
            update="true"
            insert="true"
            column="DEVICE_ID"
            not-null="false"
        />

        <many-to-one
            name="firmware"
            class="com.tira.coreserv.domain.base.Firmware"
            cascade="save-update"
            outer-join="auto"
            update="true"
            insert="true"
            column="FIRMWARE_ID"
            not-null="false"
        />

        <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-DeviceCapability.xml
            containing the additional properties and place it in your merge dir.
        -->
        <subclass
            name="com.tira.coreserv.domain.base.NumericCapability"
            dynamic-update="false"
            dynamic-insert="false"
            discriminator-value="N"
        >

        <property
            name="numericValue"
            type="java.lang.Long"
            update="true"
            insert="true"
            column="NUMERIC_CAPABILITY"
            not-null="false"
        />

        </subclass>
        <subclass
            name="com.tira.coreserv.domain.base.StringCapability"
            dynamic-update="false"
            dynamic-insert="false"
            discriminator-value="S"
        >

        <property
            name="stringValue"
            type="java.lang.String"
            update="true"
            insert="true"
            column="STRING_CAPABILITY"
            not-null="false"
        />

        </subclass>

    </class>

</hibernate-mapping>

Top

Page 1 of 1

[ 4 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum