Hibernate Join and Restrictions on multiple tables

Question

I have three tables to query with Hibernate or JPA...

Tables structure

CREATE TABLE `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `insert_date` datetime NOT NULL,
  `last_update` datetime NOT NULL,
  ...
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8;

CREATE TABLE `language` (
  `language_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  ...
  PRIMARY KEY (`language_id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


CREATE TABLE `product_description` (
  `product_id` int(11) unsigned NOT NULL,
  `language_id` int(11) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `tag` text NOT NULL,
  `meta_title` varchar(255) NOT NULL,
  `meta_description` varchar(255) NOT NULL,
  `meta_keyword` varchar(255) NOT NULL,
  PRIMARY KEY (`product_id`,`language_id`),
  KEY `name` (`name`),
  KEY `product_language_idx` (`language_id`),
  CONSTRAINT `product_description` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `product_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Product     <OneToMany>     ProductDescription
Language    <ManyToOne>     ProductDescription

Assuming my entities are the following:

Product Entity:

@Entity
@Table(name="product")
@NamedQuery(name="Product.findAll", query="SELECT p FROM Product p")
public class Product implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="product_id", unique=true, nullable=false)
private int productId;
....
//bi-directional many-to-one association to ProductDescription
@OneToMany(fetch = FetchType.LAZY, mappedBy = "product")
private List<ProductDescription> productDescriptions;

Language Entity:

@Entity
@Table(name="language")
@NamedQuery(name="Language.findAll", query="SELECT l FROM Language l")
public class Language implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="language_id", unique=true, nullable=false)
private int languageId;
...

//bi-directional many-to-one association to ProductDescription
@ManyToOne(fetch=FetchType.LAZY)
//@JoinColumn(name="language_id", referencedColumnName="id",nullable=false, insertable=false, updatable=false)
private ProductDescription productDescription;

ProductDescription Entity (id is a composite key in class ProductDescriptionPK):

@Entity
@Table(name="product_description")
@NamedQuery(name="ProductDescription.findAll", query="SELECT p FROM ProductDescription p")
public class ProductDescription implements Serializable {
    private static final long serialVersionUID = 1L;

@EmbeddedId
private ProductDescriptionPK id;
//bi-directional many-to-one association to Language
@OneToMany(mappedBy="productDescription")
private List<Language> languages;

//bi-directional many-to-one association to Product
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="product_id", nullable=false, insertable=false, updatable=false)
private Product product;

So I have to execute a query as sample, using a join between product and product_description, where language is 1:

SELECT p.product_id, pd.description
FROM product p
INNER JOIN product_description pd
USING ( product_id )
INNER JOIN language
USING ( language_id )
WHERE language_id = 1

Firstly I have used Hibernate with Criteria Query:

     Criteria criteria = getSession().createCriteria(Product.class,"p");
     criteria.createAlias("p.productDescriptions", "pd");
     criteria.createAlias("pd.languages", "l");
     criteria.add(Restrictions.eq("l.languageId", new Integer(1)));
     result = criteria.list();

But that code retrieves this error:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'l2_.productDescription_language_id' in 'field list'

What's my issue? How can I perform queries like this?

Thanks a lot!

Answer 1

I do not understand completely your data model. I think that the relationship between a Language and a ProductDescription should be one-to-many from the Language point of view but putting that aside ...

UPDATED:

Effectively, Hibernate do not map correctly the relationships using the annotations that you have indicated above. It's trying to map the strange ManyToOne relation in the table language and it can not find those fields: productDescription_language_id and productDescription_product_id.

I think that the correct mapping for your tables is:

LANGUAGE ENTITY

@Entity
public class Language {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="language_id", unique=true, nullable=false)
    private Long languageId;

    private String name;

    @OneToMany(fetch=FetchType.LAZY, mappedBy="language")
    private List<ProductDescription> productDescriptions = 
         new ArrayList<ProductDescription>();

    // Other fields + getters and setters
}

PRODUCT DESCRIPTION ENTITY

@Entity
@Table(name="product_description")
public class ProductDescription {

      @Embeddable
      public static class ProductDescriptionPK implements Serializable {

      private static final long serialVersionUID = 1L;

      @Column(name = "product_id")
      protected Long productId;

      @Column(name = "language_id")
      protected Long languageId;

      public ProductDescriptionPK() {
      }

      public ProductDescriptionPK(Long productId, Long languageId) {
        super();
        this.productId = productId;
        this.languageId = languageId;
     }

   }

   @EmbeddedId
   private ProductDescriptionPK id;

   private String description;

   @ManyToOne
   @JoinColumn(name="language_id", nullable=false, insertable=false, updatable=false)
   private Language language;

   @ManyToOne(fetch=FetchType.LAZY)
   @JoinColumn(name="product_id", nullable=false, insertable=false, updatable=false)
   private Product product;

  // Other fields + getters and setters
}

Here is a working example of how you can chain joins with JPQL using the entities defined as you have declared them on your question.

    EntityManager em = emf.createEntityManager();

    // [UPDATED] QUERY
    String jpql = "SELECT p.id, pd.description FROM Product p "
                + "JOIN p.productDescriptions pd "
                + "JOIN pd.language l WHERE l.language_id = :idLanguage)";

    Query query = newEm.createQuery(jpql);
    query.setParameter("idLanguage", new Long(1));

    List<Object> resultList = query.getResultList();

    System.out.println( resultList.size() + " product(s) found:" );

    for (Object singleResult : resultList) {
         Object[] singleRow = (Object[]) singleResult;
         System.out.println(singleRow[0] + " " + singleRow[1]);
    }

That code generates this SQL query [UPDATED]

select
    product0_.product_id as col_0_0_,
    productdes1_.description as col_1_0_ 
from
    Product product0_ 
inner join
    product_description productdes1_ 
        on product0_.product_id=productdes1_.product_id 
inner join
    Language language2_ 
        on productdes1_.language_id=language2_.language_id 
where
    language2_.language_id=?

I have been reading some articles and books on the subject and using a left join fetch with a where clause is invalid. Quoting "Java Persistence with Hibernate" by Gavin King et al: "The query left join fetch i.bids b where b.amount ... is invalid. You can't say, "Load the Item instances and initializes their bids collections, but only with Bid instances that have a certain amount"

Hope this helps.