0

I have 3 tables : BookInfo, AuthorInfo and BookAuthors

BookInfo has one to many relationship with BookAuthors. Mapping in BookInfo.hbm.xml :

<set name="bookAuthorsesByBookId" inverse="true">
    <key>
        <column name="book_id" not-null="true"/>
    </key>
    <one-to-many not-found="ignore" class="com.pojo.hibernate.BookAuthors"/>
</set>

BookAuthors contain bookInfo and authorInfo. Mapping in BookAuthors.hbm.xml : 

<composite-id mapped="true" class="com.pojo.hibernate.BookAuthors">
    <key-many-to-one name="bookInfoByBookId" class="com.pojo.hibernate.BookInfo" column="book_id"/>
    <key-many-to-one name="authorInfoByAuthorId" class="com.pojo.hibernate.AuthorInfo" column="author_id"/>
</composite-id>

And, relationship between AuthorInfo and BookAuthors is one-to-many,too. Mapping in AuthorInfo.hbm.xml :

<set name="bookAuthorsesByAuthorId" inverse="true">
    <key>
        <column name="author_id" not-null="true"/>
    </key>
    <one-to-many not-found="ignore" class="com.pojo.hibernate.BookAuthors"/>
</set>

Now, i'm trying to get all books which has author name = "authorname" using detachedcriteria like this :

DetachedCriteria detachedCriteria = DetachedCriteria.forClass(BookInfo.class,"bookInfo");
detachedCriteria.createAlias("bookInfo.bookAuthorsesByBookId","bookAuthorses");
detachedCriteria.createAlias("bookAuthorses.authorInfoByAuthorId", "authorInfo");

detachedCriteria.add(Restrictions.like("authorInfo.authorName", searchQuery, MatchMode.ANYWHERE));

hibernateTemplate.findByCriteria(detachedCriteria);

But, it generates an error. Summarized error :

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select this_.book_id as book1_7_2_,....]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'authorinfo2_.author_name' in 'where clause'

and the query it generates is : 

select this_.book_id as book1_7_2_, ... from book.book_info this_ 
inner join book.book_authors authors1_ on this_.book_id=authors1_.book_id
where (author2_.author_name like ?)

Full Query : *

SELECT this_.book_id              AS book1_7_2_,
   this_.isbn_10              AS isbn2_7_2_,
   this_.isbn_13              AS isbn3_7_2_,
   this_.book_title           AS book4_7_2_,
   this_.book_subtitle        AS book5_7_2_,
   this_.book_description     AS book6_7_2_,
   this_.no_of_pages          AS no7_7_2_,
   this_.book_mrp             AS book8_7_2_,
   this_.book_language        AS book9_7_2_,
   this_.book_format          AS book10_7_2_,
   this_.book_img_url         AS book11_7_2_,
   this_.publishing_date      AS publishing12_7_2_,
   this_.verified             AS verified7_2_,
   this_.publisher_id         AS publisher14_7_2_,
   publisher3_.publisher_id   AS publisher1_19_0_,
   publisher3_.publisher_name AS publisher2_19_0_,
   bookauthor1_.book_id       AS book1_5_1_,
   bookauthor1_.author_id     AS author2_5_1_
FROM   book.book_info this_
   INNER JOIN book.publisher_info publisher3_
           ON this_.publisher_id = publisher3_.publisher_id
   INNER JOIN book.book_authors bookauthor1_
           ON this_.book_id = bookauthor1_.book_id
WHERE  ( ( this_.book_title LIKE ?
        OR authorinfo2_.author_name LIKE ?
        OR publisher3_.publisher_name LIKE ?
        OR this_.isbn_10 LIKE ?
        OR this_.isbn_13 LIKE ? )
     AND 1 = 1 )
Ashish Tanna
  • 645
  • 1
  • 10
  • 25
  • That's not the query you're looking for. Unknown column 'authorinfo2_.author_name' means the query has that value in `where` clause. Basically the sql you provide does not match with your error. Just try to find the query that hibernate complains about and you'll realize that the field `author_name` is being searched in the incorrect table. – Aritz Aug 14 '13 at 05:55
  • The query is same. Still i have added the full query. I don't know why it uses **authorinfo2_** alias, though it hasn't generated that alias. – Ashish Tanna Aug 14 '13 at 09:32
  • So, are that queries working if you execute them in your sql tool directly (adding param values)? Hibernate logs publish what they are retrieving from DB. If you put that query logs into your SQL tool they should also work. Just try to find in your logs the field which is not working and see what's failing in that query. – Aritz Aug 14 '13 at 09:50
  • it works when i add : `INNER JOIN book.author_info authorinfo2_ ON bookauthor1_.author_id = authorinfo2_.author_id` but, i don't know how to add this using detached criteria. – Ashish Tanna Aug 14 '13 at 10:35
  • That's what `createAlias` does, basically it performs a join operation. What you basically have is a many-to-many relation, however you're mapping it as an unecessary one to many, from my point of view. Isn't it easier to map a bidirectional many-to-many relation and to have the books with their authors and authors with their books directly in entities? Just dessign matter. – Aritz Aug 14 '13 at 11:04
  • Any way to do this in this type of relations? cause i've used same in many other tables,too. – Ashish Tanna Aug 14 '13 at 13:10
  • Look at [ternary associations field](http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/collections.html#collections-ternary) of Hibernate docs. – Aritz Aug 14 '13 at 13:21

0 Answers0