Severe SQL Syntax Error when persisting object relations

daniele.dml · **Joined:** Tue Sep 16, 2008 11:10 am **Posts:** 7

Hello,

I'm trying to persist two objects that present a many-to-many relation. When persisting them individually there are no problems, but when I include the references in both, I receive the expection I report below.

I double checked all the mapping and the code but there is something I must be missing - perhaps it's a bit more conceptual?!?

Any suggestions or hints on the possible causes and solutions?

Thanks!

Dan

======================================

Hibernate version 3.3.1.GA

(mapping: )

Code:

<class name="db.data.persistence.hibernate.Model" table="MODELS">
        <id column="ID" name="id">
            <generator class="increment"/>
        </id>
        <property column="name" name="name" type="string"/>
        <property column="description" name="description" type="string"/>
        <property column="keywords" name="keywords" type="string"/>
        <property column="file" name="file" type="text"/>

        <set name="createdBy" table="CREATE" lazy="true" cascade="save-update">
            <key column="AUTHORID"/>
            <many-to-many class="db.data.persistence.hibernate.Author" column="id"/>
        </set>       
    </class>

<class name="db.data.persistence.hibernate.Person" table="PERSONS">
        <id column="ID" name="id">
            <generator class="increment"/>
        </id>
        <property column="givenName" name="givenName" type="string"/>
        <property column="familyName" name="familyName" type="string"/>
        <property column="email" name="email" type="string"/>
        <property column="affiliation" name="affiliation" type="string"/>
       
        <!-- User -->
        <joined-subclass name="db.data.persistence.hibernate.User" table="USERS">
            <key column="ID"/>
            <property column="accountName" name="accountName" type="string"/>
            <property column="password" name="password" type="string"/>
            <property column="enabled" name="enabled" type="boolean"/>
        </joined-subclass>
       
        <!-- Author -->
        <joined-subclass name="db.data.persistence.hibernate.Author" table="AUTHORS">
            <key column="ID"/>
            <property column="otherInfo" name="otherInfo" type="string"/>
            <set name="created" table="CREATE" lazy="true" inverse="true" cascade="save-update">
                <key column="MODELID"/>
                <many-to-many class="db.data.persistence.hibernate.Model" column="ID"/>
            </set>
        </joined-subclass>
    </class>

================================================================
CODE:

Code:

Session session = HibernateUtil.getSessionFactory().getCurrentSession();

        session.beginTransaction();
       
        // find author              
        Integer authorId = 4; // la tabella authors ha un author con id == 4

        Author author = (Author )session.load(Author.class, authorId);
       
        System.out.println();
        System.out.println("Loaded author : ");
        System.out.println(author.toString());
        System.out.println();
        System.out.println("****************************************");
        // create model
        Model model = new Model();
        model.setDescription("Description of the first model");
        model.setKeywords("keywords");
        model.setName("firstModel");
        model.setFile("This text should be the xml file for this model");
       
        Integer modelId = (Integer )session.save(model);
       
       // session.getTransaction().
        model = (Model )session.load(Model.class, modelId);
        // add author to model
        model.getCreatedBy().add(author);                       
       
        // add model to author               
        author.getCreated().add(model);

        // persist author and model in the database       
        session.getTransaction().commit();

Stack trace:

SEVERE: 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 'CREATE createdby0_ left outer join AUTHORS author1_ on createdby0_.id=author1_.I' at line 1
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not initialize a collection: [db.data.persistence.hibernate.Model.createdBy#1]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.loadCollection(Loader.java:2001)
at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:36)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:565)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:60)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1716)
at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:344)
at org.hibernate.collection.PersistentSet.add(PersistentSet.java:189)
at db.persistence.hibernate.testing.TestWritingPersonUserAndAuthor.createAndStoreModel(TestWritingPersonUserAndAuthor.java:73)
at db.persistence.hibernate.testing.TestWritingPersonUserAndAuthor.main(TestWritingPersonUserAndAuthor.java:30)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'CREATE createdby0_ left outer join AUTHORS author1_ on createdby0_.id=author1_.I' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.loadCollection(Loader.java:1994)
... 8 more
Java Result: 1

Database:

MySQL 5.0.67

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

Yes.
You need to see SQL statements generated by Hibernate. For that, you can use the file I describe here:

http://hibernar.org/articulos_en/logging.php

Once you are able to retrieve the SQL, grab the last statement, the one causing the problem, and try to execute it directly on MySQL.

That will give you the clue of what is wrong with your mapping.

daniele.dml · **Joined:** Tue Sep 16, 2008 11:10 am **Posts:** 7

Thanks!

Indeed I discovered I was using a table named "create", which is a reserved word in SQL (or at least MySQL).

Anyway, now with the same code and I am getting this error:

ERROR - Unknown column 'createdby0_.id' in 'field list'

which honestly don't understand because I properly defined it?

Any further suggestions?

Thanks,

Dan

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

I don't know how your tables look like, or whether or not they your DB is set as case insensitive (notice that the error says you are asking for an .id, not ID)

But evidently, your mapping file is suggesting the existence of an id column that is not in the tables. Perhaps one or more of the joined-subclass mappings?