关于Hibernate的两表查询的问题解决！

主　　题：		hibernate两表查询，求解，高手来帮帮忙啊
作　　者：		lzb821 ()        Blog
等　　级：
信 誉 值：		100
所属社区：		Java J2EE / EJB / JMS
问题点数：		20
回复次数：		5
发表时间：		2006-11-30 13:16:09

 FundsAccount 持久化类：
public class FundsAccount  implements java.io.Serializable {
     private Integer fundsAccountId;
     private Operators operators;
     private Customers customers;
     private String numberedAccount;
     private String password;
     private Double amountOfMoney;
     private String type;
     private Date accountOpenTime;
     private Set fundAccountses = new HashSet(0);
     private Set upFundsAccountLogses = new HashSet(0);
    //下面是get,set方法略
         ..............
}
Customers  持久化类：
public class Customers  implements java.io.Serializable {
     private Integer customerId;
     private Operators operators;
     private String name;
     private String sex;
     private String card;
     private String phone;
     private String address;
     private String email;
     private String hobby;
     private Set fundsAccounts = new HashSet(0);
     private Set upCustomersLogses = new HashSet(0);
       //下面是get,set方法略
         ..............
}
现在想从两个对象就查询数据：
根据FundsAccount 中的numberedAccount查询得到FundsAccount 中的numberedAccount,amountOfMoney,accountOpenTime
得到Customers 中的customerId,name :
我的写方法是：
select funds.numberedAccount, funds.amountOfMoney,
funds.accountOpenTime,
cust.customerId,cust.name
from FundsAccount as funds
join Customers as cust
on funds.customers.customerId=cust.customerId
where funds.numberedAccount='2006'
结果出错：
Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 172 [select funds.numberedAccount,funds.amountOfMoney,funds.accountOpenTime,cust.customerId,cust.name from com.hibernate.pojo.FundsAccount as funds left join Customers as cust on funds.customers.customerId=cust.customerId where funds.numberedAccount= '2006']
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:244)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:155)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1583)
at com.dao.fundAccountAdmin.FundAccountAdminDao.queryFundAccountByNumberedAccount(FundAccountAdminDao.java:135)
at com.dao.fundAccountAdmin.FundAccountAdminDao.main(FundAccountAdminDao.java:230)
Caused by: line 1:172: unexpected token: on
at org.hibernate.hql.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1765)
at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1420)
at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1130)
at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:702)
at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:296)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:159)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:238)
... 10 more

zhh1981(**的猪头) ( ) 信誉：100    Blog

2006-11-30 13:37:34

得分: 20

你的是SQL语句形式吧，HQL不是那样的，把    on    去掉看看

lzb821() ( ) 信誉：100    Blog

把on去了，改成：
select funds.numberedAccount, funds.amountOfMoney,
funds.accountOpenTime,
cust.customerId,cust.name
from FundsAccount as funds
join Customers as cust
where funds.numberedAccount='2006'
出错：
Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxException: Path expected for join! [select funds.numberedAccount,funds.amountOfMoney,funds.accountOpenTime,cust.customerId,cust.name from com.hibernate.pojo.FundsAccount as funds left join Customers as cust where funds.numberedAccount= '2006']
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:225)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1583)
at com.dao.fundAccountAdmin.FundAccountAdminDao.queryFundAccountByNumberedAccount(FundAccountAdminDao.java:135)
at com.dao.fundAccountAdmin.FundAccountAdminDao.main(FundAccountAdminDao.java:230)
Caused by:  Path expected for join!
at org.hibernate.hql.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:307)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3268)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3060)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2938)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:688)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:544)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)
... 10 more

zhh1981(**的猪头) ( ) 信誉：100    Blog

2006-11-30 14:05:25

得分: 0

select funds.numberedAccount, funds.amountOfMoney,
funds.accountOpenTime,
cust.customerId,cust.name
from FundsAccount as funds
join funds.customers as cust
where funds.numberedAccount='2006'
看看

lzb821() ( ) 信誉：100    Blog

可能查询可以，我的是两个表里得到数据，但不知道怎么来得到查询的值，我是这样写的：
session=HibernateSessionFactory.getSession();
Query q=session.createQuery("select funds.numberedAccount,funds.amountOfMoney,funds.accountOpenTime,cust.customerId,cust.name from FundsAccount as funds join funds.customers as cust where funds.numberedAccount= '"+numberedAccount+"'");
List li=q.list();
Iterator it=li.iterator();
Object[] bean =(Object[])it.next();
String numberedAc=(String)bean[0];
Double prise=(Double)bean[1];
Date dateTime=(Date)bean[2];
Integer id=(Integer)bean[3];
String name=(String)bean[4];
list.add(numberedAc);
list.add(prise);
list.add(dateTime);
list.add(id);
list.add(name);
报错：Exception in thread "main" java.lang.NullPointerException
at com.dao.fundAccountAdmin.FundAccountAdminDao.queryFundAccountByNumberedAccount(FundAccountAdminDao.java:147)
at com.dao.fundAccountAdmin.FundAccountAdminDao.main(FundAccountAdminDao.java:246)

lzb821() ( ) 信誉：100    Blog

问题解决：
list没有new，我在下面写上list=new Arraylist();后正确得到结果．
谢谢！！
session=HibernateSessionFactory.getSession();
Query q=session.createQuery("select funds.numberedAccount,funds.amountOfMoney,funds.accountOpenTime,cust.customerId,cust.name from FundsAccount as funds join funds.customers as cust where funds.numberedAccount= '"+numberedAccount+"'");
List li=q.list();
Iterator it=li.iterator();
Object[] bean =(Object[])it.next();
String numberedAc=(String)bean[0];
Double prise=(Double)bean[1];
Date dateTime=(Date)bean[2];
Integer id=(Integer)bean[3];
String name=(String)bean[4];
list.add(numberedAc);
list.add(prise);
list.add(dateTime);
list.add(id);
list.add(name);