Eclipse Community Forums: EclipseLink

Help

Home

Home » Eclipse Projects » EclipseLink » MySQL Autoinc FK

Show: Today's Messages :: Show Polls :: Message Navigator

MySQL Autoinc FK [message #389702]

Mon, 22 June 2009 07:38

Devinder Singh

Messages: 8
Registered: July 2009

Junior Member

I have two entities :

Pessoa :

// Data Defination

CREATE TABLE `pessoa` (
`ID_PESSOA` int(11) NOT NULL AUTO_INCREMENT,
`NO_PESSOA` varchar(80) NOT NULL,
PRIMARY KEY (`ID_PESSOA`)
) ENGINE=InnoDB ;

@Entity
@Table(name = "pessoa")
public class Pessoa implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "ID_PESSOA")
private Integer idPessoa;
@Basic(optional = false)
@Column(name = "NO_PESSOA")
private String noPessoa;
@OneToOne(cascade = CascadeType.ALL, mappedBy = "pessoa", fetch =
FetchType.LAZY)
private PessoaFisica pessoaFisica;

PessoaFisica :

// Data Defination

CREATE TABLE `pessoa_fisica` (
`ID_PESSOA` int(11) NOT NULL,
`NR_CPF` char(11) COLLATE utf8_unicode_ci DEFAULT NULL,
`id_ESTADO_CIVIL` smallint(6) DEFAULT NULL,
`dt_nascimento` date DEFAULT NULL,
PRIMARY KEY (`ID_PESSOA`),
KEY `NR_CPF` (`NR_CPF`),
KEY `fk_pessoa_fisica_pessoa` (`ID_PESSOA`),
CONSTRAINT `fk_pessoa_fisica_pessoa` FOREIGN KEY (`ID_PESSOA`)
REFERENCES `pessoa` (`ID_PESSOA`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB ;

@Entity
@Table(name = "pessoa_fisica")
public class PessoaFisica implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@Column(name = "ID_PESSOA")
private Integer idPessoa;
@Column(name = "NR_CPF")
private String nrCpf;
@Column(name = "id_ESTADO_CIVIL")
private Short idESTADOCIVIL;
@Column(name = "dt_nascimento")
@Temporal(TemporalType.DATE)
private Date dtNascimento;
@JoinColumn(name = "ID_PESSOA", referencedColumnName = "ID_PESSOA",
insertable = false, updatable = false)
@OneToOne(optional = false, fetch = FetchType.LAZY)
private Pessoa pessoa;

While persisting Pessoa I am getting following error :

SEVERE: >>javax.persistence.RollbackException: Exception
[EclipseLink-4002] (Eclipse Persistence Services - 1.1.0.r3634):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception:
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationE xception:
Column 'ID_PESSOA' cannot be null
Error Code: 1048
Call: INSERT IGNORE INTO pessoa_fisica (ID_PESSOA, id_ESTADO_CIVIL,
dt_nascimento, NR_CPF) VALUES (?, ?, ?, ?)
bind => [null, 1, 2009-06-16, 001.797.717-79]
Query:
InsertObjectQuery(br.com.mecsoft.imobiliario.model.PessoaFis ica[idPessoa=null])
>>org.eclipse.persistence.exceptions.DatabaseException:
Internal Exception:
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationE xception:
Column 'ID_PESSOA' cannot be null
Error Code: 1048
Call: INSERT IGNORE INTO pessoa_fisica (ID_PESSOA, id_ESTADO_CIVIL,
dt_nascimento, NR_CPF) VALUES (?, ?, ?, ?)
bind => [null, 1, 2009-06-16, 001.797.717-79]
Query:
InsertObjectQuery(br.com.mecsoft.imobiliario.model.PessoaFis ica[idPessoa=null])
[SQL: 1048, 23000]
>> com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationE xception: Column
'ID_PESSOA' cannot be null
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931 )
>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
>> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
>> at
com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerP reparedStatement.java:1169)
>> at
com.mysql.jdbc.ServerPreparedStatement.executeInternal(Serve rPreparedStatement.java:693)
>>...

Report message to a moderator

Re: MySQL Autoinc FK [message #389703 is a reply to message #389702]

Mon, 22 June 2009 07:59

Tom Eugelink

Messages: 825
Registered: July 2009

Senior Member

> CREATE TABLE `pessoa_fisica` (
> `ID_PESSOA` int(11) NOT NULL,

> @Id
> @Basic(optional = false)
> @Column(name = "ID_PESSOA")

> While persisting Pessoa I am getting following error :
>
> SEVERE: >>javax.persistence.RollbackException: Exception
> [EclipseLink-4002] (Eclipse Persistence Services - 1.1.0.r3634):
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception:
> com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationE xception:
> Column 'ID_PESSOA' cannot be null
> Error Code: 1048
> Call: INSERT IGNORE INTO pessoa_fisica (ID_PESSOA, id_ESTADO_CIVIL,
> dt_nascimento, NR_CPF) VALUES (?, ?, ?, ?)
> bind => [null, 1, 2009-06-16, 001.797.717-79]
> Query:
> 'ID_PESSOA' cannot be null

AFAIK you have defined pessoa_fisica.ID_PESSOA as "not null", but have not defined an AUTO_INCREMENT on it in the table, nor an @GeneratedValue on the Class. So it is never assigned a value.

Tom

Report message to a moderator

Re: MySQL Autoinc FK [message #389706 is a reply to message #389703]

Mon, 22 June 2009 11:33

Devinder Singh

Messages: 8
Registered: July 2009

Junior Member

Thanks for the reply Tom.

Won't the one to one relation take care of it. Since id_pesso is PK and
also a FK.

regards

Devinder

Report message to a moderator

Re: MySQL Autoinc FK [message #389707 is a reply to message #389706]

Mon, 22 June 2009 11:54

Tom Eugelink

Messages: 825
Registered: July 2009

Senior Member

> Won't the one to one relation take care of it. Since id_pesso is PK and
> also a FK.

Yes, but you somewhere have to assign a value to the field, so in the Java code you must somewhere assign the master entity to the child. You did not include the java code.

Report message to a moderator

Re: MySQL Autoinc FK [message #389708 is a reply to message #389707]

Mon, 22 June 2009 12:59

Devinder Singh

Messages: 8
Registered: July 2009

Junior Member

Hi :

Thanks once again for a quick reply.

Here is the code :

public void onClick$btnSalvar() {
PessoaDao.getInstance().create(pessoa);
}

public void create(Pessoa pessoa) {
EntityManager em = getEntityManager();
EntityTransaction tx = em.getTransaction();

try {
tx.begin();
PessoaFisica pessoaFisica = pessoa.getPessoaFisica();
em.persist(pessoa);
if (pessoaFisica != null) {
Pessoa oldPessoaOfPessoaFisica = pessoaFisica.getPessoa();
if (oldPessoaOfPessoaFisica != null) {
oldPessoaOfPessoaFisica.setPessoaFisica(null);
oldPessoaOfPessoaFisica =
em.merge(oldPessoaOfPessoaFisica);
}
pessoaFisica.setPessoa(pessoa);
pessoaFisica = em.merge(pessoaFisica);
}

em.getTransaction().commit();
} finally {
if (em != null) {
em.close();
}
}
}

regards

Devinder

Report message to a moderator

Re: MySQL Autoinc FK [message #389709 is a reply to message #389708]

Mon, 22 June 2009 13:17

Tom Eugelink

Messages: 825
Registered: July 2009

Senior Member

> public void onClick$btnSalvar() {
> PessoaDao.getInstance().create(pessoa);
> }
>
>
> public void create(Pessoa pessoa) {
> EntityManager em = getEntityManager();
> EntityTransaction tx = em.getTransaction();
>
> try {
> tx.begin();
> PessoaFisica pessoaFisica = pessoa.getPessoaFisica();
> em.persist(pessoa);
> if (pessoaFisica != null) {
> Pessoa oldPessoaOfPessoaFisica = pessoaFisica.getPessoa();
> if (oldPessoaOfPessoaFisica != null) {
> oldPessoaOfPessoaFisica.setPessoaFisica(null);
> oldPessoaOfPessoaFisica =
> em.merge(oldPessoaOfPessoaFisica);
> }
> pessoaFisica.setPessoa(pessoa);
> pessoaFisica = em.merge(pessoaFisica);
> }
>
> em.getTransaction().commit();
> } finally {
> if (em != null) {
> em.close();
> }
> }
> }

Still not enough information; with all info you provided I cannot trace from start (here) to the actual persisted variable.
In what line does the exception occur?
Where does pessoa come from?
What does the getPessoaFisica() do?

The "setPessoa" is the method (I assume) where the value is set; how does that look? Does it actually set the value or are you maybe setting a local variable instead of an instance?

Tom

Report message to a moderator

Re: MySQL Autoinc FK [message #389711 is a reply to message #389709]

Mon, 22 June 2009 14:13

Devinder Singh

Messages: 8
Registered: July 2009

Junior Member

Hi :

Thanks for the reply, Tom.

The exception occurs when we commit the transaction.

em.getTransaction().commit();

If I debug I can see the the objects having the value.

Back in the Table the id also increments.

I can mail you the debug info if that is required.

regards

Devinder

Report message to a moderator

Re: MySQL Autoinc FK [message #389712 is a reply to message #389711]

Mon, 22 June 2009 14:19

Devinder Singh

Messages: 8
Registered: July 2009

Junior Member

... and getPessoaFisica() setPessoa() are the methods in the Pessoa and
PessoaFisica respectively used for defining the one-to-one relationship.

regards

Devinder

Report message to a moderator

Re: MySQL Autoinc FK [message #389713 is a reply to message #389711]

Mon, 22 June 2009 14:24

Tom Eugelink

Messages: 825
Registered: July 2009

Senior Member

> If I debug I can see the the objects having the value.

If the objects have values, then they should be persisted. One possible cause could be that there is a another instance of the object.
Since you get the error at the commit, it is unlikely that you try to persist something before it is "complete".

> Back in the Table the id also increments.

That would be the assigning of the PK, it does not mean the FK's was assigned as well...

> I can mail you the debug info if that is required.

Maybe it is wise to first build a small running example outside the application. Just a main which mimickes the button's behavior, in that way you contain the problem. See if it occurs there as well.

Tom

Report message to a moderator

Re: MySQL Autoinc FK [message #389714 is a reply to message #389712]

Mon, 22 June 2009 14:26

Tom Eugelink

Messages: 825
Registered: July 2009

Senior Member

> .. and getPessoaFisica() setPessoa() are the methods in the Pessoa and
> PessoaFisica respectively used for defining the one-to-one relationship.

Naturally; but they can be faulty as well... It's a common mistake not to prefix the instance variable.

Report message to a moderator

Re: MySQL Autoinc FK [message #389715 is a reply to message #389713]

Mon, 22 June 2009 17:09

Devinder Singh

Messages: 8
Registered: July 2009

Junior Member

tbee wrote:

> Maybe it is wise to first build a small running example outside the
application. Just a main which mimickes the button's behavior, in that way you
contain the problem. See if it occurs there as well.

Your suggestion helped.

I found a bug em.merge(pessoaFisica) was resulting a new object which was
resulting in a null object in pessoa. So I modifed the code. Now it
doesn't give me any error but it doesn't post anything in the table either.

public void onClick$btnSalvar() {
Pessoa pessoa = new Pessoa();
pessoa.setNoPessoa("Name of Pessoa");

PessoaFisica pessoaFisica = new PessoaFisica();
pessoaFisica.setDtNascimento(new Date());
pessoaFisica.setIdESTADOCIVIL((short)1);
pessoaFisica.setNrCpf("11541660153");
pessoaFisica.setPessoa(pessoa);
pessoa.setPessoaFisica(pessoaFisica);

PessoaDao.getInstance().create(pessoa);
}

public void create(Pessoa pessoa) {
EntityManager em = getEntityManager();
EntityTransaction tx = em.getTransaction();

try {
tx.begin();
PessoaFisica pessoaFisica = pessoa.getPessoaFisica();

em.persist(pessoa);
if (pessoaFisica != null) {
Pessoa oldPessoaOfPessoaFisica = pessoaFisica.getPessoa();
if (oldPessoaOfPessoaFisica != null) {
oldPessoaOfPessoaFisica.setPessoaFisica(null);
oldPessoaOfPessoaFisica =
em.merge(oldPessoaOfPessoaFisica);
}
pessoaFisica.setPessoa(pessoa);
pessoa.setPessoaFisica(em.merge(pessoaFisica)); // the
change
}
tx.commit();
} catch (Exception ex) {
if (tx != null && tx.isActive()) {
tx.rollback();
}
} finally {
em.close();
}
}

regards

Devinder

Report message to a moderator

Re: MySQL Autoinc FK [message #389717 is a reply to message #389706]

Mon, 22 June 2009 19:53

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

No it will not. You must assign the value to your idPessoa field
yourself. If it is derived from the 1-1 pessoa, then in your setPessoa()
you should also set the idPessoa.

If you make the 1-1 writable and the Id insert/updatable=false, then it
will write from the 1-1, but you should still keep the basic in synch in
your set method.

In JPA 2.0, you can avoid the basic id and just have the 1-1 and mark it
as the Id.

---
James
http://www.nabble.com/EclipseLink---Users-f26658.html

James : Wiki : Book : Blog : Twitter

Report message to a moderator