4 Replies Latest reply on Nov 13, 2006 7:40 PM by Serge Emmanuel Pagop

    Cannot join @OneToOne on non-PK fields?

    tsar_bomba Newbie

      I have a very simple case where I'm joining two entities w/ @OneToOne on fields of which neither side are primary keys...I have no choice, it's a legacy database and it's ugly.

      I'm porting this code from Glassfish to JBoss...the kicker is; it works on Glassfish but breaks when running it on JBoss, obviously the question is how Hibernate handles this relationship vs. Toplink in Glassfish.

      Here are the entities w/ the relationships:

      @Entity
      @Table(name="chg")
      public class ChangeOrder implements Serializable
      {
       @Id
       @Column(name="id", nullable=false)
       private Integer id;
      
       @Column(name="status", insertable=false, updatable=false)
       private String status;
      
       @OneToOne(fetch=FetchType.LAZY)
       @JoinColumn(name="status", referencedColumnName="code")
       private ChangeOrderStatus changeOrderStatus;
      ................................
      }
      
      @Entity
      @Table(name="chgstat")
      public class ChangeOrderStatus implements Serializable
      {
       @Id
       @Column(name="id", nullable=false)
       private int id;
      
       @Column(name="code", nullable=false, insertable=false, updatable=false)
       private String code;
      
       @OneToOne(mappedBy="changeOrderStatus", fetch=FetchType.LAZY)
       private ChangeOrder changeOrder;
      .........................
      }
      


      Here is how I'm querying these entities:

       public List<ChangeOrder> getChangeOrders(Integer orgId)
       {
       String query = "select cho from ChangeOrder cho " +
       "left join fetch cho.changeOrderStatus " +
       "where cho.organizationId = :orgId " +
       "and cho.activeFlag = 1 " +
       "order by cho.priority desc";
      
       Query q = this.em.createQuery(query);
       q.setParameter("orgId", orgId);
      
       return q.getResultList();
       }
      


      Here is the SQL that is generated...which mysteriously, runs just fine against the database when executed manually!

      Hibernate:
       /* select
       cho
       from
       ChangeOrder cho
       left join
       fetch cho.changeOrderStatus
       where
       cho.organizationId = :orgId
       and cho.activeFlag = 1
       order by
       cho.priority desc */
       select
       ...lots of fields here...
       from
       chg changeorde0_
       left outer join
       chgstat changeorde1_
       on changeorde0_.status=changeorde1_.code 'this is correct!!
       where
       changeorde0_.organization=? 'I pass in this parameter...
       and changeorde0_.active_flag=1
       order by
       changeorde0_.priority desc
      Hibernate:
       /* load com.myapp.model.ChangeOrder */
       select
       ...lots of fields here...
       from
       chg changeorde0_
       where
       changeorde0_.status=?
      


      ...also notice that even though I've specified LAZY loading on the owning side of the relationship...it is getting loaded eagerly...I would not have expected to see that second query being generated.

      Here is the exception I get:

      javax.ejb.EJBException
      .......................
      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Syntax error converting the varchar value 'CL' to a column of data type int.
       at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
       at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.buildNextRowset(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
       at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
       at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
       at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)
       at org.hibernate.loader.Loader.doQuery(Loader.java:662)
       at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
       at org.hibernate.loader.Loader.loadEntity(Loader.java:1784)
       ... 90 more
      
      


      'CL' is a status and would be found in ChangeOrder.status, so I'm unsure where this issue is happening.

      I'm pretty sure something is going wrong w/ the second query...which I hoped to eliminate by setting the fetch type to 'lazy'. I can't find the persistence.xml property to show the value being bound to the query so I'll just have to guess that it's trying to use an incorrect type, maybe the primary key from the other class?

      I even tried flipping the relationship around so that ChangeOrder was the owning side, just to see what would happen, and predictably it generates incorrect SQL and throws an exception:

      ...................
       from
       chg changeorde0_
       left outer join
       chgstat changeorde1_
       on changeorde0_.id=changeorde1_.code 'wrong fields joined!
       where
       changeorde0_.organization=?
       and changeorde0_.active_flag=1
       order by
       changeorde0_.priority desc
      


      Am I doing something wrong? Like I said, this ran fine on Glassfish w/ Toplink but we'd like to move this app to a JBoss server in-house...we need this type of relationship to work.

      I'd even be happy if it were uni-directional (though I can't change the tables)...I don't need the ChangeOrderStatus entity to have a reference to the ChangeOrder - that will never be used.

      All help & suggestions are much appreciated, thanks.


        • 1. Re: Cannot join @OneToOne on non-PK fields?
          tsar_bomba Newbie

          boop! Sorry to bump but it's been a few days...any ideas?

          • 2. Re: Cannot join @OneToOne on non-PK fields?
            Serge Emmanuel Pagop Newbie

            use the referencedColumnName( ) attribute of @JoinColumn if you want to map a field other than a primary-key column, but this field must be unique

            • 3. Re: Cannot join @OneToOne on non-PK fields?
              tsar_bomba Newbie

               

              "sisepago" wrote:
              use the referencedColumnName( ) attribute of @JoinColumn if you want to map a field other than a primary-key column, but this field must be unique


              I'm guessing you didn't actually read my post. I *am* doing that and it doesn't work correctly. Have a look at the entities I showed in my original post.

              Thanks anyhow.

              • 4. Re: Cannot join @OneToOne on non-PK fields?
                Serge Emmanuel Pagop Newbie

                # @JoinColumn(name="status", referencedColumnName="code")
                -----
                please read the jpa spec to understand how to use @JoinColumn: you can only use one of this attributes not both, because if you use both, name="status" is used as referenced key

                # Lazy fetching has no problem, because I also use it:

                @javax.persistence.Entity
                public class Customer implements java.io.Serializable{
                 /** The serialVersionUID */
                 private static final long serialVersionUID = 1L;
                 private long id;
                 private String lastName;
                 private String firstName;
                 private Address address;
                
                 #1 can be used
                 @javax.persistence.OneToOne(cascade={CascadeType.ALL}, fetch=FetchType.LAZY)
                 @javax.persistence.JoinColumn(name="ADDRESSID")
                
                 #2 can also be used
                 @javax.persistence.OneToOne(cascade={CascadeType.ALL}, fetch=FetchType.LAZY)
                 @javax.persistence.JoinColumn(referencedColumnName="ZIP")
                 public Address getAddress() {return this.address;}
                 public void setAddress(Address address){this.address = address;}
                
                }
                
                


                and in my client class LEFT JOIN FETCH syntax to fetch customer with lazy address object

                public List<Customer> getCustomersLazyUsing(long cId) {
                 String query = "select c from Customer c " +
                 "left join fetch c.address" +
                 " where c.id = :cId ";


                and all work perfect