Hibernate JPA, MySQL and TinyInt(1) for Boolean instead of bit or char

Question

Here is my JPA2 / Hibernate definition:

Code:
@Column(nullable = false)
private boolean enabled;

In MySql this column is resolved to a bit(1) datatype - which does not work for me. For legacy issues I need to map the boolean to a tinyint not to a bit. But I do not see a possibility to change the default datatype. Is there any?

Solution 1

Try the NumericBooleanType. For some reason this doesn't have a declared short type name so you'd have to use:

@Column(nullable = false)
@Type(type = "org.hibernate.type.NumericBooleanType")
private boolean enabled;

This does map to an INTEGER type but it will probably work fine with a TINYINT.

UPDATE: org.hibernate.type.NumericBooleanType Does not work with TINYINT in some RDBMS. Switch the database column type to INTEGER. Or use a different Java @Type value, or columnDefinition, as appropriate.

In this example, Dude's answer of @Column(nullable = false, columnDefinition = "TINYINT(1)") would work without any database changes.

Solution 2

@Type annotation is an Hibernate annotation.

In full JPA2 (with Hibernate 3.6+), the way to map a Boolean field to a TINYINT(1) SQL type instead of BIT(1), is to use the columnDefinition attribute.

@Column(nullable = false, columnDefinition = "TINYINT(1)")
private boolean enabled;

nb: length attribute seems to have no effect in this case, then we use (1) syntax.

---

With Hibernate 4.0+, this kind of syntax can cause an runtime error like this :

Wrong column type Found: bit, expected: TINYINT(1)

It seems that in this case, your only way is to use tinyInt1isBit=false in the MySQL datasource connection string like this :

jdbc:mysql://server_host:3306/database?tinyInt1isBit=false

By the way, you can now use the length attribute like this :

@Column(nullable = false, columnDefinition = "TINYINT", length = 1)
private boolean enabled;

Solution 3

I'm using JPA with Spring Data/Hibernate 5.0 on a MySQL database.

In my Entity object, I put the following:

@Column(name = "column_name", columnDefinition = "BOOLEAN")
private Boolean variableName;

My dev environment has hibernate auto-ddl set to update, so when I deployed to dev, it created the table with column_name of type tinyint(1).

My code that uses this column considers null as false, so I'm not worried about nulls, if you are, you could make it a primitive boolean or add ", nullable = false" to the Column annotation.

This solution is fully JPA (doesn't use hibernate Type annotation) and requires no change to the connection string.

Solution 4

I had this error:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory' defined in ServletContext resource [/WEB-INF/config/context-config.xml]: Invocation of init method failed; nested exception is org.hibernate.MappingException: Could not determine type for: org.hibernate.type.NumericBooleanType, at table: bookingItem, for columns: [org.hibernate.mapping.Column(enabled)]

And this worked for me:

@Column(nullable = false, columnDefinition = "TINYINT(1)")
private boolean enabled;

Solution 5

When using Microsoft sql and some versions of mysql use the following:

@Column(name = "eanbled", columnDefinition = "bit default 0", nullable = false)
private boolean enabled;

For me, tinybit, boolean, and other such definitions failed.

View more solutions