Invalid SQL from trying to join tables

davidl2 · **Posted:** Sat Dec 29, 2007 3:40 pm

I'm trying to use Hibernate to access a database that includes a unique many-to-many relationship between two entities with a couple of extra fields. My first attempt is to use a set in one of the entity objects and component as the member-type of that set. I can query both entity tables without any trouble, but when I try to join them Hibernate generates invalid SQL which causes it to die with a JDBC exception.

Two questions:

1. As far as I can tell, this is a bug in Hibernate. If I give it an invalid mapping document, it should catch the error, not generate SQL referring to a "blank" field-name. Is there something I'm missing?

2. Is this the right way to map such a relationship? Each person can be associated with multiple addresses, and each address with multiple people, but there should only be one status for a given <address,person> pair. Right now I have a public PersonsAddress[] getAddresses() member of Person, and I was planning to write an addAddress() method. There should be no removeAddress method; instead that will be accomplished by setting status to "invalid".

Hibernate version:

3.2.0.cr3

Mapping documents:

Code:

 <class name="com.lmert.learnhib.Person" table="people" 
   discriminator-value="None"   > 
     <id     type="string"    name="nick"
    column="nick"    access="field" unsaved-value="null" >
    <generator class="com.lmert.learnhib.MeaninglessNickGenerator" />
     </id>
     <timestamp name="updateTime" column="update_time" 
      access="field" generated="always" />
  <property name="name" type="java.lang.String" access="field" />
   <set name="addresses" access="field" table="personXaddress" >  
     <key column="nick" />          
     <composite-element class="com.lmert.learnhib.PersonsAddress" >
        <parent name="person" />
        <many-to-one name="address" column="placeId" access="field" 
                     class="com.lmert.learnhib.Address" />
        <property name="updateTime" column="update_time" generated="always"
                  update="false" not-null="true" access="field" />
        <property name="status" type="string" access="field" />
        <property name="type" type="string" access="field" />
     </composite-element>
   </set>
 </class>

 <class name="com.lmert.learnhib.Address" table="addresses" >
  <id 
      type="int"
      column="placeId"
      access="field" >
   <generator class="native" />
  </id>
  <property name="street" type="java.lang.String" access="field" />
  <property name="number" type="java.lang.String" access="field" />
  <property name="unitNo" type="java.lang.String" access="field" />
  <property name="cc" type="java.lang.String" access="field" />
  <property name="city"   type="java.lang.String" access="field" />
  <property name="zip"    type="java.lang.String" access="field" />
  <property column="update_time" name="updateTime" type="timestamp" 
       access="field" />
  <property name="placeTitle"  type="java.lang.String" />
 </class>

Code between sessionFactory.openSession() and session.close():

I'm using the following HQL query:

Code:

select new list(p,p.addresses) from Person p WHERE p.nick='davidl'

Full stack trace of any exception that occurs:

Code:

2007-12-29 14:15:00,345 [main] WARN  org.hibernate.util.JDBCExceptionReporter - SQL Error: 1064, SQLState: 42000
2007-12-29 14:15:00,370 [main] ERROR org.hibernate.util.JDBCExceptionReporter - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as col_1_0_ from people person0_ inner join personXaddress addresses1_ on person' at line 1
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.loader.Loader.doList(Loader.java:2147)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
        at org.hibernate.loader.Loader.list(Loader.java:2023)
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393)
        at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
        at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
        at com.lmert.learnhib.Main.main(Main.java:45)
Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as col_1_0_ from people person0_ inner join personXaddress addresses1_ on person' at line 1
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2994)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030)
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)
        at org.hibernate.loader.Loader.doQuery(Loader.java:662)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
        at org.hibernate.loader.Loader.doList(Loader.java:2144)
        ... 8 more

Name and version of the database you are using:

5.0.32-Debian_7etch1-log ; JDBC driver mysql-connector-java-3.1.12-bin.jar

The generated SQL (show_sql=true):

Code:

071229 14:15:00     711 Query       select person0_.nick as col_0_0_, . as col_1_0_ from people person0_ inner join personXaddress addresses1_ on person0_.nick=addresses1_.nick where person0_.nick='davidl'

(from the MySQL server log...)

davidl2 · **Posted:** Tue Jan 01, 2008 4:58 pm

I just updated to the JARs from Hibernate 3.2.5-ga. I still get the same error.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

David,

The one-to-many side of your mapping looks a little confusing.
If you absolutely need other fields in PersonAddress besides the 2 minimun ones to establish a relationship, then I would suggest you to map the class as a full-fledged class in a separate class element. I don't quite understand what that "composite-element" is doing there.

Since there those extra fields involved give PersonAddress its own "personality", I would recommend stop visualizing the relationship between Persons and Addresses as a single many-to-many, but instead as 2 separate relationships:
-a one-to-many between Person and PersonAddress
-a many-to-one between PersonAddress and Address
I have been there too, believe me, it is tempting to force a many-to-many somehow, but it creates more problems than it solves.

Additionally, notice that you will have to specify the property-ref parameter in the first, one-to-many relationship, since the primary key of the relation table (nick) is not the foreign key to Person.

davidl2 · **Posted:** Sun Jan 06, 2008 7:42 pm

Your advice makes sense. I was hoping two lightweight additional attributes wouldn't trip things up, but perhaps they do. I probably won't have to rewrite the classes much to do it that way.

gonzao_diaz wrote:

Additionally, notice that you will have to specify the property-ref parameter in the first, one-to-many relationship, since the primary key of the relation table (nick) is not the foreign key to Person.

The primary key of the "people" table (class "Person") is "nick", a string.
The primary key of the "addresses" table (class "Address") is "placeId", an integer.
The primary key of the relationship is <nick,placeId>. I guess I could either make that a composite ID, or add a new meaningless integer ID and make that subject to a unique constraint. I don't see where I'll need a property-ref. One way involves a new class, but no change to the schema; the other is a change to the schema.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

David,

Yes, it would be nice to keep the composite primary key on that PersonAddress table, but I am not sure how you would indicate FK's on the mapping files, etc.

Don't complicate yourself, pal. Add that "meaningless ID" to the relationship table and create full-fledged PersonAddress class, with a one-to-many relationship from person to PersonAddress, and a one-to-one relationship from PersonAddress to Address. I know it sounds retarded, but it saves you trouble in the long run.

Welcome to the XXI century where having rich, meaningful multi-column PKs is not cool anymore, and everything is a "framework" that requires quick and dirty single-column identifiers for expediency. You can create a unique index, if it makes you feel better. It works for me, sometimes. :)

As for property-ref: notice that once you start using the "meaningless ID", then <person, address> lose their status as PK's, therefore you have to indicate that the column involved in the "FK" is not a primary key, that is achieved using this "property-ref" attribute instead of the normal key column.

davidl2 · **Posted:** Wed Jan 09, 2008 8:35 am

OK, I changed the mapping for "PersonsAddress" (and changed the table and code) as follows:

Code:

   <class name="com.lmert.learnhib.PersonsAddress" >
     <id type="integer" column="id" ><generator class="native" /></id>
     <timestamp name="updateTime" column="update_time" 
      access="field" generated="always" />     
     <many-to-one name="person" class="com.lmert.learnhib.Person" access="field"  />
     <many-to-one name="address" class="com.lmert.learnhib.Address" access="field"  />
     <property name="status" type="string" access="field" />
     <property name="type" type="string" access="field" />
   </class>

I also changed the mapping for Person:

Code:

  <set name="addresses" access="field" >  
     <key property-ref="person" />          
     <one-to-many class="com.lmert.learnhib.PersonsAddress" />
   </set>

However, now I get an exception while parsing the mapping file (instead of invalid SQL from only that particular query):

Code:

Exception in thread "main" org.hibernate.MappingException: property-ref [person] not found on entity [com.lmert.learnhib.Person]
        at org.hibernate.mapping.PersistentClass.getReferencedProperty(PersistentClass.java:339)

Where does property-ref go, and what does it refer to?

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

property-ref is normally used:

- on the many-to-one side, in order to indicate that the column used as FK is not the primary key of the table, indicating at the same time what column that is (reference documentation, 5.1.10. many-to-one)
- on the "key" sub element of a "set" tag, to indicate that the column used to relate is not a primary key of the child table. (reference doc 23.4.5)

In your case, since "person" and "address" are the primary key of the related table(s), property-ref is unnecessary for those many-to-one tags.

Additionally, the many-to-one from PersonAddress to Person would also be unnecessary, unless you plan to have available in your PersonAddress class a getPersons() method (bidirectional).