JWorld@TW the best professional Java site in Taiwan
      註冊 | 登入 | 全文檢索 | 排行榜  

» JWorld@TW » 違規輔導區  

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友   
reply to postflat modego to previous topicgo to next topic
話題被移動
該話題已被移動 - koji , 2010-10-29 18:26
如果您尚不清楚該話題被移動的原因,請參考論壇規則以及本版公告或者聯系本版版主。
本主題所含的標籤
無標籤
作者 要怎麼使用SQL或HQL來join兩個"沒有關聯起來"的表格
godspeed1979





發文: 9
積分: 0
於 2010-10-29 18:03 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
我有個問題想要請教版上的高手,目前資料庫裏面有兩個表格需要做join的動作,
但實際上這兩個表格並沒有設定外鍵這個constraint(因為...的因素我不被允許),

這邊我遇到兩個問題:

1. 我要怎麼使用SQL或者HQL來把兩個table join之後的資料查出來呢?

2. 我試了老半天,以下面的設計為例,只試出奇怪的結果,問題出在哪呢?

先謝謝各位了!!

EMP class
==================================
package joinTable;

public class EMP {

  private Long id;
  private String name;
  private String birthday;
  private String nationalityId;
  
  private EMP() {
  }

  public EMP(String name, String birthday, String nationalityId) {
    super();
    this.name = name;
    this.birthday = birthday;
    this.nationalityId = nationalityId;
  }

  Long getId() {
    return id;
  }

  void setId(Long id) {
    this.id = id;
  }

  String getName() {
    return name;
  }

  void setName(String name) {
    this.name = name;
  }

  String getBirthday() {
    return birthday;
  }

  void setBirthday(String birthday) {
    this.birthday = birthday;
  }

  String getNationalityId() {
    return nationalityId;
  }

  void setNationalityId(String nationalityId) {
    this.nationalityId = nationalityId;
  }  
}

EMP.hbm.xml
====================================
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping>

  <class name="joinTable.EMP" table="emp" lazy="true">

    <id name="id" column="id" unsaved-value="null">
      <generator class="sequence">
        <param name="sequence">seq_join_emp</param>
      </generator>
    </id>

    <property name="name" column="name" />
    <property name="birthday" column="birthday" />
    <property name="nationalityId" column="nationality_id" />

  </class>

</hibernate-mapping>

Nationality class
=============================
package joinTable;

public class Nationality {

  private Long id;
  private String nationalityId;
  private String name;
  
  private Nationality() {
  }

  public Nationality(String nationalityId, String name) {
    super();
    this.nationalityId = nationalityId;
    this.name = name;
  }

  Long getId() {
    return id;
  }

  void setId(Long id) {
    this.id = id;
  }

  String getNationalityId() {
    return nationalityId;
  }

  void setNationalityId(String nationalityId) {
    this.nationalityId = nationalityId;
  }

  String getName() {
    return name;
  }

  void setName(String name) {
    this.name = name;
  }
}

Nationality.hbm.xml
=============================
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping>

  <class name="joinTable.Nationality" table="nationality" lazy="true">

    <id name="id" column="id" unsaved-value="null">
      <generator class="sequence">
        <param name="sequence">seq_join_nationality</param>
      </generator>
    </id>

    <property name="nationalityId" column="nationality_id" />
    <property name="name" column="name" />

  </class>

</hibernate-mapping>

主程式:
===============================

package joinTable;

import java.util.Iterator;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Transaction;
import org.hibernate.classic.Session;

import util.HibernateHelper;

public class TestJoin {

  public static void main(String[] args) {

    Session session = HibernateHelper.getSessionFactory().openSession();
    Transaction tx = session.beginTransaction();

    String sql = " select emp.id, emp.name, emp.birthday, emp.nationality_id, nationality.name from emp,nationality " +
    " where emp.nationality_id = nationality.nationality_id ";

SQLQuery sqlQuery = session.createSQLQuery(sql);
    List results = sqlQuery.list();
    
    Iterator iterator = results.iterator();

    while(iterator.hasNext()){
      Object[] pair = (Object[]) iterator.next();
      
      System.out.print(pair[0] + " ");
      System.out.print(pair[1] + " ");
      System.out.print(pair[2] + " ");
      System.out.print(pair[3] + " ");
      System.out.println(pair[4] + " ");
    }
  
    tx.commit();
    session.close();
  }

}

EMP table的資料
====================================
70;"Cavendish";"19790416";"D"
71;"Godspeed";"19790320";"E"
72;"Tina";"19780221";"F"

Nationality table的資料
====================================
70;"D";"Taiwan"
71;"E";"America"
72;"F";"Japan"

查出來的資料卻是, 該顯示為人名的部分 居然變成國名了... 是甚麼原因呢?
=======================
70 Taiwan 19790416 D Taiwan
71 America 19790320 E America
72 Japan 19780221 F Japan


reply to postreply to post
話題樹型展開
人氣 標題 作者 字數 發文時間
231 要怎麼使用SQL或HQL來join兩個"沒有關聯起來"的表格 godspeed1979 4451 2010-10-29 18:03
» JWorld@TW »  違規輔導區

reply to postflat modego to previous topicgo to next topic
  已讀文章
  新的文章
  被刪除的文章
Jump to the top of page

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8