These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 
  Previous topic | Next topic 
Author Message
Yashnoo
 Post subject: How to use join if two table have not any association?
PostPosted: Thu Nov 25, 2004 3:02 am 
Senior
Senior

Joined: Wed Dec 17, 2003 4:24 am
Posts: 188
Hi friends!

I have three tables but they haven't any association(foreign key). I want to select some columns from them. I must use join(outer join or inner join).But hibernate report error if I use join.

for example:
Code:
Query q=session.createQuery("select a.name,b.age from A as a  left outer join B b on b.company_id=a.apply_id");


createSQLQuery method also don't work!



select {s.ProjectCode} from pub_pro_status s
Code:
Query q=session.createSQLQuery("select a.USER_NAME as {user.name},b.USER_AGE as {user.age}  from A as a  left outer join B b on b.company_id=a.apply_id");


error message:
Code:

net.sf.hibernate.JDBCException: SQLException occurred
   at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3809)
   at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
   at test.ReadWritePrintXML.exc(ReadWritePrintXML.java:123)
   at test.ReadWritePrintXML.main(ReadWritePrintXML.java:58)
Caused by: java.sql.SQLException: ORA-00904: "USER_NAME_1_0_": identifier invalid


Can't I use join if two tables have not association?

If I want to use join how to do it? Thks!

[/code]

_________________
You are not alone...

Top
 Profile  
 
Yashnoo
 Post subject: add db info
PostPosted: Thu Nov 25, 2004 3:17 am 
Senior
Senior

Joined: Wed Dec 17, 2003 4:24 am
Posts: 188
2.1.6

three tables have not any association

above

net.sf.hibernate.JDBCException: SQLException occurred
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3809)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at test.ReadWritePrintXML.exc(ReadWritePrintXML.java:123)
at test.ReadWritePrintXML.main(ReadWritePrintXML.java:58)
Caused by: java.sql.SQLException: ORA-00904: "USER_NAME_1_0_": identifier invalid

Oracle 10g

The generated SQL (show_sql=true):

INFO

_________________
You are not alone...

Top
 Profile  
 
Yashnoo
 Post subject: use inner join error
PostPosted: Thu Nov 25, 2004 3:40 am 
Senior
Senior

Joined: Wed Dec 17, 2003 4:24 am
Posts: 188
15:39:31,984 WARN EhCache:94 - Could not find configuration for net.sf.hibernate.cache.StandardQueryCache. Configuring using the defaultCache settings.
net.sf.hibernate.QueryException: outer or full join must be followed by path expression [select a.id,b.id from com.hibernate.test.A a inner join B b where a.id=b.id]
at net.sf.hibernate.hql.FromParser.token(FromParser.java:166)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:294)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1562)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1533)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at com.hibernate.test.test.main(test.java:28)
Exception in thread "main"

_________________
You are not alone...

Top
 Profile  
 
Yashnoo
 Post subject: Error message
PostPosted: Thu Nov 25, 2004 3:45 am 
Senior
Senior

Joined: Wed Dec 17, 2003 4:24 am
Posts: 188
Code:
String[] str=new String[2];
      str[0]="a";
      str[1]="b";
      Class[] clazz=new Class[2];
      clazz[0]=A.class;
      clazz[1]=B.class;
      Query q=session.createSQLQuery(sql,str,clazz);
      List list=q.list();
      System.out.println("get list:"+list);
      
      tx.commit();
      session.close();


Error message:

Code:
Hibernate: select a.id as id0_,b.id as id1_ from A a inner join B b on a.id=b.id
15:43:34,218  WARN JDBCExceptionReporter:38 - SQL Error: 0, SQLState: S0022
15:43:34,218 ERROR JDBCExceptionReporter:46 - Column 'name0_' not found.
15:43:34,218  WARN JDBCExceptionReporter:38 - SQL Error: 0, SQLState: S0022
15:43:34,218 ERROR JDBCExceptionReporter:46 - Column 'name0_' not found.
15:43:34,234 ERROR JDBCExceptionReporter:38 - SQLException occurred
java.sql.SQLException: Column 'name0_' not found.
   at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:2317)
   at com.mysql.jdbc.ResultSet.getString(ResultSet.java:1822)
   at net.sf.hibernate.type.StringType.get(StringType.java:18)
   at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
   at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
   at net.sf.hibernate.type.AbstractType.hydrate(AbstractType.java:66)
   at net.sf.hibernate.loader.Loader.hydrate(Loader.java:686)
   at net.sf.hibernate.loader.Loader.loadFromResultSet(Loader.java:627)
   at net.sf.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:586)
   at net.sf.hibernate.loader.Loader.getRow(Loader.java:501)
   at net.sf.hibernate.loader.Loader.getRowFromResultSet(Loader.java:213)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:281)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
   at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
   at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
   at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
   at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3806)
   at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
   at com.hibernate.test.test.main(test.java:34)
[/code]

_________________
You are not alone...

Top
 Profile  
 
Yashnoo
 Post subject: Error message
PostPosted: Thu Nov 25, 2004 3:45 am 
Senior
Senior

Joined: Wed Dec 17, 2003 4:24 am
Posts: 188
Code:
String[] str=new String[2];
      str[0]="a";
      str[1]="b";
      Class[] clazz=new Class[2];
      clazz[0]=A.class;
      clazz[1]=B.class;
      Query q=session.createSQLQuery(sql,str,clazz);
      List list=q.list();
      System.out.println("get list:"+list);
      
      tx.commit();
      session.close();


Error message:

Code:
Hibernate: select a.id as id0_,b.id as id1_ from A a inner join B b on a.id=b.id
15:43:34,218  WARN JDBCExceptionReporter:38 - SQL Error: 0, SQLState: S0022
15:43:34,218 ERROR JDBCExceptionReporter:46 - Column 'name0_' not found.
15:43:34,218  WARN JDBCExceptionReporter:38 - SQL Error: 0, SQLState: S0022
15:43:34,218 ERROR JDBCExceptionReporter:46 - Column 'name0_' not found.
15:43:34,234 ERROR JDBCExceptionReporter:38 - SQLException occurred
java.sql.SQLException: Column 'name0_' not found.
   at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:2317)
   at com.mysql.jdbc.ResultSet.getString(ResultSet.java:1822)
   at net.sf.hibernate.type.StringType.get(StringType.java:18)
   at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
   at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
   at net.sf.hibernate.type.AbstractType.hydrate(AbstractType.java:66)
   at net.sf.hibernate.loader.Loader.hydrate(Loader.java:686)
   at net.sf.hibernate.loader.Loader.loadFromResultSet(Loader.java:627)
   at net.sf.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:586)
   at net.sf.hibernate.loader.Loader.getRow(Loader.java:501)
   at net.sf.hibernate.loader.Loader.getRowFromResultSet(Loader.java:213)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:281)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
   at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
   at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
   at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
   at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3806)
   at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
   at com.hibernate.test.test.main(test.java:34)

_________________
You are not alone...

Top
 Profile  
 
sureshgnv
 Post subject: I have the same problem with joining 3 tables
PostPosted: Sat Dec 11, 2004 3:53 am 
Newbie

Joined: Sat Dec 11, 2004 3:43 am
Posts: 3
hi Yashnoo,
i have the same problem. I need to join 3 tables. They don't have a foreign-key relationship. How can i do the inner join using hibernate
Can you please let me know as soon as possible. i am close to missing the deadline. i appreciate if you can tell me what you found out even if you didn't find a solution.
thx in advance
Top
 Profile  
 
david
 Post subject: Re: How to use join if two table have not any association?
PostPosted: Sat Dec 11, 2004 7:22 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 7:19 pm
Posts: 2364
Location: Brisbane, Australia
Yashnoo wrote:
Hi friends!

I have three tables but they haven't any association(foreign key). I want to select some columns from them. I must use join(outer join or inner join).But hibernate report error if I use join.

for example:
Code:
Query q=session.createQuery("select a.name,b.age from A as a  left outer join B b on b.company_id=a.apply_id");






Sorry but HQL does not support outer join for a ternary query. This is what you can do. Ref Hibernate In Action.
Code:
select a.name,b.age from A as a, B as b where b.company_id=a.apply_id


It is easy to add the relationships into the mapping documents or use session.createSQLQuery()
Top
 Profile  
 
solen
 Post subject: Re: How to use join if two table have not any association?
PostPosted: Fri Jan 21, 2005 4:28 am 
Newbie

Joined: Tue Jan 11, 2005 4:34 am
Posts: 18
david wrote:
Yashnoo wrote:
Hi friends!

I have three tables but they haven't any association(foreign key). I want to select some columns from them. I must use join(outer join or inner join).But hibernate report error if I use join.

for example:
Code:
Query q=session.createQuery("select a.name,b.age from A as a  left outer join B b on b.company_id=a.apply_id");






Sorry but HQL does not support outer join for a ternary query. This is what you can do. Ref Hibernate In Action.
Code:
select a.name,b.age from A as a, B as b where b.company_id=a.apply_id


It is easy to add the relationships into the mapping documents or use session.createSQLQuery()


from here how will you display it?
let say i have a List
how will i put it into a list?

List lll = session.find(q); <--- like this?
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 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

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.