Entity manager: Hibernate generating wrong SQL (composite key of composite keys)

[Karsten Wutzke]

Hello,

I'm using a table that has four columns as primary key, with pairs of two columns each referencing another table via composite FK (MySQL 5.1). Both references are identifying relationships, that is they are PK as well as FK in the PlayerStats table. The design can be viewed here:
http://www.kawoolutions.com/media/hibernate-wrong-sql-playerstats.jpg

Here's the stack trace (root cause):

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'playerstat0_.score' in 'field list'
	sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
	com.mysql.jdbc.Util.getInstance(Util.java:384)
	com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
	com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
	com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
	com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
	com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
	com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
	com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
	com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
	org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
	org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
	org.hibernate.loader.Loader.doQuery(Loader.java:802)
	org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
	org.hibernate.loader.Loader.loadEntity(Loader.java:2037)
	org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:86)
	org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:76)
	org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3268)
	org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:496)
	org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:477)
	org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:227)
	org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:285)
	org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:152)
	org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:1089)
	org.hibernate.impl.SessionImpl.get(SessionImpl.java:1004)
	org.hibernate.impl.SessionImpl.get(SessionImpl.java:997)
	org.hibernate.ejb.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:614)
	org.hibernate.ejb.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:589)
	com.kawoolutions.bbstats.servlet.BasketballStatsServlet.doGet(BasketballStatsServlet.java:103)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

The generated SQL is:

select
 playerstat0_.score as score497_6_,
 playerstat0_.teamMember as teamMember497_6_,
 playerstat0_.score_game_id as score10_497_6_,
 playerstat0_.score_is_home as score11_497_6_,
 playerstat0_.teamMember_player_id as teamMember12_497_6_,
 playerstat0_.teamMember_roster_id as teamMember13_497_6_,
 playerstat0_.roster_id as roster3_497_6_,
 playerstat0_.player_id as player4_497_6_,
 playerstat0_.is_home as is5_497_6_,
 playerstat0_.game_id as game6_497_6_,
 playerstat0_.is_starter as is7_497_6_,
 playerstat0_.jersey_nbr as jersey8_497_6_,
 playerstat0_.pf as pf497_6_,
 score1_.game_id as game1_493_0_,
 score1_.is_home as is2_493_0_,
 score1_.final_score as final3_493_0_,
 score1_.roster_id as roster4_493_0_,
 game2_.id as id504_1_,
 game2_.actual_tipoff as actual2_504_1_,
 game2_.arena_id as arena8_504_1_,
 game2_.attendance as attendance504_1_,
 game2_.ref_club_id as ref9_504_1_,
 game2_.ordinal_nbr as ordinal10_504_1_,
 game2_.round_id as round11_504_1_,
 game2_.matchday_nbr as matchday4_504_1_,
 game2_.official_nbr as official5_504_1_,
 game2_.recap as recap504_1_,
 game2_.scheduled_tipoff as scheduled7_504_1_,
 roster3_.id as id483_2_,
 roster3_.jersey_color_name as jersey3_483_2_,
 roster3_.image_path as image2_483_2_,
 roster3_.season_start_year as season4_483_2_,
 roster3_.club_id as club5_483_2_,
 roster3_.ordinal_nbr as ordinal6_483_2_,
 roster3_.teamtype_code as teamtype7_483_2_,
 teammember4_.player_id as player1_492_3_,
 teammember4_.roster_id as roster2_492_3_,
 teammember4_.image_path as image3_492_3_,
 teammember4_.position as position492_3_,
 player5_.id as id486_4_,
 player5_1_.birth_date as birth1_507_4_,
 player5_1_.first_name as first2_507_4_,
 player5_1_.gender as gender507_4_,
 player5_1_.is_incognito as is4_507_4_,
 player5_1_.last_name as last5_507_4_,
 player5_1_.middle_name as middle6_507_4_,
 player5_.registration_nbr as registra1_508_4_,
 roster6_.id as id483_5_,
 roster6_.jersey_color_name as jersey3_483_5_,
 roster6_.image_path as image2_483_5_,
 roster6_.season_start_year as season4_483_5_,
 roster6_.club_id as club5_483_5_,
 roster6_.ordinal_nbr as ordinal6_483_5_,
 roster6_.teamtype_code as teamtype7_483_5_
from PlayerStats playerstat0_
 left outer join Scores score1_ on playerstat0_.score_game_id=score1_.game_id and playerstat0_.score_is_home=score1_.is_home
 left outer join Games game2_ on score1_.game_id=game2_.id
 left outer join Rosters roster3_ on score1_.roster_id=roster3_.id
 left outer join TeamMembers teammember4_ on playerstat0_.teamMember_player_id=teammember4_.player_id and playerstat0_.teamMember_roster_id=teammember4_.roster_id
 left outer join Players player5_ on teammember4_.player_id=player5_.id
 left outer join Persons player5_1_ on player5_.id=player5_1_.id
 left outer join Contacts player5_2_ on player5_.id=player5_2_.id
 left outer join Rosters roster6_ on teammember4_.roster_id=roster6_.id
where playerstat0_.score=? and playerstat0_.teamMember=?

As you can see from the generated SQL, the first two columns in the SELECT are the names of the two composite primary key classes: "playerstat0_.score" and "playerstat0_.teamMember". They're also used in the WHERE clause for whatever reason. The code is obviously wrong. Can't say whether there's something wrong in my code or Hibernate.

Here's the Java code for the PlayerStat and PlayerStatId classes:

@Entity
@Table(name = "PlayerStats")
@IdClass(value = PlayerStatId.class)
public class PlayerStat implements Serializable
{
	@Id
	@Column(name = "game_id", insertable = false, updatable = false)
	private Integer gameId;

	@Id
	@Column(name = "is_home", insertable = false, updatable = false)
	private Boolean isHome;

	@Id
	@Column(name = "player_id", insertable = false, updatable = false)
	private Integer playerId;

	@Id
	@Column(name = "roster_id", insertable = false, updatable = false)
	private Integer rosterId;

	@Column(name = "jersey_nbr")
	private Integer jerseyNbr = null;

	@Column(name = "is_starter")
	private Boolean isStarter = null;

	@Column(name = "pf")
	private Integer pf;

	@ManyToOne
	@PrimaryKeyJoinColumns(value = {@PrimaryKeyJoinColumn(name = "game_id", referencedColumnName = "game_id"), @PrimaryKeyJoinColumn(name = "is_home", referencedColumnName = "is_home")})
	private Score score = null;

	@ManyToOne
	@PrimaryKeyJoinColumns(value = {@PrimaryKeyJoinColumn(name = "player_id", referencedColumnName = "player_id"), @PrimaryKeyJoinColumn(name = "roster_id", referencedColumnName = "roster_id")})
	private TeamMember teamMember = null;

	public PlayerStat()
	{
	}

	public Integer getGameId()
	{
		return gameId;
	}

	public void setGameId(Integer gameId)
	{
		if ( gameId == null )
		{
			throw new NullArgumentException("gameId is null!");
		}

		this.gameId = gameId;
	}

	public Boolean isHome()
	{
		return isHome;
	}

	public void setHome(Boolean isHome)
	{
		if ( isHome == null )
		{
			throw new NullArgumentException("isHome is null!");
		}

		this.isHome = isHome;
	}

	public Integer getPlayerId()
	{
		return playerId;
	}

	public void setPlayerId(Integer playerId)
	{
		if ( playerId == null )
		{
			throw new NullArgumentException("playerId is null!");
		}

		this.playerId = playerId;
	}

	public Integer getRosterId()
	{
		return rosterId;
	}

	public void setRosterId(Integer rosterId)
	{
		if ( rosterId == null )
		{
			throw new NullArgumentException("rosterId is null!");
		}

		this.rosterId = rosterId;
	}

	public Integer getJerseyNbr()
	{
		return jerseyNbr;
	}

	public void setJerseyNbr(Integer jerseyNbr)
	{
		this.jerseyNbr = jerseyNbr;
	}

	public Boolean isStarter()
	{
		return isStarter;
	}

	public void setStarter(Boolean isStarter)
	{
		this.isStarter = isStarter;
	}

	public Integer getPf()
	{
		return pf;
	}

	public void setPf(Integer pf)
	{
		if ( pf == null )
		{
			throw new NullArgumentException("pf is null!");
		}

		this.pf = pf;
	}

	public Score getScore()
	{
		return score;
	}

	public void setScore(Score score)
	{
		if ( score == null )
		{
			throw new NullArgumentException("score is null!");
		}

		this.score = score;
	}

	public TeamMember getTeamMember()
	{
		return teamMember;
	}

	public void setTeamMember(TeamMember teamMember)
	{
		if ( teamMember == null )
		{
			throw new NullArgumentException("teamMember is null!");
		}

		this.teamMember = teamMember;
	}
}

public class PlayerStatId implements Serializable
{
	private ScoreId score;

	private TeamMemberId teamMember;

	public PlayerStatId()
	{
	}

	public PlayerStatId(Integer gameId, Boolean isHome, Integer playerId, Integer rosterId)
	{
		if ( gameId == null )
		{
			throw new NullArgumentException("gameId is null!");
		}

		if ( isHome == null )
		{
			throw new NullArgumentException("isHome is null!");
		}

		if ( playerId == null )
		{
			throw new NullArgumentException("playerId is null!");
		}

		if ( rosterId == null )
		{
			throw new NullArgumentException("rosterId is null!");
		}

		this.score = new ScoreId(gameId, isHome);
		this.teamMember = new TeamMemberId(playerId, rosterId);
	}

	public ScoreId getScoreId()
	{
		return score;
	}

	public void setScoreId(ScoreId score)
	{
		this.score = score;
	}

	public TeamMemberId getTeamMemberId()
	{
		return teamMember;
	}

	public void setTeamMemberId(TeamMemberId teamMember)
	{
		this.teamMember = teamMember;
	}
}

Note that I omitted the imports and the equals and hashCode methods. Note also this is supposed to be JPA 1.0 code. However I believe that doesn't matter much in terms of code generation.

In the servlet I simply call:

EntityManagerFactory emf = Persistence.createEntityManagerFactory("bbstats");
EntityManager em = emf.createEntityManager();
PlayerStat ps = em.find(PlayerStat.class, new PlayerStatId(5, false, 2, 1));

Can anyone tell what might go wrong?

Karsten