0

I created an Entity-Bean which maps an order-table of my MSSQL Server 2012. So far it works fine, but I'd like to join some columns from an Article-View to provide more information for the selected articles.

The order consists of several rows with its ID as Prime-Key and the Article-Nr as Foreign-Key.

I tried to map the View with the articles into an Article-Entity but due to the amount of rows (>10.000) it slows everything down (But works as it should... just incredibly slow... Too there's no other need to map this view any more - except for 3 rows of Information). So I'd just like to join the necessary columns to the Order-Entity.

But I don't get it working...

I tried it with the Annotation "NamedQuery"

@Entity
@NamedNativeQuery(name = "Joinnecessary", 
query = "SELECT Order.PK_ID, Order.FK_OrderID, Order.FK_ArticleNr, Order.Amount,
Article.Description, Article.Price 
FROM Order left outer join Article 
on Order.FK_ArticleNr=Article.PK_ArticleNr",
resultClass = Order.class)

I'm not sure where exactly the Problem is... My Records-Entity has a List of Order-Entities and displays them. 

for(orders o: orderlist){

In this line I get the following errors:

SEVERE: Invalid object name 'Order'. Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not initialize a collection: [qohelet.Record.orders#1] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)

In the literature I found something interesting for mapping 1:1-Relations. (From this perspective my relation can be seen as such one) if found the @SecondaryTable-Annotation which could be a fine solution for all my problems:

@Table(name = "Order")
@SecondaryTable(name = "Article", pkJoinColumns = {
    @PrimaryKeyJoinColumn(name = "PK_ArticleNr", referencedColumnName = "FK_ArticleNr")
})

The other columns I join like this:

@Column(table = "Article")
private String description;

But I guess the Annotation "PrimaryKeyJoinColumn" isn't named like that for no reason...

Initial SessionFactory creation failed.org.hibernate.AnnotationException: SecondaryTable JoinColumn cannot reference a non primary key

java.lang.ExceptionInInitializerErrorException in thread "main"

Is there another annotation which has the effect I'd like to get? Or am I using the current ones wrong? Thanks!

Micha
  • 5,117
  • 8
  • 34
  • 47
Qohelet
  • 1,459
  • 4
  • 24
  • 41

1 Answers1

0

the word order is special statement in T-SQL. -> use [ ] :

select [order].* 
from [order]
left outer join Article on [order].FK_ArticleNr = Article.PK_ArticleNr
order by price
jean-hugues
  • 31
  • 3