Hibernate Community • View topic - Inheritance in SQL query

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Inheritance in SQL query

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

jmajor

Post subject: Inheritance in SQL query

Posted: Fri Mar 05, 2004 4:30 am

Newbie

Joined: Thu Mar 04, 2004 4:37 pm
Posts: 1

Hi,

I am running Hibernate 2.1.1 and Oracle 9.2 for my web application.

I have two classes one inherited from the other
class Person:-

Code:

/** @hibernate.class */
public class Person {
  private Integer personId
  private String loginName;
  ...

  /** @hibernate.id generator-class="native" */
  public Integer getPersonId() {
    return personId;
  }
  public void setPersonId(Integer integer) {
    personId=integer;
  }

  /** @hibernate.property */
  public String getLoginName() {
    return loginName;
  }
  public void setLoginName(String loginName) {
    this.loginName=loginName;
  }

  ...
}

class Staff:-

Code:

/**
  * @hibernate.joined-subclass
  * @hibernate.joined-subclass-key column="personId"
  */
public class Staff extends Person {
  private String referenceNumber;
  private Timestamp expectedRetirementDate;
  ...

  /** @hibernate.property length="15" */
  public String getReferenceNumber() {
    return referenceNumber;
  }
  public void setReferenceNumber(String ref) {
    referenceNumber=ref;
  }

  /** @hibernate.property type="timestamp" */
  public Timestamp getExpectedRetirementDate() {
    return expectedRetirementDate;
  }
  public void setExpectedRetirementDate(Timestamp retDate) {
    expectedRetirementDate = retDate;
  }

  ...
}

I need to use direct sql to find people, my query is:-

Code:

List people = new ArrayList(50);
String sql = select distinct {p.*} from person {p}";
Query query = session.createSQLQuery(sql, "p", Person.class};
query.setMaxResults(50);
people = query.list();

My problem is that this throws a sql exception of an invalid identifier for one of the columns in the query executed. This also happens if there are no staff in my database. Is this because hibernate is attempting to bring back the staff data for records of person? If so, is there any way to avoid this?

Thanks

John

Top

horst21

Post subject: Inheritance in SQL query - same problem

Posted: Mon Sep 20, 2004 11:59 am

Newbie

Joined: Mon Sep 20, 2004 11:09 am
Posts: 8

hi there I got the same problem:
Mapping document:

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

<hibernate-mapping>
<class
name="musicfinder.persistence.hibernate.bean.Archivable"
table="Archivable"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="id"
column="id"
type="long"
unsaved-value="0"
>
<generator class="native">
</generator>
</id>



<joined-subclass
name="musicfinder.persistence.hibernate.bean.Media"
dynamic-update="false"
dynamic-insert="false"
>
<key
column="archivable"
/>
<property
name="timestamp"
type="timestamp"
update="true"
insert="true"
access="property"
column="timestamp"
/>

</joined-subclass>

</class>

</hibernate-mapping>

CODE:

query = "SELECT {Archivable.*} FROM Archivable Archivable";
logger.debug("findArchivable: query: " + query);
Session session = HibernateSession.acquireSession();
try {
Query q = session.createSQLQuery(query, "Archivable", Archivable.class);
List list = q.list();
return list;
} finally {
HibernateSession.releaseSession();
}

LOG:
Hibernate: SELECT Archivable.id as id0_, case when Archivable_1_.archivable is not null then 1 when Archivable.id is not null then 0 end as clazz_0_, Archivable_1_.timestamp as timestamp1_0_ FROM Archivable Archivable
WARN 2004-09-20 17:50:42,532 [util.JDBCExceptionReporter; main]: SQL Error: 1109, SQLState: S1000
ERROR 2004-09-20 17:50:42,533 [util.JDBCExceptionReporter; main]: General error, message from server: "Unknown table 'Archivable_1_' in field list"
WARN 2004-09-20 17:50:42,538 [util.JDBCExceptionReporter; main]: SQL Error: 1109, SQLState: S1000
ERROR 2004-09-20 17:50:42,538 [util.JDBCExceptionReporter; main]: General error, message from server: "Unknown table 'Archivable_1_' in field list"
ERROR 2004-09-20 17:50:42,538 [util.JDBCExceptionReporter; main]: SQLException occurred
java.sql.SQLException: General error, message from server: "Unknown table 'Archivable_1_' in field list"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
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 musicfinder.persistence.hibernate.MediaDAOSessionBean.findArchivable(MediaDAOSessionBean.java:111)
at musicfinder.persistence.hibernate.MediaDAOSessionBean.main(MediaDAOSessionBean.java:376)

when i do not have a joined-subclass element in the mapping document, everthing goes fine!
anybody? any ideas? - thanks!

Top

Page 1 of 1

[ 2 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