Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » MySQL Autoinc FK
MySQL Autoinc FK [message #389702] Mon, 22 June 2009 07:38 Go to next message
Devinder Singh is currently offline 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)
>>...
Re: MySQL Autoinc FK [message #389703 is a reply to message #389702] Mon, 22 June 2009 07:59 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 807
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
Re: MySQL Autoinc FK [message #389706 is a reply to message #389703] Mon, 22 June 2009 11:33 Go to previous messageGo to next message
Devinder Singh is currently offline 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
Re: MySQL Autoinc FK [message #389707 is a reply to message #389706] Mon, 22 June 2009 11:54 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 807
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.
Re: MySQL Autoinc FK [message #389708 is a reply to message #389707] Mon, 22 June 2009 12:59 Go to previous messageGo to next message
Devinder Singh is currently offline 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
Re: MySQL Autoinc FK [message #389709 is a reply to message #389708] Mon, 22 June 2009 13:17 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 807
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
Re: MySQL Autoinc FK [message #389711 is a reply to message #389709] Mon, 22 June 2009 14:13 Go to previous messageGo to next message
Devinder Singh is currently offline 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
Re: MySQL Autoinc FK [message #389712 is a reply to message #389711] Mon, 22 June 2009 14:19 Go to previous messageGo to next message
Devinder Singh is currently offline 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
Re: MySQL Autoinc FK [message #389713 is a reply to message #389711] Mon, 22 June 2009 14:24 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 807
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
Re: MySQL Autoinc FK [message #389714 is a reply to message #389712] Mon, 22 June 2009 14:26 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 807
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.
Re: MySQL Autoinc FK [message #389715 is a reply to message #389713] Mon, 22 June 2009 17:09 Go to previous messageGo to next message
Devinder Singh is currently offline 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
Re: MySQL Autoinc FK [message #389717 is a reply to message #389706] Mon, 22 June 2009 19:53 Go to previous messageGo to next message
James Sutherland is currently offline 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
Re: MySQL Autoinc FK [message #389719 is a reply to message #389717] Mon, 22 June 2009 20:11 Go to previous messageGo to next message
Devinder Singh is currently offline Devinder Singh
Messages: 8
Registered: July 2009
Junior Member
Thanks for the reply James.

But idPessao, doesn't have the value till the time I commit the
transaction.


regards

Devinder
Re: MySQL Autoinc FK [message #389723 is a reply to message #389719] Tue, 23 June 2009 18:49 Go to previous message
Devinder Singh is currently offline Devinder Singh
Messages: 8
Registered: July 2009
Junior Member
Thanks for the reply James, I managed to solved the problem

regards

Devinder
Previous Topic:toplink console, JMX
Next Topic:upgrading to 1.1.1.: IdentityWeakHashMap does not support null as a key or value
Goto Forum:
  


Current Time: Sat Sep 20 18:15:08 GMT 2014

Powered by FUDForum. Page generated in 0.02149 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software