Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Foreign key to unique field not primary key problem(ReferencedColumnName elements must be specified in each)
Foreign key to unique field not primary key problem [message #542115] Wed, 23 June 2010 22:13 Go to next message
Gabriele Muscas is currently offline 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
	**/
}


Re: Foreign key to unique field not primary key problem [message #542386 is a reply to message #542115] Thu, 24 June 2010 16:42 Go to previous messageGo to next message
Gabriele Muscas is currently offline 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?
Re: Foreign key to unique field not primary key problem [message #542435 is a reply to message #542386] Thu, 24 June 2010 19:47 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1035
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
Re: Foreign key to unique field not primary key problem [message #542442 is a reply to message #542435] Thu, 24 June 2010 20:56 Go to previous messageGo to next message
Gabriele Muscas is currently offline 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.
Re: Foreign key to unique field not primary key problem [message #543129 is a reply to message #542435] Mon, 28 June 2010 13:43 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1035
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
Re: Foreign key to unique field not primary key problem [message #545328 is a reply to message #542115] Wed, 07 July 2010 13:03 Go to previous message
Gabriele Muscas is currently offline 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
Previous Topic:Question on Native Sequence Implementation Choices..
Next Topic:JPA 2.0 and WebLogic 10
Goto Forum:
  


Current Time: Thu Oct 30 16:17:01 GMT 2014

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

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