CODEWITHSUNDEEP
springhibernatejpa
numerical25
numerical25

Reputation: 10790

org.h2.jdbc.JdbcSQLException: NULL not allowed for column "ID"

I've already tried doing some of the solutions found on the web none of them worked. I am using in-memory persistent hibernate database. 

@Entity
@Table(name = "Users")
public class User implements Serializable {

    private String id;

    private String username;

    private String password;

    private String email;



    private Boolean enabled;


    private Event checkedInEvent;

    @Id
    @GeneratedValue(generator="uuid", strategy = GenerationType.AUTO)
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    @Column(name = "id", unique = true)
    public String getId() {
        return id;
    }

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

    @Column(name = "username")
    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    @Column(name = "password")
    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @PrePersist
    public void encryptPassword() throws Exception {
        MessageDigest md = MessageDigest.getInstance("MD5");
        md.update(password.getBytes());
        byte byteData[] = md.digest();
        //convert the byte to hex format method 1
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < byteData.length; i++) {
            sb.append(Integer.toString((byteData[i] & 0xff) + 0x100, 16).substring(1));
        }
        password = sb.toString();
    }

    @Column(name = "email")
    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }


    @Column(name = "enabled")
    public Boolean getEnabled() {
        return enabled;
    }

    public void setEnabled(Boolean enabled) {
        this.enabled = enabled;
    }

    @PostLoad
    public void postLoad()
    {
        password = null;
    }


    @ManyToOne(optional=true,cascade=CascadeType.ALL)
    @JoinTable
    (
        name="CheckedInUsers",
        joinColumns={ @JoinColumn(name="checkedInUsersId", referencedColumnName="id") },
        inverseJoinColumns={ @JoinColumn(name="checkedInEventsId", referencedColumnName="id") }
    )
    public Event getCheckedInEvent() {
        return checkedInEvent;
    }

    public void setCheckedInEvent(Event checkedInEvent) {
        this.checkedInEvent = checkedInEvent;
    }
}

Checked in User

@Entity
@Table(name = "CheckedInUsers")
public class CheckedInUsers {

    private String id;

    private String checkedInEventsId;

    private String checkedInUsersId;

    @Id
    @GeneratedValue(generator="uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    @Column(name = "id", unique = true)
    public String getId() {
        return id;
    }

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

    @Column(name = "checkedInEventsId")
    public String getEvent() {
        return checkedInEventsId;
    }

    public void setEvent(String event) {
        this.checkedInEventsId = event;
    }

    @Column(name = "checkedInUsersId")
    public String getUser() {
        return checkedInUsersId;
    }

    public void setUser(String user) {
        this.checkedInUsersId = user;
    }
}

Event

@Entity
@Spatial(spatialMode = SpatialMode.GRID)
@Indexed
@Table(name = "Events")
public class Event {

    private String id;

    private String name;

    private Date startTime;

    private Venue venue;

    private Set<User> checkedInUsers;


    @Id
    @DocumentId
    @GeneratedValue(generator="uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    @Column(name = "id", unique = true)
    @Field(name = "id")
    public String getId() {
        return id;
    }

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

    @Column(name = "name")
    @Field(name = "name")
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Column(name = "starttime")
    @Temporal(TemporalType.TIMESTAMP)
    @Field(name = "starttime")
    public Date getStartTime() {
        return startTime;
    }

    public void setStartTime(Date startTime) {
        this.startTime = startTime;
    }


    @ManyToOne(optional=false,targetEntity = Venue.class)
    @JoinColumn(name="venueid",referencedColumnName="id")
    @JsonBackReference
    public Venue getVenue() {
        return venue;
    }

    @OneToMany(mappedBy = "checkedInEvent")
    @Fetch(FetchMode.JOIN)
    public Set<User> getCheckedInUsers() {
        return checkedInUsers;
    }

    public void setCheckedInUsers(Set<User> checkedInUsers) {
        this.checkedInUsers = checkedInUsers;
    }

    public void setVenue(Venue venue) {
        this.venue = venue;
    }

}

error

org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:225)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:521)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:761)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:485)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:291)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:131)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
    at com.sun.proxy.$Proxy110.save(Unknown Source)
    at Superior.TestCases.Config.TestDataSetup.setUpEastonEvents(TestDataSetup.java:130)
    at Superior.TestCases.Config.TestDataSetup.setUpVenues(TestDataSetup.java:81)
    at Superior.TestCases.Config.TestDataSetup.setUp(TestDataSetup.java:65)
    at Superior.TestCases.Tests.Models.TestAppManager.setup(TestAppManager.java:73)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:24)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:254)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:193)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:78)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:212)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:68)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:129)
    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:211)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3127)
    at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3190)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3525)
    at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:159)
    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.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:77)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:517)
    ... 46 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLException: NULL not allowed for column "ID"; SQL statement:
insert into CheckedInUsers (checkedInEventsId, checkedInUsersId) values (?, ?) [23502-191]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.table.Column.validateConvertUpdateSequence(Column.java:307)
    at org.h2.table.Table.validateConvertUpdateSequence(Table.java:779)
    at org.h2.command.dml.Insert.insertRows(Insert.java:151)
    at org.h2.command.dml.Insert.update(Insert.java:114)
    at org.h2.command.CommandContainer.update(CommandContainer.java:98)
    at org.h2.command.Command.executeUpdate(Command.java:258)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:160)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:146)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
    ... 60 common frames omitted

The data that will be persisted

    user = new User();
    user.setUsername("billTheTester");
    user.setEmail("[email protected]");
    user.setPassword("test123");
    user = daoManager.getUserRepo().save(user);
    this.testUserTwo = user;

    Calendar cal = Calendar.getInstance();
    Event event = new Event();
    event.setName("Hanging Out!");
    event.setVenue(venue);
    event.setStartTime(cal.getTime());
    event = daoManager.getEventRepo().save(event);
    testUserTwo.setCheckedInEvent(event);
    daoManager.getUserRepo().save(testUserTwo);

Datasource Configuration

package Superior.Config.DatabaseConfig;

import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.annotation.Order;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Properties;


@Configuration
@PropertySource({ "classpath:application.properties" })
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "Superior.Models",
        entityManagerFactoryRef = "prodEntityManager",
        transactionManagerRef = "transactionManager"
)
@Order(1)
public class PrimaryDatasourceConfig {

    @Autowired
    private Environment env;

    HashMap<String, Object> properties = new HashMap<String, Object>();

    @Bean(name = "prodDataSource")
    public DataSource prodDataSource() {
        Properties props = new Properties();
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(env.getProperty("hibernate.connection.url"));
//        dataSource.setUsername(env.getProperty("hibernate.username"));

        return dataSource;
    }

    @Primary
    @Bean(name = "prodEntityManager")
    public SuperiorLocalContainerEntityManagerFactoryBean prodEntityManager() {
        SuperiorLocalContainerEntityManagerFactoryBean em = new SuperiorLocalContainerEntityManagerFactoryBean();
        em.setDataSource(prodDataSource());
        em.setPackagesToScan(new String[]{"Superior.Models.Tables"});

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        this.properties = new HashMap<String, Object>();
        properties.put("hibernate.dialect",
                env.getProperty("hibernate.dialect"));
        properties.put("hibernate.hbm2ddl.auto",
                env.getProperty("hibernate.hbm2ddl.auto"));
        properties.put("hibernate.hbm2ddl.import_files",
                env.getProperty("hibernate.hbm2ddl.import_files"));
        em.setJpaPropertyMap(properties);
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(em.getObject());
        return em;
    }

    @Primary
    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(prodEntityManager().getObject());
        return transactionManager;
    }

    @Bean
    public SessionFactory sessionFactory(EntityManagerFactory factory) {
        if(factory.unwrap(SessionFactory.class) == null){
            throw new NullPointerException("factory is not a hibernate factory");
        }
        return factory.unwrap(SessionFactory.class);
    }

}

hibernate.properties

hibernate.connection.driver_class=org.h2.Driver
hibernate.connection.url=jdbc:h2:~/test
hibernate.dialect=org.hibernate.dialect.H2Dialect
hibernate.query.factory_class=org.hibernate.hql.classic.ClassicQueryTranslatorFactory
hibernate.username=sa
hibernate.hbm2ddl.auto=create-drop
hibernate.hbm2ddl.import_files=loadtable.sql

loadtable.sql

CREATE TABLE CheckedInUsers (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 checkedInEventsId varchar(35),
 checkedInUsersId varchar(35)
);

Upvotes: 6

Views: 20295

Answers (2)

Valerio Vaudi
Valerio Vaudi

Reputation: 4532

I'm analayzing the your prntstack:

...
Caused by: org.h2.jdbc.JdbcSQLException: NULL not allowed for column "ID"; SQL statement:
insert into CheckedInUsers (checkedInEventsId, checkedInUsersId) values (?, ?) [23502-191]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.table.Column.validateConvertUpdateSequence(Column.java:307)
    at org.h2.table.Table.validateConvertUpdateSequence(Table.java:779)
    at org.h2.command.dml.Insert.insertRows(Insert.java:151)
    at org.h2.command.dml.Insert.update(Insert.java:114)
    at org.h2.command.CommandContainer.update(CommandContainer.java:98)
    at org.h2.command.Command.executeUpdate(Command.java:258)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:160)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:146)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.e

and in my opinion the real problem is in the id column af the your CheckedInUsers entity. in fact if you see the your code:

@Entity
@Table(name = "CheckedInUsers")
public class CheckedInUsers {

    private String id;

    private String checkedInEventsId;

    private String checkedInUsersId;

    @Id
    @GeneratedValue(generator="uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    @Column(name = "id", unique = true)
    public String getId() {
        return id;
    }

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

    @Column(name = "checkedInEventsId")
    public String getEvent() {
        return checkedInEventsId;
    }

    public void setEvent(String event) {
        this.checkedInEventsId = event;
    }

    @Column(name = "checkedInUsersId")
    public String getUser() {
        return checkedInUsersId;
    }

    public void setUser(String user) {
        this.checkedInUsersId = user;
    }
}

and the your sql for creatig the table:

CREATE TABLE CheckedInUsers (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 checkedInEventsId varchar(35),
 checkedInUsersId varchar(35)
);

I found that you declared the id property in java as a String and in sql as an int with the autoincremented strategy. I consider that the problem is here.

Now I don't know well the your domain but try to make the data type in your java code the same in the sql.

I Seeing the code and I see that you use SpringData, spring data creates a new object if the id is null and updates the object if the id is not null, however i know that for JPA exists a strategy that tract numeric id for entity and not string. I read from the code that you wish you had string has id, I can suggest to see the implementation that spring data for mongo use because for mongo the default strategy for id generation use uuid.

I hope that this can help you

Upvotes: 2

Ali Dehghani
Ali Dehghani

Reputation: 48123

The problem is you're using a separate JPA Entity to handle the relationship between the User and Event, which is seems unreasonable to me, since you can use getCheckedInEvent() and getCheckedInUsers() methods in order to get associated event with a User and get all users associated with an Event. So, if you delete the CheckedInUsers entity entirely, your problem will be solved.

Upvotes: 0

Related Questions