Solve the two-Table query problem of hibernate!

Subject:		Hibernate two-Table query, solving, expert help
Author:		Lzb821 () blog
Level 1:
Reputation value:		100
Community:		Java J2EE/EJB/JMS
Problem points:		20
Replies:		5
Posting time:		2006-11-30 13:16:09

Fundsaccount persistence class:
Public class fundsaccount implements java. Io. serializable {
Private integer fundsaccountid;
Private operators;
Private MERs 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 );
// The following is the get and set method.
..............
}
Customers persistence class:
Public class MERs implements java. Io. serializable {
Private integer customerid;
Private operators;
Private string name;
Private string sex;
Private string card;
Private string phone;
Private string address;
Private string email;
Private string holobby;
Private set fundsaccounts = new hashset (0 );
Private set upcustomerslogses = new hashset (0 );
// The following is the get and set method.
..............
}
Now you want to query data from two objects:
Obtain numberedaccount, amountofmoney, and accountopentime in fundsaccount according to the numberedaccount query in fundsaccount.
Obtain the customerid, Name:
My write method is:
Select funds. numberedaccount, funds. amountofmoney,
Funds. accountopentime,
Cust. customerid, Cust. Name
From fundsaccount as funds
Join MERs as cust
On funds. Customers. customerid = Cust. customerid
Where funds. numberedaccount = '20140901'
Result error:
Exception in thread "Main" org. hibernate. hql. ast. querysyntaxexception: Unexpected token: On near line 1, colum172 [select funds. numberedaccount, funds. amountofmoney, funds. accountopentime, Cust. customerid, Cust. name from Com. hibernate. pojo. fundsaccount as funds left join MERs as Cust on funds. MERs. customerid = Cust. customerid where funds. numberedaccount = '123']
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. anlr. hqlbaseparser. fromjoin (hqlbaseparser. Java: 1765)
At org. hibernate. hql. anlr. hqlbaseparser. fromclause (hqlbaseparser. Java: 1420)
At org. hibernate. hql. anlr. hqlbaseparser. selectfrom (hqlbaseparser. Java: 1130)
At org. hibernate. hql. anlr. hqlbaseparser. queryrule (hqlbaseparser. Java: 702)
At org. hibernate. hql. anlr. hqlbaseparser. selectstatement (hqlbaseparser. Java: 296)
At org. hibernate. hql. anlr. hqlbaseparser. Statement (hqlbaseparser. Java: 159)
At org. hibernate. hql. Ast. querytranslatorimpl. parse (querytranslatorimpl. Java: 238)
... 10 more

Zhh1981 (** pig head)() Reputation: 100 blog

2006-11-30 13:37:34

Score: 20

You are in the form of SQL statements. hql is not like that. Remove the on statement.

Lzb821 ()() Reputation: 100 blog

Change on:
Select funds. numberedaccount, funds. amountofmoney,
Funds. accountopentime,
Cust. customerid, Cust. Name
From fundsaccount as funds
Join MERs as cust
Where funds. numberedaccount = '20140901'
Error:
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 MERs as Cust where funds. numberedaccount = '123']
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. anlr. hqlsqlbasewalker. joinelement (hqlsqlbasewalker. Java: 3268)
At org. hibernate. hql. anlr. hqlsqlbasewalker. fromelement (hqlsqlbasewalker. Java: 3060)
At org. hibernate. hql. anlr. hqlsqlbasewalker. fromelementlist (hqlsqlbasewalker. Java: 2938)
At org. hibernate. hql. anlr. hqlsqlbasewalker. fromclause (hqlsqlbasewalker. Java: 688)
At org. hibernate. hql. anlr. hqlsqlbasewalker. Query (hqlsqlbasewalker. Java: 544)
At org. hibernate. hql. anlr. hqlsqlbasewalker. selectstatement (hqlsqlbasewalker. Java: 281)
At org. hibernate. hql. anlr. hqlsqlbasewalker. Statement (hqlsqlbasewalker. Java: 229)
At org. hibernate. hql. Ast. querytranslatorimpl. Analyze (querytranslatorimpl. Java: 218)
... 10 more

Zhh1981 (** pig head)() Reputation: 100 blog

2006-11-30 14:05:25

Score: 0

Select funds. numberedaccount, funds. amountofmoney,
Funds. accountopentime,
Cust. customerid, Cust. Name
From fundsaccount as funds
Join funds. Customers as cust
Where funds. numberedaccount = '20140901'
Look

Lzb821 ()() Reputation: 100 blog

The query is possible. I got data from two tables, but I don't know how to get the query value. I wrote this as follows:
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 );
Error: 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 ()() Reputation: 100 blog

Solution:
List does not have new. I will write list = new arraylist () below and get the correct result.
Thank you !!
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 );