Hibernate Community • View topic - One to many relations in queries

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

One to many relations in queries

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

bemar

Post subject: One to many relations in queries

Posted: Tue Mar 11, 2008 5:45 am

Newbie

Joined: Mon Mar 10, 2008 11:55 am
Posts: 8

Hi @ all,

Hope anybody can help me.

I have a table "pref_language" with all possible languages (currently german and english). Furthermore I have table "pref_Translations" with all translations of an issue in example "table" for english and "Tisch" for german.

pref_language

Code:

pl_id     pl_name      pl_shortage
1          deutsch       ger
2          englisch      eng

pref_translation

Code:

pt_id    pl_id      pt_translation      pt_transl_group
12       1           Tisch                4
13       2           Table                4

How do I have to build the query in "findByTranslGroup" (look bottom)
to fetch the correct value from "pref_language" and "transl_group"

Code:

findByTranslGroup("eng",4);

In the current query I will get two lines.

I want the result as object of entity "pref_translation" or only the translated string.

Hibernate version: MyEclipse 6.0 bzw. Hibernate 3.x

Mapping documents:

Code:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- 
    Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
    <class name="de.bemar.hibernate.PrefLanguage" table="pref_language" catalog="test">
        <id name="plId" type="java.lang.Integer">
            <column name="pl_id" />
            <generator class="assigned" />
        </id>
        <property name="plName" type="java.lang.String">
            <column name="pl_name" length="45" not-null="true" />
        </property>
        <property name="plShortage" type="java.lang.String">
            <column name="pl_shortage" length="3" not-null="true" />
        </property>
        <set name="prefTranslations" inverse="true">
            <key>
                <column name="pl_id" not-null="true">
                    <comment>Spach ID</comment>
                </column>
            </key>
            <one-to-many class="de.bemar.hibernate.PrefTranslation" />
        </set>
    </class>
</hibernate-mapping>

Code:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- 
    Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
    <class name="de.bemar.hibernate.PrefTranslation" table="pref_translation" catalog="test">
        <id name="ptId" type="java.lang.Integer">
            <column name="pt_id" />
            <generator class="assigned" />
        </id>
        <many-to-one name="prefLanguage" class="de.bemar.hibernate.PrefLanguage" fetch="select">
            <column name="pl_id" not-null="true">
                <comment>Sprach ID</comment>
            </column>
        </many-to-one>
        <property name="ptTranslation" type="java.lang.String">
            <column name="pt_translation" not-null="true">
                <comment>Uebersetzungstext</comment>
            </column>
        </property>
        <property name="ptTranslGroup" type="java.lang.Integer">
            <column name="pt_transl_group" not-null="true">
                <comment>Ãœber diesen Key wird die Translation geholt</comment>
            </column>
        </property>
        <set name="prefGroups" inverse="true">
            <key>
                <column name="pg_transl_id" not-null="true">
                    <comment>Uebersetzungsgruppe</comment>
                </column>
            </key>
            <one-to-many class="de.bemar.hibernate.PrefGroup" />
        </set>
    </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

public List findByTranslGroup(int translGroup, String shorty) {
      log.debug("getting PrefTranslation instance with translGroup: " + translGroup + " and language "+shorty);
      try {
         
         String queryString = "from PrefTranslation as pt, PrefLanguage as pl " +
               "where pt.ptTranslGroup="+translGroup+" " +
               "and pl.plShortage= '"+shorty+"' ";
         Query queryObject = getSession().createQuery(queryString);         
         return queryObject.list();
      } catch (RuntimeException re) {
         log.error("get failed", re);
         throw re;
      }
   }

Name and version of the database you are using:
MySQL 5.x

Top

Yuunli

Post subject:

Posted: Tue Mar 11, 2008 8:09 am

Newbie

Joined: Fri Apr 21, 2006 10:35 am
Posts: 13
Location: de

Quote:

findByTranslGroup("eng",4);

So you want the English translation of group 4 (that is, "Table")?

To get only the String, use this HQL query:

Code:

select pt.pt_translation
from PrefLanguage pl join pl.prefTranslations pt
where pl.pl_shortage = :shorty and pt.pt_transl_group = :translGroup

If you want the whole PrefTranslation object, use:

Code:

select pt
from PrefLanguage pl join pl.prefTranslations pt
where pl.pl_shortage = :shorty and pt.pt_transl_group = :translGroup

Then set the parameters for your Query object:

Code:

queryObject.setString("shorty", shorty);
queryObject.setInteger("translGroup", new Integer(translGroup));

By the way: "Kürzel" würde ich mit "abbreviation" oder "code" übersetzen, "shortage" heißt "Mangel". ;-)

Top

Page 1 of 1

[ 2 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