Hibernate Community • View topic - To "not null" or not to "not null" :-)

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

To "not null" or not to "not null" :-)

Page 1 of 1

[ 12 posts ]

Previous topic | Next topic

Author

Message

stephaneeybert

Post subject: To "not null" or not to "not null" :-)

Posted: Mon May 17, 2010 5:27 am

Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246

Hello,

I have a MySql schema with many tables, with lots of them have fields that are not required.

For example, one such table is

Code:

mysql> desc preference;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| version    | int(10) unsigned | NO   |     |         |                | 
| name       | varchar(50)      | NO   | UNI |         |                | 
| is_ml_text | varchar(1)       | NO   |     |         |                | 
| value      | text             | NO   |     |         |                | 
+------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Code:

mysql> show create table preference;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                     |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| preference | CREATE TABLE `preference` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `version` int(10) unsigned NOT NULL,
  `name` varchar(50) NOT NULL,
  `is_ml_text` varchar(1) NOT NULL,
  `value` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `id` (`id`),
  KEY `name_2` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=435 DEFAULT CHARSET=latin1 | 
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)de]

My business logic is that the name column cannot be empty. A validator makes sure of this.

But the value column can be empty. So does the is_ml_text column.

However, all of these fields are set up with a non null attribute in the table. I had read that it is better to always have such a non null attribute for a field in a MySql table, when I built this schema some years ago.

But now that I'm trying to access this schema with Hibernate, if I don't explicitly set an empty value for the value column and the is_ml_text column, then I get the following exception:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'value' cannot be null

To avoid this exception, I have to explicitly set an empty value when building the object that is persisted, like:

Code:

      Preference preference = new Preference();
      preference.setName("A_UNIQUE_NAME");
      preference.setValue("");
      preference.setIsMlText("");
      preference = preferenceDao.makePersistent(preference);

But I would prefer NOT to have to explicitly set such empty values so as to avoid coding statements like:

Code:

preference.setValue("");

I then tried to have the default attribute in the mapping file, like:

Code:

        <property name="isMlText" type="string">
            <column name="is_ml_text" length="1" not-null="false" default="" />
        </property>

but the exception remained the same.

I also tried to have the attribute dynamic-insert="true" like:

Code:

    <class name="com.thalasoft.learnintouch.core.domain.Preference" table="preference" dynamic-insert="true">
        <property name="isMlText" type="string">
            <column name="is_ml_text" length="1" not-null="false" default="" />
        </property>

but it now gives me the exception:

Caused by: java.sql.SQLException: Field 'is_ml_text' doesn't have a default value

Is there any way, to tell Hibernate, to insert an empty value instead of a NULL value ?

Thanks

Top

kossmo

Post subject: Re: To "not null" or not to "not null" :-)

Posted: Mon May 17, 2010 8:32 am

Regular

Joined: Thu Dec 10, 2009 10:53 am
Posts: 50

1. http://en.lmgtfy.com/?q=default+value+hibernate

Code:

    class Test {

        private Integer count = 3;

        @Column(name = “count”, nullable = false, columnDefinition = “bigint(20) default 0″)
        public Long getCount() {
            return Count;
        }
    }

2. The alternative, as you have already found out, is to use dynamicinsert, but you need default values for the "name" and "is_ml_text" defined in the DB for that!

Top

stephaneeybert

Post subject: Re: To "not null" or not to "not null" :-)

Posted: Mon May 17, 2010 9:38 am

Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246

I just read : mysql inserts an empty string, even without a default value for this field, when the the field is defined as not null.
at : http://www.webmasterworld.com/forum88/441.htm

So I tried to do the following insert statement:

mysql> insert into preference set name = 'AJETER0';
Query OK, 1 row affected, 3 warnings (0.02 sec)

which went just fine:
mysql> select * from preference order by id desc limit 1;
+-----+---------+---------+------------+-------+
| id | version | name | is_ml_text | value |
+-----+---------+---------+------------+-------+
| 439 | 0 | AJETER0 | | |
+-----+---------+---------+------------+-------+

And the table still has no default values set for these columns.

This seems to contradict your suggestion or is it me ?

Thanks for your tip anyway.

Top

TRex2003

Post subject: Re: To "not null" or not to "not null" :-)

Posted: Mon May 17, 2010 9:49 am

Newbie

Joined: Thu Sep 03, 2009 12:37 pm
Posts: 13

Hi,

same thing here. I put the property dynamic-insert="true" on class level (like stephan), but it remains with an error. In my case, the database has a default value:

Code:

+--------------+------------------+------+-----+---------------------+----------------+
| Field        | Type             | Null | Key | Default             | Extra          |
+--------------+------------------+------+-----+---------------------+----------------+
...
| P_RS_ID      | int(12) unsigned | NO   | MUL | 1                   |                |

The P_RS_ID column is a foreign key. Do I have to modify the other table, too? As far as I understand, dynamic-insert only inserts properties, which are not null, right?

EDIT: Problem "solved". If you set the property not-null to "false", it works as intended. But I see this as a workaround, since the column *is* not-null=true.

Top

stephaneeybert

Post subject: Re: To "not null" or not to "not null" :-)

Posted: Mon May 17, 2010 11:36 am

Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246

@ TRex2003

You mean, you solved your issue ?

Could you be more specific on your "Problem "solved". If you set the property not-null to "false", it works as intended." ?

Thanks.

Top

stephaneeybert

Post subject: Re: To "not null" or not to "not null" :-)

Posted: Mon May 17, 2010 5:50 pm

Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246

Here is the error I get:

Code:

Running com.thalasoft.learnintouch.core.dao.PreferenceDaoTest
Hibernate: 
    insert 
    into
        preference
        (version, name, value) 
    values
        (?, ?, ?)
23:44:36,897 DEBUG IntegerType:133 - binding '0' to parameter: 1
23:44:36,897 DEBUG StringType:133 - binding 'A_UNIQUE_NAME' to parameter: 2
23:44:36,911 DEBUG TextType:133 - binding 'VALUE' to parameter: 3
23:44:36,923  WARN JDBCExceptionReporter:77 - SQL Error: 1364, SQLState: HY000
23:44:36,923 ERROR JDBCExceptionReporter:78 - Field 'is_ml_text' doesn't have a default value
Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 0.065 sec <<< FAILURE!

So I opened a command line MySql client and ran the following statement:

mysql> insert into preference (version, name, value) values ('0', 'my name', '3');
Query OK, 1 row affected, 1 warning (0.00 sec)

And it went just fine.

Why is it, that when run from Hibernate, the same sql statement gives an error ?

Top

TRex2003

Post subject: Re: To "not null" or not to "not null" :-)

Posted: Tue May 18, 2010 7:38 am

Newbie

Joined: Thu Sep 03, 2009 12:37 pm
Posts: 13

You need a default value in your database column. I'm not the first who writes that in that thread...

Top

stephaneeybert

Post subject: Re: To "not null" or not to "not null" :-)

Posted: Tue May 18, 2010 9:51 am

Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246

Thanks Trex, I just modified all the tables of the schema and added a default attribute. It now works like a charm !

Top

stephaneeybert

Post subject: Re: To "not null" or not to "not null" :-)

Posted: Fri Jun 25, 2010 5:13 pm

Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246

Now I'm having yet another issue, still with the default value attribute in MySql, this time having to do with the date columns.

Indeed I need to use the following Hibernate method
Restrictions.isNotNull("validUntil")
which works fine if the MySql column can have a NULL value, that is, if it is a
valid_until datetime

If instead the MySql column is a
valid_until datetime not null default '0000-00-00 00:00:00'
then the above Hibernate method does not match, a normal behavior from Hibernate I would say.

So, this leads me to think that I should not have the attribute
not null default '0000-00-00 00:00:00'
for the date columns.

I'm ready to change all the date columns of my schema for that, and remove all these not null default... attributes.

But is it the right way of handling the dates with Hibernate and MySql ?

Has any of you some tip on that front ?

I'm asking this, because I thought that it was better to always have a
not null attribute on the MySql columns, although I didn't know the rationale for that...

Top

stephaneeybert

Post subject: Re: To "not null" or not to "not null" :-)

Posted: Sun Jul 04, 2010 2:02 pm

Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246

Really no one has had this question about MySql dates and Hibernate ?

Top

stephaneeybert

Post subject: Re: To "not null" or not to "not null" :-)

Posted: Sat Jul 10, 2010 10:53 am

Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246

Has anyone a hint on how to handle these date columns with MySql on Hibernate ?

Top

stephaneeybert

Post subject: Re: To "not null" or not to "not null" :-)

Posted: Thu Jul 22, 2010 4:03 am

Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246

Okay, I changed the schema definition and now have date properties nullable.

I can then use the methos isNotNull.

All non required columns shall have a nullable column.

Top

Page 1 of 1

[ 12 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum