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

Question

我已经尝试过在网上找到的一些解决方案，但都没有奏效。我正在使用内存中持久 hibernate 数据库。

@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;
    }
}

checkin 用户

@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;
    }
}

事件

@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;
    }

}

错误

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

将被持久化的数据

    user = new User();
    user.setUsername("billTheTester");
    user.setEmail("bill@google.com");
    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);

数据源配置

包 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

加载表

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

Answer 1

我正在分析你的 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

在我看来，真正的问题出在 CheckedInUsers 实体的 id 列中。事实上，如果你看到你的代码:

@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;
    }
}

以及用于创建表的 sql:

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

我发现您在 java 中将 id 属性声明为 String ，在 sql 中声明为 int 并使用自动递增策略。我认为问题就在这里。

现在我不太了解您的域，但尝试使您的 Java 代码中的数据类型与 sql 中的数据类型相同。

我看到代码，我看到你使用 SpringData，如果 id 为空，spring 数据会创建一个新对象，如果 id 不为空，则更新对象，但是我知道对于 JPA 存在一种策略，可以为实体和不是字符串。我从代码中读到您希望字符串具有 id，我可以建议查看 mongo 使用的 spring 数据的实现，因为对于 mongo，id 生成的默认策略使用 uuid。

我希望这可以帮助你