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

Question

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.

score 0 · Answer 1 · answered May 27 '20 at 03:18

0

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.

answered May 27 '20 at 03:18

Hakan Ozbay

4,639
2
22
28

Tried this but did not solve the issue. The same exception is thrown. – Agustín Roca May 27 '20 at 16:51
I noticed in your schema the data type of your `picture_id` column isn't set at all. Can you try setting this as an Integer followed by the rest of your metadata (identity not null ...etc) and try again? The HSQL documentation mandate a column data type be declared after the column name before any further metadata : http://www.hsqldb.org/doc/guide/databaseobjects-chapt.html#dbc_table_creation – Hakan Ozbay May 28 '20 at 02:02
I changed the `province_id` column to `province_id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY` matching exactly with the documentation's example you send. However, the same exception is thrown. – Agustín Roca May 28 '20 at 02:13
You mention `province_id` but we have been discussing `picture_id`. Is this a typing mistake or did you accidentally change a different column called `province_id`? – Hakan Ozbay May 28 '20 at 03:18
Yes sorry, I meant `picture_id`. – Agustín Roca May 28 '20 at 06:06

score 0 · Accepted Answer · answered May 29 '20 at 00:36

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.

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

2 Answers2