SQLGrammarException in Maven JUnit Tests

[Nathan Joyes]

Utilizing H2 for my project's JUnit tests, I am currently seeing an exception thrown in a number of tests run by Maven, but not when run in Eclipse as a JUnit test.

Exception is:

[INFO] Running ca.joyes.sportssjef.service.scheduled.SendApprovalRequestNotificationServiceTest
22:13:51.052 [main] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Syntax error in SQL statement "select approvalre0_.uuid as uuid1_18_0_, approvalre0_.active as active2_18_0_, approvalre0_.createdBy as createdb3_18_0_, approvalre0_.createdDate as createdd4_18_0_, approvalre0_.modifiedDate as modified5_18_0_, approvalre0_.updatedBy as updatedb6_18_0_, approvalre0_.approvalformquestion_uuid as approval8_18_0_, approvalre0_.approvalrequest_uuid as approval9_18_0_, approvalre0_.[*]value as value7_18_0_ from approvalrequestresponse approvalre0_ where approvalre0_.uuid=?"; expected "identifier"; SQL statement:
select approvalre0_.uuid as uuid1_18_0_, approvalre0_.active as active2_18_0_, approvalre0_.createdBy as createdb3_18_0_, approvalre0_.createdDate as createdd4_18_0_, approvalre0_.modifiedDate as modified5_18_0_, approvalre0_.updatedBy as updatedb6_18_0_, approvalre0_.approvalformquestion_uuid as approval8_18_0_, approvalre0_.approvalrequest_uuid as approval9_18_0_, approvalre0_.value as value7_18_0_ from approvalrequestresponse approvalre0_ where approvalre0_.uuid=? [42001-214]
[ERROR] Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 13.287 s <<< FAILURE! - in ca.joyes.sportssjef.service.scheduled.SendApprovalRequestNotificationServiceTest
[ERROR] ca.joyes.sportssjef.service.scheduled.SendApprovalRequestNotificationServiceTest  Time elapsed: 13.286 s  <<< ERROR!
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement
at ca.joyes.sportssjef.service.scheduled.SendApprovalRequestNotificationServiceTest.createApprovalRequest(SendApprovalRequestNotificationServiceTest.java:232)
at ca.joyes.sportssjef.service.scheduled.SendApprovalRequestNotificationServiceTest.setup(SendApprovalRequestNotificationServiceTest.java:165)
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
at ca.joyes.sportssjef.service.scheduled.SendApprovalRequestNotificationServiceTest.createApprovalRequest(SendApprovalRequestNotificationServiceTest.java:232)
at ca.joyes.sportssjef.service.scheduled.SendApprovalRequestNotificationServiceTest.setup(SendApprovalRequestNotificationServiceTest.java:165)
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException:
Syntax error in SQL statement "select approvalre0_.uuid as uuid1_18_0_, approvalre0_.active as active2_18_0_, approvalre0_.createdBy as createdb3_18_0_, approvalre0_.createdDate as createdd4_18_0_, approvalre0_.modifiedDate as modified5_18_0_, approvalre0_.updatedBy as updatedb6_18_0_, approvalre0_.approvalformquestion_uuid as approval8_18_0_, approvalre0_.approvalrequest_uuid as approval9_18_0_, approvalre0_.[*]value as value7_18_0_ from approvalrequestresponse approvalre0_ where approvalre0_.uuid=?"; expected "identifier"; SQL statement:
select approvalre0_.uuid as uuid1_18_0_, approvalre0_.active as active2_18_0_, approvalre0_.createdBy as createdb3_18_0_, approvalre0_.createdDate as createdd4_18_0_, approvalre0_.modifiedDate as modified5_18_0_, approvalre0_.updatedBy as updatedb6_18_0_, approvalre0_.approvalformquestion_uuid as approval8_18_0_, approvalre0_.approvalrequest_uuid as approval9_18_0_, approvalre0_.value as value7_18_0_ from approvalrequestresponse approvalre0_ where approvalre0_.uuid=? [42001-214]
at ca.joyes.sportssjef.service.scheduled.SendApprovalRequestNotificationServiceTest.createApprovalRequest(SendApprovalRequestNotificationServiceTest.java:232)
at ca.joyes.sportssjef.service.scheduled.SendApprovalRequestNotificationServiceTest.setup(SendApprovalRequestNotificationServiceTest.java:165)

From what I think I have found, the "[*]" identifier in the failing tests only seems to appear for Maven run unit tests as opposed to not showing in the JUnit run tests.

I have already tried unsuccessfully the hibernate.globally_quoted_identifiers property configuration.

For reference the persistence configuration is:

<persistence-unit name="sportssjef_test">
<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
<properties>
<property name="hibernate.archive.autodetection" value="class" />
<!-- H2 -->
<property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" />
<property name="javax.persistence.jdbc.driver" value="org.h2.Driver" />
<property name="javax.persistence.jdbc.url" value="jdbc:h2:mem:test;DB_CLOSE_DELAY=-1" />
<property name="javax.persistence.jdbc.user" value="sa" />
<property name="javax.persistence.jdbc.password" value="" />
<property name="hibernate.jdbc.time_zone" value="UTC"/>
<property name="hibernate.show_sql" value="false" />
<property name="hibernate.flushMode" value="FLUSH_AUTO" />
<property name="org.hibernate.envers.audit_table_suffix" value="_audit" />
<property name="hibernate.hbm2ddl.auto" value="create-drop" />
</properties>
</persistence-unit>

Thank you,

Nathan.

[Evgenij Ryazanov]

Hi!

VALUE is a reserved word in the SQL Standard and H2, it cannot be used as unquoted identifier.

https://h2database.com/html/advanced.html#keywords

[Nathan Joyes]

Hi Evangenij:

Thanks, that was the issue. I was able to resolve by adding backticks to the @Column name definition to the various affected class attributes.

Thank you for the quick help,