Simple JOIN in JPA proving difficult

[Rj Jones]

Obviosuly a bit new to Hibernate, but I'm struggling if it is possible to do a simple JOIN in JPA/Hibernate ....

I have 2 tables. GAME table and GAME_DESCRIPTION TABLE for example..

CREATE TABLE "GAMES" ( "GAME_ID" NUMBER(*,0) , -- PRIMARY KEY "GAME_DESCRIPTION_ID" NUMBER(*,0), -- FOREIGN KEY "OTHER_COLUMN" NUMBER(*,0) ...... ) CREATE TABLE "GAME_DESCRIPTION" ( "GAME_DESCRIPTION_ID" NUMBER(*,0), -- PRIMARY KEY "DESCRIPTION" VARCHAR2(100 CHAR) ..... )

I want to map an Entity that contains all the columns of the GAMES table plus the game description from game_description table (so I want to join on the GAME_DESCRIPTION_ID columns from both tables). I tried to map directly to a String ...

        @OneToOne(cascade=CascadeType.ALL)
	@JoinTable(name="GAMES", joinColumns={@JoinColumn(name="GAME_ID")}, 
			inverseJoinColumns={@JoinColumn(name="GAME_ID")})
	public String getDescription() {
		return description;
	}

But I got a Entity not found java.lang.String error.

So, I then created a GAME_DESCRIPTION entity and tried joining the entities as follows : -

@Entity(name="GAME")
@Table(name="GAMES")
public class Game implements Serializable{

        private GameDetails details;
        private Integer id;        


        @Id
	@Column(name="GAME_DESCRIPTION_ID", insertable=false, updatable=false)
	public Integer getId() {
		return id;
	}
	
	@OneToOne(optional=true)
	@JoinTable(name="GAMES", joinColumns={@JoinColumn(name="GAME_DESCRIPTION_ID")}, 
			inverseJoinColumns={@JoinColumn(name="GAME_DESCRIPTION_ID")})
	public GameDetails getDetails() {
		return details;
	}

        .....
}

Now I am getting a "Repeated column in mapping for entity: com.sportech.support.jobs.model.sa.Game column: GAME_ID (should be mapped with insert="false" update="false")" error

Any ideas? Many thanks in advance.

[E Armitage]

Your @Id column mapping in the Game entity doesn't look right.
You have put column name as GAME_DESCRIPTION_ID when I think it should have been GAME_ID.

[Rj Jones]

Many thanks for reply E Armitage.

Think it was a bit of a typo sorry, but still getting the same error .....

    @Entity(name="GAME")  
    @Table(name="GAMES")  
    public class Game implements Serializable{  
      
        private GameDetails details;  
        private Integer id;
        private Integer descriptionNo;
      
        @Id  
        @Column(name="GAME_ID", insertable=false, updatable=false)  
        public Integer getId() {  
            return id;  
        }  
          
        @OneToOne(optional=true)  
        @JoinTable(name="GAMES", joinColumns={@JoinColumn(name="GAME_DESCRIPTION_ID")},   
                inverseJoinColumns={@JoinColumn(name="GAME_DESCRIPTION_ID")})  
        public GameDetails getDetails() {  
            return details;  
        }

        @Column(name="GAME_DESCRIPTION_ID", nullable=false, insertable=false, updatable=false)
        public Integer getDescriptionNo() {
		return descriptionNo;
	}
            .....  
    }  

Also here is the GameDetails entity ....

@Entity
@Table(name="GAME_DESCRIPTION")
public class GameDetails implements Serializable{

	/**
	 * 
	 */
	private static final long serialVersionUID = 5456800180678459141L;

	@Id
	@Column(name="GAME_DESCRIPTION_ID", insertable=false, updatable=false)
	private Integer id;
	
	@Column(name="DESCRIPTION")
	private String description;

	public Integer getGame_id() {
		return game_id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getDescription() {
		return description;
	}

	public void setDescription(String description) {
		this.description = description;
	}
}

Reminder of the error message - Repeated column in mapping for entity: Game column: GAME_DESCRIPTION_ID (should be mapped with insert="false" update="false")

[E Armitage]

Remove the descriptionNo and associated mapping

 @Column(name="GAME_DESCRIPTION_ID", nullable=false, insertable=false, updatable=false)  
    public Integer getDescriptionNo() {  

JPA takes away the need to use Ids in your code. Think objects. You already have the GameDetails object in Game so you get the descriptionNo through that object.

[Rj Jones]

Great reply thanks E Armitage

I think (perhaps wrongly) i was thinking more in terms of SQL than object and entities.

I need to search from the GAMES table with the GAME_DESCRIPTION_ID within a certain range (say 1-5). I have added a criteria for this search, but I would have thought the GAME entity requires the descriptionNo to run the search or is JPA clever enough to realize the mappings?

[E Armitage]

Yes JPA will handle it fine.

In this case your query will be something like

select o from Game o where o.details.id in (1,2,3,45)

assuming fetch type eager. Again here it's all about objects.