I read through this post...
Is there a SQL mode for MySQL allowing "WHERE x = NULL" (meaning "WHERE x IS NULL")?
In hibernate, NamedQuery is constantly used as the goto in our company and criteria api is a big one-off. They are just easy as far as
select c from Customer c where c.firstName=:firstName and c.middleName=:middleName and c.lastName=:lastName
Of course, then we ran into middleName being null and things broke. Then we ran into firstName being null which then broke. All over the schema, we are going to have situations like these constantly soooo, we are looking for a way to interpret
c.middleName = null to c.middleName is null when it is null.
Is there a hibernate setting for this so it just does it correctly in mysql and postgres(the best ideal situation) OR that paramater in the above post for postgres. Where is that and how can I set that up? or is it per query(ugh)?
We are in the process of switching from MySQL to postgres since MySQL's method of dealing with uniqueConstraints on null columns is very non-ideal for us compared to postgres (in that MySQL allows John null Smith twice where postgres does not).
thanks, Dean
In PostgreSQL the IS NOT DISTINCT FROM operator is the null-safe equal predicate. However, similarly to mysql's <=> operator, this predicate is an extension to the SQL standard.
I'm not aware of hibernate having any null-safe equal operators, so I guess the only way to handle such queries is through the native query feature. Obviously, such queries are specific to the underlying database used, so not really ideal.