@ManyToOne - extra column?

[Mimi Tam]

Hi,

I got an exception doing this HQL query below. Same code worked fine before I added the @ManyToOne annotation.

Exception on createQuery():

		public static List<Category> findCategoryEntries(int firstResult, int maxResults) {
	        return entityManager().createQuery("select o from Category o").setFirstResult(firstResult).setMaxResults(maxResults).getResultList();
	    }

In adding @ManyToOne, I had to declare a new field for an instance of the mapped object class - "consumer". This extra field is a whole class object and my MySQL database table does not have this column (i.e. consumer). I suspect that the mapping is out-of-sync. I don't know if I need to add this column and if so, how to.

The @ManyToOne side:

@Configurable
@Entity
public class Category {<--

	// Bidirectional association with join table
        // Category <--> Consumer (N:1)
	@ManyToOne()
            @JoinColumn(name = "cat_id", insertable=false,updatable=false)
		
        @PersistenceContext
	transient private Consumer consumer; // <------THIS IS THE EXTRA field added

	public void setConsumer(Consumer consumer) {
		this.consumer = consumer;
	}
	public Consumer getConsumer() {
		return consumer;
	}
	
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)       
    @Column(name = "id")
    private Long id;
  
	public Long getId() {
        return this.id;
    }
	public void setId(Long id) {
        this.id = id;
    }
	
    @NotNull
    @Column(name = "cat_id")
    private String catId;
	
	public String getCatId() {
        return this.catId;
    }
	public void setCatId(String catId) {
        this.catId = catId;
    }...... 

The @OneToMany side:

@Configurable
@Entity
public class Consumer {

    // Bidirectional association wih join table
    // Consumer <--> Category (1:N)
    @OneToMany (mappedBy="consumer")
	@JoinTable(name="ConCat",
			joinColumns={@JoinColumn(name="con_id", unique=true)},
			inverseJoinColumns= {@JoinColumn(name="cat_id")})

    // THIS IS THE EXTRA FIELD ADDED - a list of categories
    @PersistenceContext
    transient private static List<Category> categories = new ArrayList<Category>();
    
    public static List<Category> getCategories() {    
        return categories;
    }

    public void setCategory(List<Category> categories) {
        this.categories = categories;
    } 
   .....

I had to add an extra field (a list) on the @OneToMany side as well. My database table does not have this field as a column either. Do I need to add this List<> as a column on my table and if so, how?

I might have the @ManyToOne and @OneToMany mapping done incorrectly as well.

Also, I declared the extra fields with @PersistenceContext and 'transient'. Is this a wrong thing to do?

I am doing this the first time. The answer could be obvious but I couldn't find info/help on this specific scenario. Your help will be greatly appreciated.

Many Thanks in advance.

[John Bengler]

Hi Mimi,

I think the first problem is that when you use the mappedBy on the Consumer side you have to do all definitions on the Category side, I think in your example you have to move the definition of the join table to the Category.

Hibernate Annotations - mapping relations


John

[John Bengler]

Hi again..

I read your post for a second time...

In your DB you have a join table ConCat with two columns con_id and cat_id?

And I think you don't need the PersistenceContext annotation and the transient definition...
At least in EJB3 which has a very similar syntax you don't need those...


John

[Mimi Tam]

Thank you so much for your responses, John.

I was indeed expecting the ConCat link table to be created for me but I don't see it. I guess it exception-ed before having a chance to do that.

I took out the @PersistenceContext and transient.

The problem is still there and I suspect the regular @Entity mapping of the POJO is the culprit but I don't know how to handle it with the extra fields. My @ManyToOne and @OnetoMany could be quite wrong as well.

Thanks for the reference link. I did read that and a whole lot of other resources to find a clear answer but to no avail.

I want 'Consumer' to be the owner and I thought 'mappedBy' should be put on the side of the owner. Is that not true?

Any other help will be greatly appreciated. I am stuck.

Many Thanks in advance.

[John Bengler]

Hi Mimi,

do you have a special reason for using an assignment table?

If not here is what I would do:


I would create my tables like this:

Table Consumer

columns:

id
.
.
.


Table Category

id
con_id
.
.
.


and my classes like this:

  

# @Entity  
# public class Category
#   
#     // Bidirectional association with join table  
#     // Category <--> Consumer (N:1)  
#     @ManyToOne()  
#     @JoinColumn(name = "con_id")  
#     private Consumer consumer;  
#   
#       
#     @Id  
#     @GeneratedValue(strategy=GenerationType.AUTO)         
#     @Column(name = "id")  
#     private Long id;  
#
# setters,getters,additional attributes...
 

   
# @Entity  
# public class Consumer {  
#   
#     // Bidirectional association wih join table  
#     // Consumer <--> Category (1:N)  
#     @OneToMany (mappedBy="consumer")  
#     private List<Category> categories = new ArrayList<Category>();  
#       
#     @Id  
#     @GeneratedValue(strategy=GenerationType.AUTO)         
#     @Column(name = "id")  
#     private Long id;  
#
# setters,getters,additional attributes... 

If you define a OneToMany relation without a join column it makes sense to use the mappedBy on the One-side, because the column used here is obvious - it's the primary key.. On the Many-side you have a real join column, in this case con_id...


You only need the insertable=false,updateable=false when you add the con_id as a normal column to your entity, too..

In the example above:

  

# @Entity  
# public class Category
#   
#     // Bidirectional association with join table  
#     // Category <--> Consumer (N:1)  
#     @ManyToOne()  
#     @JoinColumn(name = "con_id", insertable=false,updatable=false)  
#     private Consumer consumer; 
#   
#       
#     @Id  
#     @GeneratedValue(strategy=GenerationType.AUTO)         
#     @Column(name = "id")  
#     private Long id;  
#
#     @Column(name = "con_id")  
#     private Long conId;  
#
# setters,getters,additional attributes...
 

Hope this helps you..

John

[Mimi Tam]

Hi John,

Thank you so very much for the detailed explanation and sample code. Its tremendously helpful. I really appreciate it. I made the changes as instructed and ran into my initial hurdle.

In my Category class, Hibernate is complaining about the missing column con_id (i.e. consumer).

@Configurable
@Entity
public class Category {
    @ManyToOne()    
    @JoinColumn(name = "con_id")    
    private Consumer consumer;  
    ......

Here's the exception:

Caused by: org.hibernate.HibernateException: Missing column: con_id in demoDb.category
	at org.hibernate.mapping.Table.validateColumns(Table.java:254)
	at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1089)....

On the Consumer side, I suspect it will complain about the 'categories' list that it could not be mapped to (missing column):

    @OneToMany (mappedBy="consumer")    
    private List<Category> categories = new ArrayList<Category>();

How should I handle these mapping key fields (i.e. con_id in Category and List<category> in Consumer)? Here're the two Tables:

Table Category
id - primary key
cat_id
cat_name
...


Table Consumer
id - primary key
con_id
con_name
....

I know I am missing somethluing key to this solution but don't know what. Will you please illuminate me a bit further?

My sincere THANKS.

[John Bengler]

Hi Mimi,

you're welcome!

In your table definition, what is the differenct between id and cat_id (for Category)? Looks to me pretty much the same... If so, I would get rid of one of them..

And what's missing for your relation is a foreign key column con_id in table Categories, which points to the primary key of table Consumer - as Hibernate implies..

And no, Hibernate will not complain, that there is no List in table Consumer...

Just add a column con_id to table Category and it should work..

John

[Mimi Tam]

Thank you again so much for your prompt reply.

I need the 'id' which is auto_incremented by MySQL and also 'cat_id' for application logic so I have to keep both.

I added the 'con_id' column to table Category and got this message:

'Wrong type.....add insert=false,update=false"

So, I added "insertable=false,updatable=false" next to the JoinColumn. Field 'con_id' (i.e. consumer id) is unknown and I expect the @OneToMany to auto populate it.

I have an entry in table Consumer with id=3 but I am getting the following error message:

Unable to find com.bzinc.demoBackup.domain.Consumer with id 0; nested exception is javax.persistence.EntityNotFoundException: Unable to find com.bzinc.demoBackup.domain.Consumer with id 0

In my Category:

    @ManyToOne()    
    @JoinColumn(name = "con_id", insertable=false,updatable=false)    
    private Consumer consumer;   
	
 	public void setConsumer(Consumer consumer) {
		this.consumer = consumer;
	}
	public Consumer getConsumer() {
		return consumer;
	}
	
	@NotNull
    @Column(name = "con_id")
    private int conId;
...

In my Consumer:

    @OneToMany (mappedBy="consumer")    
    private List<Category> categories = new ArrayList<Category>();    
  	
	public List<Category> getCategories() {
		return categories;
	}

	public void setCategories(List<Category> categories) {
		this.categories = categories;
	}
	
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

I know I am still doing something wrong. Can you please point it out to me, John?

I really appreciate your help.

[Mimi Tam]

Hi John,

I think I saw what might be wrong. I took out declarations of the primary/foreign keys from the 2 classes and just left the getter(). The previous error went away and I now get this error: (I took the con_id column out from Category previously)

Caused by: org.hibernate.HibernateException: Missing column: con_id in demoDb.category
	at org.hibernate.mapping.Table.validateColumns(Table.java:254)
	at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1089)...

So I added 'con_id" column back to my Category table and I got the following uncaughtException:

Caused by: javax.servlet.ServletException: File "/WEB-INF/views/uncaughtException.jspx" not found
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:319)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)...

Now, my Category looks like this:

 @ManyToOne()    
    @JoinColumn(name = "con_id", insertable=false,updatable=false)   
 
      public Consumer getConsumer() {
    	// test --
    	// use con_id to get consumer.findWhatever() as return
    	Consumer consumer = null;
		return consumer;
	}

Consumer looks like below:

    @OneToMany (mappedBy="consumer")      	

	public List<Category> getCategories() {
    	// test --
    	// How can I access cat_id from here to call a Category method, Category.findWhatever()?
    	List<Category> categories = new ArrayList<Category>();
		return categories;
	

Can you please point out what I done wrong?

Should 'con_id' be declared a column (and expect values to be filled auto by @ManyToOne mapping?) for Category?

Thanks again so much for your help.

[John Bengler]

Hmm, strange.. I think this should work...

..ah, I saw your edited your post.. Hard to keep up with you.. ;-)

Ok,about the con_id in the category class.. You need to map it once with read/write access..

Either you add it as an additional column (what sometimes makes sense) and leave your relation as it is or (what I would prefer) you remove the insertable=false,updateable=false fromthe relation and set the (Database-)columns value by setting the Consumer attribute in your Category..

What I wanted to tell you before: you are aware that with this configuraion the value of con_id in table Category will point to column id in table Consumer (because this is the pk), not to column con_id..?

[Mimi Tam]

Thank you again so very much for the detailed info. I really appreciate it. If I may follow up:

Are you saying that there are 2 ways to do this:

1. Have 'con_id' as a column in Category, use insert=false, update=false, Hibernate will auto fill the 'con_id' in Category --> my current code

2. Have (or Remove?) 'con_id' as a column in Category, remove insert=false, update=false and programmatically put in values for 'con_id' from Category calling a Consumer.findxx() method at run-time? --> your preference

If it is #2 above, will it be defeating the purpose of using association annotation to auto-map for us (to avoid heavy coding)? I am sure I misunderstood you here.

No, I wasn't aware of the 'con_id' pointing to 'id' in Consumer. Thank you very much for pointing this out. Since this is the case, may I define 'con_id' as the primary key in Consumer (instead of 'id') and all other tables' <xxx>_id to be the primary key? OR May I use each and every POJO's 'id' for mapping and leave the '<xxx>_id's alone?

I just started this as the 1st relation mapping and I have many other tables which I shall need to use @ManyToMany, @ManyToOne and @OneToOne every other way. It will be less confusing to me if it is possible to map all primary keys using either 'id' or '<xxx>_id.

Sorry, I don't understand this statement and how to do it, can you elaborate please?

"...about the con_id in the category class.. You need to map it once with read/write access.."

My very sincere THANKS.

[John Bengler]

Sorry, it seems like I didn't express myself clear enough....

What I meant is that when you use the con_id in your Category for your relation AND as a normal column you have to set the relation to insertable=false, updateable=false....

If you don't define it as a column you have to remove the insertable=false, updateable=false or you won't be able to persist such a relation...


A simple example...

Let's propose I have two tables, Language and Translation....
Language should have the columns ID and NAME, Translation should have ID, TEXT_ENGLISH, TEXT_TRANSLATED and the foreign key column LANG_ID

Of course each Translation has a Language and - just to make it bidirectional - every Language should have its List of Translations...

Now we could define this as

@Entity  
public class Language { 

  @Id  
  @GeneratedValue(strategy = GenerationType.AUTO)  
  @Column(name = "id")  
  private Long id; 

  @Column(name = "text_english")
  private String textEnglish;



  @OneToMany (mappedBy="language")            
  public List<Translation> translations()

/* Getters, Setters, other stuff */
}
  

@Entity  
public class Translation { 

  @Id  
  @GeneratedValue(strategy = GenerationType.AUTO)  
  @Column(name = "id")  
  private Long id; 

  @Column(name = "text_english")
  private String textEnglish;

  @Column(name = "text_translated")
  private String textTranslated;

  @ManyToOne  
  @JoinColumn(name = "lang_id")              
  public Language  language;


/* Getters, Setters, other stuff */
}

or you could write

@Entity  
public class Language { 

  @Id  
  @GeneratedValue(strategy = GenerationType.AUTO)  
  @Column(name = "id")  
  private Long id; 

  @Column(name = "text_english")
  private String textEnglish;



  @OneToMany (mappedBy="language")            
  public List<Translation> translations()

/* Getters, Setters, other stuff */
}
  

@Entity  
public class Translation { 

  @Id  
  @GeneratedValue(strategy = GenerationType.AUTO)  
  @Column(name = "id")  
  private Long id; 

  @Column(name = "text_english")
  private String textEnglish;

  @Column(name = "text_translated")
  private String textTranslated;

  @Column(name = "lang_id")
  private Long langId;

  @ManyToOne  
  @JoinColumn(name = "lang_id", insertable=false,updatable=false)              
  public Language  language;


/* Getters, Setters, other stuff */
}

I hope it doesn't confuse you that I annotate the attributes, not the getters..
Doesn't make a difference, just avoid mixing this - at least for style reasons...

And, yes, the second example could cause you trouble, someone could try to set the language (or the consumer in your example), merge the entity and wonder why in the DB the column lang_id is empty... - I told you I would use the first one..

Just a short aditional warning: relations are not container managed in EJB3 - I think it's the same in Hibernate - this means you have to write

Language langEspanol = new Language("Spanish");

/* some code */
Translation tranBye = new Translation("Goodbye","Adios");

/*some code */
tranBye.setLanguage(langEspanol);
langEspanol.setTranslations(langEspanol.getTranslations().add(tranBye));

/* persist(), merge() */

If you don't do the second set.. then the langEspanol wouldn't know that it know has one more translation...



Now let's have a look at the relation definition... You said you want to have your con_id in table Category to point to con_id in Consumer instead of the primary key...

Even if I won't do this (for instance for performance reasons, the PK is inndexed, while your con_id may not be indexed) this can be defined using an additional parameter on Annotation @JoinColumn: referencedColumnName

 
    @ManyToOne()      
    @JoinColumn(name = "con_id", referencedColumnName="con_id")      
    private Consumer consumer; 

If you don't use the referencedColumnName attribute it will connect to the pk column...


Hopes this answers some of your questions..


Have a nice weekend!

John

[Mimi Tam]

THANK YOU!!!

I now understand . Its very clear! I am really very grateful. Thanks for your patience.

Have a Great Weekend!

[John Bengler]

You're welcome!

And, did it work?


John