CODEWITHSUNDEEP
javajpahsqldbunique-constraintdbunit
user613114
user613114

Reputation: 2821

JPA DBUnit HsqlDb: Disable constraint checks?

My application only reads data from database. No create/update/delete operations. I am trying to map one DB view to JPA entity. This view does not have any unique IDs. So basically every row is unique. But there is a possibility that value of some columns may be null.

Now the problem is that:

1) I can not make any changes in database.Its out of my scope. So view can not be added with new column for unique ID.

2) I can not create JPA entity without primary key as it will complain that it atleast requires, singly unique key.

3) If I apply @Id annotation to all the columns, then it logically makes sense as every row is unique in my case. But during my unit testing, HSQLDB will complain that some unique columns may not have null value.

4) So I decided to defer/disable all constraints. Because in my case constraints are considered only during in-memory schema creation using DBUnit and HsqlDb.

I tried to execute following prepared statement:

SET CONSTRAINTS ALL DEFERRED

But it fails with error:

Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: CONSTRAINTS

I am not sure how disable constraint checks during initial DB initialization. I have already disabled foreign key checks in past using:

SET DATABASE REFERENTIAL INTEGRITY FALSE

And it works without any issue. Please let me know, if you have any idea on how to disable all constraint checks for HsqlDb.

Jar Versions:

DbUnit - 2.5.0

Junit - 4.10

HsqlDb 2.3.0

JDK - 1.7

Update 1:

I updated my HsqlDb jar version to 2.3.4. I found folowing reference:

http://hsqldb.org/doc/guide/dbproperties-chapt.html#N155DE

Then I tried to execute these statements on connection initially:

SET DATABASE SQL UNIQUE NULLS { TRUE | FALSE }

But it didn't help.

Update 2:

I tried to set those columns explicitly to null using:

ALTER TABLE T_NAME ALTER COLUMN C_NAME SET NULL

But I get error saying that:

column is in primary key in statement [ALTER TABLE T_NAME ALTER COLUMN C_NAME SET NULL]

Upvotes: 0

Views: 2909

Answers (2)

user613114
user613114

Reputation: 2821

I resolved this issue by deleting primary key first using:

ALTER TABLE T_NAME DROP PRIMARY KEY

And then deleting not null constraint for individual columns using:

ALTER TABLE T_NAME ALTER COLUMN C_NAME SET NULL

As Jeff suggested in his answer, it makes sense to write separate class to disable constraints for individual test suites.

Upvotes: 1

Jeff
Jeff

Reputation: 953

Two thoughts that may help:

  1. When mapping a JPA entity with compound primary key to an existing database, @EmbeddedId is the correct approach on the entity vs multiple @Id fields:

    1. Create a second class with the multiple @Id fields and annotate this class with @Embeddable.
    2. Create a single field of the second class type on the entity, annotated with @EmbeddedId.

    Also, since it is used in read-only mode, use the insertable and updatable @Column attributes on the @Id fields: @Column(insertable = false, updatable = false)

    These may cause the JPA provider to behave differently with regard to the uniqueness issue.

  2. For a similar situation, I removed referential integrity constraints with Apache Derby. I assume it would work for HSQLDB too.

    Your question does not state at what point you issue the remove constraints commands, so ensure that you issue them at the start of each test needing the constraints removed.

    I wrote a "ConstraintManipulator" class that removed and restored constraint(s). The tests that needed constraints disabled called the correct ConstraintManipulator remove methods before the test and add methods after the test (only wanted specific constraints off for certain tests and restored them for subsequent tests). Those methods simply issued the DDL commands ("add constraint ...", "drop constraint ...") using the dbUnit-configured JDBC database connection.

Upvotes: 1

Related Questions