Composite primary key + fk

gugaa_df · **Joined:** Mon Jan 26, 2009 3:25 pm **Posts:** 10

Got a database legacy and I through following problem?

Got a database legacy and I through following problem?
I own a key composed and one of attributes this key composed also is a FK.
Got a database legacy and I through following problem?
I own a key composed and one of attributes this key composed also is a FK.
not happens no error when run implementation, doing a search by Find of Entity Manager Nor occurs none problem, however when I a search using Criteria of Hibernate occurs the following message: Column ambiguously difened.
Below my code:

Code:

@Embeddable
public class ConvenioTlfHistoricoPK implements Serializable{
   private long idaut;

   @Column(name="tarjeta")
   private String nroTarjeta;

   @Column(name="FECHA_NEGOCIO")
   private Date fechaNegocio;

Code:

 @Entity  
 @Table(name="convenio.tlf_historico")  
 public class ConvenioTlfHistorico extends Transacao implements Serializable {  
       
     @EmbeddedId  
     private ConvenioTlfHistoricoPK pk;  
       
     @ManyToOne   
     @JoinColumns(  
             @JoinColumn(name="TARJETA",referencedColumnName="CARTAO", insertable=false, updatable=false)  
     )  
     private Cartoes cartao;

How I do the mapping?

Hibernate version:
3

Full stack trace of any exception that occurs:
16:43:48,962 WARN [JDBCExceptionReporter] SQL Error: 918, SQLState: 42000
16:43:48,962 ERROR [JDBCExceptionReporter] ORA-00918: column ambiguously defined

16:43:48,962 ERROR [STDERR] org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at br.com.cabal.persistence.TestePersistenceBean.teste(TestePersistenceBean.java:67)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
at org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:79)
at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:191)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:95)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:77)
at org.jboss.ejb3.security.Ejb3AuthenticationInterceptor.invoke(Ejb3AuthenticationInterceptor.java:110)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:46)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessContainer.dynamicInvoke(StatelessContainer.java:304)
at org.jboss.aop.Dispatcher.invoke(Dispatcher.java:106)
at org.jboss.aspects.remoting.AOPRemotingInvocationHandler.invoke(AOPRemotingInvocationHandler.java:82)
at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:809)
at org.jboss.remoting.transport.socket.ServerThread.processInvocation(ServerThread.java:608)
at org.jboss.remoting.transport.socket.ServerThread.dorun(ServerThread.java:406)
at org.jboss.remoting.transport.socket.ServerThread.run(ServerThread.java:173)
Caused by: java.sql.SQLException: ORA-00918: column ambiguously defined

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1037)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3361)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 39 more

Name and version of the database you are using:
Oracle
The generated SQL (show_sql=true):
select
*
from
( select
this_.idaut as idaut355_12_,
this_.tarjeta as tarjeta355_12_,
this_.FECHA_NEGOCIO as FECHA3_355_12_,
this_.TARJETA as TARJETA355_12_,
... (More)

mmerder · **Posted:** Tue Jan 27, 2009 3:50 am

Try to move your ManyToOne-association into the embedded Id:

Code:

@Embeddable
public class ConvenioTlfHistoricoPK implements Serializable{
   private long idaut;

   @ManyToOne
   @JoinColumn(name="TARJETA",referencedColumnName="CARTAO")
   private Cartoes cartao; 

   @Column(name="FECHA_NEGOCIO")
   private Date fechaNegocio; 
}

gugaa_df · **Joined:** Mon Jan 26, 2009 3:25 pm **Posts:** 10

when I do this occurred the following compilation error:
Attribute "cartao" has invalid mapping type in this context

mmerder wrote:

Try to move your ManyToOne-association into the embedded Id:

Code:

@Embeddable
public class ConvenioTlfHistoricoPK implements Serializable{
   private long idaut;

   @ManyToOne
   @JoinColumn(name="TARJETA",referencedColumnName="CARTAO")
   private Cartoes cartao; 

   @Column(name="FECHA_NEGOCIO")
   private Date fechaNegocio; 
}

mmerder · **Posted:** Tue Jan 27, 2009 8:01 am

I used your classes in a test, which worked:

Code:

@Embeddable
public class ConvenioTlfHistoricoPK implements Serializable{
   private long idaut;

   @ManyToOne
   @JoinColumn(name="TARJETA",referencedColumnName="CARTAO")
   private Cartoes cartao;

   @Column(name="FECHA_NEGOCIO")
   private Date fechaNegocio;
}

@Entity 
@Table(name="tlf_historico") 
public class ConvenioTlfHistorico implements Serializable { 
      
    @EmbeddedId 
    private ConvenioTlfHistoricoPK pk; 

}


//don't know, how it looked like, so just an empty class
@Entity
public class Cartoes {
    @Id
    @Column(name="Cartao")
    private long id;
}

So, there could be some other errors in your project:
1. Is Transacao an entity? If yes, there cannot be another Id in ConvenioTlfHistorico.
2. Is the column "CARTAO" in your Cartoe-Entity the primary key, as it should be? Try to remove referencedColumnName, as hibernate should use the PK then.

gugaa_df · **Joined:** Mon Jan 26, 2009 3:25 pm **Posts:** 10

You forgot to put a part:
@ Column (name = "card")
private String nroTarjeta;

I wish I could insert the String nroTarjeta column Passes in the database, and in time to make a search I would seek to achieve the objects cards (ManyToOne) of my body

mmerder wrote:

I used your classes in a test, which worked:

Code:

@Embeddable
public class ConvenioTlfHistoricoPK implements Serializable{
   private long idaut;

   @ManyToOne
   @JoinColumn(name="TARJETA",referencedColumnName="CARTAO")
   private Cartoes cartao;

   @Column(name="FECHA_NEGOCIO")
   private Date fechaNegocio;
}

@Entity 
@Table(name="tlf_historico") 
public class ConvenioTlfHistorico implements Serializable { 
      
    @EmbeddedId 
    private ConvenioTlfHistoricoPK pk; 

}


//don't know, how it looked like, so just an empty class
@Entity
public class Cartoes {
    @Id
    @Column(name="Cartao")
    private long id;
}

So, there could be some other errors in your project:
1. Is Transacao an entity? If yes, there cannot be another Id in ConvenioTlfHistorico.
2. Is the column "CARTAO" in your Cartoe-Entity the primary key, as it should be? Try to remove referencedColumnName, as hibernate should use the PK then.

mmerder · **Posted:** Tue Jan 27, 2009 8:40 am

gugaa_df wrote:

You forgot to put a part:
@ Column (name = "card")
private String nroTarjeta;

Ok, but it also works of you insert this column.

gugaa_df wrote:

I wish I could insert the String nroTarjeta column Passes in the database, and in time to make a search I would seek to achieve the objects cards (ManyToOne) of my body

Sorry, I don't understand what you need...

gugaa_df · **Joined:** Mon Jan 26, 2009 3:25 pm **Posts:** 10

Quote:

So, there could be some other errors in your project:
1. Is Transacao an entity? If yes, there cannot be another Id in ConvenioTlfHistorico.
2. Is the column "CARTAO" in your Cartoe-Entity the primary key, as it should be? Try to remove referencedColumnName, as hibernate should use the PK then.

1 - No. Transacao is an abstract class.

gugaa_df · **Joined:** Mon Jan 26, 2009 3:25 pm **Posts:** 10

I need to INSERT A STRING IN ACHIEVING TARJETA COLUMN OF MY DATABASE.
Maybe I do not need it, but do not know how to do otherwise.
Currently I am using to do so.
convenioTlfHistorico.setNroCartao ( "6278800000126222");

mmerder · **Posted:** Tue Jan 27, 2009 9:19 am

Quote:

convenioTlfHistorico.setNroCartao ( "6278800000126222");

Ok, now you just need to save your object. You can use session.saveOrUpdate to do that.

Have you read the tutorial, which shows how to start simple hibernate applications?

gugaa_df · **Joined:** Mon Jan 26, 2009 3:25 pm **Posts:** 10

But I can not do so because it is two mappings for a column in my database.
V. I know well the Hibernate and how to CRUD.
my problem is I can not do that.
To Insert: Here i am using @Column(name="tarjeta")
private String nroTarjeta;
convenioTlfHistorico.setNroCartao ( "6278800000126222");

To search: Here i am using
@ManyToOne
@JoinColumns(
@JoinColumn(name="TARJETA",referencedColumnName="CARTAO", insertable=false, updatable=false))
private Cartoes cartao;

ConvenioTlfHistorico convenioTlfHistorico = (ConvenioTlfHistorico) persistenceFacade.executeQueryObjet
( "T ConvenioTlfHistorico from where t.pk.idaut ='3720884 ');
System.out.println (convenioTlfHistorico.getCartao (). GetNomeImpresso ());
System.out.println (convenioTlfHistorico.getCartao (). GetNumeroCartao ());

The following error occurs:
Column ambiguously defined.
understand the problem?

mmerder · **Posted:** Tue Jan 27, 2009 11:20 am

Ok, now I understand your problem (, I think ;-)). You have mapped one column to two attributes, which is normally also reported by hibernate, but in your case hibernate is case-sensitive, but the database is not.

In order to solve this problem:
1. Remove "private String nroTarjeta;". It is part of your foreign key, and thus you should set the object, not it's id.

2. Remove "insertable=false, updatable=false"

3. I guess you want to set cartao before you insert, where cartao is an object already existing in the database, right? So you can set it like this

Code:

convenioTlfHistorico.getPk().setCartao(session.get(Cartoes.class, "6278800000126222"))

or even better (as it does no extra select), but only if you are sure that the object exists in db:

Code:

convenioTlfHistorico.getPk().setCartao(session.load(Cartoes.class, "6278800000126222"))

rating is welcome ;-)

gugaa_df · **Joined:** Mon Jan 26, 2009 3:25 pm **Posts:** 10

Very Thanks for your help.

The whole problem was occurring because of the case sensitive
private String nroTarjeta I had, I was just switch to @ Column (name = "TARJETA")
private String nroTarjeta;
that everything is solved! hehehe
Thanks again.