Hello,

I have a MySql schema with more than 100 tables, each having some fields that require a value and some other fields that do not require a value.

For example, the use table requires a value for the email field, but not for the company field.

Now, my first reaction was to create the table with a NOT NULL attribute only for the reuired fields, like for the email property, and not for the company property.

This way, Hibernate could do an insert with NULL values on the non required properties.

But I've been told that, for a MySql table, it is better to have NOT NULL on all properties, and to have a NOT NULL DEFAULT '' for the properties that are not required.

But,this latter choice conflicts with Hibernate's default way of inserting into a table.

Indeed, by default, Hibernate specifies all the fields of a table in an insert statement, with a null value set for the object properties that were not explicitly set, like:

insert
into
user
(version, firstname, lastname, company, email, password)
values
(?, ?, ?, ?, ?, ?)
binding '0' to parameter: 1
binding null to parameter: 2
binding null to parameter: 3
binding null to parameter: 4
binding 'my@email.com' to parameter: 5
binding 'mypassword' to parameter: 6

This above sql statement attempts to insert a null value into the MySql table field firstname marked as being NOT NULL DEFAULT '' which triggers an exception.

A solution to this is to have Hibernate insert only on the explicitly specified values, like:

insert
into
user
(version, email, password)
values
(?, ?, ?, ?, ?, ?)
binding '0' to parameter: 1
binding 'my@email.com' to parameter: 5
binding 'mypassword' to parameter: 6

To have it so, the Hibernate mapping attribute dynamic-insert needs to be set to true in the mapping file.

With the dynamic-insert attribute set to true, the object can be created and persisted like:

User user = new User();
user.setEmail("mitt@yahoo.se");
user.setPassword("apassword");
user = userDao.makePersistent(user);

Another solution, if not wanting to use the dynamic-insert mapping attribute, is to explicitly set all the properties of the object.

With the dynamic-insert attribute set to false, the object must be created and persisted like:

User user = new User();
user.setEmail("mitt@yahoo.se");
user.setPassword("apassword");
user.setFirstname("");
user.setLastname("");
user.setCompany("");
user = userDao.makePersistent(user);

So, which one of the 3 above alternatives is the recommended one ?

1- Have nullable table fields for those that are not required.

2- Have NOT NULL DEFAULT '' table fields for those that are not required, with the Hibernate mapping attribute dynamic-insert set to true.

3- Have NOT NULL DEFAULT '' MySql table fields for those that are not required, with the Hibernate mapping attribute dynamic-insert set to false (Hibernate's default), and explicitly set all the properties of the object.