-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
Christophe Laumond
 Post subject: Hibernate insert statement with missing columns
PostPosted: Wed Oct 04, 2006 7:44 am 
Beginner
Beginner

Joined: Mon Apr 03, 2006 2:41 am
Posts: 25
Location: Mauritius
Hi

I have an entity 'Questionnaire' with a many-to-one relationship on entity 'Questionnairetemplate'.
So several questionnaires are linked to the same template.

When I try to persist a new 'Questionnaire' in the database, the insert statement generated by Hibernate doesn't contain the column 'questionnairetemplateid' which represent the link many-to-one in the database.

The code looks correct and Hibernate detects that an instance QuestionnaireTemplate is set into the new Questionnaire instance according to the code.

Something special is that the foreign key on the 'QuestionnaireTemplate' is using one of the column of the 'Questionnaire' primary key ( the 'Year' column ).

Any idea why the insert statement is incomplete ?
Where can I have look in order to find more details about the generation of the insert statement ?

Thanks in advance for your help.

Regards,
Christophe

Hibernate version:

Hibernate EntityManager 3.2.0.CR1
Hibernate Annotations 3.2.0.CR1
Hibernate 3.2 cr2

Code between sessionFactory.openSession() and session.close():

Questionnaire questionnaire = new Questionnaire();
questionnaire.setId(questionnaireId);
Questionnairetemplate questionnairetemplate = questionnaireTemplateDAO.findById(questionnairetemplateId);
questionnaire.setQuestionnairetemplate(questionnairetemplate);
entityManager.persist(questionnaire);

Name and version of the database you are using:
MySQL Server 5.0

Debug level Hibernate log excerpt:

Code:
[testng] DEBUG 04-10 10:23:43,828 (Log4JLogger.java:debug:84)  -generated identifier: component[companyId,financialStatementId,screeningTypeId,year]{companyId=1, year=2005, screeningTypeId=2, financialStatementId=1}, using strategy: org.hibernate.id.Assigned
   [testng] DEBUG 04-10 10:23:43,843 (Log4JLogger.java:debug:84)  -merge successful
   [testng] DEBUG 04-10 10:23:43,859 (Log4JLogger.java:debug:84)  -processing flush-time cascades
   [testng] DEBUG 04-10 10:23:43,859 (Log4JLogger.java:debug:84)  -dirty checking collections
   [testng] DEBUG 04-10 10:23:43,875 (Log4JLogger.java:debug:84)  -Collection found: [accountingAudit.businessObjects.Questionnairetemplate.questionnairetypes#component[questionnaireId,year]{questionnaireId=1, year=2005}], was: [accountingAudit.businessObjects.Questionnairetemplate.questionnairetypes#component[questionnaireId,year]{questionnaireId=1, year=2005}] (uninitialized)
   [testng] DEBUG 04-10 10:23:43,875 (Log4JLogger.java:debug:84)  -Collection found: [accountingAudit.businessObjects.Questionnairetemplate.questionnaires#component[questionnaireId,year]{questionnaireId=1, year=2005}], was: [accountingAudit.businessObjects.Questionnairetemplate.questionnaires#component[questionnaireId,year]{questionnaireId=1, year=2005}] (uninitialized)
   [testng] DEBUG 04-10 10:23:43,875 (Log4JLogger.java:debug:84)  -Collection found: [accountingAudit.businessObjects.Questionnairetemplate.questionnairegroups#component[questionnaireId,year]{questionnaireId=1, year=2005}], was: [accountingAudit.businessObjects.Questionnairetemplate.questionnairegroups#component[questionnaireId,year]{questionnaireId=1, year=2005}] (uninitialized)
   [testng] DEBUG 04-10 10:23:43,890 (Log4JLogger.java:debug:84)  -Collection found: [accountingAudit.businessObjects.Questionnaire.answers#component[companyId,financialStatementId,screeningTypeId,year]{companyId=1, year=2005, screeningTypeId=2, financialStatementId=1}], was: [<unreferenced>] (initialized)
   [testng] DEBUG 04-10 10:23:43,890 (Log4JLogger.java:debug:84)  -Flushed: 1 insertions, 0 updates, 0 deletions to 2 objects
   [testng] DEBUG 04-10 10:23:43,890 (Log4JLogger.java:debug:84)  -Flushed: 1 (re)creations, 0 updates, 0 removals to 4 collections
   [testng] DEBUG 04-10 10:23:43,906 (Log4JLogger.java:debug:84)  -listing entities:
   [testng] DEBUG 04-10 10:23:43,906 (Log4JLogger.java:debug:84)  -accountingAudit.businessObjects.Questionnairetemplate{enforcementyear=accountingAudit.businessObjects.Enforcementyear#2005, questionnaires=<uninitialized>, questionnairetypes=<uninitialized>, questionnairegroups=<uninitialized>, question=accountingAudit.businessObjects.Question#component[questionId,year]{year=2005, questionId=1}, id=component[questionnaireId,year]{questionnaireId=1, year=2005}}
   [testng] DEBUG 04-10 10:23:43,906 (Log4JLogger.java:debug:84)  -accountingAudit.businessObjects.Questionnaire{financialstatement=null, screeningtype=null, concludingRemarks=null, answers=[], questionnairetemplate=accountingAudit.businessObjects.Questionnairetemplate#component[questionnaireId,year]{questionnaireId=1, year=2005}, userByConcludedUserId=null, concludedTimestamp=null, lastEditedTimestamp=null, userByLastEditedUserId=null, id=component[companyId,financialStatementId,screeningTypeId,year]{companyId=1, year=2005, screeningTypeId=2, financialStatementId=1}}
   [testng] DEBUG 04-10 10:23:43,937 (Log4JLogger.java:debug:84)  -about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
   [testng] DEBUG 04-10 10:23:43,937 (Log4JLogger.java:debug:84)  -opening JDBC connection
   [testng] DEBUG 04-10 10:23:43,937 (Log4JLogger.java:debug:84)  -insert into accountingaudit.questionnaire (LastEditedUserId, ConcludedUserId, LastEditedTimestamp, ConcludingRemarks, ConcludedTimestamp, CompanyId, FinancialStatementId, ScreeningTypeId, Year) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
   [testng] Hibernate: insert into accountingaudit.questionnaire (LastEditedUserId, ConcludedUserId, LastEditedTimestamp, ConcludingRemarks, ConcludedTimestamp, CompanyId, FinancialStatementId, ScreeningTypeId, Year) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
   [testng] DEBUG 04-10 10:23:43,953 (Log4JLogger.java:debug:84)  -Executing batch size: 1
   [testng] DEBUG 04-10 10:23:44,296 (Log4JLogger.java:debug:84)  -about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
   [testng] DEBUG 04-10 10:23:44,296 (Log4JLogger.java:debug:84)  -skipping aggressive-release due to flush cycle
   [testng] DEBUG 04-10 10:23:44,312 (Log4JLogger.java:debug:89)  -Could not execute JDBC batch update [insert into accountingaudit.questionnaire (LastEditedUserId, ConcludedUserId, LastEditedTimestamp, ConcludingRemarks, ConcludedTimestamp, CompanyId, FinancialStatementId, ScreeningTypeId, Year) values (?, ?, ?, ?, ?, ?, ?, ?, ?)]
   [testng] java.sql.BatchUpdateException: Cannot add or update a child row: a foreign key constraint fails (`accountingaudit/questionnaire`, CONSTRAINT `Ref_36` FOREIGN KEY (`Year`, `QuestionnaireId`) REFERENCES `questionnairetemplate` (`Year`, `QuestionnaireId`) ON DELETE NO ACTION ON UPDATE NO ACT)34


Mapping annotations:

Code:
@Entity
@Table(name = "questionnaire", catalog = "accountingaudit", uniqueConstraints = {})
public class Questionnaire implements java.io.Serializable {

@EmbeddedId
@AttributeOverrides( {
@AttributeOverride(name = "year", column = @Column(name = "Year", unique = false, nullable = false, insertable = true, updatable = true)),
@AttributeOverride(name = "companyId", column = @Column(name = "CompanyId", unique = false, nullable = false, insertable = true, updatable = true)),
@AttributeOverride(name = "financialStatementId", column = @Column(name = "FinancialStatementId", unique = false, nullable = false, insertable = true, updatable = true)),
@AttributeOverride(name = "screeningTypeId", column = @Column(name = "ScreeningTypeId", unique = false, nullable = false, insertable = true, updatable = true)) })
public QuestionnaireId getId() {
   return this.id;
}

@ManyToOne(cascade = {}, fetch = FetchType.LAZY)
@JoinColumns( {
@JoinColumn(name = "Year", referencedColumnName = "Year", unique = false, nullable = false, insertable = false, updatable = false),
@JoinColumn(name = "QuestionnaireTemplateId", referencedColumnName = "QuestionnaireTemplateId", unique = false, nullable = false, insertable = false, updatable = false) })
public Questionnairetemplate getQuestionnairetemplate() {
   return this.questionnairetemplate;
}


Code:
@Entity
@Table(name = "questionnairetemplate", catalog = "accountingaudit", uniqueConstraints = {})
public class Questionnairetemplate implements java.io.Serializable {

@EmbeddedId
@AttributeOverrides( {
@AttributeOverride(name = "year", column = @Column(name = "Year", unique = false, nullable = false, insertable = true, updatable = true)),
@AttributeOverride(name = "questionnaireTemplateId", column = @Column(name = "QuestionnaireTemplateId", unique = false, nullable = false, insertable = true, updatable = true)) })
public QuestionnairetemplateId getId() {
   return this.id;
}

@OneToMany(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY, mappedBy = "questionnairetemplate")
public Set<Questionnaire> getQuestionnaires() {
   return this.questionnaires;
}


Code:


@Embeddable
public class QuestionnaireId implements java.io.Serializable {

@Column(name = "Year", unique = false, nullable = false, insertable = true, updatable = true)
public int getYear() {
   return this.year;
}

@Column(name = "CompanyId", unique = false, nullable = false, insertable = true, updatable = true)
public long getCompanyId() {
   return this.companyId;
}

@Column(name = "FinancialStatementId", unique = false, nullable = false, insertable = true, updatable = true)
public short getFinancialStatementId() {
   return this.financialStatementId;
}

@Column(name = "ScreeningTypeId", unique = false, nullable = false, insertable = true, updatable = true)
public byte getScreeningTypeId() {
   return this.screeningTypeId;
}


SQL for the table :
Code:
CREATE TABLE `questionnaire` (
  `Year` int(4) UNSIGNED NOT NULL DEFAULT '0',
  `CompanyId` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `FinancialStatementId` smallint(6) UNSIGNED NOT NULL DEFAULT '0',
  `ScreeningTypeId` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `QuestionnaireTemplateId` int(11) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY(`Year`, `CompanyId`, `FinancialStatementId`, `ScreeningTypeId`)
)

...

ALTER TABLE `questionnaire` ADD
  CONSTRAINT `Ref_36` FOREIGN KEY (`Year`, `QuestionnaireTemplateId`)
    REFERENCES `questionnairetemplate`(`Year`, `QuestionnaireTemplateId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;
Top
 Profile  
 
JayeshJ
 Post subject:
PostPosted: Thu Oct 05, 2006 2:46 am 
Regular
Regular

Joined: Tue May 16, 2006 3:32 am
Posts: 117
Code:
@JoinColumn(name = "QuestionnaireTemplateId", referencedColumnName = "QuestionnaireTemplateId", unique = false, nullable = false, insertable = false, updatable = false) })


Shouldn't this be insertable = true ? (also updatable = true if required)
Top
 Profile  
 
Christophe Laumond
 Post subject:
PostPosted: Thu Oct 05, 2006 7:47 am 
Beginner
Beginner

Joined: Mon Apr 03, 2006 2:41 am
Posts: 25
Location: Mauritius
Thanks for your feedback !

It looks like you are right about the insertable=true BUT I have not been able to do it working for the following reasons :

- if I put insertable=true for both column representing the 'QuestionnaireTemplate' in 'Questionnaire' so
Code:
class Questionnaire{
...
@ManyToOne(cascade = {}, fetch = FetchType.LAZY)
@JoinColumns( {
@JoinColumn(name = "Year", referencedColumnName = "Year", unique = false, nullable = false, insertable = true, updatable = false),
@JoinColumn(name = "QuestionnaireTemplateId", referencedColumnName = "QuestionnaireTemplateId", unique = false, nullable = false, insertable = true, updatable = false) })
public Questionnairetemplate getQuestionnairetemplate()
...


I got the exception :

Code:
org.hibernate.MappingException: Repeated column in mapping for entity: accountingAudit.businessObjects.Questionnaire column: Year (should be mapped with insert="false" update="false")


This happens because the primary key and the foreigh key share a column ( the 'Year' column ).

- if I put insertable=true only on the 'QuestionnaireTemplateId' column so
Code:
class Questionnaire{
...
@ManyToOne(cascade = {}, fetch = FetchType.LAZY)
@JoinColumns( {
@JoinColumn(name = "Year", referencedColumnName = "Year", unique = false, nullable = false, insertable = false, updatable = false),
@JoinColumn(name = "QuestionnaireTemplateId", referencedColumnName = "QuestionnaireTemplateId", unique = false, nullable = false, insertable = true, updatable = false) })
public Questionnairetemplate getQuestionnairetemplate()
...


I got the exception :

Code:
org.hibernate.AnnotationException: Mixing insertable and non insertable columns in a property is not allowed: accountingAudit.businessObjects.Questionnairequestionnairetemplate


Any idea about how to solve this issue ?
Does it mean Hibernate doesn't permit to use a single column in several key ( primary or foreign ) ?

I have this in several tables in my database, this would be really disturbing for me :(

Thanks in advance for your help
Regards.
Christophe
Top
 Profile  
 
JayeshJ
 Post subject:
PostPosted: Fri Oct 06, 2006 2:25 am 
Regular
Regular

Joined: Tue May 16, 2006 3:32 am
Posts: 117
Could you include 'QuestionnaireTemplateId' as part of your primary key(at a hibernate mapping level, not in the database)?

Then in your getQuestionnaireTemplateId() method, extract the value for this from 'questionnairetemplate'.

Not the best workaround I agree, but can't think of anything better for the time being. I was a bit suprised by the second exception.
Top
 Profile  
 
Christophe Laumond
 Post subject:
PostPosted: Fri Oct 06, 2006 2:49 am 
Beginner
Beginner

Joined: Mon Apr 03, 2006 2:41 am
Posts: 25
Location: Mauritius
I have found another workaround at :

http://forum.hibernate.org/viewtopic.php?t=949738

So I add a property like this :

Code:
private int questionnaireId;
   
@Column(name = "QuestionnaireId", unique = false, nullable = false, insertable = true, updatable = true)
public int getQuestionnaireId() {
      return this.questionnaireId;
}

public void setQuestionnaireId(int questionnaireId) {
   this.questionnaireId = questionnaireId;
}


It works so I will use it, but I don't like this very much, it's not coherent with the way hibernate is working usually :(

But I need this working and since it looks like Hibernate have a limitation about that, I will use this work around.

I need to go through all my classes in order to add such properties and this is really bad :(

Anyway, Thanks for your feedback.

If anyone know a better way to handle this, please let me know.

Regards,
Christophe
Top
 Profile  
 
Christophe Laumond
 Post subject:
PostPosted: Fri Oct 06, 2006 8:32 am 
Beginner
Beginner

Joined: Mon Apr 03, 2006 2:41 am
Posts: 25
Location: Mauritius
Sorry the code in the previous code wasn't the one linked to the sample I gave in this post :

So in the class 'Questionnaire' I added a property for the missing column 'QuestionnaireTemplateId' and I use this property rather than the property of type 'QuestionnaireTemplate' when I want to insert / update.

Code:
private int questionnaireTemplateId;
   
@Column(name = "QuestionnaireTemplateId", unique = false, nullable = false, insertable = true, updatable = true)
public int getQuestionnaireTemplateId() {
      return this.questionnaireTemplateId;
}

public void setQuestionnaireTemplateId(int questionnaireTemplateId) {
   this.questionnaireTemplateId = questionnaireTemplateId;
}



Christophe
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.