Eclipse Community Forums: EclipseLink » Foreign key to unique field not primary key problem

Home » Eclipse Projects » EclipseLink » Foreign key to unique field not primary key problem(ReferencedColumnName elements must be specified in each)

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

Foreign key to unique field not primary key problem [message #542115]

Wed, 23 June 2010 22:13

Gabriele Muscas

Messages: 8
Registered: June 2010

Junior Member

Hi all, i have a problem with annotations in a foreign key to unique field not primary key.

With this simple code:

EntityManager em = Persistence.createEntityManagerFactory("TestJpaPU").createEntityManager();
Query query = em.createQuery("SELECT c FROM Contatto c");
List<Contatto> contatti = query.getResultList();
for(Contatto contatto:contatti){
	out.println(contatto.getNome());
}

I get this error:

Quote:

Exception Description: The @JoinColumns on the annotated element [field contatto] from the entity class [class com.testjpa.persistence.Recapito] is incomplete. When the source entity class uses a composite primary key, a @JoinColumn must be specified for each join column using the @JoinColumns. Both the name and the referencedColumnName elements must be specified in each such @JoinColumn.

Am I wrong or annotated classes have some problems?
(Same behavior with toplink and eclipselink)

Thanks!
Gabriele

Here my two tables:

CREATE TABLE contatto
(
  id serial NOT NULL,
  tipo_contatto character varying(50) NOT NULL,
  nome character varying(100),
  CONSTRAINT contatto_pkey PRIMARY KEY (id, tipo_contatto),
  CONSTRAINT contatto_id_key UNIQUE (id)
)

CREATE TABLE recapito
(
  id serial NOT NULL,
  recapito character varying(300) NOT NULL,
  id_contatto integer NOT NULL,
  CONSTRAINT recapito_pkey PRIMARY KEY (id),
  CONSTRAINT recapito_id_contatto_fkey FOREIGN KEY (id_contatto)
      REFERENCES contatto (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

Here my three generated classes:

@Entity
@Table(name = "contatto", catalog = "jpatest", uniqueConstraints = {
    @UniqueConstraint(columnNames = {"id"})})
@NamedQueries({
    @NamedQuery(name = "Contatto.findAll", query = "SELECT c FROM Contatto c"),
    @NamedQuery(name = "Contatto.findById", query = "SELECT c FROM Contatto c WHERE c.contattoPK.id = :id"),
    @NamedQuery(name = "Contatto.findByTipoContatto", query = "SELECT c FROM Contatto c WHERE c.contattoPK.tipoContatto = :tipoContatto"),
    @NamedQuery(name = "Contatto.findByNome", query = "SELECT c FROM Contatto c WHERE c.nome = :nome")})
public class Contatto implements Serializable {
    private static final long serialVersionUID = 1L;
    @EmbeddedId
    protected ContattoPK contattoPK;
    @Column(name = "nome", length = 100)
    private String nome;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "contatto")
    private List<Recapito> recapitoList;

	/**
	*Constructor, Getters, Setters, hashCode, equals
	**/
}

@Entity
@Table(name = "recapito", catalog = "jpatest", schema = "jpaschema")
@NamedQueries({
    @NamedQuery(name = "Recapito.findAll", query = "SELECT r FROM Recapito r"),
    @NamedQuery(name = "Recapito.findById", query = "SELECT r FROM Recapito r WHERE r.id = :id"),
    @NamedQuery(name = "Recapito.findByRecapito", query = "SELECT r FROM Recapito r WHERE r.recapito = :recapito")})
public class Recapito implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "id", nullable = false)
    private Integer id;
    @Basic(optional = false)
    @Column(name = "recapito", nullable = false, length = 450)
    private String recapito;
    @JoinColumn(name = "id_contatto", referencedColumnName = "id", nullable = false)
    @ManyToOne(optional = false)
    private Contatto contatto;

	/**
	*Constructor, Getters, Setters, hashCode, equals
	**/
}

@Embeddable
public class ContattoPK implements Serializable {
    @Basic(optional = false)
    @Column(name = "id", nullable = false)
    private int id;
    @Basic(optional = false)
    @Column(name = "tipo_contatto", nullable = false, length = 50)
    private String tipoContatto;

    /**
	*Constructor, Getters, Setters, hashCode, equals
	**/
}

Report message to a moderator

Re: Foreign key to unique field not primary key problem [message #542386 is a reply to message #542115]

Thu, 24 June 2010 16:42

Gabriele Muscas

Messages: 8
Registered: June 2010

Junior Member

That tables are a test of problem i have on a legacy db. So i can't change the foreign key.

how can I do?

Report message to a moderator

Re: Foreign key to unique field not primary key problem [message #542435 is a reply to message #542386]

Thu, 24 June 2010 19:47

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

While you cannot change the tables, is there any reason you can't set the unique key in the object to be the primary key instead of the field you are using in the database? The primary key in the JPA entity does not need to be the database primary key, as long as the key used is unique.

If this is not an option, I do not believe there is away you can map the relation in JPA. Instead, you will need to mark it as transient or as a basic mapping, and then add the mapping using a customizer and EclipseLink api.

Best Regards,
Chris

Report message to a moderator

Re: Foreign key to unique field not primary key problem [message #542442 is a reply to message #542435]

Thu, 24 June 2010 20:56

Gabriele Muscas

Messages: 8
Registered: June 2010

Junior Member

Hello, meanwhile, thanks for the help. I did not know the DescriptorCustomizer, now I have a lot to learn.

Unfortunately, the db will be used by a legacy application too, so I can not change it.

Now I created the class RecapitoCustomizer:

public class RecapitoCustomizer implements DescriptorCustomizer {
    @Override
    public void customize(ClassDescriptor descriptor) throws Exception {
        OneToManyMapping mapping = (OneToManyMapping)descriptor.getMappingForAttributeName("contatto");
        ExpressionBuilder builder = new ExpressionBuilder();
        Expression expression = builder.getField("CONTATTO.ID").equal(builder.getParameter("RECAPITO.ID_CONTATTO"));
        mapping.setSelectionCriteria(expression);

    }
}

I change my Recapito class in this way:

@Entity
@Table(name = "recapito", catalog = "jpatest", schema = "jpaschema")
@Customizer(com.testjpa.persistence.customizers.RecapitoCustomizer.class)
@NamedQueries({
    @NamedQuery(name = "Recapito.findAll", query = "SELECT r FROM Recapito r"),
    @NamedQuery(name = "Recapito.findById", query = "SELECT r FROM Recapito r WHERE r.id = :id"),
    @NamedQuery(name = "Recapito.findByRecapito", query = "SELECT r FROM Recapito r WHERE r.recapito = :recapito")})
public class Recapito implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "id", nullable = false, insertable=false, updatable=false)
    private Integer id;
    @Basic(optional = false)
    @Column(name = "recapito", nullable = false, length = 450)
    private String recapito;
    @ManyToOne(optional = false)
    private Contatto contatto;

    /**
	*Constructor, Getters, Setters, hashCode, equals
	**/
}

And there are no more problems with the old code. I can list my contact.

Now i have some problem when i try to list the address list.
This code:

Query query = em.createQuery("SELECT c FROM Contatto c");
            List<Contatto> contatti = query.getResultList();
            List<Recapito> recapiti;
            for(Contatto contatto:contatti){
                out.println(contatto.getNome());
                recapiti = contatto.getRecapitoList();
                for(Recapito recapito:recapiti){
                    out.println(recapito.getRecapito()+"<br/>");
                }
                out.println("<hr/>");
            }

generates this error:

Internal Exception: org.postgresql.util.PSQLException: ERROR: column "tipo_contatto" does not exist
Error Code: 0
Call: SELECT id, recapito, tipo_contatto FROM jpatest.jpaschema.recapito WHERE ((tipo_contatto = ?) AND (id = ?))
        bind => [agente, 1]
Query: ReadAllQuery(name="recapitoList" referenceClass=Recapito sql="SELECT id, recapito, tipo_contatto FROM jpatest.jpaschema.recapito WHERE ((tipo_contatto = ?) AND (id = ?))")

As I said, I still have much to learn. Smile

Thanks!
Gabriele.

Report message to a moderator

Re: Foreign key to unique field not primary key problem [message #543129 is a reply to message #542435]

Mon, 28 June 2010 13:43

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

Hello,

The tipo_contatto field I assume is due to the default field being used for the ManyToOne contatto relationship. Instead of setting the selection critieria on the mapping, you should instead replace the join fields used so that it uses your foreignkey->unique key relationship. The selection critieria is only used when reading in the referenced object, not when inserting/updating/selecting the Recapito entity, and if the relationship is set properly, it will be set automatically.

See the addForeignKeyField() method. You may need to clear the getForeignKeyFields(), getSourceToTargetKeyFields(), and getTargetToSourceKeyFields() collections first to ensure references to the defaulted id field are removed.

Best Regards,
Chris

Report message to a moderator

Re: Foreign key to unique field not primary key problem [message #545328 is a reply to message #542115]

Wed, 07 July 2010 13:03

Gabriele Muscas

Messages: 8
Registered: June 2010

Junior Member

Chris,
I read more carefully your initial response.
It was so simple!

I can change the primary key in my object without altering the DB.

Thank you!

Gabriele

Report message to a moderator

Previous Topic:	Question on Native Sequence Implementation Choices..
Next Topic:	JPA 2.0 and WebLogic 10

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 20:31:12 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter