java - Hibernate 多个表的连接和限制

Question

我有三个表要使用 Hibernate 或 JPA 查询...

表格结构

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

假设我的实体如下:

产品实体:

@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;

语言实体:

@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 实体(id 是 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;

因此，我必须使用产品和产品描述之间的联接来执行查询作为示例，其中语言为 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

首先，我使用 Hibernate 和 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();

但是该代码检索到此错误:

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

我的问题是什么？我怎样才能执行这样的查询？

非常感谢!

Answer 1

我不完全理解你的数据模型。我认为从语言的角度来看，语言和产品描述之间的关系应该是一对多，但先把它放在一边。 ..

更新:

实际上，Hibernate 没有使用您上面指出的注释正确映射关系。它试图映射表 language 中奇怪的 ManyToOne 关系，但找不到这些字段:productDescription_language_id 和 productDescription_product_id。

我认为您的表的正确映射是:

语言实体

@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
}

产品描述实体

@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
}

以下是一个工作示例，说明如何使用您在问题中声明的实体定义的 JPQL 进行链接连接。

    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]);
    }

该代码生成此 SQL 查询[已更新]

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=?

我一直在阅读有关该主题的一些文章和书籍，并且使用带有 where 子句的左连接获取是无效的。引用 Gavin King 等人的《Java Persistence with Hibernate》:“查询 left join fetch i.bids b where b.amount ... 无效。您不能说，“加载 Item 实例并初始化他们的出价集合，但仅限于具有一定金额的 Bid 实例”

希望这有帮助。