Skip to main content

SQL, Booleans, JPA, and Hibernate

For a long time, SQL and Booleans have not gotten along.  Standards for SQL never really addressed the need for boolean data -- it was assumed that some other data type could easily just step in and address this need.  The result was a lot of different data models for boolean values.  Here are some examples.

  • TRUE or FALSE
  • T or F
  • Y or N
  • 1 or 0
  • <any value> vs NULL
The internet shows the debate has gone on, even as SQL standards have changed.  Coming from a professional background with Oracle, I struggled with this across my teams because everyone had a different opinion, which led to a lot of time wasted due to debate.

This said, I appreciate working with native queries in hibernate's JPA implementation against MySQL.  MySQL supports a BIT data type I recently discussed.  When we represent data in MySQL with BIT and restrict the length to just 1 (ie 1 bit), Hibernate JPA magically knows to query and return this data as a Boolean in the data returned by getResultList().  This kind of magic helps to solve the problem easily and gets rid of lots of debate.
Labels:

Comments

Post a Comment

Popular posts from this blog

Spring Security - Authority vs Role

I have spent a lot of time recently trying to understand the difference between Authority and Role in Spring Security.  This is a brief review of what I found. When creating a UserDetailsService or overriding configure(AuthenticationManagerBuilder auth) in the security config class that extends WebSecurityConfigurerAdapter, I basically get complete control over what I populate inside of the UserDetails that is used/returned.  This is important because the UserDetails interface really only cares about how to return one thing: Collection<? extends GrantedAuthority> getAuthorities(); A GrantedAuthority just seems like a glorified String wrapper that names some thing.  The question is... what is that thing? This is where the subtle difference between Authority and Role comes into play. I think that Role is an older thought/construct that automatically gets plugged into Authority if we just create a user with a Role.  But completely forget about the code a...
Post a Comment
Read more

JHipster, Liquibase, MySQL, and initializing data, including booleans!

When generating a data model from JHipster JDL, we will often declare entities with Boolean fields.  I have so far abandoned H2 as a database because of liquibase issues, and both my dev and production databases will be MySQL.  This is relevant to the Boolean field desire there is a long history in software development of how to store Boolean data types in a SQL database whose standards classically do not support Boolean. In the current JHipster/Liquibase incarnation, tables in MySQL are generated for us, which is really nice.  The Boolean data types are stored as BIT  (1).  This is not a problem so far -- most developers seem to agree now that as a best practice, we should store values in databases as false = 0 and true = 1, and a BIT(1) is a great, simple way to do that. An issue arises when we try to use liquibase to set/update our database to the desired starting state.  For my project, I've chosen gradle instead of maven as a build tool, and gradle...
Post a Comment
Read more