Hi all,
Am facing an issue with inserting multiple foreign keys in a table,
In my case
There are 4 classes .. Person, Customer , Address and Office
Its relation is like
Quote:
Office_
|__ Set<Person>___Set<Address>
|
|__ Set<Customer>__Set<Address>
Here I have 2 different address that used in both Customer and Person,
Customer and Person are added in the Office with bi-directional mapping
While I save the Office entity I like to insert person and customer id as foreign key in address table
Table
Code:
CREATE TABLE office
(
office_id serial NOT NULL,
CONSTRAINT "oficePK" PRIMARY KEY (office_id )
)
CREATE TABLE person
(
person_id serial NOT NULL,
first_name character varying(50) NOT NULL,
last_name character varying(50),
office_id_fk bigint NOT NULL,,
CONSTRAINT person_id_pk PRIMARY KEY (person_id ),
CONSTRAINT "OFFICE_FK" FOREIGN KEY (office_id_fk)
REFERENCES office (office_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
CREATE TABLE customer
(
customer_id serial NOT NULL,
customer_name character varying,
office_id_fk bigint NOT NULL,
CONSTRAINT "customer_PK" PRIMARY KEY (customer_id ),
CONSTRAINT "OFFIC_Fk" FOREIGN KEY (office_id_fk)
REFERENCES office (office_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
CREATE TABLE address
(
address_id serial NOT NULL,
addres character varying(50),
person_id_fk bigint NOT NULL,
customer_id_fk bigint NOT NULL,
CONSTRAINT address_pk PRIMARY KEY (address_id ),
CONSTRAINT "customer_Fk" FOREIGN KEY (customer_id_fk)
REFERENCES customer (customer_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT persn_fk FOREIGN KEY (person_id_fk)
REFERENCES person (person_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
Office entityCode:
@Entity
@org.hibernate.annotations.Entity(dynamicUpdate = true)
@Table(name = "office")
public class Office {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "office_id")
private Long officeId;
@OneToMany(cascade = {CascadeType.ALL}, fetch = FetchType.EAGER, orphanRemoval = true)
@JoinColumn(name = "office_id_fk", nullable = false)
private Set<Person> person = new HashSet<Person>(0);
@OneToMany(cascade = {CascadeType.ALL}, fetch = FetchType.EAGER, orphanRemoval = true)
@JoinColumn(name = "office_id_fk", nullable = false)
private Set<Customer> customer = new HashSet<Customer>(0);
Code:
@Entity
@org.hibernate.annotations.Entity( dynamicUpdate = true)
@Table(name = "person")
public class Person {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "person_id")
private Long id;
@Column(name = "first_name")
private String firstName;
@Column(name = "last_name")
private String lastName;
@OneToMany(mappedBy="personDet", cascade = {CascadeType.ALL}, orphanRemoval = true, fetch = FetchType.EAGER)
private Set<Address> addresses = new HashSet<Address>(0);
Code:
@Entity
@org.hibernate.annotations.Entity(dynamicUpdate = true)
@Table(name = "customer")
public class Customer {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "customer_id")
private Long customerId;
@Column(name = "customer_name")
private String customerName;
@OneToMany(mappedBy="customerDet", cascade = {CascadeType.ALL}, orphanRemoval = true, fetch = FetchType.EAGER)
private Set<Address> addresses = new HashSet<Address>(0);
Code:
@Entity
@org.hibernate.annotations.Entity(dynamicUpdate = true)
@Table(name = "address")
public class Address {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "address_id")
private Long addressId;
@Column(name = "addres")
private String adres;
@ManyToOne
@JoinColumn(name = "person_id_fk", nullable = false)
private Person personDet;
@ManyToOne
@JoinColumn(name = "customer_id_fk", nullable = false)
private Customer customerDet;
Main methodCode:
session.getTransaction().begin();
Address firstAddress = new Address();
firstAddress.setAdres("SG-109");
Address secondAddress = new Address();
secondAddress.setAdres("SG-110");
Set<Address> addresses = new HashSet<Address>();
addresses.add(firstAddress);
addresses.add(secondAddress);
Customer customer = new Customer();
customer.setCustomerName("MMS stores");
customer.setAddresses(addresses);
Set<Customer> customers = new HashSet<Customer>();
customers.add(customer);
Person person = new Person();
person.setFirstName("Sam");
person.setLastName("Kumar");
person.setAddresses(addresses);
Set<Person> persons = new HashSet<Person>();
persons.add(person);
Office office = new Office();
office.setCustomer(customers);
office.setPerson(persons);
session.save(office);
session.flush();
session.getTransaction().commit();
While I execute, am getting the following error
Quote:
Hibernate: select nextval ('office_office_id_seq')
Hibernate: select nextval ('customer_customer_id_seq')
Hibernate: select nextval ('address_address_id_seq')
Hibernate: select nextval ('address_address_id_seq')
Hibernate: select nextval ('person_person_id_seq')
Hibernate: insert into office (office_id) values (?)
Hibernate: insert into customer (customer_name, office_id_fk, customer_id) values (?, ?, ?)
Hibernate: insert into address (addres, customer_id_fk, person_id_fk, address_id) values (?, ?, ?, ?)
Hibernate: insert into address (addres, customer_id_fk, person_id_fk, address_id) values (?, ?, ?, ?)
Exception in thread "main" org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:96)
..................
..................
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into address (addres, customer_id_fk, person_id_fk, address_id) values ('SG-110', '20', NULL, '154')
Am not sure the @OneToMany association in Person and Customer class are right, may i need to change any thing to get it work.
If i remove the NotNull constrain from Table it working fine, but table NotNull constraine is mandatory for me.
Thanks in advance
Login
FAQ
Search