Converting a MySQL database to a SQL Server 2008 database using Java + Hibernate. Running into this strange error message.
I know it's supposed to mean that an integer somewhere is too big to fit into the numeric(x,y) that it corresponds to on the SQL Server database, but I can't see anywhere where that is the case.
It happens whenever I try to add things to a Player's statistics. Here's Player.java attributes
@Entity(name = "player")
public class Player {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
Integer playerId;
@ElementCollection
@CollectionTable(name = "playerposition", joinColumns = @JoinColumn(name = "playerid"))
@Column(name = "position")
@Fetch(FetchMode.JOIN)
Set<String> positions = new HashSet<String>();
@OneToMany(fetch = FetchType.LAZY, cascade=CascadeType.ALL, mappedBy="player")
@Fetch(FetchMode.JOIN)
Set<PlayerSeason> seasons = new HashSet<PlayerSeason>();
@Column
String name;
@Column
String nickName;
@Column
Date birthDay;
@Column
Date deathDay;
@Column
String battingHand;
@Column
String throwingHand;
@Column
String birthCity;
@Column
String birthState;
@Column
String college;
@Column
Date firstGame;
@Column
Date lastGame;
Here is PlayerSeason.java attributes
@SuppressWarnings("serial")
@Entity(name = "playerseason")
public class PlayerSeason implements Serializable {
@Id
@ManyToOne
@JoinColumn(name = "playerid", referencedColumnName = "playerid", insertable = false, updatable = false)
public Player player;
@Id
@Column
public Integer year;
@Column
int gamesPlayed;
@Column
double salary;
@OneToOne(cascade=CascadeType.ALL, mappedBy="id")
BattingStats battingStats;
@OneToOne(cascade=CascadeType.ALL, mappedBy="id")
FieldingStats fieldingStats;
@OneToOne(cascade=CascadeType.ALL, mappedBy="id")
PitchingStats pitchingStats;
@OneToOne(cascade=CascadeType.ALL, mappedBy="id")
CatchingStats catchingStats;
Here's the CatchingStats.java attributes
@SuppressWarnings("serial")
@Entity(name="catchingstats")
public class CatchingStats implements Serializable{
@Id
@OneToOne
@JoinColumns({
@JoinColumn(name="playerid"),
@JoinColumn(name="year")
})
PlayerSeason id;
@Column
Integer passedBalls;
@Column
Integer wildPitches;
@Column
Integer stealsAllowed;
@Column
Integer stealsCaught;
I am 95% sure that the issue is with statistics because when I don't add any thing to a Player's PlayerSeasons's "season" set, it works. But as soon as I try to add ANY statistics (catching, hitting or otherwise) it throws the exception and Hibernate does not persist the Player.
Can someone spot the issue?
-----EDIT--------
Here's the ddl for the database
create table player (
playerId numeric(10,0) IDENTITY(10000,5) primary key,
name varchar(100) not null,
nickName varchar(255),
birthDay date,
deathDay date,
battingHand varchar(1) check (battingHand in ('L', 'R', 'S')),
throwingHand varchar(1) check (throwingHand in ('L', 'R')),
birthCity varchar(50),
birthState varchar(2),
college varchar(50),
firstGame date,
lastGame date,
CONSTRAINT uniqueConstraint_Player UNIQUE (name, birthDay, deathDay));
create table playerposition (
playerId numeric(10,0),
position varchar(10),
primary key(playerId, position),
foreign key(playerId) references player on delete cascade);
create table playerseason (
playerId numeric(10,0) references player on delete cascade,
year numeric(4,0),
gamesPlayed numeric(3,0),
salary numeric(12,2),
primary key (playerId, year));
create table battingstats (
playerId numeric(10,0),
year numeric(4,0),
atBats numeric(4,0),
hits numeric(4,0),
doubles numeric(4,0),
triples numeric(4,0),
homeRuns numeric(4,0),
runsBattedIn numeric(4,0),
strikeouts numeric(4,0),
walks numeric(4,0),
hitByPitch numeric(4,0),
intentionalWalks numeric(4,0),
steals numeric(4,0),
stealsAttempted numeric(4,0),
primary key(playerId, year),
foreign key(playerId, year) references playerseason on delete cascade);
create table catchingstats (
playerId numeric(10,0),
year numeric(4,0),
passedBalls numeric(4,0),
wildPitches numeric(4,0),
stealsAllowed numeric(4,0),
stealsCaught numeric(4,0),
primary key(playerId, year),
foreign key(playerId, year) references playerseason on delete cascade);
create table fieldingstats (
playerId numeric(10,0),
year numeric(4,0),
errors numeric(4,0),
putOuts numeric(4,0),
primary key(playerId, year),
foreign key(playerId, year) references playerseason on delete cascade);
create table pitchingstats (
playerId numeric(10,0),
year numeric(4,0),
outsPitched numeric(4,0),
earnedRunsAllowed numeric(4,0),
homeRunsAllowed numeric(4,0),
strikeouts numeric(4,0),
walks numeric(4,0),
wins numeric(4,0),
losses numeric(4,0),
wildPitches numeric(4,0),
battersFaced numeric(4,0),
hitBatters numeric(4,0),
saves numeric(4,0),
primary key(playerId, year),
foreign key(playerId, year) references playerseason on delete cascade);
create table team (
teamId varchar(3),
otherId varchar(3),
name varchar(50),
league varchar(2),
yearFounded numeric(4,0),
yearLast numeric(4,0),
primary key(teamId));
create table teamseason (
teamId varchar(3),
year numeric(4,0),
gamesPlayed int,
wins int check (wins >= 0),
losses int check (losses >= 0),
rank int,
totalAttendance int,
primary key(teamId, year),
foreign key(teamId) references team);
create table teamseasonplayer (
teamId varchar(3),
year numeric(4,0),
playerId numeric(10,0),
primary key(teamId,year,playerId),
foreign key (teamId) references team,
foreign key (playerId) references player);
-----EDIT--------
Here's the stack trace as printed by Eclipse
Apr 30, 2014 2:19:58 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 8115, SQLState: S0008
Apr 30, 2014 2:19:58 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Arithmetic overflow error converting int to data type numeric.
Apr 30, 2014 2:19:58 PM org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl release
INFO: HHH000010: On release of batch it still contained JDBC statements
org.hibernate.exception.SQLGrammarException: could not execute statement
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:190)
at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:62)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3124)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3581)
at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:103)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:461)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:347)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56)
at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1222)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:425)
at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101)
at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:177)
at dataaccesslayer.HibernateUtil.persistPlayer(HibernateUtil.java:131)
at conversion.Convert.convertPlayers(Convert.java:116)
at conversion.Convert.main(Convert.java:30)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Arithmetic overflow error converting int to data type numeric.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:314)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)
... 15 more
