Field doesn't have a default value with manyToMany Hibernate relationship

[Samar Land]

In manyToMany relationship, When I'm trying to add a new driver with selected vehicle from dropdown list, it's throwing an exception
java.sql.SQLException: Field 'vehicle_id' doesn't have a default value

However, if I set a default value in the DB, It works fine by inserting two different rows to the (driver_series_stats_xref) table, one for what I’m trying to add and other row for the default value that I set in the db.

Entity.java

  @Column(name="driver_id", unique = true,  nullable=false)
	private Long driverId;
	
	@Column(name="first_name")
	private String firstName;
	
	@Column(name="last_name")
	private String lastName;

     @ManyToMany
	@JoinTable(name = "driver_series_stats_xref",
	joinColumns = @JoinColumn(name = "driver_id"),
	inverseJoinColumns = @JoinColumn(name = "vehicle_id", referencedColumnName ="vehicle_id"))
	private List<Vehicle> listOfVehicles = new ArrayList<Vehicle>();
    //getter and setter

the create table:

      
    CREATE TABLE `driver_vehicle_xref` (
	`driver_id` TINYINT(3) UNSIGNED NOT NULL,
	`vehicle_id` SMALLINT(5) UNSIGNED NOT NULL,
	`starts_num` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
	PRIMARY KEY (`driver_id`, `vehicle_id`),
	INDEX `fk_driver_vehicle_xref_2` (`vehicle_id`),
	CONSTRAINT `fk_driver_vehicle_xref_1` FOREIGN KEY (`driver_id`) REFERENCES `driver` (`driver_id`),CONSTRAINT `fk_driver_vehicle_xref_2` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicle` (`vehicle_id`)
)

driverDao.java

       public Long addorUpdateDriver(final Driver driver) {
		sessionFactory.getCurrentSession().saveOrUpdate(driver);
		return driver.getDriverId();
	}
	

driverService.java

     @Transactional
	public Long addToDriver(Driver driver, Vehicle vehicle) {
		vehicle = vehicleDao.getVehicleById(vehicle.getVehicleId());
		driver.getListOfVehicles().add(vehicle);
		Long driverId = driverDao.addorUpdateDriver(driver);
		driver.setDriverId(driverId);
		return driverId;
	}

controller.java(post method)

    public String addDriverSubmit(@ModelAttribute Driver driver, Vehicle vehicle, BindingResult result, @RequestParam (value="mode") String mode,
			HttpServletRequest request) {
    String vehicleId = request.getParameter("vehicleId");
		request.getAttribute("driverId");
		if("Save".equalsIgnoreCase(mode)) {
			driverService.addToDriver(driver, vehicle);
		} 

error meesage
HTTP Status 500 - Request processing failed; nested exception is org.springframework.orm.hibernate4.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [n/a]; SQL state [HY000]; error code [1364]; could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statement

and
java.sql.SQLException: Field 'vehicle_id' doesn't have a default value

[Roel De Nijs]

Here you'll find a detailed example of a ManyToMany relationship.

Is it intentional that you use driver_series_stats_xref as the join table, but that you provide the create script of another table?

[Samar Land]

Roel De Nijs wrote:Here you'll find a detailed example of a ManyToMany relationship.

Is it intentional that you use driver_series_stats_xref as the join table, but that you provide the create script of another table?

I was trying to recreate the xref table in case I did something wrong, but didn't work ND BACK IT AS IT.

I will check the link, thanks for this.

[Samar Land]

Any suggestions to solve this issue, I'm not sure yet what's going on here, and why it's working when I set the default value in the db.

[Roel De Nijs]

Could you increase the logging of your application to see which SQL statements your ORM tool is generating...

[Samar Land]

java.sql.SQLException: Field 'vehicle_id' doesn't have a default value
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
	at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:62)
	at org.hibernate.persister.collection.AbstractCollectionPersister.recreate(AbstractCollectionPersister.java:1311)
	at org.hibernate.action.internal.CollectionRecreateAction.execute(CollectionRecreateAction.java:67)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:465)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:351)
	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:1258)
	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 org.springframework.orm.hibernate4.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:557)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:757)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:726)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:478)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:272)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:643)
	at com.menards.racingadmin.integration.service.DriverService$$EnhancerByCGLIB$$ed51b2a2.addToDriver(<generated>)
	at com.menards.racingadmin.presentation.controller.DriverController.DriverSubmit(DriverController.java:98)
	at sun.reflect.GeneratedMethodAccessor570.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:214)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:690)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:945)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:876)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
	at org.apache.catalina.core.StandardContextValve.__invoke(StandardContextValve.java:123)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.__invoke(StandardHostValve.java:171)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)

threw exception [Request processing failed; nested exception is org.springframework.orm.hibernate4.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [n/a]; SQL state [HY000]; error code [1364]; could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statement] with root cause

[Roel De Nijs]

That's the stack trace. These are not the SQL statements generated by the ORM tool

[Samar Land]

I have this issue fixed by deleting the add to list line in myService.

driver.getListOfVehicles().add(vehicle);

[Roel De Nijs]

Samar Land wrote:I have this issue fixed by deleting the add to list line in myService.

driver.getListOfVehicles().add(vehicle);

If that line of code is removed, how did you add the vehicle to the driver's list of vehicles?

[Samar Land]

well, it's a many to many relationship, I created a new Domain, Dao,Service for the DriverVehicle and I called the addOrUpdate method in DriverService to save the vehicle. (I have the vehicle as a dropdown list in my JSP) so All what I have to do is passing the vehicle id and then save the relation in DriverVehicl table)

DriverVehicle.java

@Entity
@Table(name="driver_vehicle")
@SuppressWarnings("serial")
public class DriverSeriesStats implements Serializable {
	
	@Id
	@Column(name = "vehicle_id")
	private long vehicleId;
	
	@Id
	@Column(name = "driver_id")
	private long driverId;
//Getter and setter


}

DriverService.java

@Transactional
	public void addToDriver(Driver driver,  Vehicle vehicle) {
		vehicle = vehicleDao.getVehicleById(vehicle.getVehicleId());
		
		 driverDao.addorUpdateDriver(driver);
		 DriverVehicle drivervehicle= new DriverVehicle ();

		 driverSeriesStats.setVehicleId(vehicle.getVehicleId());
		 driverSeriesStats.setDriverId(driver.getDriverId());
		 service.addorUpdateDriverVehicle(drivervehicle);
	}

Hope this will be useful for other people.

Sorry for not explain all that in my previous post, but I was excited

[Roel De Nijs]

Samar Land wrote:Hope this will be useful for other people.

Thanks for sharing your solution. It might indeed be helpful to other ranchers. Have a cow!

Samar Land wrote:well, it's a many to many relationship, I created a new Domain, Dao,Service for the DriverVehicle and I called the addOrUpdate method in DriverService to save the vehicle. (I have the vehicle as a dropdown list in my JSP) so All what I have to do is passing the vehicle id and then save the relation in DriverVehicl table)

It doesn't matter if it's a many-to-many relationship. I'm a bit surprised you need to add a new domain, dao and service for DriverVehicle. I would expect to be able to create a many-to-many mapping without these additional classes, just using the Driver and Vehicle entities with an appropriate mapping (as explained here).

[Samar Land]

I would expect to be able to create a many-to-many mapping without these additional classes, just using the Driver and Vehicle entities with an appropriate mapping

You're right, it works for me with other classes(driver - news)
All what I needed is mapping the Driver in news class.

I had to create the other classes to handle an extra columns, but my issue was before that.

Have a cow! 

Yaay! thanks

[Roel De Nijs]

Samar Land wrote:I had to create the other classes to handle an extra columns, but my issue was before that.

Then you definitely need an additional entity, as illustrated in the Mapping a Join Table with Additional Columns section.

[Samar Land]

That's right, Also I've learned that when I had more than two keys in my reference table I had to create entity.

Like (vehicleId, driverId, teamId) the three keys are set up in DB as primary key, So I needed to create a new entity to handle the primary key with @Id annotation.


It's working now for all my forms! Thanks everybody for the help

[Roel De Nijs]

Samar Land wrote:It's working now for all my forms!

Awesome!