potala的空间

当前位置： 维客笔记>> 乱写1 >> 正文

• 乱写1

• Hibernate里单独查询某个表还算方便,但是关联查询多个表结果集该如何方便处理呢?

  有2种解决方案:

  1. 针对复杂查询的结果集定义POJO及HBM, POJO比较简单,不再重述, HBM如下所示

  <?xml version="1.0"?>
  <!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

  <hibernate-mapping>
   <!-- 定义查询返回的query VO类 -->
   
    <class
    name="org.springframework.samples.jpetstore.dao.hibernate.native_sql.vo.AccountVO">
    <id name="username" type="string">
     <column name="USERID" />
     <generator class="assigned" />
    </id>
    <property name="email" type="string">
     <column name="EMAIL" />
    </property>
    <property name="firstName" type="string">
     <column name="FIRSTNAME" />
    </property>
    <property name="lastName" type="string">
     <column name="LASTNAME" />
    </property>
    <property name="status" type="string">
     <column name="STATUS" />
    </property>
    <property name="address1" type="string">
     <column name="ADDR1" />
    </property>
    <property name="address2" type="string">
     <column name="ADDR2" />
    </property>
    <property name="city" type="string">
     <column name="CITY" />
    </property>
    <property name="state" type="string">
     <column name="STATE" />
    </property>
    <property name="zip" type="string">
     <column name="ZIP" />
    </property>
    <property name="country" type="string">
     <column name="COUNTRY" />
    </property>
    <property name="phone" type="string">
     <column name="PHONE" />
    </property>
    <property name="languagePreference" type="string">
     <column name="langpref" />
    </property>
    <property name="favouriteCategoryId" type="string">
     <column name="favcategory" />
    </property>
    <property name="listOption" type="boolean">
     <column name="mylistopt" />
    </property>
    <property name="bannerOption" type="boolean">
     <column name="banneropt" />
    </property>
    <property name="bannerName" type="string">
     <column name="bannername" />
    </property>
   </class>

   

   <!-- 用SQL-QUERY时,需要定义返回值列表对应某个VO类 -->
   <sql-query name="getAccountByUsername">
    <return
     class="org.springframework.samples.jpetstore.dao.hibernate.native_sql.vo.AccountVO">
    </return>

    <![CDATA[
    select
            signon.username as userid,
            account.email,
            account.firstname,
            account.lastname,
            account.status,
            account.addr1,
            account.addr2,
            account.city,
            account.state,
            account.zip,
            account.country,
            account.phone,
            profile.langpref,
            profile.favcategory,
            profile.mylistopt,
            profile.banneropt,
            bannerdata.bannername
      from account, profile, signon, bannerdata
      where account.userid = :username
        and signon.username = account.userid
        and profile.userid = account.userid
        and profile.favcategory = bannerdata.favcategory
    ]]>

   </sql-query>

   <sql-query name="getAccountByUsernameAndPassword">
    <return
     class="org.springframework.samples.jpetstore.dao.hibernate.native_sql.vo.AccountVO">
    </return>

    <![CDATA[
     select
        signon.username as userid,
        account.email,
        account.firstname,
        account.lastname,
        account.status,
        account.addr1,
        account.addr2,
        account.city,
        account.state,
        account.zip,
        account.country,
        account.phone,
        profile.langpref,
        profile.favcategory,
        profile.mylistopt,
        profile.banneropt,
        bannerdata.bannername
      from account, profile, signon, bannerdata
      where account.userid = :username
        and signon.password = :password
        and signon.username = account.userid
        and profile.userid = account.userid
        and profile.favcategory = bannerdata.favcategory
    ]]>

   </sql-query>

  </hibernate-mapping>
   

  
  这样麻烦一点,但是从ORM方案一致性来说,最终结果还是POJO, 而不是Map弱类型,  而且能够加强编译期的校验.

   

  2. 结果集作为Map返回, HBM如下定义:

  <?xml version="1.0"?>
  <!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

  <hibernate-mapping package="org.springframework.samples.jpetstore.dao.hibernate.po">
   
   <query name="getAccountByUsername2">

    <![CDATA[
     select new Map(
          s.username as userid,
          a.email as email,
          a.firstname as firstname,
          a.lastname as lastname,
          a.status as status,
          a.addr1 as addr1,
          a.addr2 as addr2,
          a.city as city,
          a.state as state,
          a.zip as zip,
          a.country as country,
          a.phone as phone,
          p.langpref as langpref,
          p.favcategory as favcategory,
          p.mylistopt as mylistopt,
          p.banneropt as banneropt,
          b.bannername as bannername
         )
       from AccountPO a, ProfilePO p, SignonPO s, BannerdataPO b
       where a.userid = :username
         and s.username = a.userid
         and p.userid = a.userid
         and p.favcategory = b.favcategory
    ]]>

   </query>
  </hibernate-mapping>
   

  
   

  这个方案比较简洁.

   

• 标签： 方案   答案  
• 打印 导出Word 复制地址
• 评论： 乱写1

       当前尚无网友评论!
• 发表我的评论
  选择字体颜色 黑色 红色 黄色 粉色 绿色 桔黄色 紫色 蓝色 米色 棕色 茶色 Navy Maroon LimeGreen

  Email：

  密 码：

  
  

  验证码：

  
  
  
  
  
  

  (请输入图中数字)   看不清，换一个试试
• 提示

  • 1.遵守国家有关法律、法规，尊重网上道德
  • 2.承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 3.互动百科留言板管理人员有权保留或删除其管辖留言中的任意内容