SimpleJdbcInsert fail to insert because of null autogenerated id (HSQLDB)

2020-05-27 java spring hibernate junit hsqldb

Good morning everyone

I am unit testing the persistence tier of a project with JUnit using HSQLDB. The project is using Spring with Hibernate + JPA. I'm using Spring's SimpleJdbcInsert to insert some data in the database for testing. However whenever I try to insert something I am getting: org.hsqldb.HsqlException: violación del restricción de integridad: restricción ('check') NOT NULL; SYS_CT_10137 table: PICTURE column: PICTURE_ID as shown below:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO picture (DATA, MIME_TYPE, NAME, SIZE) VALUES(?, ?, ?, ?)]; violación del restricción de integridad: restricción ('check') NOT NULL; SYS_CT_10137 table: PICTURE column: PICTURE_ID; nested exception is java.sql.SQLIntegrityConstraintViolationException: violación del restricción de integridad: restricción ('check') NOT NULL; SYS_CT_10137 table: PICTURE column: PICTURE_ID

    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:85)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:866)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:927)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:932)
    at org.springframework.jdbc.core.simple.AbstractJdbcInsert.executeInsertInternal(AbstractJdbcInsert.java:362)
    at org.springframework.jdbc.core.simple.AbstractJdbcInsert.doExecute(AbstractJdbcInsert.java:341)
    at org.springframework.jdbc.core.simple.SimpleJdbcInsert.execute(SimpleJdbcInsert.java:122)
    at ar.edu.itba.paw.tests.AppointmentDaoImplTest.insertPicture(AppointmentDaoImplTest.java:330)
    at ar.edu.itba.paw.tests.AppointmentDaoImplTest.insertUser(AppointmentDaoImplTest.java:345)
    at ar.edu.itba.paw.tests.AppointmentDaoImplTest.insertPatient(AppointmentDaoImplTest.java:449)
    at ar.edu.itba.paw.tests.AppointmentDaoImplTest.testCreateAppointmentSuccessfully(AppointmentDaoImplTest.java:557)
    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:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    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.junit.rules.ExpectedException$ExpectedExceptionStatement.evaluate(ExpectedException.java:168)
    at org.junit.rules.RunRules.evaluate(RunRules.java:20)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
    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:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:193)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:230)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:58)
Caused by: java.sql.SQLIntegrityConstraintViolationException: violación del restricción de integridad: restricción ('check') NOT NULL; SYS_CT_10137 table: PICTURE column: PICTURE_ID
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:873)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:866)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629)
    ... 41 more
Caused by: org.hsqldb.HsqlException: violación del restricción de integridad: restricción ('check') NOT NULL; SYS_CT_10137 table: PICTURE column: PICTURE_ID
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.Table.enforceRowConstraints(Unknown Source)
    at org.hsqldb.Table.insertSingleRow(Unknown Source)
    at org.hsqldb.StatementDML.insertSingleRow(Unknown Source)
    at org.hsqldb.StatementInsert.getResult(Unknown Source)
    at org.hsqldb.StatementDMQL.execute(Unknown Source)
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 46 more

Here is my test (where insertPatient() is failing):

@Test
public void testCreateAppointmentSuccessfully() {
    cleanAllTables();
    insertPatient();

    ... some code ...
}

The insertPatient function (where insertPicture() is failing):

private void insertPatient() {
    insertUser();

    ... some code ...
}

private void insertUser() {
    insertPicture();

    ... some code ...
}

private void insertPicture() {
    Map<String, Object> map = new HashMap<>();
    map.put("data", IMG_DATA);
    map.put("mime_type", MIME_TYPE);
    map.put("size", IMG_SIZE);
    map.put("name", PICTURE);
    pictureJdbcInsert.execute(map);
}

The SimpleJdbcInsert pictureJdbcInsert instance is set in the following method:

@Before
public void setUp(){
    this.pictureJdbcInsert = new SimpleJdbcInsert(this.ds)
                .withTableName(PICTURES_TABLE) // private static final String PICTURES_TABLE = "picture";
                .usingGeneratedKeyColumns("picture_id");

    ... some code ...
}

The test class is annotated with @Sql(scripts = "classpath:sql/schema.sql") being schema.sql:

create table if not exists picture
(
    picture_id identity        not null constraint picture_pk primary key,
    name      varchar(1023),
    mime_type varchar(255)     not null,
    size      bigint           not null default 0,
    data      varbinary(65535) not null
);

... some more tables ...

As you can see, I am not putting the key manually. I am expecting that usingGeneratedKeyColumns method will automatically generate it for me. From the stacktrace I understand that is trying to execute the following query: INSERT INTO picture (DATA, MIME_TYPE, NAME, SIZE) VALUES(?, ?, ?, ?) where the ? are replaced by the corresponding values in the map, but failing because there was no value for picture_id specified. I don't understand is this happening as the schema.sql file specifies that picture_id is of type identity meaning that is auto-generated and by default starting by 0 and with increment of 1.

I have been trying and looking for solutions but everything suggests this should be working.

Answers

When utilising the usingGeneratedKeyColumns() method it seems you must use the corresponding method executeAndReturnKey() instead of execute() to auto generate the key while inserting. With the execute method you would need to provide the picture_id column and value in the parameter map.

Take a look at section 5.1 of this guide for an example of this difference: https://www.baeldung.com/spring-jdbc-jdbctemplate

Therefore in your code you can do this:

private void insertPicture() {
    Map<String, Object> map = new HashMap<>();
    map.put("data", IMG_DATA);
    map.put("mime_type", MIME_TYPE);
    map.put("size", IMG_SIZE);
    map.put("name", PICTURE);
    Number id = pictureJdbcInsert.executeAndReturnKey(map);
}

You can capture or discard the returned id value if you wish.

The actual problem was in the configuration class. I had this properties.setProperty("hibernate.hbm2ddl.auto", "update"); in the EntityManagerFactory bean. Therefore, JPA was creating the table before running the schema.sql file. This was causing that the picture_id column was not of identity type. Deleting that line from the bean solved the issue.

Related