org.springframework.jdbc.BadSqlGrammarException:

Hibernate operation: could not execute query;

bad SQL grammar [

select distinct

this_.BOOK_ID as y0_,
this_.TITLE as y1_,
this_.ISBN as y2_ from BOOKS this_

inner join LINK_BOOK_AUTHOR authors5_ on this_.BOOK_ID=authors5_.BOOK_ID
inner join AUTHORS author3_ on authors5_.AUTHOR_ID=author3_.AUTHOR_ID
inner join NOTES note1_ on this_.BOOK_ID=note1_.NOTE_ID
inner join PUBLISHERS publisher2_ on this_.PUBLISHER_ID=publisher2_.PUBLISHER_ID

where (lower(y2_) like ?) and (1=1) and (1=1) and (1=1) order by y1_ asc];

nested exception is java.sql.SQLException: Unknown column 'y2_' in 'where clause'

java.sql.SQLException: Unknown column 'y2_' in 'where clause'
...
...

   public Collection findBooksByCriteriaWithLimit(BookCommand bookCommand)
         throws DataAccessException {
      
      final Book book = bookCommand.getBook();
      final Author iAuthor = bookCommand.getAuthor();
      final Publisher iPublisher = book.getPublisher();
      final Note iNote = book.getNote();
      final String sortColumn = bookCommand.getS_sortColumn();
      final int page = bookCommand.getS_page();
      final int pageSize = bookCommand.getS_pageSize();
      final int ascending = bookCommand.getS_ascending();

      return getHibernateTemplate().executeFind(new HibernateCallback() {
         public Object doInHibernate(Session session)
               throws HibernateException, SQLException {

            Example exampleBook = Example.create(book).ignoreCase()
                  .enableLike(MatchMode.ANYWHERE);
            Example examplePublisher = Example.create(iPublisher).ignoreCase()
                  .enableLike(MatchMode.ANYWHERE);
            Example exampleNote = Example.create(iNote).ignoreCase()
                  .enableLike(MatchMode.ANYWHERE);
            Example exampleAuthor = Example.create(iAuthor).ignoreCase()
                  .enableLike(MatchMode.ANYWHERE);

            Criteria bookCriteria = session.createCriteria(Book.class)
                              .setProjection(Projections.distinct(Projections.projectionList()
                                    .add(Projections.property("id"),"id")
                                    .add(Projections.property("title"),"title")
                                    .add(Projections.property("isbn"),"isbn")
                                    ));

            if (ascending > 0) {
               bookCriteria.addOrder(Order.asc(sortColumn));
            } else {
               bookCriteria.addOrder(Order.desc(sortColumn));
            }
            bookCriteria.add(exampleBook);

            bookCriteria.createCriteria("note").add(exampleNote);
            bookCriteria.createCriteria("publisher").add(examplePublisher);
            bookCriteria.createCriteria("authors").add(exampleAuthor);
            bookCriteria.setFirstResult(page * pageSize)
                     .setMaxResults(pageSize);

            return bookCriteria.setResultTransformer(new AliasToBeanResultTransformer(Book.class)).list();
            
         }
      });
      
   }

select

this_.BOOK_ID as y0_, this_.TITLE as y1_, this_.ISBN as y2_

from BOOKS this_

where y0_ = 1

select

this_.BOOK_ID as y0_, this_.TITLE as y1_, this_.ISBN as y2_

from BOOKS this_

where this_.BOOK_ID = 1

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM
         "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping >

<class name="org.yourschool.library.domain.Book"
   table="BOOKS" lazy="false">

   <id name="id"
      type="long"
      column="BOOK_ID"
      unsaved-value="null">
      <generator class="native"/>
   </id>
   
    <version name="version" type="long" column="VERSION"/>
   
   <property name="title"
      type="string"
      column="TITLE"
      length="255"
      not-null="true"/>

   <property name="publishingYear"
      type="integer"
      column="PUBLISHING_YEAR"/>

   <property name="isbn"
      type="string"
      column="ISBN"
      length="15"
      unique="true"/>

   <property name="edition"
      type="string"
      column="EDITION"
      length="30"/>

   <property name="description"
      type="string"
      column="DESCRIPTION"
      length="30"/>

   <property name="publicationPlace"
      type="string"
      column="PUBLICATION_PLACE"
      length="40"/>

   <property name="classMark"
      type="string"
      column="CLASSMARK"
        length="20"/>

   <property name="attachment"
      type="string"
      column="ATTACHMENT"
      length="32"/>

<!-- describe one-to-many association between Book and Item -->
   <set name="items"
        table="ITEMS"
        lazy="true"
        inverse="true"
        cascade="none"> <!-- reason for inverse="true" , see hibernate in action pg.110 -->

        <key column="BOOK_ID"/>

        <one-to-many
             class="org.yourschool.library.domain.Item"/>
   </set>

<!-- describe many-to-many association between Book and Author -->

   <set name="authors"
      table="LINK_BOOK_AUTHOR"
      lazy="true"
      cascade="none"> <!--link table-->

      <key column="BOOK_ID"/> <!-- MINE ! -->

      <many-to-many
         class="org.yourschool.library.domain.Author"
         column="AUTHOR_ID"/>
   </set>

<!-- describe many-to-many association between Book and Subject -->

   <set name="subjects"
      table="LINK_BOOK_SUBJECT"
      lazy="true"
      cascade="none"> <!--link table-->

      <key column="BOOK_ID"/> <!-- MINE ! -->

      <many-to-many
         class="org.yourschool.library.domain.Subject"
         column="SUBJECT_ID"/>
   </set>

<!-- describe many-to-many association between Book and Series -->

   <set name="seriez"
      table="LINK_BOOK_SERIES"
      lazy="true"
      cascade="none"> <!--link table-->

      <key column="BOOK_ID"/> <!-- MINE ! -->

      <many-to-many
         class="org.yourschool.library.domain.Series"
         column="SERIES_ID"/>
   </set>

<!-- describe many-to-many association between Book and OtherTitle -->

   <set name="otherTitles"
      table="LINK_BOOK_OTHERTITLE"
      lazy="true"
      cascade="none"> <!--link table-->

      <key column="BOOK_ID"/> <!-- MINE ! -->

      <many-to-many
         class="org.yourschool.library.domain.OtherTitle"
         column="OTHERTITLE_ID"/>
   </set>

<!-- describe one-to-many relationship between Book and Reservation -->

   <set name="reservations"
         table="RESERVATIONS"
         lazy="true"
         inverse="true"
         cascade="all">

      <key column="BOOK_ID"/>

      <one-to-many
         class="org.yourschool.library.domain.Reservation"/>
   </set>

<!-- describe one-to-one association between Book and Note -->

   <one-to-one name="note"
      class="org.yourschool.library.domain.Note"
      cascade="all"/>

<!-- describe many-to-one relationship between Book and Publisher -->

   <many-to-one name="publisher"
      class="org.yourschool.library.domain.Publisher"
      cascade="none"
      column="PUBLISHER_ID"
        not-null="false"/>

</class>

</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM
         "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping>

   <class name="org.yourschool.library.domain.Note" table="NOTES"
      lazy="true">

      <id name="id"
         type="long"
         column="NOTE_ID">
         
         <generator class="foreign">
            <param name="property">book</param>
         </generator>
         
      </id>

      <version name="version" type="long" column="VERSION" />

      <property name="noteDetail"
         type="string"
         length="255"
         column="NOTE_DETAIL" />

      <!-- describe one-to-one relationship between Note and Book -->
      <one-to-one
         name="book"
         class="org.yourschool.library.domain.Book"
         cascade="none"
         constrained="true" />

   </class>

</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM
         "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping >

<class name="org.yourschool.library.domain.Publisher"
   table="PUBLISHERS" lazy="true">

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

    <version name="version" type="long" column="VERSION"/>

   <property name="publisherName"
      type="string"
      column="PUBLISHER_NAME"
                length="64"
      not-null="true"
                unique="true"/>

<!-- describe one-to-many association between Publisher and Book -->
   <set name="books"
            table="BOOKS"
            lazy="true"
            inverse="true"
            cascade="none"> <!-- reason for inverse="true" , see hibernate in action pg.110 -->

              <key column="PUBLISHER_ID"/>

              <one-to-many
                  class="org.yourschool.library.domain.Book"/>
        </set>

</class>

</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM
         "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping >

<class name="org.yourschool.library.domain.Author"
   table="AUTHORS" lazy="false">

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

    <version name="version" type="long" column="VERSION"/>

   <property name="authorName"
      type="string"
      column="AUTHOR_NAME"
                length="64"
      not-null="true"
                unique="true"/>

<!-- describe many-to-many association between Author and Book -->
   <set name="books"
            table="LINK_BOOK_AUTHOR"
            lazy="true"
            inverse="true"
            cascade="save-update"> <!-- reason for inverse="true" , see hibernate in action pg.110 -->

              <key column="AUTHOR_ID"/>

              <many-to-many
                  class="org.yourschool.library.domain.Book"
                  column="BOOK_ID"/>
        </set>

</class>

</hibernate-mapping>