one-to-many persistence, ConstraintViolationException

[Jake Vang <va...@googlemail.com>]

i am following the example at
http://struts.apache.org/2.1.8.1/docs/struts-2-spring-2-jpa-ajax.html.
in this example, a very simple persistence is given (of which i am
thankful). but, what i am trying to do is slightly more complicated
with persistence. i need to persist a one-to-many relationship. when i
attempt to do so, i keep getting the following error.

Caused by: java.sql.BatchUpdateException: Cannot add or update a child
row: a foreign key constraint fails (`demo`.`employee`, CONSTRAINT
`employee_ibfk_1` FOREIGN KEY (`companyId`) REFERENCES `company`
(`id`))

what am i doing wrong?

i am using mysql, and these are my DDLs.
create table company (
	name varchar(100) unique,
	id mediumint not null auto_increment,
	primary key(id),
	index using btree(id)
);

create table employee(
	companyId mediumint,
	firstName varchar(100),
	lastName varchar(100),
	id mediumint not null auto_increment,
	primary key(id),
	index using btree(id),
	foreign key (companyId) references company(id)
);

these are my classes for Company and Employee.

@Entity
@Table(name="company")
public class Company {

	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	private int id;
	private String name;
	@OneToMany(mappedBy="companyId",
			cascade={CascadeType.REMOVE, CascadeType.PERSIST},
			targetEntity=Employee.class,
			fetch=FetchType.EAGER)
	@JoinColumn(name="companyId")
	@ForeignKey(name="id",inverseName="companyId")
	private List<Employee> employees;
	
	public Company() { }
	
	public Company(String name) {
		this.name = name;
	}
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	public void addEmployee(Employee employee) {
		List<Employee> employees = getEmployees();
		employees.add(employee);
		
		employee.setCompanyId(getId());
	}

	public List<Employee> getEmployees() {
		if(null == employees) {
			employees = new ArrayList<Employee>();
		}
		return employees;
	}

	public void setEmployees(List<Employee> employees) {
		this.employees = employees;
	}
	
}

@Entity
@Table(name="employee")
public class Employee {

	private int companyId;
	@Id
	private int id;
	private String firstName;
	private String lastName;
	
	public Employee() { }
	
	public Employee(String firstName, String lastName) {
		this.firstName = firstName;
		this.lastName = lastName;
	}

	public int getCompanyId() {
		return companyId;
	}

	public void setCompanyId(int companyId) {
		this.companyId = companyId;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
}

my service implementation that keeps failing to persist is as follows.
any help would be greatly appreciated.

@Transactional
public class CompanyDao {
	
	@PersistenceContext
	protected EntityManager entityManager;
	
	public CompanyDao() { }
	
	public CompanyDao(EntityManager entityManager) {
		this.entityManager = entityManager;
	}

	public void create(Company company) {
		EntityManager em = getEntityManager();
		em.persist(company);
	}
	
	public Company read(int id) {
		EntityManager em = getEntityManager();
		Company company = em.find(Company.class, id);
		return company;
	}
	
	public void update(Company company) {
		EntityManager em = getEntityManager();
		em.merge(company);
	}
	
	public void delete(int id) {
		Company company = read(id);
		if(null != company) {
			EntityManager em = getEntityManager();
			em.remove(company);
		}
	}

	public EntityManager getEntityManager() {
		return entityManager;
	}

	public void setEntityManager(EntityManager entityManager) {
		this.entityManager = entityManager;
	}
}

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org

Re: one-to-many persistence, ConstraintViolationException

[Ken <ke...@aerose.com>]

Thank you for sharing,

Here is something you might find useful:

I don't know if you know about MySQL workbench?  It will help you define
your database schema.  It gives a nice graphical layout, it's free...
I'm using Ubuntu linux 10.04 and there was some issue, with it where I
needed to download 5.2.22 RC then everything was perfect.  It is
important to understand foreign key relationships when working with any
DB so read the documentation or it will cause issues later.

Another thing, I use Netbeans... Your probably using Eclipse, in any
case there are tools to automatically map your database tables for
you.  
In Netbeans:
You create a new web project, then you say you want to use the Hibernate
framework.
Then you right click your project:
New->Hibernate Configuration Wizard (then follow the wizard)
Then again right click the project:
New->Hibernate Reverse Engineering Wizard (then follow the wizard)
Then again right click the project:
New->Hibernate Mapping Files and POJO from Database Wizard (this is the
cool part, you get to select the tables and it will generate the XML and
the POJO's for you)

I know it generates xml... but everything will work and you can use it
as a great starting place.  (The same tools exist for Eclipse, never
used them, they come out of the box with Netbeans).

Re: one-to-many persistence, ConstraintViolationException

[Jake Vang <va...@googlemail.com>]

ken,

thanks for responding. i got things to work, but i haven't figured out
really why it's working (on a conceptual, philosophical level). here's
what i did to the code above.

1. for the Company class, i removed the @ForeignKey annotation
2. for the Employee class, i removed the companyId field. instead, i
used added a Company field.
@ManyToOne
@JoinColumn(name="companyId")
private Company company;
3. for the Employee class, i added
@GeneratedValue(strategy=GenerationType.AUTO) to the id field

this effectively makes the relationship a bidirectional and 1-N
multiplicity for Company and N-1 multiplicity for Employee.

one additional thing is that in my code, when you add an Employee to a
Company, you have to add the Company to the Employee too. i guess this
is what the official JEE 5 tutorial was saying when it said you have
to manage the relationship yourself (i had no idea what they were
talking about).

this JPA approach is quite strange. i don't find it easy at all or
intuitive to say the least. even though the JEE 5 and JEE 6 tutorials
tell you (in a poor way) about multiplicities and directions, their
examples are taken out-of-context.

right now, i am having a fun time trying to persist a 1-1
unidirectional relationship. i thought it would be easier, but it is
not. what complicates things is that there are many ways to accomplish
1-1 unidirectional relationships (using annotations and the DDL of the
actual database). what makes me wildly insane is that a search for
"jpa tutorial" returns pages that are too thin or too heavy weight. i
wish there was a tutorial on how to get JPA + Hibernate + Spring +
Struts2 all working on simple (very simple) examples in different
combinations of multiplicity and direction (regarding data
persistence).

by the way, i got a great response from another post regarding how to
do integration unit testing with all these guys (JPA, Hibernate,
Spring + Struts2) so now at least i can test data persistence.


On Tue, Jun 22, 2010 at 1:38 PM, Ken <ke...@aerose.com> wrote:
> I'm sorry I quickly scanned this and didn't read it in full...
>
> Here is my 2 cents.
>
> "InnoDB rejects any INSERT or UPDATE operation that attempts to create a
> foreign key value in a child table if there is no a matching candidate
> key value in the parent table." So the parent has a valid entry before a
> row is created in the child?
>
> I noticed in defining the mysql table you use:
>    foreign key (companyId) references company(id)
>
> Which has no ON DELETE or ON UPDATE clauses... so of course the above
> becomes:
>    FOREIGN KEY (jcompanyid) REFERENCES company(id)
>        ON DELETE RESTRICT
>        ON UPDATE RESTRICT
>
> This would prevent you from deleting the parent or changing the foreign
> key in the parent if there is a dependant child.  I bring this up
> because I noticed the use of CascadeType's.
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org

Re: one-to-many persistence, ConstraintViolationException

[Ken <ke...@aerose.com>]

I'm sorry I quickly scanned this and didn't read it in full... 

Here is my 2 cents.

"InnoDB rejects any INSERT or UPDATE operation that attempts to create a
foreign key value in a child table if there is no a matching candidate
key value in the parent table." So the parent has a valid entry before a
row is created in the child?

I noticed in defining the mysql table you use:
    foreign key (companyId) references company(id)

Which has no ON DELETE or ON UPDATE clauses... so of course the above
becomes:
    FOREIGN KEY (jcompanyid) REFERENCES company(id)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT

This would prevent you from deleting the parent or changing the foreign
key in the parent if there is a dependant child.  I bring this up
because I noticed the use of CascadeType's.